| 1 |
CREATE TABLE parent ( |
| 2 |
id INT NOT NULL AUTO_INCREMENT, |
| 3 |
bogus_column char(32), |
| 4 |
PRIMARY KEY (id) |
| 5 |
) ENGINE=ndb; |
| 6 |
CREATE TABLE child ( |
| 7 |
id INT NOT NULL AUTO_INCREMENT, |
| 8 |
parent_id INT NOT NULL, |
| 9 |
bogus_column char(32), |
| 10 |
PRIMARY KEY (id), |
| 11 |
KEY (parent_id) |
| 12 |
) ENGINE=ndb; |
| 13 |
alter table child add constraint fkname foreign key (parent_id) references parent (id) on update restrict on delete restrict; |
| 14 |
INSERT INTO parent (bogus_column) |
| 15 |
VALUES ('aaa'), ('bbb'), ('ccc'), ('ddd'), ('eee'); |
| 16 |
INSERT INTO child (parent_id,bogus_column) VALUES |
| 17 |
(1, 'aaa'), (2,'bbb'), (3, 'ccc'), |
| 18 |
(4, 'ddd'), (5, 'eee'); |
| 19 |
START TRANSACTION; |
| 20 |
START TRANSACTION; |
| 21 |
UPDATE child SET parent_id = 5 WHERE parent_id = 4; |
| 22 |
UPDATE parent SET bogus_column = 'new!' WHERE id = 4; |
| 23 |
UPDATE parent SET bogus_column = 'new!' WHERE id = 5; |
| 24 |
ERROR HY000: Lock wait timeout exceeded; try restarting transaction |
| 25 |
rollback; |
| 26 |
rollback; |
| 27 |
alter table child drop foreign key fkname; |
| 28 |
alter table child add constraint fkname foreign key (parent_id) references parent (id); |
| 29 |
START TRANSACTION; |
| 30 |
START TRANSACTION; |
| 31 |
UPDATE child SET parent_id = 5 WHERE parent_id = 4; |
| 32 |
UPDATE parent SET bogus_column = 'new!' WHERE id = 4; |
| 33 |
UPDATE parent SET bogus_column = 'new!' WHERE id = 5; |
| 34 |
# Commit now tries to acquire a SimpleRead lock on the parent table row |
| 35 |
# to verify the FK constraint. But since T2 already has the exclusive |
| 36 |
# lock on the same parent table row, this commit should fail. |
| 37 |
commit; |
| 38 |
ERROR HY000: Lock wait timeout exceeded; try restarting transaction |
| 39 |
# Now T2's commit should be able to get locks on child table row |
| 40 |
# as T1 has ended and locks on child table have been released. |
| 41 |
commit; |
| 42 |
drop table child, parent; |
| 43 |
CREATE TABLE t1 ( |
| 44 |
id int(11) NOT NULL auto_increment, |
| 45 |
ref_id int(11) default NULL, |
| 46 |
PRIMARY KEY (id), |
| 47 |
KEY ref_id (ref_id), |
| 48 |
CONSTRAINT ref_id FOREIGN KEY (ref_id) REFERENCES t1 (id) |
| 49 |
) ENGINE=ndb; |
| 50 |
Warnings: |
| 51 |
Warning 1681 Integer display width is deprecated and will be removed in a future release. |
| 52 |
Warning 1681 Integer display width is deprecated and will be removed in a future release. |
| 53 |
INSERT INTO t1 VALUES (1, 1); |
| 54 |
DELETE FROM t1 WHERE id = 1; |
| 55 |
drop table t1; |
| 56 |
# |
| 57 |
# Bug#22180583 - Incorrect locking of FK Child table scan check. |
| 58 |
# |
| 59 |
# Create parent and child tables with fks |
| 60 |
create table parent( |
| 61 |
id int primary key |
| 62 |
)engine = ndb; |
| 63 |
create table child( |
| 64 |
id int primary key auto_increment, |
| 65 |
ref_oi int, |
| 66 |
ref_uk int unique key, |
| 67 |
foreign key fk1(ref_oi) references parent(id), |
| 68 |
foreign key fk2(ref_uk) references parent(id) |
| 69 |
)engine = ndb; |
| 70 |
insert into parent values (1); |
| 71 |
Test 1: Test FK locking with ordered index on child column |
| 72 |
# Transaction T1 inserts child row |
| 73 |
start transaction; |
| 74 |
insert into child(ref_oi) values(1); |
| 75 |
# Delete parent from parallel transaction T2. |
| 76 |
# This should fail because T2 tries to take a SimpleRead on child row. |
| 77 |
# T2 cannot acquire lock because T1 has not yet unlocked child row. |
| 78 |
delete from parent where id = 1; |
| 79 |
ERROR HY000: Lock wait timeout exceeded; try restarting transaction |
| 80 |
commit; |
| 81 |
# Show that FK reference intact |
| 82 |
select * from parent; |
| 83 |
id |
| 84 |
1 |
| 85 |
select * from child; |
| 86 |
id ref_oi ref_uk |
| 87 |
1 1 NULL |
| 88 |
delete from child; |
| 89 |
Test 2: Test FK locking with unique index on child column |
| 90 |
# Transaction T1 inserts child row with delayed commit (error insert 5012) |
| 91 |
start transaction; |
| 92 |
insert into child(ref_uk) values(1); |
| 93 |
# Delete parent from parallel transaction T2. |
| 94 |
delete from parent where id = 1; |
| 95 |
ERROR HY000: Lock wait timeout exceeded; try restarting transaction |
| 96 |
commit; |
| 97 |
# Show that FK reference intact |
| 98 |
select * from parent; |
| 99 |
id |
| 100 |
1 |
| 101 |
select * from child; |
| 102 |
id ref_oi ref_uk |
| 103 |
2 NULL 1 |
| 104 |
drop table child, parent; |