| 1 |
--echo # |
| 2 |
--echo # Check consistency of delete-ref-check |
| 3 |
--echo # |
| 4 |
|
| 5 |
--echo # Create procedure to transactionally move a child->parent |
| 6 |
--echo # ref between several child rows. |
| 7 |
--echo # At all times there is a reference, but the reference |
| 8 |
--echo # is from rows with different pks at different times. |
| 9 |
--echo # The transaction to move the ref to a different child row |
| 10 |
--echo # might some time get a Lock wait timeout error if the other |
| 11 |
--echo # procedure's transaction has acquired the SimpleRead lock |
| 12 |
--echo # for the same parent row at the same time. |
| 13 |
--echo # We simply ignore those errors and move on. |
| 14 |
--echo # At no time should it be possible to delete the parent row. |
| 15 |
--echo # |
| 16 |
|
| 17 |
delimiter %; |
| 18 |
|
| 19 |
create procedure updateref(iterations int) |
| 20 |
ur:begin |
| 21 |
declare continue handler for 1205 SET @dummy__ = 0; |
| 22 |
set @x = 1; |
| 23 |
repeat |
| 24 |
start transaction; |
| 25 |
delete from child; |
| 26 |
insert into child values (@x, 42); |
| 27 |
commit; |
| 28 |
set @x=@x+1; |
| 29 |
until @x = iterations |
| 30 |
end repeat; |
| 31 |
end% |
| 32 |
|
| 33 |
delimiter ;% |
| 34 |
|
| 35 |
--echo # |
| 36 |
--echo # Create procedure to continually attempt to delete |
| 37 |
--echo # a parent row. This should always fail due to child |
| 38 |
--echo # references. The error return codes would vary between |
| 39 |
--echo # 1205(Lock wait time out) and 1451(child row exists) |
| 40 |
--echo # depending on the time at which the delete is trying |
| 41 |
--echo # acquire the child row lock w.r.to the other procedure. |
| 42 |
--echo # |
| 43 |
|
| 44 |
drop procedure if exists removeparent; |
| 45 |
|
| 46 |
delimiter %; |
| 47 |
|
| 48 |
create procedure removeparent(iterations int) |
| 49 |
rp:begin |
| 50 |
declare continue handler for 1205, 1451 SET @err = @err+1; |
| 51 |
set @x = 0; |
| 52 |
set @err = 0; |
| 53 |
repeat |
| 54 |
delete from parent where id=42; |
| 55 |
set @x=@x+1; |
| 56 |
if @x != @err then |
| 57 |
leave rp; |
| 58 |
end if; |
| 59 |
until @x = iterations |
| 60 |
end repeat; |
| 61 |
end% |
| 62 |
|
| 63 |
delimiter ;% |
| 64 |
|
| 65 |
--echo # Setting up initial values |
| 66 |
insert into parent values (42); |
| 67 |
insert into child values (0,42); |
| 68 |
|
| 69 |
--echo # Sanity check, delete must fail |
| 70 |
--error ER_ROW_IS_REFERENCED_2 |
| 71 |
delete from parent where id=42; |
| 72 |
|
| 73 |
connection con1; |
| 74 |
--echo # Start procedure moving child reference around |
| 75 |
--send call updateref(100); |
| 76 |
|
| 77 |
connection con2; |
| 78 |
--echo # Start procedure attempting to delete parent |
| 79 |
--send call removeparent(100); |
| 80 |
|
| 81 |
--echo # Wait for parent delete to finish, |
| 82 |
--echo # FK error is masked by the SP |
| 83 |
--reap |
| 84 |
|
| 85 |
--echo # Now let the child ref transfers complete. |
| 86 |
connection con1; |
| 87 |
--reap |
| 88 |
|
| 89 |
--echo # Verify that the parent row is intact. |
| 90 |
select * from parent; |
| 91 |
|
| 92 |
--echo # Verify that we can add a new child row ref. |
| 93 |
insert into child values (100,42); |
| 94 |
|
| 95 |
--echo # Drop the procedures and delete the rows from tables |
| 96 |
drop procedure updateref; |
| 97 |
drop procedure removeparent; |
| 98 |
delete from child; |
| 99 |
delete from parent; |