Debian Codegraph @

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

raw
1 # Save the initial number of concurrent sessions
2 --source include/count_sessions.inc
3
4 --source include/have_debug.inc
5 --source include/have_debug_sync.inc
6
7 --enable_connect_log
8 --connect (con1, localhost, root,,)
9
10 --DELIMITER //
11
12 CREATE PROCEDURE test.insert_records (IN record_count INT)
13 BEGIN
14 DECLARE counter INT DEFAULT 1;
15
16 WHILE counter <= record_count DO
17 INSERT INTO test.t1 VALUES (0);
18 SET counter = counter + 1;
19 END WHILE;
20 END //
21
22 --DELIMITER ;
23
24 --echo ##############################################################
25 --echo # Bug#36846567 Inplace ALTER TABLE might cause lost rows if concurrent purge
26 --echo ##############################################################
27 --echo
28
29 --connection default
30 CREATE TABLE t1 (pk CHAR(5) PRIMARY KEY);
31 INSERT INTO t1 VALUES ('aaaaa'), ('bbbbb'), ('bbbcc'), ('ccccc'), ('ddddd'), ('eeeee');
32
33 SET GLOBAL INNODB_PURGE_STOP_NOW=ON;
34 DELETE FROM t1 WHERE pk = 'bbbcc';
35
36 --connection con1
37 SET SESSION DEBUG='+d,ddl_buf_add_two';
38 SET DEBUG_SYNC='ddl_bulk_inserter_latches_released SIGNAL latches_released WAIT_FOR go';
39 --echo # Send ALTER TABLE INPLACE which rebuilds table.
40 --send ALTER TABLE t1 ENGINE=InnoDB, ALGORITHM=INPLACE
41
42 --connection default
43 SET DEBUG_SYNC='now WAIT_FOR latches_released';
44 SET GLOBAL innodb_purge_run_now=ON;
45 --source include/wait_innodb_all_purged.inc
46 SET DEBUG_SYNC='now SIGNAL go';
47
48 --connection con1
49 --echo # Reap ALTER TABLE
50 --reap
51 SET SESSION DEBUG='-d,ddl_buf_add_two';
52
53 --echo # Before the fix row 'ddddd' was missing from the table after ALTER.
54 SELECT * FROM t1;
55
56 --echo # Test cleanup
57 --connection default
58 DROP TABLE t1;
59
60 --echo ##############################################################
61 --echo # Test#2 Delete the first record
62 --echo ##############################################################
63 --echo
64
65 --connection default
66 CREATE TABLE t1 (pk CHAR(5) PRIMARY KEY);
67 INSERT INTO t1 VALUES ('aaaaa'), ('bbbbb'), ('bbbcc'), ('ccccc'), ('ddddd'), ('eeeee');
68
69
70 SET GLOBAL INNODB_PURGE_STOP_NOW=ON;
71 DELETE FROM t1 WHERE pk = 'aaaaa';
72
73 --connection con1
74 SET SESSION DEBUG='+d,ddl_buf_add_two';
75 SET DEBUG_SYNC='ddl_bulk_inserter_latches_released SIGNAL latches_released WAIT_FOR go';
76 --echo # Send ALTER TABLE INPLACE which rebuilds table.
77 --send ALTER TABLE t1 ENGINE=InnoDB, ALGORITHM=INPLACE
78
79 --connection default
80 SET DEBUG_SYNC='now WAIT_FOR latches_released';
81 SET GLOBAL innodb_purge_run_now=ON;
82 --source include/wait_innodb_all_purged.inc
83 SET DEBUG_SYNC='now SIGNAL go';
84
85 --connection con1
86 --echo # Reap ALTER TABLE
87 --reap
88 SET SESSION DEBUG='-d,ddl_buf_add_two';
89
90 --echo # Verify intended records are present
91 SELECT * FROM t1;
92
93 --echo # Test cleanup
94 --connection default
95 DROP TABLE t1;
96
97 --echo ##############################################################
98 --echo # Test#3 Delete the second record
99 --echo ##############################################################
100 --echo
101
102 --connection default
103 CREATE TABLE t1 (pk CHAR(5) PRIMARY KEY);
104 INSERT INTO t1 VALUES ('aaaaa'), ('bbbbb'), ('bbbcc'), ('ccccc'), ('ddddd'), ('eeeee');
105
106 SET GLOBAL INNODB_PURGE_STOP_NOW=ON;
107 DELETE FROM t1 WHERE pk = 'bbbbb';
108
109 --connection con1
110 SET SESSION DEBUG='+d,ddl_buf_add_two';
111 SET DEBUG_SYNC='ddl_bulk_inserter_latches_released SIGNAL latches_released WAIT_FOR go';
112 --echo # Send ALTER TABLE INPLACE which rebuilds table.
113 --send ALTER TABLE t1 ENGINE=InnoDB, ALGORITHM=INPLACE
114
115 --connection default
116 SET DEBUG_SYNC='now WAIT_FOR latches_released';
117 SET GLOBAL innodb_purge_run_now=ON;
118 --source include/wait_innodb_all_purged.inc
119 SET DEBUG_SYNC='now SIGNAL go';
120
121 --connection con1
122 --echo # Reap ALTER TABLE
123 --reap
124 SET SESSION DEBUG='-d,ddl_buf_add_two';
125
126 --echo # Verify intended records are present
127 SELECT * FROM t1;
128
129 --echo # Test cleanup
130 --connection default
131 DROP TABLE t1;
132
133 --echo ##############################################################
134 --echo # Test#4 Delete the fourth record
135 --echo ##############################################################
136 --echo
137
138 --connection default
139 CREATE TABLE t1 (pk CHAR(5) PRIMARY KEY);
140 INSERT INTO t1 VALUES ('aaaaa'), ('bbbbb'), ('bbbcc'), ('ccccc'), ('ddddd'), ('eeeee');
141
142 SET GLOBAL INNODB_PURGE_STOP_NOW=ON;
143 DELETE FROM t1 WHERE pk = 'ccccc';
144
145 --connection con1
146 SET SESSION DEBUG='+d,ddl_buf_add_two';
147 SET DEBUG_SYNC='ddl_bulk_inserter_latches_released SIGNAL latches_released WAIT_FOR go';
148 --echo # Send ALTER TABLE INPLACE which rebuilds table.
149 --send ALTER TABLE t1 ENGINE=InnoDB, ALGORITHM=INPLACE
150
151 --connection default
152 SET DEBUG_SYNC='now WAIT_FOR latches_released';
153 SET GLOBAL innodb_purge_run_now=ON;
154 --source include/wait_innodb_all_purged.inc
155 SET DEBUG_SYNC='now SIGNAL go';
156
157 --connection con1
158 --echo # Reap ALTER TABLE
159 --reap
160 SET SESSION DEBUG='-d,ddl_buf_add_two';
161
162 --echo # Verify intended records are present
163 SELECT * FROM t1;
164
165 --echo # Test cleanup
166 --connection default
167 DROP TABLE t1;
168
169 --echo ############################################################
170 --echo # Test#5 Bug#37318367 Delete part of records from two pages
171 --echo # to lose a record - pause in PCursor::move_to_next_block
172 --echo ############################################################
173
174 # Limit the max number of records per page to 3
175 SET GLOBAL innodb_limit_optimistic_insert_debug=3;
176
177 --connection default
178 CREATE TABLE t1 (id int AUTO_INCREMENT NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
179
180 # 8 records are inserted into four pages as following due to
181 # sysvar innodb_limit_optimistic_insert_debug=3 set above :
182 # p1: {1}, p2: {2,3,4}, p3: {5,6,7}, p4: {8}
183 CALL insert_records(8);
184
185 SET GLOBAL innodb_purge_stop_now = ON;
186
187 # Remove records in right half of page p2 and left half of page p3
188 DELETE FROM t1 WHERE id > 2 AND id < 7;
189
190 let $before_count = `SELECT COUNT(*) FROM t1`;
191 --connection con1
192 SET DEBUG_SYNC='pcursor_move_to_next_block_latches_released SIGNAL latches_released WAIT_FOR go EXECUTE 2';
193
194 --echo # Send ALTER TABLE INPLACE to rebuild the index.
195
196 # Trick the PCursor::move_to_next_block to think the latch is congested
197 SET SESSION debug="+d,pcursor_move_to_next_block_release_latches";
198 # Save the cursor position at the end of record#4 and wait for the second
199 # 'go' signal to resume operation.
200 --send ALTER TABLE t1 ENGINE=InnoDB, ALGORITHM=INPLACE
201
202 --connection default
203 # wait for the first hit of the sync point at the end of first page ...
204 SET DEBUG_SYNC='now WAIT_FOR latches_released';
205 # ... which we ignore by immediately resuming the alter table operation
206 SET DEBUG_SYNC='now SIGNAL go';
207 # Now wait for the second hit of the sync point at the end of second page
208 SET DEBUG_SYNC='now WAIT_FOR latches_released';
209 SET GLOBAL innodb_purge_run_now = ON;
210 --source include/wait_innodb_all_purged.inc
211
212 # Resume the alter table operation after page p2 is processed. Since the purge
213 # is completed so remaining records are arranged as following :
214 # p1: {1, 2, 7}, p4: {8}
215 # Without the fix, cursor is positioned to record#8 instead of record#7 thus
216 # latter is skipped.
217 SET DEBUG_SYNC='now SIGNAL go';
218
219 --connection con1
220 --echo # Reap ALTER TABLE.
221 --reap;
222 SET SESSION debug="-d,pcursor_move_to_next_block_release_latches";
223
224 --echo # Ensure all data is present after index rebuild.
225 let $after_count = `SELECT COUNT(*) FROM t1`;
226 if($before_count != $after_count) {
227 --echo # Records, before rebuild=$before_count != after rebuild=$after_count"
228 SELECT id FROM t1;
229 --die "Test failed due to record count mismatch after index rebuild"
230 }
231
232 --echo # Test cleanup.
233 --connection default
234 DROP TABLE t1;
235 SET GLOBAL innodb_limit_optimistic_insert_debug=0;
236
237 --echo #
238 --echo # Cleanup.
239 --connection default
240 DROP TABLE IF EXISTS t1;
241 DROP PROCEDURE test.insert_records;
242 --disconnect con1
243 --disable_connect_log
244 # Wait till all disconnects are completed
245 --source include/wait_until_count_sessions.inc