Debian Codegraph @

mysql-test/suite/innodb/r/autoinc_debug.result

raw
1 CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY)ENGINE=INNODB;
2 # SETTING auto_increment_increment IN CONNECTION DEFAULT
3 SET AUTO_INCREMENT_INCREMENT = 1;
4 # MDEV-24348 InnoDB shutdown hang with innodb_flush_sync=0
5 SET GLOBAL innodb_flush_sync=OFF;
6 # For the server to hang, we must have pages for temporary tables
7 # (and the bug depended on MDEV-12227 not being fixed).
8 CREATE TEMPORARY TABLE t (id SERIAL) ENGINE=InnoDB;
9 SET debug_dbug= '+d,ib_log_flush_ahead';
10 INSERT INTO t1 VALUES(NULL);
11 SELECT * FROM t1;
12 id
13 1
14 SHOW CREATE TABLE t1;
15 Table Create Table
16 t1 CREATE TABLE `t1` (
17 `id` int(11) NOT NULL AUTO_INCREMENT,
18 PRIMARY KEY (`id`)
19 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
20 # restart
21 # SETTING auto_increment_increment IN CONNECTION1
22 SET AUTO_INCREMENT_INCREMENT = 2;
23 SET DEBUG_SYNC= 'ib_after_row_insert SIGNAL opened WAIT_FOR flushed1';
24 INSERT INTO t1 VALUES(NULL);
25 connect con1, localhost, root,,;
26 SET AUTO_INCREMENT_INCREMENT = 2;
27 SET DEBUG_SYNC= 'now WAIT_FOR opened';
28 SET DEBUG_SYNC= 'ib_after_row_insert_step SIGNAL flushed1 WAIT_FOR opened1';
29 insert into t1 values(NULL);
30 connection default;
31 SELECT * FROM t1;
32 id
33 1
34 3
35 SHOW CREATE TABLE t1;
36 Table Create Table
37 t1 CREATE TABLE `t1` (
38 `id` int(11) NOT NULL AUTO_INCREMENT,
39 PRIMARY KEY (`id`)
40 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
41 SET DEBUG_SYNC= 'now SIGNAL opened1';
42 connection con1;
43 SELECT * FROM t1;
44 id
45 1
46 3
47 5
48 SHOW CREATE TABLE t1;
49 Table Create Table
50 t1 CREATE TABLE `t1` (
51 `id` int(11) NOT NULL AUTO_INCREMENT,
52 PRIMARY KEY (`id`)
53 ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
54 connection default;
55 disconnect con1;
56 DROP TABLE t1;
57 CREATE TABLE t1(id INT AUTO_INCREMENT PRIMARY KEY)ENGINE=INNODB;
58 # SETTING auto_increment_increment IN CONNECTION DEFAULT
59 SET AUTO_INCREMENT_INCREMENT = 1;
60 INSERT INTO t1 VALUES(NULL);
61 SELECT * FROM t1;
62 id
63 1
64 SHOW CREATE TABLE t1;
65 Table Create Table
66 t1 CREATE TABLE `t1` (
67 `id` int(11) NOT NULL AUTO_INCREMENT,
68 PRIMARY KEY (`id`)
69 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
70 SET DEBUG_SYNC = 'now SIGNAL flushed';
71 connect con1, localhost, root,,;
72 # SETTING auto_increment_increment in connection1
73 SET AUTO_INCREMENT_INCREMENT = 2;
74 SET DEBUG_SYNC= 'now WAIT_FOR flushed';
75 SET DEBUG_SYNC= 'ib_after_row_insert SIGNAL opened WAIT_FOR flushed1';
76 INSERT INTO t1 values(NULL);
77 connection default;
78 SET DEBUG_SYNC= 'now WAIT_FOR opened';
79 SET DEBUG_SYNC= 'ib_after_row_insert_step SIGNAL flushed1 WAIT_FOR opened1';
80 INSERT INTO t1 VALUES(NULL);
81 connection con1;
82 SELECT * FROM t1;
83 id
84 1
85 3
86 SHOW CREATE TABLE t1;
87 Table Create Table
88 t1 CREATE TABLE `t1` (
89 `id` int(11) NOT NULL AUTO_INCREMENT,
90 PRIMARY KEY (`id`)
91 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
92 SET DEBUG_SYNC= 'now SIGNAL opened1';
93 disconnect con1;
94 connection default;
95 SELECT * FROM t1;
96 id
97 1
98 3
99 5
100 SHOW CREATE TABLE t1;
101 Table Create Table
102 t1 CREATE TABLE `t1` (
103 `id` int(11) NOT NULL AUTO_INCREMENT,
104 PRIMARY KEY (`id`)
105 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
106 DROP TABLE t1;
107 SET DEBUG_SYNC='RESET';
108 #
109 # MDEV-33593: Auto increment deadlock error causes ASSERT in subsequent save point
110 #
111 CREATE TABLE t1(col1 INT PRIMARY KEY AUTO_INCREMENT, col2 INT) ENGINE=InnoDB;
112 CREATE TABLE t2(col1 INT PRIMARY KEY) ENGINE=InnoDB;
113 INSERT INTO t1(col2) values(100);
114 connect con1, localhost, root,,;
115 START TRANSACTION;
116 # T1: Acquiring Row X lock on table t2
117 INSERT INTO t2 values(100);
118 connect con2, localhost, root,,;
119 START TRANSACTION;
120 # T2: Wait for (T1) row lock on t2 after acquiring GAP Lock on t1
121 UPDATE t1 SET col2 = 20 where col1 = 10;
122 SET DEBUG_SYNC='lock_wait_before_suspend SIGNAL t2_waiting';
123 INSERT INTO t2 values(100);
124 connection default;
125 SET DEBUG_SYNC='now WAIT_FOR t2_waiting';
126 # T3: Wait for (T2) II row Lock on t1 after acquiring Auto Increment Lock on t1
127 SET DEBUG_SYNC='lock_wait_before_suspend SIGNAL t3_waiting';
128 INSERT INTO t1(col2) SELECT col2 from t1;
129 connection con1;
130 SAVEPOINT s1;
131 SET DEBUG_SYNC='now WAIT_FOR t3_waiting';
132 # T1: Wait for (T3) auto increment lock on t1 causing T1 -> T3 -> T2 -> T1 deadlock
133 SET debug_dbug = '+d,innodb_deadlock_victim_self';
134 INSERT INTO t1(col2) VALUES(200);
135 ERROR HY000: Failed to read auto-increment value from storage engine
136 # The transaction should have been rolled back
137 SELECT * FROM t1;
138 col1 col2
139 1 100
140 SELECT * FROM t2;
141 col1
142 # Release the previous savepoint using the same name
143 SAVEPOINT s1;
144 COMMIT;
145 connection con2;
146 COMMIT;
147 connection default;
148 COMMIT;
149 disconnect con1;
150 disconnect con2;
151 # Cleanup
152 SELECT * FROM t1;
153 col1 col2
154 1 100
155 2 100
156 DROP TABLE t1;
157 SELECT * FROM t2;
158 col1
159 100
160 DROP TABLE t2;
161 SET DEBUG_SYNC='RESET';
162 #
163 # End of 10.5 tests
164 #