Debian Codegraph @

mysql-test/suite/innodb/t/auto_increment_dup.test

raw
1 ##########################################################################
2 # LP bug #1035225 / MySQL bug #66301: INSERT ... ON DUPLICATE KEY UPDATE +
3 # innodb_autoinc_lock_mode=1 is broken
4 ##########################################################################
5
6 --source include/have_innodb.inc
7 --source include/have_debug.inc
8 --source include/have_debug_sync.inc
9 --source include/innodb_binlog.inc
10
11 set global transaction isolation level repeatable read;
12
13 CREATE TABLE t1(
14 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
15 k INT,
16 c CHAR(1),
17 UNIQUE KEY(k)) ENGINE=InnoDB;
18
19 SHOW CREATE TABLE t1;
20
21 --enable_info
22
23 --echo #
24 --echo # Sequential execution
25 --echo #
26
27 INSERT INTO t1(k) VALUES (1), (2), (3) ON DUPLICATE KEY UPDATE c='1';
28
29 --echo #
30 --echo # 1 duplicate
31 --echo #
32 INSERT INTO t1(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2';
33 --echo #
34 --echo # 5 rows, consecutive auto_inc values
35 --echo #
36
37 SELECT * FROM t1 order by k;
38
39 DROP TABLE t1;
40
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
47 --echo #
48 --echo # Sequential execution 2
49 --echo #
50
51 INSERT INTO t1(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2';
52
53 --echo #
54 --echo # 1 duplicate
55 --echo #
56 INSERT INTO t1(k) VALUES (1), (2), (3) ON DUPLICATE KEY UPDATE c='1';
57 --echo #
58 --echo # 5 rows, consecutive auto_inc values
59 --echo #
60
61 SELECT * FROM t1 order by k;
62
63 DROP TABLE t1;
64
65 CREATE TABLE t1(
66 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
67 k INT,
68 c CHAR(1),
69 UNIQUE KEY(k)) ENGINE=InnoDB;
70
71 --echo #
72 --echo # Parallel execution
73 --echo #
74
75 --connect(con2, localhost, root)
76 --send SET DEBUG_SYNC='now WAIT_FOR write_row_done'
77
78 --connect(con1, localhost, root)
79 SET DEBUG_SYNC='ha_write_row_end SIGNAL write_row_done WAIT_FOR continue';
80 --send INSERT INTO t1(k) VALUES (1), (2), (3) ON DUPLICATE KEY UPDATE c='1'
81 --connection con2
82 --reap
83
84 SET DEBUG_SYNC='execute_command_after_close_tables SIGNAL continue';
85 INSERT INTO t1(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2';
86
87 --connection con1
88 --echo #
89 --echo # 2 duplicates
90 --echo #
91 --reap
92 --connection default
93 --echo #
94 --echo # 3 rows
95 --echo #
96
97 SELECT * FROM t1 order by k;
98
99 INSERT INTO t1(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2';
100
101 SELECT * FROM t1 order by k;
102
103
104 --disable_info
105
106 --disconnect con1
107 --disconnect con2
108
109 --connection default
110
111 DROP TABLE t1;
112
113 --echo #
114 --echo # Parallel test with read_committed
115 --echo #
116
117 set global transaction isolation level read committed;
118
119 --disable_warnings
120 drop table if exists t1;
121 --enable_warnings
122
123 CREATE TABLE t1(
124 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
125 k INT,
126 c CHAR(1),
127 UNIQUE KEY(k)) ENGINE=InnoDB;
128
129 --enable_info
130
131 --connect(con1, localhost, root)
132 SET DEBUG_SYNC='ha_write_row_end SIGNAL continue2 WAIT_FOR continue1';
133 --send INSERT INTO t1(k) VALUES (1), (2), (3) ON DUPLICATE KEY UPDATE c='1'
134
135 --connect(con2, localhost, root)
136 SET DEBUG_SYNC='ha_write_row_start WAIT_FOR continue2';
137 SET DEBUG_SYNC='after_mysql_insert SIGNAL continue1';
138 INSERT INTO t1(k) VALUES (2), (4), (5) ON DUPLICATE KEY UPDATE c='2';
139 --disconnect con2
140
141 --connection con1
142 --reap
143 --disable_info
144 SET DEBUG_SYNC='RESET';
145
146 --echo #
147 --echo # 5 rows, gap in autoinc values
148 --echo #
149 SELECT * FROM t1 ORDER BY k;
150
151 --disconnect con1
152
153 --connection default
154
155 DROP TABLE t1;
156
157 set global transaction isolation level repeatable read;