-- 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