Debian Codegraph @

mysql-test/suite/period/r/innodb_debug.result

raw
1 #
2 # MDEV-37199 UNIQUE KEY USING HASH accepting duplicate records
3 #
4 ## INSERT
5 create table t1 (
6 id int, s date, e date,
7 period for p(s,e),
8 unique(id, p without overlaps)
9 ) engine=innodb;
10 start transaction;
11 select * from t1;
12 id s e
13 connect con1,localhost,root;
14 insert t1 values(10, date'2010-10-10', date'2010-11-11');
15 delete from t1;
16 set innodb_snapshot_isolation=0;
17 set transaction isolation level read committed;
18 set debug_sync="innodb_row_ins_step_enter SIGNAL checked_duplicate WAIT_FOR do_insert";
19 insert t1 values(10, date'2010-09-09', date'2010-11-10');
20 connect con2,localhost,root;
21 set debug_sync="now WAIT_FOR checked_duplicate";
22 set innodb_snapshot_isolation=0;
23 set transaction isolation level read committed;
24 insert t1 values(10, date'2010-10-10', date'2010-11-12');
25 set debug_sync="now SIGNAL do_insert";
26 connection con1;
27 ERROR 23000: Duplicate entry '10-2010-11-10-2010-09-09' for key 'id'
28 connection default;
29 select * from t1;
30 id s e
31 commit;
32 select * from t1;
33 id s e
34 10 2010-10-10 2010-11-12
35 disconnect con1;
36 disconnect con2;
37 set debug_sync='RESET';
38 drop table t1;
39 ## UPDATE
40 create table t1 (
41 id int, s date, e date,
42 period for p(s,e),
43 unique(id, p without overlaps)
44 ) engine=innodb;
45 start transaction;
46 select * from t1;
47 id s e
48 connect con1,localhost,root;
49 insert t1 values(10, date'2010-10-10', date'2010-11-11');
50 delete from t1;
51 insert t1 values(10, date'2010-09-09', date'2010-09-10');
52 insert t1 values(10, date'2010-12-10', date'2010-12-12');
53 set innodb_snapshot_isolation=0;
54 set transaction isolation level read committed;
55 set debug_sync="innodb_row_update_for_mysql_begin SIGNAL checked_duplicate WAIT_FOR do_insert";
56 update t1 set e=e + interval 2 month where s=date'2010-09-09';
57 connect con2,localhost,root;
58 set debug_sync="now WAIT_FOR checked_duplicate";
59 set innodb_snapshot_isolation=0;
60 set transaction isolation level read committed;
61 update t1 set s=date'2010-10-10' where e=date'2010-12-12';
62 set debug_sync="now SIGNAL do_insert";
63 connection con1;
64 ERROR 23000: Duplicate entry '10-2010-11-10-2010-09-09' for key 'id'
65 connection default;
66 select * from t1;
67 id s e
68 commit;
69 select * from t1;
70 id s e
71 10 2010-09-09 2010-09-10
72 10 2010-10-10 2010-12-12
73 disconnect con1;
74 disconnect con2;
75 set debug_sync='RESET';
76 drop table t1;
77 ## INSERT IGNORE
78 create table t1 (
79 id int, s date, e date,
80 period for p(s,e),
81 unique(id, p without overlaps)
82 ) engine=innodb;
83 start transaction;
84 select * from t1;
85 id s e
86 connect con1,localhost,root;
87 insert t1 values(10, date'2010-10-10', date'2010-11-11');
88 delete from t1;
89 set innodb_snapshot_isolation=0;
90 set transaction isolation level read committed;
91 set debug_sync="innodb_row_ins_step_enter SIGNAL checked_duplicate WAIT_FOR do_insert";
92 insert ignore t1 values(10, date'2010-09-09', date'2010-11-10'),(11, date'2010-09-09', date'2010-11-10');
93 connect con2,localhost,root;
94 set debug_sync="now WAIT_FOR checked_duplicate";
95 set innodb_snapshot_isolation=0;
96 set transaction isolation level read committed;
97 insert t1 values(10, date'2010-10-10', date'2010-11-12');
98 set debug_sync="now SIGNAL do_insert";
99 connection con1;
100 Warnings:
101 Warning 1062 Duplicate entry '10-2010-11-10-2010-09-09' for key 'id'
102 connection default;
103 select * from t1;
104 id s e
105 commit;
106 select * from t1;
107 id s e
108 10 2010-10-10 2010-11-12
109 11 2010-09-09 2010-11-10
110 disconnect con1;
111 disconnect con2;
112 set debug_sync='RESET';
113 drop table t1;
114 ## UPDATE IGNORE
115 create table t1 (
116 id int, s date, e date,
117 period for p(s,e),
118 unique(id, p without overlaps)
119 ) engine=innodb;
120 start transaction;
121 select * from t1;
122 id s e
123 connect con1,localhost,root;
124 insert t1 values(10, date'2010-10-10', date'2010-11-11');
125 delete from t1;
126 insert t1 values(10, date'2010-09-09', date'2010-09-10');
127 insert t1 values(10, date'2010-12-10', date'2010-12-12');
128 set innodb_snapshot_isolation=0;
129 set transaction isolation level read committed;
130 set debug_sync="innodb_row_update_for_mysql_begin SIGNAL checked_duplicate WAIT_FOR do_insert";
131 update ignore t1 set e=e + interval 2 month where s=date'2010-09-09';
132 connect con2,localhost,root;
133 set debug_sync="now WAIT_FOR checked_duplicate";
134 set innodb_snapshot_isolation=0;
135 set transaction isolation level read committed;
136 update t1 set s=date'2010-10-10' where e=date'2010-12-12';
137 set debug_sync="now SIGNAL do_insert";
138 connection con1;
139 ERROR 42000: UPDATE IGNORE in READ COMMITTED isolation mode of a table with a UNIQUE constraint WITHOUT OVERLAPS is not currently supported
140 connection default;
141 select * from t1;
142 id s e
143 commit;
144 select * from t1;
145 id s e
146 10 2010-09-09 2010-09-10
147 10 2010-10-10 2010-12-12
148 disconnect con1;
149 disconnect con2;
150 set debug_sync='RESET';
151 drop table t1;
152 ## UPDATE modifying PK
153 create table t1 (
154 id int, s date, e date,
155 period for p(s,e),
156 primary key (id, p without overlaps)
157 ) engine=innodb;
158 start transaction;
159 select * from t1;
160 id s e
161 connect con1,localhost,root;
162 insert t1 values(10, date'2010-10-10', date'2010-11-11');
163 delete from t1;
164 insert t1 values(10, date'2010-09-09', date'2010-09-10');
165 insert t1 values(10, date'2010-12-10', date'2010-12-12');
166 set innodb_snapshot_isolation=0;
167 set transaction isolation level read committed;
168 set debug_sync="innodb_row_update_for_mysql_begin SIGNAL checked_duplicate WAIT_FOR do_insert";
169 update t1 set e=e + interval 2 month where s=date'2010-09-09';
170 connect con2,localhost,root;
171 set debug_sync="now WAIT_FOR checked_duplicate";
172 set innodb_snapshot_isolation=0;
173 set transaction isolation level read committed;
174 update t1 set s=date'2010-10-10' where e=date'2010-12-12';
175 set debug_sync="now SIGNAL do_insert";
176 connection con1;
177 ERROR 23000: Duplicate entry '10-2010-11-10-2010-09-09' for key 'PRIMARY'
178 connection default;
179 select * from t1;
180 id s e
181 commit;
182 select * from t1;
183 id s e
184 10 2010-09-09 2010-09-10
185 10 2010-10-10 2010-12-12
186 disconnect con1;
187 disconnect con2;
188 set debug_sync='RESET';
189 drop table t1;
190 ## UPDATE IGNORE modifying PK
191 create table t1 (
192 id int, s date, e date,
193 period for p(s,e),
194 primary key (id, p without overlaps)
195 ) engine=innodb;
196 start transaction;
197 select * from t1;
198 id s e
199 connect con1,localhost,root;
200 insert t1 values(10, date'2010-10-10', date'2010-11-11');
201 delete from t1;
202 insert t1 values(10, date'2010-09-09', date'2010-09-10');
203 insert t1 values(10, date'2010-12-10', date'2010-12-12');
204 set innodb_snapshot_isolation=0;
205 set transaction isolation level read committed;
206 set debug_sync="innodb_row_update_for_mysql_begin SIGNAL checked_duplicate WAIT_FOR do_insert";
207 update ignore t1 set e=e + interval 2 month where s=date'2010-09-09';
208 connect con2,localhost,root;
209 set debug_sync="now WAIT_FOR checked_duplicate";
210 set innodb_snapshot_isolation=0;
211 set transaction isolation level read committed;
212 update t1 set s=date'2010-10-10' where e=date'2010-12-12';
213 set debug_sync="now SIGNAL do_insert";
214 connection con1;
215 ERROR 42000: UPDATE IGNORE in READ COMMITTED isolation mode of a table with a UNIQUE constraint WITHOUT OVERLAPS is not currently supported
216 connection default;
217 select * from t1;
218 id s e
219 commit;
220 select * from t1;
221 id s e
222 10 2010-09-09 2010-09-10
223 10 2010-10-10 2010-12-12
224 disconnect con1;
225 disconnect con2;
226 set debug_sync='RESET';
227 drop table t1;
228 # End of 10.6 tests