593 lines
13 KiB
Plaintext
593 lines
13 KiB
Plaintext
-- source include/have_ndb.inc
|
|
|
|
connect (con1,localhost,root,,test);
|
|
connect (con2,localhost,root,,test);
|
|
|
|
create table parent (
|
|
a int primary key auto_increment,
|
|
b int not null,
|
|
c int not null,
|
|
unique(b) using hash,
|
|
index(c)) engine = ndb;
|
|
|
|
create table child (
|
|
a int primary key auto_increment,
|
|
b int not null,
|
|
c int not null,
|
|
unique(b) using hash,
|
|
index(c)) engine = ndb;
|
|
|
|
alter table child add constraint fkname foreign key (a) references parent(a) on delete restrict on update restrict;
|
|
|
|
insert into parent values (1,1,1);
|
|
insert into child values (1,1,1);
|
|
|
|
#
|
|
# Check disabling foreign_key_checks for
|
|
# autocommit
|
|
#
|
|
|
|
--error ER_NO_REFERENCED_ROW_2
|
|
insert into child values (2,2,2);
|
|
|
|
--echo # Disabling foreign key checks
|
|
set foreign_key_checks = 0;
|
|
|
|
insert into child values (2,2,2);
|
|
select * from child order by 1,2,3;
|
|
|
|
set foreign_key_checks = 1;
|
|
|
|
#
|
|
# Check disabling foreign_key_checks for
|
|
# user defined transactions.
|
|
#
|
|
|
|
begin;
|
|
--error ER_ROW_IS_REFERENCED_2
|
|
delete from parent;
|
|
rollback;
|
|
|
|
--echo # Disabling foreign key checks
|
|
set foreign_key_checks = 0;
|
|
|
|
begin;
|
|
delete from parent;
|
|
rollback;
|
|
|
|
delete from child;
|
|
delete from parent;
|
|
|
|
set foreign_key_checks = 1;
|
|
|
|
begin;
|
|
--error ER_NO_REFERENCED_ROW_2
|
|
insert into child values (2,2,2);
|
|
rollback;
|
|
|
|
--echo # Disabling foreign key checks
|
|
set foreign_key_checks = 0;
|
|
|
|
begin;
|
|
insert into child values (2,2,2);
|
|
rollback;
|
|
|
|
set foreign_key_checks = 1;
|
|
|
|
delete from child;
|
|
delete from parent;
|
|
|
|
#
|
|
# Check that lock conflicts due to FK checking
|
|
# do not occurr with set foreign_key_checks = 0;
|
|
#
|
|
|
|
insert into parent values (1,1,1);
|
|
|
|
connection con1;
|
|
begin;
|
|
insert into child values (1,1,1);
|
|
|
|
connection con2;
|
|
begin;
|
|
--error ER_LOCK_WAIT_TIMEOUT
|
|
delete from parent;
|
|
|
|
connection con1;
|
|
commit;
|
|
|
|
connection con2;
|
|
commit;
|
|
|
|
delete from child;
|
|
delete from parent;
|
|
|
|
--echo # Disabling foreign key checks
|
|
set foreign_key_checks = 0;
|
|
|
|
insert into parent values (1,1,1);
|
|
|
|
connection con1;
|
|
--echo # Disabling foreign key checks
|
|
set foreign_key_checks = 0;
|
|
begin;
|
|
insert into child values (1,1,1);
|
|
|
|
connection con2;
|
|
--echo # Disabling foreign key checks
|
|
set foreign_key_checks = 0;
|
|
begin;
|
|
#--error ER_LOCK_WAIT_TIMEOUT
|
|
delete from parent;
|
|
|
|
connection con1;
|
|
commit;
|
|
set foreign_key_checks = 1;
|
|
|
|
connection con2;
|
|
commit;
|
|
set foreign_key_checks = 1;
|
|
|
|
select * from parent order by 1,2,3;
|
|
select * from child order by 1,2,3;
|
|
|
|
delete from child;
|
|
delete from parent;
|
|
|
|
#
|
|
# Test that unique constraint checking is
|
|
# not affected by setting of foreign_key_checks.
|
|
#
|
|
|
|
set foreign_key_checks = 0;
|
|
insert into parent values (1,1,1);
|
|
--error ER_DUP_ENTRY
|
|
insert into parent values (1,1,1);
|
|
--error ER_DUP_ENTRY
|
|
insert into parent values (2,1,1);
|
|
|
|
delete from child;
|
|
delete from parent;
|
|
|
|
#
|
|
# Test deferred constraint checking.
|
|
# The setting of foreign_key_checks when
|
|
# when the operation was executed is what
|
|
# should determine if checks are made at commit.
|
|
#
|
|
insert into parent values (1,1,1);
|
|
insert into child values (1,1,1);
|
|
|
|
set ndb_deferred_constraints = 1;
|
|
set foreign_key_checks = 1;
|
|
|
|
set foreign_key_checks = 0;
|
|
begin;
|
|
set foreign_key_checks = 1;
|
|
insert into child values (2,2,2);
|
|
--error ER_NO_REFERENCED_ROW_2
|
|
commit;
|
|
|
|
begin;
|
|
insert into child values (2,2,2);
|
|
set foreign_key_checks = 0;
|
|
--error ER_NO_REFERENCED_ROW_2
|
|
commit;
|
|
|
|
begin;
|
|
insert into child values (2,2,2);
|
|
set foreign_key_checks = 1;
|
|
commit;
|
|
|
|
begin;
|
|
insert into child values (3,3,3);
|
|
insert into child values (4,4,4);
|
|
set foreign_key_checks = 0;
|
|
insert into parent values (3,3,3);
|
|
--error ER_NO_REFERENCED_ROW_2
|
|
commit;
|
|
|
|
set foreign_key_checks = 1;
|
|
|
|
begin;
|
|
insert into child values (3,3,3);
|
|
set foreign_key_checks = 0;
|
|
insert into parent values (3,3,3);
|
|
insert into child values (4,4,4);
|
|
commit;
|
|
|
|
set foreign_key_checks = 1;
|
|
|
|
set ndb_deferred_constraints = 0;
|
|
|
|
alter table child drop foreign key fkname;
|
|
|
|
delete from child;
|
|
delete from parent;
|
|
|
|
#
|
|
# Test FK no action (which behaves as deferred restrict)
|
|
#
|
|
|
|
alter table child add constraint fkname foreign key (a) references parent(a) on delete no action on update no action;
|
|
|
|
insert into parent values (1,1,1);
|
|
insert into child values (1,1,1);
|
|
|
|
--error ER_NO_REFERENCED_ROW_2
|
|
insert into child values (2,2,2);
|
|
insert into parent values (2,2,2);
|
|
insert into child values (2,2,2);
|
|
begin;
|
|
update parent set a = a + 2;
|
|
--error ER_ROW_IS_REFERENCED_2
|
|
commit;
|
|
|
|
select * from parent order by 1,2,3;
|
|
select * from child order by 1,2,3;
|
|
|
|
--echo # Disabling foreign key checks
|
|
set foreign_key_checks = 0;
|
|
|
|
insert into child values (3,3,3);
|
|
insert into parent values (3,3,3);
|
|
begin;
|
|
update parent set a = a + 3;
|
|
commit;
|
|
|
|
select * from parent order by 1,2,3;
|
|
select * from child order by 1,2,3;
|
|
|
|
set foreign_key_checks = 1;
|
|
|
|
alter table child drop foreign key fkname;
|
|
|
|
delete from child;
|
|
delete from parent;
|
|
|
|
#
|
|
# Test skipping of DELETE cascade
|
|
#
|
|
|
|
alter table child add constraint fkname foreign key (a) references parent (a) on delete cascade on update restrict;
|
|
|
|
create table grandchild (
|
|
a int primary key auto_increment,
|
|
b int not null,
|
|
c int not null,
|
|
unique(b) using hash,
|
|
index(c)) engine = ndb;
|
|
|
|
alter table grandchild add constraint fkname_child foreign key (a) references child (a) on delete cascade on update restrict;
|
|
|
|
insert into parent values (1,1,1),(2,2,2);
|
|
insert into child values (1,1,1),(2,2,2);
|
|
insert into grandchild values (1,1,1),(2,2,2);
|
|
|
|
begin;
|
|
delete from parent where a = 1;
|
|
select * from child order by 1,2,3;
|
|
select * from grandchild order by 1,2,3;
|
|
commit;
|
|
|
|
--echo # Disabling foreign key checks
|
|
set foreign_key_checks = 0;
|
|
|
|
begin;
|
|
delete from parent where a = 2;
|
|
select * from child order by 1,2,3;
|
|
select * from grandchild order by 1,2,3;
|
|
commit;
|
|
|
|
set foreign_key_checks = 1;
|
|
|
|
alter table child drop foreign key fkname;
|
|
alter table grandchild drop foreign key fkname_child;
|
|
|
|
delete from grandchild;
|
|
delete from child;
|
|
delete from parent;
|
|
|
|
#
|
|
# Test skipping of UPDATE cascade
|
|
#
|
|
|
|
alter table child add constraint fkname foreign key (b) references parent(b) on delete restrict on update cascade;
|
|
|
|
alter table grandchild add constraint fkname_child foreign key (b) references child(b) on delete restrict on update cascade;
|
|
|
|
insert into parent values (1,1,1), (2,2,2);
|
|
insert into child values (1,1,1),(2,2,2);
|
|
insert into grandchild values (1,1,1),(2,2,2);
|
|
|
|
begin;
|
|
update parent set b = 3 where a = 1;
|
|
select * from child order by 1,2,3;
|
|
select * from grandchild order by 1,2,3;
|
|
rollback;
|
|
|
|
--echo # Disabling foreign key checks
|
|
set foreign_key_checks = 0;
|
|
|
|
begin;
|
|
update parent set b = 3 where a = 1;
|
|
select * from child order by 1,2,3;
|
|
select * from grandchild order by 1,2,3;
|
|
rollback;
|
|
|
|
set foreign_key_checks = 1;
|
|
|
|
alter table child drop foreign key fkname;
|
|
alter table grandchild drop foreign key fkname_child;
|
|
|
|
delete from grandchild;
|
|
delete from child;
|
|
delete from parent;
|
|
|
|
#
|
|
# Test DELETE cascade deferred
|
|
#
|
|
|
|
alter table child add constraint fkname foreign key (a) references parent (a) on delete cascade on update restrict;
|
|
|
|
alter table grandchild add constraint fkname_child foreign key (a) references child (a) on delete cascade on update restrict;
|
|
|
|
set ndb_deferred_constraints = 1;
|
|
|
|
insert into parent values (1,1,1), (2,2,2);
|
|
insert into child values (1,1,1),(2,2,2);
|
|
insert into grandchild values (1,1,1),(2,2,2);
|
|
|
|
begin;
|
|
--echo # Disabling foreign key checks
|
|
set foreign_key_checks = 0;
|
|
delete from parent where a = 1;
|
|
select * from child order by 1,2,3;
|
|
select * from grandchild order by 1,2,3;
|
|
commit;
|
|
|
|
select * from child order by 1,2,3;
|
|
select * from grandchild order by 1,2,3;
|
|
|
|
delete from grandchild;
|
|
delete from child;
|
|
delete from parent;
|
|
|
|
insert into parent values (1,1,1), (2,2,2);
|
|
insert into child values (1,1,1),(2,2,2);
|
|
insert into grandchild values (1,1,1),(2,2,2);
|
|
|
|
set foreign_key_checks = 1;
|
|
|
|
begin;
|
|
delete from parent where a = 1;
|
|
--echo # Disabling foreign key checks
|
|
set foreign_key_checks = 0;
|
|
select * from child order by 1,2,3;
|
|
select * from grandchild order by 1,2,3;
|
|
commit;
|
|
|
|
select * from child order by 1,2,3;
|
|
select * from grandchild order by 1,2,3;
|
|
|
|
set ndb_deferred_constraints = 0;
|
|
set foreign_key_checks = 1;
|
|
|
|
alter table child drop foreign key fkname;
|
|
alter table grandchild drop foreign key fkname_child;
|
|
|
|
delete from grandchild;
|
|
delete from child;
|
|
delete from parent;
|
|
|
|
#
|
|
# Test UPDATE cascade deferred
|
|
#
|
|
|
|
alter table child add constraint fkname foreign key (b) references parent(b) on delete restrict on update cascade;
|
|
|
|
alter table grandchild add constraint fkname_child foreign key (b) references child(b) on delete restrict on update cascade;
|
|
|
|
set ndb_deferred_constraints = 1;
|
|
|
|
insert into parent values (1,1,1), (2,2,2);
|
|
insert into child values (1,1,1),(2,2,2);
|
|
insert into grandchild values (1,1,1),(2,2,2);
|
|
|
|
begin;
|
|
--echo # Disabling foreign key checks
|
|
set foreign_key_checks = 0;
|
|
update parent set b = 3 where a = 1;
|
|
select * from child order by 1,2,3;
|
|
select * from grandchild order by 1,2,3;
|
|
commit;
|
|
|
|
select * from child order by 1,2,3;
|
|
select * from grandchild order by 1,2,3;
|
|
|
|
delete from grandchild;
|
|
delete from child;
|
|
delete from parent;
|
|
|
|
insert into parent values (1,1,1), (2,2,2);
|
|
insert into child values (1,1,1),(2,2,2);
|
|
insert into grandchild values (1,1,1),(2,2,2);
|
|
|
|
set foreign_key_checks = 1;
|
|
|
|
begin;
|
|
update parent set b = 3 where a = 1;
|
|
--echo # Disabling foreign key checks
|
|
set foreign_key_checks = 0;
|
|
select * from child order by 1,2,3;
|
|
select * from grandchild order by 1,2,3;
|
|
commit;
|
|
|
|
select * from child order by 1,2,3;
|
|
select * from grandchild order by 1,2,3;
|
|
|
|
set ndb_deferred_constraints = 0;
|
|
set foreign_key_checks = 1;
|
|
|
|
alter table child drop foreign key fkname;
|
|
alter table grandchild drop foreign key fkname_child;
|
|
|
|
delete from grandchild;
|
|
delete from child;
|
|
delete from parent;
|
|
|
|
drop table parent, child, grandchild;
|
|
|
|
#
|
|
# Test that joins are not affected by disabling FKs
|
|
#
|
|
|
|
set @save_ndb_join_pushdown = @@session.ndb_join_pushdown;
|
|
set ndb_join_pushdown = true;
|
|
|
|
create table t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int,
|
|
d int not null,
|
|
primary key (a),
|
|
unique (b),
|
|
unique (c),
|
|
unique (d)
|
|
) engine=ndb;
|
|
|
|
#
|
|
# CREATE TABLE references are not supported
|
|
#
|
|
#create table t2 (
|
|
# a int not null references t1(a) on delete cascade on update restrict,
|
|
# b int not null references t1(b) on delete cascade on update restrict,
|
|
# c int references t1(c) on delete cascade on update restrict,
|
|
# d int references t1(d) on delete cascade on update restrict,
|
|
# primary key (a,b)
|
|
#) engine=ndb;
|
|
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
c int,
|
|
d int,
|
|
primary key (a)
|
|
) engine=ndb;
|
|
|
|
alter table t2 add constraint fa foreign key (a) references t1 (a) on delete cascade on update restrict;
|
|
|
|
alter table t2 add constraint fb foreign key (b) references t1 (b) on delete cascade on update restrict;
|
|
|
|
alter table t2 add constraint fc foreign key (c) references t1 (c) on delete cascade on update restrict;
|
|
|
|
alter table t2 add constraint fd foreign key (d) references t1 (d) on delete cascade on update restrict;
|
|
|
|
insert into t1 values
|
|
(1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5), (6,6,6,6);
|
|
|
|
insert into t2 values
|
|
(1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5), (6,6,6,6);
|
|
|
|
--error ER_ROW_IS_REFERENCED_2
|
|
update t1 set b = 17 where c > 5;
|
|
|
|
--error ER_ROW_IS_REFERENCED_2
|
|
update t1 set c = null where c = 2;
|
|
|
|
--sorted_result
|
|
select *
|
|
from t1,t2
|
|
where t2.a = t1.b and t2.b = t1.c;
|
|
|
|
delete from t1 where d = 3;
|
|
select * from t2 order by a;
|
|
|
|
--sorted_result
|
|
select *
|
|
from t1,t2
|
|
where t2.a = t1.b + 1;
|
|
|
|
insert into t1 values (3,3,3,3);
|
|
|
|
insert into t2 values (3,3,3,3);
|
|
|
|
--echo # Disabling foreign key checks
|
|
set foreign_key_checks = 0;
|
|
|
|
update t1 set b = 17 where c > 5;
|
|
|
|
update t1 set c = null where c = 2;
|
|
|
|
--sorted_result
|
|
select *
|
|
from t1,t2
|
|
where t2.a = t1.b and t2.b = t1.c;
|
|
|
|
select * from t2 order by a;
|
|
delete from t1 where d = 3;
|
|
select * from t2 order by a;
|
|
|
|
--sorted_result
|
|
select *
|
|
from t1,t2
|
|
where t2.a = t1.b + 1;
|
|
|
|
drop table t2, t1;
|
|
|
|
set ndb_join_pushdown = @save_ndb_join_pushdown;
|
|
|
|
set foreign_key_checks = 1;
|
|
|
|
--echo #
|
|
--echo # Bug#25063506 : Updating parent row cascades to child but not to grandchild
|
|
--echo #
|
|
|
|
--echo # Setup tables
|
|
create table parent(
|
|
id int primary key auto_increment,
|
|
val1 int unique,
|
|
val2 int
|
|
) engine ndb;
|
|
|
|
create table child(
|
|
ref int unique,
|
|
foreign key fk1(ref) references parent(val1) on update cascade
|
|
) engine ndb;
|
|
|
|
create table grandchild(
|
|
ref int,
|
|
foreign key fk2(ref) references child(ref) on update cascade
|
|
) engine ndb;
|
|
|
|
insert into parent(val1, val2) values(10, 100), (20, 400), (30, 900);
|
|
insert into child(ref) values(10), (20), (30);
|
|
insert into grandchild(ref) values(10), (20), (30);
|
|
|
|
--echo # Change ndb_optimized_node_selection to 2
|
|
--echo # so that the following queries goes to the same node
|
|
set ndb_optimized_node_selection=2;
|
|
|
|
--echo # Perform query with the foreign_key_checks off
|
|
--echo # This transaction will use an indexOperation internally and release it when done
|
|
set foreign_key_checks = 0;
|
|
delete from parent where val1 = 100;
|
|
set foreign_key_checks = 1;
|
|
|
|
begin;
|
|
|
|
--echo # The following transaction would reuse the indexOperation
|
|
--echo # when the update is cascaded to grandchild
|
|
update parent set val1 = 40 where val2 = 100;
|
|
|
|
--echo # Print tuples to check if they are consistent
|
|
select * from parent order by id;
|
|
select * from child order by ref;
|
|
select * from grandchild order by ref;
|
|
|
|
rollback;
|
|
|
|
--echo # Cleanup
|
|
set ndb_optimized_node_selection=3;
|
|
drop table grandchild, child, parent;
|