[ Avaa Bypassed ]

CREATE TABLE parent (
a INT, d INT, b VARCHAR(20), c VARCHAR(75),
PRIMARY KEY (a, d, b), INDEX (b, c), INDEX (c))
ENGINE = INNODB;
CREATE TABLE child (
a INT, d INT, b VARCHAR(20), c VARCHAR(75),
PRIMARY KEY (a, d, b), INDEX (b, c), INDEX (c),
FOREIGN KEY (a, d) REFERENCES parent (a, d)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT kukkuu FOREIGN KEY (b, c) REFERENCES parent (b, c)
ON DELETE CASCADE ON UPDATE CASCADE)
ENGINE = INNODB;
INSERT INTO parent VALUES (1, 5, 'khD','khD');
SET innodb_lock_wait_timeout=1;
# connection con1: blocked before FOREIGN KEY check during INSERT
SET DEBUG_SYNC = 'foreign_constraint_check_for_ins WAIT_FOR drop_done';
/* con1 send */ INSERT INTO child VALUES (1, 5, 'khD','khD');
# connection default: replace the underlying index of FOREIGN KEY
CREATE INDEX idx1 ON parent(b, c);
Warnings:
Note	1831	Duplicate index 'idx1' defined on the table 'test.parent'. This is deprecated and will be disallowed in a future release.
DROP INDEX b ON parent;
SET DEBUG_SYNC = 'now SIGNAL drop_done';
/* con1 reap */ INSERT INTO child VALUES (1, 5, 'khD','khD');
# connection con1: test ON UPDATE CASCADE
BEGIN;
SET DEBUG_SYNC = 'foreign_constraint_update_cascade WAIT_FOR create_index';
SET DEBUG_SYNC = 'now SIGNAL start_test';
/* con1 send */ DELETE FROM parent WHERE a = 1;
# connection default: con1 already IX-locked table child
/* default send */ CREATE INDEX idx ON child(b, c);
SET DEBUG_SYNC = 'now WAIT_FOR start_test';
# connection con2: unblock connection default
SET DEBUG_SYNC = 'now SIGNAL create_index';
/* default reap */ CREATE INDEX idx ON child(b, c);
/* con1 reap */ DELETE FROM parent WHERE a = 1;
# connection default: con1 is holding IX-lock on table child
CREATE INDEX idx ON child(b, c);
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
DROP INDEX b ON child;
ERROR HY000: Cannot drop index 'b': needed in a foreign key constraint
# connection con1
COMMIT;
# connection default: no locks on table child
CREATE INDEX idx ON child(b, c);
Warnings:
Note	1831	Duplicate index 'idx' defined on the table 'test.child'. This is deprecated and will be disallowed in a future release.
DROP INDEX b ON child;
SELECT * FROM child;
a	d	b	c
SELECT * FROM parent;
a	d	b	c
# connection con1: test ON UPDATE CASCADE
INSERT INTO parent VALUES (1, 5, 'khD','khD');
INSERT INTO child VALUES (1, 5, 'khD','khD');
BEGIN;
SET DEBUG_SYNC = 'foreign_constraint_check_for_update_done WAIT_FOR alter';
/* con1 send */ UPDATE parent SET a = 2;
# connection default: con1 is holding IX-lock on table child
ALTER TABLE child ADD INDEX idx_2(B, C), DROP INDEX idx, LOCK=NONE;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
SET DEBUG_SYNC = 'now SIGNAL alter';
/* con1 reap */ UPDATE parent SET a = 2;
# connection default: con1 is holding IX-lock on table child
ALTER TABLE child ADD INDEX idx_2(B, C), DROP INDEX idx, ALGORITHM=INPLACE;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
# connection con1
ROLLBACK;
BEGIN;
SET DEBUG_SYNC = 'foreign_constraint_check_for_update WAIT_FOR altered';
SET DEBUG_SYNC = 'now SIGNAL start_test';
/* con1 send */ UPDATE parent SET a = 3;
# connection default: con1 is not yet holding locks on table child
/* default send */ ALTER TABLE child ADD INDEX idx_2(B, C), DROP INDEX idx;
SET DEBUG_SYNC = 'now WAIT_FOR start_test';
# connection con2: unblock connection con1
SET DEBUG_SYNC = 'now SIGNAL altered';
/* con1 reap */ UPDATE parent SET a = 3;
/* default reap */ ALTER TABLE child ADD INDEX idx_2(B, C), DROP INDEX idx;
SELECT * FROM child;
a	d	b	c
1	5	khD	khD
# connection con1
SELECT * FROM child;
a	d	b	c
3	5	khD	khD
COMMIT;
SELECT * FROM child;
a	d	b	c
3	5	khD	khD
SELECT * FROM parent;
a	d	b	c
3	5	khD	khD
DROP TABLE child;
DROP TABLE parent;
SET DEBUG_SYNC = 'RESET';