Debian Codegraph @

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

raw
1 set global transaction isolation level repeatable read;
2 CREATE TABLE t1(
3 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
4 k INT,
5 c CHAR(1),
6 UNIQUE KEY(k)) ENGINE=InnoDB;
7 SHOW CREATE TABLE t1;
8 Table Create Table
9 t1 CREATE TABLE `t1` (
10 `id` int(11) NOT NULL AUTO_INCREMENT,
11 `k` int(11) DEFAULT NULL,
12 `c` char(1) DEFAULT NULL,
13 PRIMARY KEY (`id`),
14 UNIQUE KEY `k` (`k`)
15 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
16 #
17 # Sequential execution
18 #
19 INSERT INTO t1(k) VALUES (1), (2), (3) ON DUPLICATE KEY UPDATE c='1';
20 affected rows: 3
21 info: Records: 3 Duplicates: 0 Warnings: 0
22 #
23 # 1 duplicate
24 #
25 INSERT INTO t1(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2';
26 affected rows: 4
27 info: Records: 3 Duplicates: 1 Warnings: 0
28 #
29 # 5 rows, consecutive auto_inc values
30 #
31 SELECT * FROM t1 order by k;
32 id k c
33 1 1 NULL
34 2 2 2
35 3 3 NULL
36 4 4 NULL
37 5 5 NULL
38 affected rows: 5
39 DROP TABLE t1;
40 affected rows: 0
41 CREATE TABLE t1(
42 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
43 k INT,
44 c CHAR(1),
45 UNIQUE KEY(k)) ENGINE=InnoDB;
46 affected rows: 0
47 #
48 # Sequential execution 2
49 #
50 INSERT INTO t1(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2';
51 affected rows: 3
52 info: Records: 3 Duplicates: 0 Warnings: 0
53 #
54 # 1 duplicate
55 #
56 INSERT INTO t1(k) VALUES (1), (2), (3) ON DUPLICATE KEY UPDATE c='1';
57 affected rows: 4
58 info: Records: 3 Duplicates: 1 Warnings: 0
59 #
60 # 5 rows, consecutive auto_inc values
61 #
62 SELECT * FROM t1 order by k;
63 id k c
64 4 1 NULL
65 1 2 1
66 5 3 NULL
67 2 4 NULL
68 3 5 NULL
69 affected rows: 5
70 DROP TABLE t1;
71 affected rows: 0
72 CREATE TABLE t1(
73 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
74 k INT,
75 c CHAR(1),
76 UNIQUE KEY(k)) ENGINE=InnoDB;
77 affected rows: 0
78 #
79 # Parallel execution
80 #
81 connect con2, localhost, root;
82 SET DEBUG_SYNC='now WAIT_FOR write_row_done';
83 connect con1, localhost, root;
84 SET DEBUG_SYNC='ha_write_row_end SIGNAL write_row_done WAIT_FOR continue';
85 affected rows: 0
86 INSERT INTO t1(k) VALUES (1), (2), (3) ON DUPLICATE KEY UPDATE c='1';
87 connection con2;
88 affected rows: 0
89 SET DEBUG_SYNC='execute_command_after_close_tables SIGNAL continue';
90 affected rows: 0
91 INSERT INTO t1(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2';
92 affected rows: 3
93 info: Records: 3 Duplicates: 0 Warnings: 0
94 connection con1;
95 #
96 # 2 duplicates
97 #
98 affected rows: 4
99 info: Records: 3 Duplicates: 1 Warnings: 0
100 connection default;
101 #
102 # 3 rows
103 #
104 SELECT * FROM t1 order by k;
105 id k c
106 1 1 NULL
107 4 2 1
108 2 3 NULL
109 5 4 NULL
110 6 5 NULL
111 affected rows: 5
112 INSERT INTO t1(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2';
113 affected rows: 6
114 info: Records: 3 Duplicates: 3 Warnings: 0
115 SELECT * FROM t1 order by k;
116 id k c
117 1 1 NULL
118 4 2 2
119 2 3 NULL
120 5 4 2
121 6 5 2
122 affected rows: 5
123 disconnect con1;
124 disconnect con2;
125 connection default;
126 DROP TABLE t1;
127 #
128 # Parallel test with read_committed
129 #
130 set global transaction isolation level read committed;
131 drop table if exists t1;
132 CREATE TABLE t1(
133 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
134 k INT,
135 c CHAR(1),
136 UNIQUE KEY(k)) ENGINE=InnoDB;
137 connect con1, localhost, root;
138 SET DEBUG_SYNC='ha_write_row_end SIGNAL continue2 WAIT_FOR continue1';
139 affected rows: 0
140 INSERT INTO t1(k) VALUES (1), (2), (3) ON DUPLICATE KEY UPDATE c='1';
141 connect con2, localhost, root;
142 SET DEBUG_SYNC='ha_write_row_start WAIT_FOR continue2';
143 affected rows: 0
144 SET DEBUG_SYNC='after_mysql_insert SIGNAL continue1';
145 affected rows: 0
146 INSERT INTO t1(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2';
147 affected rows: 3
148 info: Records: 3 Duplicates: 0 Warnings: 0
149 disconnect con2;
150 connection con1;
151 affected rows: 4
152 info: Records: 3 Duplicates: 1 Warnings: 0
153 SET DEBUG_SYNC='RESET';
154 #
155 # 5 rows, gap in autoinc values
156 #
157 SELECT * FROM t1 ORDER BY k;
158 id k c
159 1 1 NULL
160 4 2 1
161 2 3 NULL
162 5 4 NULL
163 6 5 NULL
164 disconnect con1;
165 connection default;
166 DROP TABLE t1;
167 set global transaction isolation level repeatable read;