Debian Codegraph @

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

raw
1 #
2 # Test for Bug#30113362 : BTR_CUR_WILL_MODIFY_TREE() IS INSUFFICIENT FOR HIGHER TREE LEVEL
3 #
4
5 --source include/have_debug.inc
6 --source include/have_debug_sync.inc
7 --source include/have_innodb_16k.inc
8
9 --disable_query_log
10 SET @old_innodb_limit_optimistic_insert_debug = @@innodb_limit_optimistic_insert_debug;
11 SET @old_innodb_adaptive_hash_index = @@innodb_adaptive_hash_index;
12 SET @old_innodb_stats_persistent = @@innodb_stats_persistent;
13 SET @old_innodb_flush_log_at_trx_commit = @@innodb_flush_log_at_trx_commit;
14 --enable_query_log
15
16 # Save the initial number of concurrent sessions
17 --source include/count_sessions.inc
18
19 SET GLOBAL innodb_adaptive_hash_index = false;
20 SET GLOBAL innodb_stats_persistent = false;
21 SET GLOBAL innodb_flush_log_at_trx_commit = 0;
22
23 --connect (con1,localhost,root,,)
24 --connect (con2,localhost,root,,)
25
26 CREATE TABLE t1 (
27 a00 CHAR(255) NOT NULL DEFAULT 'a',
28 a01 CHAR(255) NOT NULL DEFAULT 'a',
29 a02 CHAR(255) NOT NULL DEFAULT 'a',
30 b INT NOT NULL DEFAULT 0,
31 CONSTRAINT pkey PRIMARY KEY(a00, a01, a02)
32 ) charset latin1 ENGINE = InnoDB COMMENT='MERGE_THRESHOLD=45';
33
34 #
35 # Prepare primary key index tree to be used for this test.
36 #
37
38 SET GLOBAL innodb_limit_optimistic_insert_debug = 3;
39
40 DROP PROCEDURE IF EXISTS data_load_t1;
41 delimiter |;
42 CREATE PROCEDURE data_load_t1()
43 BEGIN
44 DECLARE c1 INT DEFAULT 97;
45 DECLARE c2 INT DEFAULT 97;
46 DECLARE c3 INT DEFAULT 97;
47
48 WHILE c1 < 102 DO
49 WHILE c2 < 123 DO
50 WHILE c3 < 123 DO
51 INSERT INTO t1 (a00) VALUES (CHAR(c1,c2,c3));
52 SET c3 = c3 + 1;
53 END WHILE;
54 SET c3 = 97;
55 SET c2 = c2 + 1;
56 END WHILE;
57 SET c2 = 97;
58 SET c1 = c1 + 1;
59 END WHILE;
60 END |
61 delimiter ;|
62 call data_load_t1();
63 DROP PROCEDURE data_load_t1;
64
65 # all node pages are sparse (max 3 node_ptrs)
66 ANALYZE TABLE t1;
67 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
68
69 # causes "domino falling" merges to upper level
70 SET GLOBAL innodb_limit_optimistic_insert_debug = 0;
71 DELETE FROM t1 WHERE a00 = 'cnm';
72 --source suite/innodb/include/force_purge.inc
73
74 # at this moment, in the tree,
75 # ...
76 # level 4: ...(ast,avw,ayz)(bcc,bff,bii,bll,boo,brr,buu,bxx,cba,ced,cqp,cts)(cwv,czy,ddb)...
77 # ...
78
79 --echo # Test start
80
81 # (1) Similar case to the first reported corefile at bug#30113362
82 # - Deleting 'bii' causes "domino falling" merges and the node_ptr becomes left_most of level 4.
83 # So, the operation needs upper level pages' X-latch, though doesn't cause merge more.
84 SET DEBUG_SYNC = 'RESET';
85
86 SET GLOBAL innodb_purge_stop_now = ON;
87 SET GLOBAL DEBUG = "+d,pessimistic_row_purge_clust";
88 DELETE FROM t1 WHERE a00 = 'bii';
89 SET GLOBAL innodb_purge_run_now = ON;
90 SET DEBUG_SYNC = "now WAIT_FOR pessimistic_row_purge_clust_pause";
91
92 --connection con1
93 SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1';
94 # Blocked
95 --send
96 SELECT a00 FROM t1 WHERE a00 = 'bcc';
97
98 --connection default
99 SET DEBUG_SYNC = 'now WAIT_FOR lockwait1';
100 # bug#30113362 caused deadlock
101 # signal purge thread to go
102 SET GLOBAL DEBUG = "-d,pessimistic_row_purge_clust";
103 SET DEBUG_SYNC = "now SIGNAL pessimistic_row_purge_clust_continue";
104
105 --connection con1
106 --reap
107
108 --connection default
109
110 --source suite/innodb/include/force_purge.inc
111 ANALYZE TABLE t1;
112 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
113
114
115 # (2) Confirm blocking domain caused by DELETE modify_tree for tall index tree
116 SET DEBUG_SYNC = 'RESET';
117
118 # at this moment, in the tree,
119 # ...
120 # level 4: ...(ajk,amn,apq)(ast,avw,ayz,bll,boo,brr,buu,bxx,cba,ced,cqp,cts)(cwv,czy,ddb)(dge,djh,dmk)(dpn,dsq,dvt)(dyw,ebz,efc)...
121 # ...
122
123 # makes >17 records in level4 [(2^(4-1))*2 + 1]. (causes never left_most records)
124 DELETE FROM t1 WHERE a00 = 'dpn';
125 --source suite/innodb/include/force_purge.inc
126 # at this moment, in the tree,
127 # (* before "]" and after "[" records are treated as left_most possible records)
128 # ...
129 # level 4: ...(ajk,amn,apq)(ast,avw,ayz,bll,boo,brr,buu,bxx],cba,ced,[cqp,cts,cwv,czy,ddb,dge,dsq,dvt)(dyw,ebz,efc)...
130 # level 3: ...(cba,ccb,cdc)(ced,cfe,cgf,chg],cih,cji,[ckj,clk,con,cpo)(cqp,crq,csr)...
131 # level 2: ...(ckj,cks,clb)(clk,clt],cmc,cml,cmu,cnd,[cnv,coe)(con,cow,cpf)...
132 # level 1: ...(cmu,cmx,cna)(cnd],cng,cnj,cnp,[cns)(cnv,cny,cob)...
133 # level 0: ...(cnd,cne,cnf)(cng,cnh,cni)(cnj,cnk,cnl,cnn,cno)(cnp,cnq,cnr)...
134
135 # deletes just 'ced' node_ptr only from level 4. doesn't cause merge and never left_most.
136 # adjusts MERGE_THRESHOLD to do so.
137 ALTER TABLE t1 COMMENT='MERGE_THRESHOLD=35';
138 SET GLOBAL innodb_purge_stop_now = ON;
139 SET GLOBAL DEBUG = "+d,pessimistic_row_purge_clust";
140 DELETE FROM t1 WHERE a00 = 'cnd';
141 SET GLOBAL innodb_purge_run_now = ON;
142 SET DEBUG_SYNC = "now WAIT_FOR pessimistic_row_purge_clust_pause";
143
144 # The expectation should be...
145 # level 0: (#cnd#,cne,cnf): causes merge
146 # level 1: (#cnd#],cng,cnj,cnp,[cns): left_most
147 # level 2: (clk,clt],cmc,cml,cmu,#cnd#,[cnv,coe): causes merge
148 # level 3: (ced,cfe,cgf,chg],cih,cji,[ckj,#clk#,con,cpo): left_most possible (not cause merge)
149 # level 4: (ast,avw,ayz,bll,boo,brr,buu,bxx],cba,#ced#,[cqp,cts,cwv,czy,ddb,dge,dsq,dvt): no merge, not left_most possible
150 # So, the top X-latch page is at level4. (ast~dvt)
151
152 # blocking domain based on whether its ancestor is latched or not.
153 # (*[]: ancestor is X-latched)
154 # level 0: ...(asq,asr,ass) [(ast,asu,asv)...(dyt,dyu,dyv)] (dyw,dyx,dyy)...
155
156 # Not blocked searches
157 SELECT a00 FROM t1 WHERE a00 = 'ass';
158 SELECT a00 FROM t1 WHERE a00 = 'dyx';
159
160 --connection con1
161 SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait1';
162 # Blocked
163 --send
164 SELECT a00 FROM t1 WHERE a00 = 'ast';
165
166 --connection con2
167 SET DEBUG_SYNC = 'rw_s_lock_waiting SIGNAL lockwait2';
168 # Blocked
169 --send
170 SELECT a00 FROM t1 WHERE a00 = 'dyw';
171
172 --connection default
173 SET DEBUG_SYNC = 'now WAIT_FOR lockwait1';
174 SET DEBUG_SYNC = 'now WAIT_FOR lockwait2';
175 # signal purge thread to go
176 SET GLOBAL DEBUG = "-d,pessimistic_row_purge_clust";
177 SET DEBUG_SYNC = "now SIGNAL pessimistic_row_purge_clust_continue";
178
179 --connection con1
180 --reap
181
182 --connection con2
183 --reap
184
185 --connection default
186 ANALYZE TABLE t1;
187 SELECT CLUST_INDEX_SIZE FROM information_schema.INNODB_TABLESTATS WHERE NAME = 'test/t1';
188
189
190 # Cleanup
191 SET DEBUG_SYNC = 'RESET';
192
193 --connection default
194 --disconnect con1
195 --disconnect con2
196
197 DROP TABLE t1;
198
199 --disable_query_log
200 SET GLOBAL innodb_limit_optimistic_insert_debug = @old_innodb_limit_optimistic_insert_debug;
201 SET GLOBAL innodb_adaptive_hash_index = @old_innodb_adaptive_hash_index;
202 SET GLOBAL innodb_stats_persistent = @old_innodb_stats_persistent;
203 SET GLOBAL innodb_flush_log_at_trx_commit = @old_innodb_flush_log_at_trx_commit;
204 --enable_query_log
205
206 # Wait till all disconnects are completed.
207 --source include/wait_until_count_sessions.inc