Debian Codegraph @

mysql-test/suite/galera/t/galera_FK_duplicate_client_insert.test

raw
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;