| 1 |
--source include/have_innodb.inc |
| 2 |
--source include/have_debug.inc |
| 3 |
--source include/have_debug_sync.inc |
| 4 |
--source include/galera_cluster.inc |
| 5 |
# |
| 6 |
# This test will reproduce regressions of bugs |
| 7 |
# https://github.com/codership/mysql-wsrep/issues/335 |
| 8 |
# https://github.com/codership/mysql-wsrep/issues/336 |
| 9 |
# |
| 10 |
# The test will issue duplicate inserts into a table, which has foreign key |
| 11 |
# constraint for a parent table. |
| 12 |
# The inserts happen in separate nodes, and the |
| 13 |
# The test outline is as follows: |
| 14 |
# 1. in node 1, an update is done for the FK parent row, in a transaction, |
| 15 |
# which is left open. The purpose of this is just to establish a X lock on |
| 16 |
# the parent row. |
| 17 |
# 2. in node 1, an insert for the child table is sent. The insert will have to wait |
| 18 |
# for the parent row X lock, because it needs S lock on the same row. |
| 19 |
# However, we have set an DBUG sync point just before the insert will call for |
| 20 |
# innodb suspend |
| 21 |
# 3. in node 2, a similar insert is issued. This will replicate to node 1 and try to |
| 22 |
# abort the conflicting update and blocked insert |
| 23 |
# 4. the inserts dbug sync point is relased, and insert should continue and abort |
| 24 |
# gracefully |
| 25 |
# 5. update is continued to commit, and it should also observe the deadlock |
| 26 |
# |
| 27 |
# This test is run in 3 phases: |
| 28 |
# |
| 29 |
# 1. with plain SQL statement |
| 30 |
# 2. as SQL prepared statements |
| 31 |
# 3. as SQl stored procedures |
| 32 |
# |
| 33 |
# The test phase 3 is for reproducing the issue in bug #336 specifically |
| 34 |
# |
| 35 |
|
| 36 |
# |
| 37 |
# create the test tables and foreign key constraint between them |
| 38 |
# |
| 39 |
CREATE TABLE user(id int primary key, j int) ENGINE=InnoDB; |
| 40 |
CREATE TABLE user_session(id int primary key, fk1 int, fk2 int) ENGINE=InnoDB; |
| 41 |
alter table user_session add foreign key (fk1) references user(id); |
| 42 |
|
| 43 |
# populate a few initial rows |
| 44 |
INSERT INTO user values (1,0), (2,0), (3,0), (4,0); |
| 45 |
INSERT INTO user_session values (1,1,1); |
| 46 |
|
| 47 |
# |
| 48 |
# prepare test connections, for: |
| 49 |
# update of parent row in node1 |
| 50 |
# insert of child row in node1 |
| 51 |
# insert of child row in node2 |
| 52 |
|
| 53 |
--connect node_1_u, 127.0.0.1, root, , test, $NODE_MYPORT_1 |
| 54 |
--connect node_1_i, 127.0.0.1, root, , test, $NODE_MYPORT_1 |
| 55 |
--connect node_2_i, 127.0.0.1, root, , test, $NODE_MYPORT_2 |
| 56 |
|
| 57 |
|
| 58 |
# |
| 59 |
# test phase 1: plain SQL statements |
| 60 |
# |
| 61 |
--echo "Phase 1: plain SQL statements" |
| 62 |
|
| 63 |
|
| 64 |
--connection node_1 |
| 65 |
let $counter=10; |
| 66 |
let $sleep_period=1; |
| 67 |
|
| 68 |
while($counter > 0) |
| 69 |
{ |
| 70 |
--connection node_1_u |
| 71 |
begin; |
| 72 |
update user set j = j + 1 WHERE id > 0; |
| 73 |
|
| 74 |
--connection node_1_i |
| 75 |
set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins'; |
| 76 |
send insert into user_session(id,fk1,fk2) values (2, 2, 2); |
| 77 |
|
| 78 |
--connection node_1 |
| 79 |
set debug_sync='now WAIT_FOR ins_waiting'; |
| 80 |
|
| 81 |
--connection node_2_i |
| 82 |
insert into user_session(id,fk1,fk2) values (2, 2, 3); |
| 83 |
|
| 84 |
--connection node_1 |
| 85 |
set debug_sync='now SIGNAL cont_ins'; |
| 86 |
|
| 87 |
--connection node_1_i |
| 88 |
--error 0,ER_LOCK_DEADLOCK,ER_DUP_ENTRY |
| 89 |
reap; |
| 90 |
|
| 91 |
--connection node_1_u |
| 92 |
--error 0,ER_LOCK_DEADLOCK |
| 93 |
commit; |
| 94 |
|
| 95 |
--connection node_1 |
| 96 |
--error 0,ER_LOCK_DEADLOCK |
| 97 |
truncate user_session; |
| 98 |
set debug_sync = reset; |
| 99 |
|
| 100 |
dec $counter; |
| 101 |
} |
| 102 |
|
| 103 |
# |
| 104 |
# test phase 2: prepared statements |
| 105 |
# |
| 106 |
--echo "Phase 2: prepared statements" |
| 107 |
|
| 108 |
--connection node_1_u |
| 109 |
prepare upd from 'update user set j = j + 1 WHERE id > 0'; |
| 110 |
|
| 111 |
--connection node_1_i |
| 112 |
prepare ins1 from 'insert into user_session(id,fk1,fk2) values (2, 2, 2)'; |
| 113 |
|
| 114 |
--connection node_2_i |
| 115 |
prepare ins2 from 'insert into user_session(id,fk1,fk2) values (2, 2, 3)'; |
| 116 |
|
| 117 |
--connection node_1 |
| 118 |
let $counter=10; |
| 119 |
let $sleep_period=1; |
| 120 |
|
| 121 |
while($counter > 0) |
| 122 |
{ |
| 123 |
--connection node_1_u |
| 124 |
begin; |
| 125 |
execute upd; |
| 126 |
#update user set j = j + 1 WHERE id > 0; |
| 127 |
|
| 128 |
--connection node_1_i |
| 129 |
set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins'; |
| 130 |
send execute ins1; |
| 131 |
|
| 132 |
--connection node_1 |
| 133 |
set debug_sync='now WAIT_FOR ins_waiting'; |
| 134 |
|
| 135 |
--connection node_2_i |
| 136 |
execute ins2; |
| 137 |
|
| 138 |
--connection node_1 |
| 139 |
set debug_sync='now SIGNAL cont_ins'; |
| 140 |
|
| 141 |
--connection node_1_i |
| 142 |
--error 0,ER_LOCK_DEADLOCK,ER_DUP_ENTRY |
| 143 |
reap; |
| 144 |
|
| 145 |
--connection node_1_u |
| 146 |
--error 0,ER_LOCK_DEADLOCK |
| 147 |
commit; |
| 148 |
|
| 149 |
--connection node_1 |
| 150 |
|
| 151 |
--error 0,ER_LOCK_DEADLOCK |
| 152 |
truncate user_session; |
| 153 |
set debug_sync = reset; |
| 154 |
|
| 155 |
dec $counter; |
| 156 |
} |
| 157 |
|
| 158 |
|
| 159 |
--connection node_1 |
| 160 |
|
| 161 |
drop table user_session,user; |