Debian Codegraph @

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

raw
1 --source include/count_sessions.inc
2 --source include/have_debug.inc
3 # Remove when Bug#30320009 has been fixed
4 --source include/not_binlog_format_mixed.inc
5
6 --echo #
7 --echo # Bug#25966845 INSERT ON DUPLICATE KEY GENERATE A DEADLOCK
8 --echo #
9
10 CREATE TABLE t1(f1 int primary key,
11 f2 int, f3 int, unique key(f2))engine=innodb;
12
13 SHOW CREATE TABLE t1;
14 INSERT INTO t1(f1, f2, f3) VALUES(1, 10, 100);
15
16 --echo # Connection default
17 BEGIN;
18 INSERT INTO t1 VALUES(2, 20, 300) ON DUPLICATE KEY UPDATE f3 = 500;
19
20 --echo # Connection other:
21 --connect (other,localhost,root,,test,,)
22 BEGIN;
23 SET DEBUG_SYNC = 'ib_after_row_insert_step SIGNAL default_commit';
24 --send INSERT INTO t1 VALUES(2, 10, 200) ON DUPLICATE KEY UPDATE f3 = 120
25
26 --echo # Connection default
27 connection default;
28 SET DEBUG_SYNC = 'now WAIT_FOR default_commit';
29 COMMIT;
30
31 --echo # Connection other
32 connection other;
33 --reap
34 COMMIT;
35
36 connection default;
37 disconnect other;
38
39 --echo # Verify Results:
40 SELECT * FROM t1;
41
42 SET DEBUG_SYNC ='RESET';
43 DROP TABLE t1;
44 --source include/wait_until_count_sessions.inc
45
46 --echo #
47 --echo # Bug #29195848 ASSERTION "!OTHER_LOCK" IN LOCK_REC_ADD_TO_QUEUE
48 --echo #
49 # Test 1 :- Partial rollback of Insert (secondary Index):
50 # Pre condition-1: Secondary index has one unpurged delete marked key
51 # [say K1] that matches exactly with the key to be inserted
52 # (including cluster index key).
53 # Pre-condition-2: Secondary index has one committed key [say K2] that matches
54 # with the key to be inserted but with different cluster index key.
55
56 # Test: During Insert, secondary index entry [K1] is not updated (un-delete marked)
57 # because of duplicate key [K2].In this case the transaction doesn't
58 # hold implicit lock on secondary index key and partial rollback should
59 # not try to convert it to explicit lock.
60
61 SET GLOBAL innodb_purge_stop_now = ON;
62 CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY, b INT, UNIQUE KEY(b));
63
64 INSERT INTO t1 VALUES(2, 300);
65 #It generates the delete marked key [K1]
66 DELETE FROM t1;
67
68 #It generates the committed key [K2] 300,3 in Secondary index.
69 INSERT INTO t1 VALUES(3, 300);
70 SELECT * FROM t1;
71
72 connect (conn1,localhost,root,,);
73 connect (conn2,localhost,root,,);
74
75 connection conn1;
76 SET DEBUG_SYNC='ib_after_row_insert_step SIGNAL after_insert WAIT_FOR
77 rollback';
78
79 # We have inserted successfully the cluster index key [2]
80 # when ib_after_row_insert_step is reached. We failed to un-delete mark
81 # the secondary index key K1 [300,2] because of duplicate key K2 [300,3].
82 # So key K1 [300,2] stays delete marked and the transaction have shared
83 # lock on it instead of implicit lock.
84 --send INSERT INTO t1 VALUES(2, 300);
85
86 connection conn2;
87 SET DEBUG_SYNC='now WAIT_FOR after_insert';
88 START TRANSACTION;
89 # We acquire S lock on delete marked secondary index key K1 [300, 2].
90 --error ER_DUP_ENTRY
91 INSERT INTO t1 VALUES(1, 300);
92
93 connection default;
94 # For purge going
95 SET GLOBAL innodb_purge_run_now=ON;
96 SET DEBUG_SYNC='now SIGNAL rollback';
97
98 connection conn1;
99 # The transaction is partially rolled back here.
100 # Before the fix, it would assert while trying to create explicit lock on K1 [300,2]
101 # because other transaction already holds shared lock on it.
102 # Since we don't have implicit lock here, the fix makes sure that we don't try to
103 # create explicit lock during such partial rollback for a delete marked record.
104 --error ER_DUP_ENTRY
105 --reap
106
107 connection conn2;
108 COMMIT;
109
110 disconnect conn1;
111 disconnect conn2;
112 connection default;
113 DROP TABLE t1;
114
115 # Test 2 Partial Rollback of Insert (Cluster Index):
116 # Pre condition-1: Cluster index has one unpurged delete marked key [say PK1]
117 # that matches exactly with the key to be inserted.
118 # Pre-condition-2: Unique 2ndary index has one committed key [say K1] that matches
119 # with the key to be inserted but with different cluster index key.
120 # Test: During Insert, the cluster index Key K1 is reused by undelete marking it.
121 # The command fails and goes for partial rollback because of matching secondary
122 # index key K1. During partial rollback, the implicit lock is released when we
123 # update the cluster index key replacing with previous delete marked key.The
124 # test verifies that the implicit lock is converted to explicit lock before
125 # releasing it.
126
127 SET GLOBAL innodb_purge_stop_now = ON;
128 CREATE TABLE t1 (a INT PRIMARY KEY, b INT, UNIQUE KEY(b));
129
130 #Create delete marked key PK1 (1) in cluster index and (10, 1) K1 in Secondary index.
131 INSERT INTO t1 VALUES(1,10);
132 DELETE FROM t1;
133
134 #Create committed key (2) in cluster index and (10, 2) K2 in Secondary index.
135 INSERT INTO t1 VALUES(2,10);
136
137 connect (conn1,localhost,root,,);
138 connect (conn2,localhost,root,,);
139
140 connection conn1;
141 SET DEBUG_SYNC='ib_undo_mod_before_remove_clust SIGNAL during_rollback
142 WAIT_FOR rollback';
143
144 #Try to Insert with cluster index key matching PK1
145 --send INSERT INTO t1 VALUES(1, 10) ;
146
147 connection conn2;
148
149 SET DEBUG_SYNC='now WAIT_FOR during_rollback';
150 # In Conn1, we have got duplicate key error due to mathing secondary index key
151 # and at this stage half way through partial rollback. We are waiting after rolling
152 # back cluster index key with old delete marked key K1. The implicit lock on PK1
153 # is already released.Try to lock the cluster index key PK1. The bug has the issue
154 # that Conn1 transaction has not created the explicit lock on PK1 but implicit lock
155 # is already released,so taking a shared lock would succeeded which caused assert
156 # when conn1 tried to rollback. After the fix we ensure that implicit lock is
157 # converted to explicit before the implicit protection is released,therfore the
158 # share lock request times out
159
160 --error ER_LOCK_WAIT_TIMEOUT
161 SELECT * FROM t1 WHERE a = 1 LOCK IN SHARE MODE;
162
163 connection default;
164 SET GLOBAL innodb_purge_run_now=ON;
165 SET DEBUG_SYNC='now SIGNAL rollback';
166
167 connection conn1;
168 --error ER_DUP_ENTRY
169 --reap
170
171 disconnect conn1;
172 disconnect conn2;
173 connection default;
174 SELECT * FROM t1;
175 DROP TABLE t1;
176
177
178 --echo #
179 --echo # Bug #29718243 MYQL SERVER CRASHING
180 --echo #
181
182 CREATE TEMPORARY TABLE tmpTest(tmpField INT , UNIQUE KEY uq_tmpField (tmpField));
183 CREATE TEMPORARY TABLE tmpTest1(tmpField INT , UNIQUE KEY uq_tmpField (tmpField));
184
185 DELIMITER |;
186 CREATE FUNCTION ZZtest() RETURNS int(11)
187 BEGIN
188 DECLARE l_total INTEGER;
189 SET l_total = 0;
190
191 INSERT INTO tmpTest SET tmpField = 40;
192 INSERT IGNORE INTO tmpTest SET tmpField = 40;
193 INSERT IGNORE INTO tmpTest1 SET tmpField = 40;
194
195 DROP TEMPORARY TABLE IF EXISTS tmpTest;
196 DROP TEMPORARY TABLE IF EXISTS tmpTest1;
197
198 RETURN l_total;
199 END|
200 DELIMITER ;|
201 SELECT ZZtest() AS test;
202 DROP FUNCTION ZZtest;