133 lines
3.5 KiB
Plaintext
133 lines
3.5 KiB
Plaintext
-- source include/have_multi_ndb.inc
|
|
|
|
--disable_warnings
|
|
DROP TABLE IF EXISTS t1;
|
|
--enable_warnings
|
|
|
|
connection server1;
|
|
create table t1 ( a int primary key, b varchar(10), c varchar(10), index (b) )
|
|
engine=ndb;
|
|
insert into t1 values (1,'one','one'), (2,'two','two'), (3,'three','three');
|
|
create index c on t1(c);
|
|
connection server2;
|
|
show indexes from t1;
|
|
select * from t1 where c = 'two';
|
|
connection server1;
|
|
alter table t1 drop index c;
|
|
connection server2;
|
|
show indexes from t1;
|
|
select * from t1 where c = 'two';
|
|
connection server1;
|
|
drop table t1;
|
|
|
|
connection server1;
|
|
create table t3 (a int primary key) engine=ndbcluster;
|
|
|
|
connection server2;
|
|
insert into t3 values (1);
|
|
|
|
connection server1;
|
|
alter table t3 rename t4;
|
|
|
|
connection server2;
|
|
# with rbr the below will not work as the "alter" event
|
|
# explicitly invalidates the dictionary cache.
|
|
## This should work as transaction is ongoing...
|
|
#delete from t3;
|
|
#insert into t3 values (1);
|
|
|
|
# This should fail as its a new transaction
|
|
--error 1146
|
|
select * from t3;
|
|
select * from t4;
|
|
drop table t4;
|
|
show tables;
|
|
connection server1;
|
|
|
|
--disable_warnings
|
|
drop table if exists t6;
|
|
--enable_warnings
|
|
create table t6 (a int primary key ,b int) engine=ndbcluster;
|
|
insert into t6 values(1,12), (2,12);
|
|
--error 1169
|
|
alter table t6 add unique key ui_t(b);
|
|
delete from t6;
|
|
--disable_result_log
|
|
insert into t6 values(1,12), (2,12);
|
|
--enable_result_log
|
|
--error 1169
|
|
alter table t6 add unique key ui_t(b);
|
|
delete from t6;
|
|
drop table t6;
|
|
|
|
--echo #
|
|
--echo # Bug#22173891 : CIRCULAR FOREIGN KEYS + INDEX = FAILURE
|
|
--echo #
|
|
|
|
--echo #create table t1
|
|
create table t1(
|
|
a int primary key,
|
|
b int unique key
|
|
)engine = ndb;
|
|
|
|
--echo #create table t2 with fks referring columns from t1
|
|
create table t2(
|
|
a int,
|
|
b int,
|
|
c int unique key,
|
|
constraint fk1 foreign key (a) references t1(a),
|
|
constraint fk2 foreign key (b) references t1(b)
|
|
)engine = ndb;
|
|
|
|
--echo #t2 now has additional keys on col `a` and `b` to support fks.
|
|
--disable_warnings
|
|
show create table t2;
|
|
--enable_warnings
|
|
|
|
--echo #create an index on t2(a) using 'create index'
|
|
create index id1 on t2(a);
|
|
|
|
--echo #verify that the implicit key on col `a` is gone now and fk is intact.
|
|
--disable_warnings
|
|
show create table t2;
|
|
--enable_warnings
|
|
insert into t1 values (1,10), (2,20), (3,30), (4,40);
|
|
insert into t2 values (1,10,100), (2,20,200);
|
|
# Mask the column names and fk names, as the following query violates
|
|
# multiple FK constraints and the fk getting printed in the error might
|
|
# vary based on which FK trigger gets executed first.
|
|
--replace_regex /fk1/fk_name/ /fk2/fk_name/ /`a`/`col_name`/ /`b`/`col_name`/
|
|
--error ER_NO_REFERENCED_ROW_2
|
|
insert into t2 values (5,50,500);
|
|
|
|
--echo #create a table t3 with an fk refering to t2(c)
|
|
create table t3(
|
|
a int,
|
|
constraint fk3 foreign key (a) references t2(c)
|
|
)engine = ndb;
|
|
|
|
--echo #create an index on col `b` on t2
|
|
create unique index id2 on t2(b);
|
|
|
|
--echo #verify that the implicit key on col `b` is also gone now.
|
|
--disable_warnings
|
|
show create table t2;
|
|
--enable_warnings
|
|
|
|
--echo #verify that the fks are intact in t2 and t3
|
|
insert into t2 values (3,30,300), (4,40,400);
|
|
# Mask the column names and fk names, as the following query violates
|
|
# multiple FK constraints and the fk getting printed in the error might
|
|
# vary based on which FK trigger gets executed first.
|
|
--replace_regex /fk1/fk_name/ /fk2/fk_name/ /`a`/`col_name`/ /`b`/`col_name`/
|
|
--error ER_NO_REFERENCED_ROW_2
|
|
insert into t2 values (5,50,500);
|
|
insert into t3 values (100), (200);
|
|
--error ER_NO_REFERENCED_ROW_2
|
|
insert into t3 values (500);
|
|
|
|
--echo #cleanup
|
|
drop table t3, t2, t1;
|
|
|
|
# end of tests
|