Debian Codegraph @

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

raw
1 connect con1, localhost, root,,;
2 CREATE PROCEDURE test.insert_records (IN record_count INT)
3 BEGIN
4 DECLARE counter INT DEFAULT 1;
5 WHILE counter <= record_count DO
6 INSERT INTO test.t1 VALUES (0);
7 SET counter = counter + 1;
8 END WHILE;
9 END //
10 ##############################################################
11 # Bug#36846567 Inplace ALTER TABLE might cause lost rows if concurrent purge
12 ##############################################################
13
14 connection default;
15 CREATE TABLE t1 (pk CHAR(5) PRIMARY KEY);
16 INSERT INTO t1 VALUES ('aaaaa'), ('bbbbb'), ('bbbcc'), ('ccccc'), ('ddddd'), ('eeeee');
17 SET GLOBAL INNODB_PURGE_STOP_NOW=ON;
18 DELETE FROM t1 WHERE pk = 'bbbcc';
19 connection con1;
20 SET SESSION DEBUG='+d,ddl_buf_add_two';
21 SET DEBUG_SYNC='ddl_bulk_inserter_latches_released SIGNAL latches_released WAIT_FOR go';
22 # Send ALTER TABLE INPLACE which rebuilds table.
23 ALTER TABLE t1 ENGINE=InnoDB, ALGORITHM=INPLACE;
24 connection default;
25 SET DEBUG_SYNC='now WAIT_FOR latches_released';
26 SET GLOBAL innodb_purge_run_now=ON;
27 SET DEBUG_SYNC='now SIGNAL go';
28 connection con1;
29 # Reap ALTER TABLE
30 SET SESSION DEBUG='-d,ddl_buf_add_two';
31 # Before the fix row 'ddddd' was missing from the table after ALTER.
32 SELECT * FROM t1;
33 pk
34 aaaaa
35 bbbbb
36 ccccc
37 ddddd
38 eeeee
39 # Test cleanup
40 connection default;
41 DROP TABLE t1;
42 ##############################################################
43 # Test#2 Delete the first record
44 ##############################################################
45
46 connection default;
47 CREATE TABLE t1 (pk CHAR(5) PRIMARY KEY);
48 INSERT INTO t1 VALUES ('aaaaa'), ('bbbbb'), ('bbbcc'), ('ccccc'), ('ddddd'), ('eeeee');
49 SET GLOBAL INNODB_PURGE_STOP_NOW=ON;
50 DELETE FROM t1 WHERE pk = 'aaaaa';
51 connection con1;
52 SET SESSION DEBUG='+d,ddl_buf_add_two';
53 SET DEBUG_SYNC='ddl_bulk_inserter_latches_released SIGNAL latches_released WAIT_FOR go';
54 # Send ALTER TABLE INPLACE which rebuilds table.
55 ALTER TABLE t1 ENGINE=InnoDB, ALGORITHM=INPLACE;
56 connection default;
57 SET DEBUG_SYNC='now WAIT_FOR latches_released';
58 SET GLOBAL innodb_purge_run_now=ON;
59 SET DEBUG_SYNC='now SIGNAL go';
60 connection con1;
61 # Reap ALTER TABLE
62 SET SESSION DEBUG='-d,ddl_buf_add_two';
63 # Verify intended records are present
64 SELECT * FROM t1;
65 pk
66 bbbbb
67 bbbcc
68 ccccc
69 ddddd
70 eeeee
71 # Test cleanup
72 connection default;
73 DROP TABLE t1;
74 ##############################################################
75 # Test#3 Delete the second record
76 ##############################################################
77
78 connection default;
79 CREATE TABLE t1 (pk CHAR(5) PRIMARY KEY);
80 INSERT INTO t1 VALUES ('aaaaa'), ('bbbbb'), ('bbbcc'), ('ccccc'), ('ddddd'), ('eeeee');
81 SET GLOBAL INNODB_PURGE_STOP_NOW=ON;
82 DELETE FROM t1 WHERE pk = 'bbbbb';
83 connection con1;
84 SET SESSION DEBUG='+d,ddl_buf_add_two';
85 SET DEBUG_SYNC='ddl_bulk_inserter_latches_released SIGNAL latches_released WAIT_FOR go';
86 # Send ALTER TABLE INPLACE which rebuilds table.
87 ALTER TABLE t1 ENGINE=InnoDB, ALGORITHM=INPLACE;
88 connection default;
89 SET DEBUG_SYNC='now WAIT_FOR latches_released';
90 SET GLOBAL innodb_purge_run_now=ON;
91 SET DEBUG_SYNC='now SIGNAL go';
92 connection con1;
93 # Reap ALTER TABLE
94 SET SESSION DEBUG='-d,ddl_buf_add_two';
95 # Verify intended records are present
96 SELECT * FROM t1;
97 pk
98 aaaaa
99 bbbcc
100 ccccc
101 ddddd
102 eeeee
103 # Test cleanup
104 connection default;
105 DROP TABLE t1;
106 ##############################################################
107 # Test#4 Delete the fourth record
108 ##############################################################
109
110 connection default;
111 CREATE TABLE t1 (pk CHAR(5) PRIMARY KEY);
112 INSERT INTO t1 VALUES ('aaaaa'), ('bbbbb'), ('bbbcc'), ('ccccc'), ('ddddd'), ('eeeee');
113 SET GLOBAL INNODB_PURGE_STOP_NOW=ON;
114 DELETE FROM t1 WHERE pk = 'ccccc';
115 connection con1;
116 SET SESSION DEBUG='+d,ddl_buf_add_two';
117 SET DEBUG_SYNC='ddl_bulk_inserter_latches_released SIGNAL latches_released WAIT_FOR go';
118 # Send ALTER TABLE INPLACE which rebuilds table.
119 ALTER TABLE t1 ENGINE=InnoDB, ALGORITHM=INPLACE;
120 connection default;
121 SET DEBUG_SYNC='now WAIT_FOR latches_released';
122 SET GLOBAL innodb_purge_run_now=ON;
123 SET DEBUG_SYNC='now SIGNAL go';
124 connection con1;
125 # Reap ALTER TABLE
126 SET SESSION DEBUG='-d,ddl_buf_add_two';
127 # Verify intended records are present
128 SELECT * FROM t1;
129 pk
130 aaaaa
131 bbbbb
132 bbbcc
133 ddddd
134 eeeee
135 # Test cleanup
136 connection default;
137 DROP TABLE t1;
138 ############################################################
139 # Test#5 Bug#37318367 Delete part of records from two pages
140 # to lose a record - pause in PCursor::move_to_next_block
141 ############################################################
142 SET GLOBAL innodb_limit_optimistic_insert_debug=3;
143 connection default;
144 CREATE TABLE t1 (id int AUTO_INCREMENT NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
145 CALL insert_records(8);
146 SET GLOBAL innodb_purge_stop_now = ON;
147 DELETE FROM t1 WHERE id > 2 AND id < 7;
148 connection con1;
149 SET DEBUG_SYNC='pcursor_move_to_next_block_latches_released SIGNAL latches_released WAIT_FOR go EXECUTE 2';
150 # Send ALTER TABLE INPLACE to rebuild the index.
151 SET SESSION debug="+d,pcursor_move_to_next_block_release_latches";
152 ALTER TABLE t1 ENGINE=InnoDB, ALGORITHM=INPLACE;
153 connection default;
154 SET DEBUG_SYNC='now WAIT_FOR latches_released';
155 SET DEBUG_SYNC='now SIGNAL go';
156 SET DEBUG_SYNC='now WAIT_FOR latches_released';
157 SET GLOBAL innodb_purge_run_now = ON;
158 SET DEBUG_SYNC='now SIGNAL go';
159 connection con1;
160 # Reap ALTER TABLE.
161 SET SESSION debug="-d,pcursor_move_to_next_block_release_latches";
162 # Ensure all data is present after index rebuild.
163 # Test cleanup.
164 connection default;
165 DROP TABLE t1;
166 SET GLOBAL innodb_limit_optimistic_insert_debug=0;
167 #
168 # Cleanup.
169 connection default;
170 DROP TABLE IF EXISTS t1;
171 Warnings:
172 Note 1051 Unknown table 'test.t1'
173 DROP PROCEDURE test.insert_records;
174 disconnect con1;