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

--source include/have_innodb.inc
--source include/have_debug.inc
--source include/have_debug_sync.inc
--source include/galera_cluster.inc
#
# This test will reproduce regressions of bugs
#   https://github.com/codership/mysql-wsrep/issues/335
#   https://github.com/codership/mysql-wsrep/issues/336
#
# The test will issue duplicate inserts into a table, which has foreign key
# constraint for a parent table.
# The inserts happen in separate nodes, and the
# The test outline is as follows:
# 1. in node 1, an update is done for the FK parent row, in a transaction,
#    which is left open. The purpose of this is just to establish a X lock on
#    the parent row.
# 2. in node 1, an insert for the child table is sent. The insert will have to wait
#    for the parent row X lock, because it needs S lock on the same row.
#    However, we have set an DBUG sync point just before the insert will call for
#    innodb suspend
# 3. in node 2, a similar insert is issued. This will replicate to node 1 and try to
#    abort the conflicting update and blocked insert
# 4. the inserts dbug sync point is relased, and insert should continue and abort
#    gracefully
# 5. update is continued to commit, and it should also observe the deadlock
#
# This test is run in 3 phases:
#
# 1. with plain SQL statement
# 2. as SQL prepared statements
# 3. as SQl stored procedures
#
# The test phase 3 is for reproducing the issue in bug #336 specifically
#

#
# create the test tables and foreign key constraint between them
#
CREATE TABLE user(id int primary key, j int) ENGINE=InnoDB;
CREATE TABLE user_session(id int primary key, fk1 int, fk2 int) ENGINE=InnoDB;
alter table user_session add foreign key (fk1) references user(id);

# populate a few initial rows
INSERT INTO user values (1,0), (2,0), (3,0), (4,0);
INSERT INTO user_session values (1,1,1);

#
# prepare test connections, for:
#   update of parent row in node1
#   insert of child row in node1
#   insert of child row in node2

--connect node_1_u, 127.0.0.1, root, , test, $NODE_MYPORT_1
--connect node_1_i, 127.0.0.1, root, , test, $NODE_MYPORT_1
--connect node_2_i, 127.0.0.1, root, , test, $NODE_MYPORT_2


#
# test phase 1: plain SQL statements
#
--echo "Phase 1: plain SQL statements"


--connection node_1
let $counter=10;
let $sleep_period=1;

while($counter > 0)
{
	--connection node_1_u
	begin;
	update user set j = j + 1 WHERE id > 0;

	--connection node_1_i
	set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins';
	send insert into user_session(id,fk1,fk2) values (2, 2, 2);

	--connection node_1
	set debug_sync='now WAIT_FOR ins_waiting';

	--connection node_2_i
	insert into user_session(id,fk1,fk2) values (2, 2, 3);

	--connection node_1
	set debug_sync='now SIGNAL cont_ins';

	--connection node_1_i
	--error 0,ER_LOCK_DEADLOCK,ER_DUP_ENTRY
	reap;

	--connection node_1_u
	--error 0,ER_LOCK_DEADLOCK
	commit;

	--connection node_1
	--error 0,ER_LOCK_DEADLOCK
	truncate user_session;
	set debug_sync = reset;

	dec $counter;
}

#
# test phase 2: prepared statements
#
--echo "Phase 2: prepared statements"

--connection node_1_u
prepare upd  from 'update user set j = j + 1 WHERE id > 0';

--connection node_1_i
prepare ins1 from 'insert into user_session(id,fk1,fk2) values (2, 2, 2)';

--connection node_2_i
prepare ins2 from 'insert into user_session(id,fk1,fk2) values (2, 2, 3)';

--connection node_1
let $counter=10;
let $sleep_period=1;

while($counter > 0)
{
	--connection node_1_u
	begin;
	execute upd;
	#update user set j = j + 1 WHERE id > 0;

	--connection node_1_i
	set debug_sync='lock_wait_start SIGNAL ins_waiting WAIT_FOR cont_ins';
	send execute ins1;

	--connection node_1
	set debug_sync='now WAIT_FOR ins_waiting';

	--connection node_2_i
	execute ins2;

	--connection node_1
	set debug_sync='now SIGNAL cont_ins';

	--connection node_1_i
	--error 0,ER_LOCK_DEADLOCK,ER_DUP_ENTRY
	reap;

	--connection node_1_u
	--error 0,ER_LOCK_DEADLOCK
	commit;

	--connection node_1

	--error 0,ER_LOCK_DEADLOCK
	truncate user_session;
	set debug_sync = reset;

	dec $counter;
}


--connection node_1

drop table user_session,user;