1071 lines
25 KiB
Plaintext
1071 lines
25 KiB
Plaintext
-- source include/have_ndb.inc
|
|
-- source suite/ndb/include/backup_restore_setup.inc
|
|
|
|
-- disable_warnings
|
|
drop table if exists t2, t1;
|
|
-- enable_warnings
|
|
|
|
# BUG 14095785 - DROPPING FK WITHOUT LABEL CAUSED MYSQLD TO CRASH
|
|
-- echo # bug#14095785
|
|
|
|
create table t1 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
unique key using hash (b)
|
|
) engine ndb;
|
|
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b)
|
|
) engine ndb;
|
|
|
|
alter table t2
|
|
add constraint foreign key (b) references t1 (b);
|
|
|
|
-- error ER_PARSE_ERROR
|
|
alter table t2
|
|
drop foreign key;
|
|
|
|
drop table t2, t1;
|
|
|
|
# BUG 14095838 - CHANGING PK CAUSES CASCADE FK TO DELETE ROWS
|
|
-- echo # bug#14095838
|
|
|
|
create table t1 (
|
|
a int not null,
|
|
primary key using hash (a)
|
|
) engine ndb;
|
|
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b)
|
|
) engine ndb;
|
|
|
|
-- error ER_CANNOT_ADD_FOREIGN
|
|
alter table t2
|
|
add constraint fk1 foreign key (b) references t1 (a)
|
|
on delete cascade on update cascade;
|
|
show warnings;
|
|
|
|
alter table t2
|
|
add constraint fk1 foreign key (b) references t1 (a)
|
|
on delete cascade on update restrict;
|
|
|
|
drop table t2, t1;
|
|
|
|
# Bug #14195170 FAILING CREATE TABLE WITH FK CONSTRAINTS LEAVES ORPHANED .FRM
|
|
--echo # bug#14195170
|
|
|
|
CREATE TABLE customer (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=NDB;
|
|
|
|
CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
|
|
price DECIMAL, PRIMARY KEY(category, id)) ENGINE=NDB;
|
|
|
|
--error ER_CANNOT_ADD_FOREIGN
|
|
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_id INT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id)) ENGINE=ndb;
|
|
show warnings;
|
|
|
|
--error ER_CANNOT_ADD_FOREIGN
|
|
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_id INT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id)) ENGINE=ndb;
|
|
show warnings;
|
|
|
|
DROP TABLE customer, product;
|
|
|
|
# Bug #14124623 - CREATING TABLE WITH FK CONSTRAINT FAILS AND LEAVES DB CORRUPT
|
|
--echo # bug#14124623
|
|
|
|
CREATE TABLE counties (county VARCHAR(30) NOT NULL PRIMARY KEY,
|
|
country VARCHAR(30), INDEX county_country_index(country)) ENGINE=ndb;
|
|
|
|
INSERT INTO counties VALUES
|
|
('Berkshire','England'),('Shropshire','England'),('Oxfordshire','England'),('Buckinghamshire','England');
|
|
|
|
CREATE TABLE towns (town VARCHAR(30) NOT NULL PRIMARY KEY, county
|
|
VARCHAR(30), INDEX county_county_index (county), CONSTRAINT county_town
|
|
FOREIGN KEY (county) REFERENCES counties(county) ON DELETE RESTRICT ON UPDATE
|
|
RESTRICT) ENGINE=ndb;
|
|
|
|
INSERT INTO towns VALUES
|
|
('Maidenhead','Berkshire'),('Reading','Berkshire'),('Shrewsbury','Shropshire')
|
|
,('Oxford','Oxfordshire');
|
|
|
|
--error ER_FK_NO_INDEX_PARENT
|
|
CREATE TABLE languages (language VARCHAR(30) NOT NULL PRIMARY KEY,
|
|
country VARCHAR(30), INDEX lang_country_index(country), CONSTRAINT
|
|
country_language FOREIGN KEY (country) REFERENCES counties(country) ON DELETE
|
|
RESTRICT ON UPDATE RESTRICT) engine=ndb;
|
|
|
|
--error ER_FK_NO_INDEX_PARENT
|
|
CREATE TABLE languages (language VARCHAR(30) NOT NULL PRIMARY KEY,
|
|
country VARCHAR(30), INDEX lang_country_index(country), CONSTRAINT
|
|
country_language FOREIGN KEY (country) REFERENCES counties(country) ON DELETE
|
|
RESTRICT ON UPDATE RESTRICT) engine=ndb;
|
|
|
|
CREATE TABLE languages (id INT NOT NULL PRIMARY KEY) engine=ndb;
|
|
|
|
drop table languages, towns, counties;
|
|
|
|
--echo # test: child vs parent column types
|
|
|
|
create table t1 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
unique key using hash (b)
|
|
) engine ndb;
|
|
|
|
--error ER_FK_INCOMPATIBLE_COLUMNS
|
|
create table t2 (
|
|
a int not null,
|
|
b char(20) not null,
|
|
primary key using hash (a),
|
|
key (b),
|
|
constraint fk1 foreign key (b) references t1 (b)
|
|
) engine ndb;
|
|
|
|
create table t2 (
|
|
a int not null,
|
|
b char(10) not null,
|
|
primary key using hash (a),
|
|
key (b)
|
|
) engine ndb;
|
|
|
|
--error ER_FK_INCOMPATIBLE_COLUMNS
|
|
alter table t2 algorithm=inplace,
|
|
add constraint fk1 foreign key (b) references t1 (b);
|
|
|
|
drop table t2, t1;
|
|
|
|
--echo # test: parent table not exist
|
|
|
|
--error ER_FK_CANNOT_OPEN_PARENT
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b),
|
|
constraint fk1 foreign key (b) references t1 (b)
|
|
) engine ndb;
|
|
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b)
|
|
) engine ndb;
|
|
|
|
--error ER_FK_CANNOT_OPEN_PARENT
|
|
alter table t2 algorithm=inplace,
|
|
add constraint fk1 foreign key (b) references t1 (b);
|
|
|
|
drop table t2;
|
|
|
|
--echo # test: parent column not exist
|
|
|
|
create table t1 (
|
|
a int not null,
|
|
primary key using hash (a)
|
|
) engine ndb;
|
|
|
|
--error ER_FK_NO_COLUMN_PARENT
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b),
|
|
constraint fk1 foreign key (b) references t1 (b)
|
|
) engine ndb;
|
|
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b)
|
|
) engine ndb;
|
|
|
|
--error ER_FK_NO_COLUMN_PARENT
|
|
alter table t2 algorithm=inplace,
|
|
add constraint fk1 foreign key (b) references t1 (b);
|
|
|
|
drop table t2, t1;
|
|
|
|
--echo # test: parent key no index
|
|
|
|
create table t1 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a)
|
|
) engine ndb;
|
|
|
|
--error ER_FK_NO_INDEX_PARENT
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b),
|
|
constraint fk1 foreign key (b) references t1 (b)
|
|
) engine ndb;
|
|
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b)
|
|
) engine ndb;
|
|
|
|
--error ER_FK_NO_INDEX_PARENT
|
|
alter table t2 algorithm=inplace,
|
|
add constraint fk1 foreign key (b) references t1 (b);
|
|
|
|
drop table t2, t1;
|
|
|
|
--echo # test: parent key no unique index
|
|
|
|
create table t1 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b)
|
|
) engine ndb;
|
|
|
|
--error ER_FK_NO_INDEX_PARENT
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b),
|
|
constraint fk1 foreign key (b) references t1 (b)
|
|
) engine ndb;
|
|
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b)
|
|
) engine ndb;
|
|
|
|
--error ER_FK_NO_INDEX_PARENT
|
|
alter table t2 algorithm=inplace,
|
|
add constraint fk1 foreign key (b) references t1 (b);
|
|
|
|
drop table t2, t1;
|
|
|
|
--echo # test: child column not exist
|
|
|
|
create table t1 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
unique key using hash (b)
|
|
) engine ndb;
|
|
|
|
--error ER_KEY_COLUMN_DOES_NOT_EXITS
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b),
|
|
constraint fk1 foreign key (c) references t1 (b)
|
|
) engine ndb;
|
|
show warnings;
|
|
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b)
|
|
) engine ndb;
|
|
|
|
--error ER_KEY_COLUMN_DOES_NOT_EXITS
|
|
alter table t2 algorithm=inplace,
|
|
add constraint fk1 foreign key (c) references t1 (b);
|
|
show warnings;
|
|
|
|
drop table t2, t1;
|
|
|
|
--echo # test: child column no index
|
|
--echo # no error - server creates KEY
|
|
|
|
create table t1 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
unique key using hash (b)
|
|
) engine ndb;
|
|
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
constraint fk1 foreign key (b) references t1 (b)
|
|
) engine ndb;
|
|
|
|
drop table t2;
|
|
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a)
|
|
) engine ndb;
|
|
|
|
alter table t2 algorithm=inplace,
|
|
add constraint fk1 foreign key (b) references t1 (b);
|
|
|
|
drop table t2, t1;
|
|
|
|
--echo # test: child vs parent column count
|
|
|
|
create table t1 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key using hash (a),
|
|
unique key using hash (b, c)
|
|
) engine ndb;
|
|
|
|
--error ER_FK_NO_INDEX_PARENT
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b),
|
|
constraint fk1 foreign key (b) references t1 (b)
|
|
) engine ndb;
|
|
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (b)
|
|
) engine ndb;
|
|
|
|
--error ER_FK_NO_INDEX_PARENT
|
|
alter table t2 algorithm=inplace,
|
|
add constraint fk1 foreign key (b) references t1 (b);
|
|
|
|
drop table t2, t1;
|
|
|
|
# Bug 16246874 - MYSQLD CRASH WHEN QUERYING KEY_COLUMN_USAGE IF NDB FOREIGN KEYS DEFINED
|
|
--echo # bug#16246874
|
|
create table t1 (
|
|
id int primary key
|
|
) engine=ndb;
|
|
|
|
create table t2 (
|
|
id int,
|
|
foreign key(id) references t1(id)
|
|
) engine=ndb;
|
|
|
|
# Run failing query, discard result since it will vary
|
|
--disable_result_log
|
|
select * from information_schema.key_column_usage;
|
|
--enable_result_log
|
|
|
|
# Show that t2 references test.t1.id
|
|
select REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
|
|
from information_schema.key_column_usage
|
|
where TABLE_SCHEMA = 'test' and TABLE_NAME = 't2';
|
|
|
|
drop table t2, t1;
|
|
|
|
# Bug#16275509 IMPLEMENT HA_NDBCLUSTER::CAN_SWITCH_ENGINES
|
|
|
|
--echo # bug#16275509 - case: alter engine from ndb
|
|
|
|
create table t1 (
|
|
a int not null,
|
|
primary key using hash (a)
|
|
) engine=ndb;
|
|
|
|
create table t2 (
|
|
a int not null,
|
|
primary key using hash (a),
|
|
foreign key fk1 (a) references t1 (a)
|
|
) engine=ndb;
|
|
|
|
--error ER_FK_CANNOT_CHANGE_ENGINE
|
|
alter table t1 engine=innodb;
|
|
|
|
--error ER_FK_CANNOT_CHANGE_ENGINE
|
|
alter table t2 engine=innodb;
|
|
|
|
drop table t2, t1;
|
|
|
|
create table t1 (
|
|
a int primary key,
|
|
b int,
|
|
key (b),
|
|
foreign key fk1 (b) references t1 (a)
|
|
) engine=ndb;
|
|
|
|
--error ER_FK_CANNOT_CHANGE_ENGINE
|
|
alter table t1 engine=innodb;
|
|
|
|
drop table t1;
|
|
|
|
# unlike innodb
|
|
--echo # bug#16275509 - case: allow FKs on partitioned
|
|
|
|
set new=on;
|
|
|
|
# partition parent
|
|
|
|
create table t1 (
|
|
a int primary key
|
|
) engine=ndb
|
|
partition by hash (a) partitions 5;
|
|
|
|
create table t2 (
|
|
a int primary key,
|
|
foreign key fk1 (a) references t1 (a)
|
|
) engine=ndb;
|
|
|
|
drop table t2, t1;
|
|
|
|
create table t1 (
|
|
a int primary key
|
|
) engine=ndb;
|
|
|
|
create table t2 (
|
|
a int primary key,
|
|
foreign key fk1 (a) references t1 (a)
|
|
) engine=ndb;
|
|
|
|
alter table t1
|
|
partition by hash (a) partitions 5;
|
|
|
|
drop table t2, t1;
|
|
|
|
# partition child
|
|
|
|
create table t1 (
|
|
a int primary key
|
|
) engine=ndb;
|
|
|
|
create table t2 (
|
|
a int primary key,
|
|
foreign key fk1 (a) references t1 (a)
|
|
) engine=ndb
|
|
partition by hash (a) partitions 5;
|
|
|
|
drop table t2;
|
|
|
|
create table t2 (
|
|
a int primary key,
|
|
foreign key fk1 (a) references t1 (a)
|
|
) engine=ndb;
|
|
|
|
alter table t2
|
|
partition by hash (a) partitions 5;
|
|
|
|
drop table t2, t1;
|
|
|
|
# Bug#16275684 - IMPLEMENT HA_NDBCLUSTER::GET_PARENT_FOREIGN_KEY_LIST
|
|
|
|
# truncate of NDB table does not hit get_parent_foreign_key_list().
|
|
# alter child FK to not null is tested in ndb_fk_alter.test.
|
|
|
|
--echo # bug#16275684 - case: alter FK parent column
|
|
|
|
create table t1 (
|
|
a int primary key,
|
|
b enum('a','b','c') not null,
|
|
unique key (b)
|
|
) engine=ndb;
|
|
|
|
create table t2 (
|
|
a int primary key,
|
|
b enum('a','b','c') not null,
|
|
key (b),
|
|
constraint fk1 foreign key (b) references t1 (b)
|
|
) engine=ndb;
|
|
|
|
insert into t1 values (1,'a'),(2,'b');
|
|
insert into t2 values (1,'a'),(2,'a'),(3,'b');
|
|
|
|
--error ER_FK_COLUMN_CANNOT_CHANGE_CHILD
|
|
alter table t1 modify column b enum('a','b') not null;
|
|
alter table t2 drop foreign key fk1;
|
|
alter table t1 modify column b enum('a','b') not null;
|
|
--disable_warnings
|
|
show create table t1;
|
|
--enable_warnings
|
|
|
|
select * from t1 order by a;
|
|
select * from t2 order by a;
|
|
|
|
drop table t2, t1;
|
|
|
|
# Bug#16286164 - DROP/ALTER TABLE NOT PROTECTED FROM BREAKING NDB FK CONSTRAINTS
|
|
|
|
--echo # bug#16286164 - case: drop when referenced by different table
|
|
let $i=2;
|
|
while ($i)
|
|
{
|
|
eval set @i=$i;
|
|
let $engine=`select if(@i=1,'ndb','innodb')`;
|
|
|
|
eval create table t1 (
|
|
a int not null,
|
|
primary key using hash (a)
|
|
) engine=$engine;
|
|
|
|
eval create table t2 (
|
|
a int not null,
|
|
b int,
|
|
primary key using hash (a),
|
|
key (b),
|
|
constraint fk1 foreign key (b) references t1 (a)
|
|
) engine=$engine;
|
|
|
|
--error ER_FK_CANNOT_DROP_PARENT
|
|
drop table t1;
|
|
|
|
drop table t2, t1;
|
|
dec $i;
|
|
}
|
|
|
|
--echo # bug#16286164 - case: drop when referenced by same table
|
|
let $i=2;
|
|
while ($i)
|
|
{
|
|
eval set @i=$i;
|
|
let $engine=`select if(@i=1,'ndb','innodb')`;
|
|
|
|
eval create table t1 (
|
|
a int not null,
|
|
b int,
|
|
primary key using hash (a),
|
|
key (b),
|
|
constraint fk1 foreign key (b) references t1 (a)
|
|
) engine=$engine;
|
|
|
|
drop table t1;
|
|
|
|
dec $i;
|
|
}
|
|
|
|
-- echo # BugXXZ - multi part unique index parent
|
|
|
|
create table t1 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
unique key using hash (a,b)
|
|
) engine ndb;
|
|
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
primary key using hash (a),
|
|
key (a,b),
|
|
foreign key fk1 (a,b) references t1 (a,b)
|
|
) engine ndb;
|
|
|
|
drop table t2, t1;
|
|
|
|
--echo # child-part-key
|
|
|
|
create table t1 (
|
|
a int not null,
|
|
primary key using hash (a)
|
|
) engine ndb;
|
|
|
|
insert into t1 values (1),(2);
|
|
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key using hash (a),
|
|
key xbc (b,c),
|
|
constraint fk1 foreign key (b) references t1 (a)
|
|
) engine ndb;
|
|
|
|
# uses index xbc
|
|
--disable_warnings
|
|
show create table t2;
|
|
--enable_warnings
|
|
|
|
insert into t2 (a,b,c) values (1,1,11),(2,2,21),(3,2,22);
|
|
--error ER_NO_REFERENCED_ROW_2
|
|
insert into t2 (a,b,c) values (4,3,33);
|
|
--error ER_ROW_IS_REFERENCED_2
|
|
delete from t1 where a = 1;
|
|
|
|
drop table t2;
|
|
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key using hash (a),
|
|
key xbc (b,c),
|
|
constraint fk1 foreign key (c) references t1 (a)
|
|
) engine ndb;
|
|
|
|
# creates index fk1 (c)
|
|
--disable_warnings
|
|
show create table t2;
|
|
--enable_warnings
|
|
|
|
insert into t2 (a,c,b) values (1,1,11),(2,2,21),(3,2,22);
|
|
--error ER_NO_REFERENCED_ROW_2
|
|
insert into t2 (a,c,b) values (4,3,33);
|
|
--error ER_ROW_IS_REFERENCED_2
|
|
delete from t1 where a = 1;
|
|
|
|
drop table t2;
|
|
|
|
# partial hash key does not work
|
|
# XXX maybe should create index fk1 (b)
|
|
--error ER_FK_NO_INDEX_CHILD
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key using hash (a),
|
|
unique key xbc (b,c) using hash,
|
|
constraint fk1 foreign key (b) references t1 (a)
|
|
) engine ndb;
|
|
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
primary key using hash (a),
|
|
unique key xbc (b,c) using hash,
|
|
constraint fk1 foreign key (c) references t1 (a)
|
|
) engine ndb;
|
|
|
|
# creates index fk1 (c)
|
|
--disable_warnings
|
|
show create table t2;
|
|
--enable_warnings
|
|
|
|
insert into t2 (a,c,b) values (1,1,11),(2,2,21),(3,2,22);
|
|
--error ER_NO_REFERENCED_ROW_2
|
|
insert into t2 (a,c,b) values (4,3,33);
|
|
--error ER_ROW_IS_REFERENCED_2
|
|
delete from t1 where a = 1;
|
|
|
|
drop table t2, t1;
|
|
|
|
#
|
|
# failed create table pollutes dict cache
|
|
#
|
|
create table t1_parent (pk1 char(255) primary key) charset=latin1, engine = ndb;
|
|
|
|
--error ER_CANT_CREATE_TABLE
|
|
create table t1 (
|
|
c50 char(255) column_format fixed, c49 char(255) column_format fixed, c48 char(255) column_format fixed, c47 char(255) column_format fixed, c46 char(255) column_format fixed,
|
|
c45 char(255) column_format fixed, c44 char(255) column_format fixed, c43 char(255) column_format fixed, c42 char(255) column_format fixed, c41 char(255) column_format fixed,
|
|
c40 char(255) column_format fixed, c39 char(255) column_format fixed, c38 char(255) column_format fixed, c37 char(255) column_format fixed, c36 char(255) column_format fixed,
|
|
c35 char(255) column_format fixed, c34 char(255) column_format fixed, c33 char(255) column_format fixed, c32 char(255) column_format fixed, c31 char(255) column_format fixed,
|
|
c30 char(255) column_format fixed, c29 char(255) column_format fixed, c28 char(255) column_format fixed, c27 char(255) column_format fixed, c26 char(255) column_format fixed,
|
|
c25 char(255) column_format fixed, c24 char(255) column_format fixed, c23 char(255) column_format fixed, c22 char(255) column_format fixed, c21 char(255) column_format fixed,
|
|
c20 char(255) column_format fixed, c19 char(255) column_format fixed, c18 char(255) column_format fixed, c17 char(255) column_format fixed, c16 char(255) column_format fixed,
|
|
c15 char(255) column_format fixed, c14 char(255) column_format fixed, c13 char(255) column_format fixed, c12 char(255) column_format fixed, c11 char(255) column_format fixed,
|
|
c10 char(255) column_format fixed, c9 char(255) column_format fixed, c8 char(255) column_format fixed, c7 char(255) column_format fixed, c6 char(255) column_format fixed,
|
|
c5 char(255) column_format fixed, c4 char(255) column_format fixed, c3 char(255) column_format fixed, c2 char(255) column_format fixed, c1 char(255) column_format fixed,
|
|
primary key using hash(c1)
|
|
,foreign key(c1) references t1_parent(pk1)
|
|
) charset=latin1, engine=ndb;
|
|
SHOW WARNINGS;
|
|
|
|
# Fails here with "Table 'test.t1' doesn't exist"
|
|
create table t1(a int primary key) engine = ndb;
|
|
drop table t1, t1_parent;
|
|
|
|
--echo # bug#17591531 - ERROR ON DROP INDEX WHEN REFERENCING FOREIGN KEYS
|
|
|
|
create table t1 (
|
|
a int not null,
|
|
b int not null,
|
|
unique key t1x1 (b) using hash,
|
|
primary key using hash (a)
|
|
) engine=ndb;
|
|
|
|
create table t2 (
|
|
a int not null,
|
|
b int not null,
|
|
c int not null,
|
|
key (c),
|
|
key t2x1 (b),
|
|
primary key using hash (a),
|
|
constraint t2fk1 foreign key (b) references t1 (b)
|
|
) engine=ndb;
|
|
|
|
--echo # try to drop indexes
|
|
--error ER_DROP_INDEX_FK
|
|
alter table t1 drop index t1x1;
|
|
--error ER_DROP_INDEX_FK
|
|
alter table t2 drop index t2x1;
|
|
|
|
insert into t1 values (1,10),(2,20),(3,30);
|
|
insert into t2 values (1,10,100),(2,20,200),(3,30,300);
|
|
--error ER_NO_REFERENCED_ROW_2
|
|
insert into t2 values (4,40,400);
|
|
--error ER_ROW_IS_REFERENCED_2
|
|
delete from t1 where a=1;
|
|
|
|
drop table t2, t1;
|
|
|
|
--echo # bug#17232212 - NDB DROP NON-EXISTENT FK SUCCEEDS
|
|
# succeeds silently before fixes
|
|
|
|
create table t1 (
|
|
a int primary key
|
|
) engine=ndb;
|
|
|
|
--echo # algorithm=inplace
|
|
# innodb gives error 1091
|
|
|
|
create table t2 (
|
|
a int primary key,
|
|
b int,
|
|
c int,
|
|
key xb (b),
|
|
key xc (c),
|
|
constraint fkb foreign key (b) references t1 (a),
|
|
constraint fkc foreign key (c) references t1 (a)
|
|
) engine=ndb;
|
|
|
|
--error ER_CANT_DROP_FIELD_OR_KEY
|
|
alter table t2
|
|
drop foreign key nosuchfk,
|
|
algorithm=inplace;
|
|
|
|
--disable_warnings
|
|
show create table t2;
|
|
--enable_warnings
|
|
|
|
--error ER_CANT_DROP_FIELD_OR_KEY
|
|
alter table t2
|
|
drop foreign key fkb,
|
|
drop foreign key nosuchfk,
|
|
drop foreign key fkc,
|
|
algorithm=inplace;
|
|
|
|
--disable_warnings
|
|
show create table t2;
|
|
--enable_warnings
|
|
|
|
drop table t2;
|
|
|
|
--echo # algorithm=copy
|
|
# innodb gives non-sense errors 1025 / 152
|
|
|
|
create table t2 (
|
|
a int primary key,
|
|
b int,
|
|
c int,
|
|
key xb (b),
|
|
key xc (c),
|
|
constraint fkb foreign key (b) references t1 (a),
|
|
constraint fkc foreign key (c) references t1 (a)
|
|
) engine=ndb;
|
|
|
|
--error ER_CANT_DROP_FIELD_OR_KEY
|
|
alter table t2
|
|
drop foreign key nosuchfk,
|
|
algorithm=copy;
|
|
|
|
--disable_warnings
|
|
show create table t2;
|
|
--enable_warnings
|
|
|
|
--error ER_CANT_DROP_FIELD_OR_KEY
|
|
alter table t2
|
|
drop foreign key fkb,
|
|
drop foreign key nosuchfk,
|
|
drop foreign key fkc,
|
|
algorithm=copy;
|
|
|
|
--disable_warnings
|
|
show create table t2;
|
|
--enable_warnings
|
|
|
|
drop table t2, t1;
|
|
|
|
--echo # bug#18662582 - NDB_FK_DISABLE.TEST FAILS TO DROP FOREIGN KEY
|
|
|
|
create table t1 (
|
|
a int, primary key using hash (a)
|
|
) engine = ndb;
|
|
|
|
create table t2 (
|
|
a int, primary key using hash (a)
|
|
) engine = ndb;
|
|
|
|
alter table t2 add constraint fkname2 foreign key (a) references t1 (a);
|
|
|
|
create table t3 (
|
|
a int, primary key using hash (a)
|
|
) engine = ndb;
|
|
|
|
alter table t3 add constraint fkname3 foreign key (a) references t2 (a);
|
|
|
|
--error ER_CANT_DROP_FIELD_OR_KEY
|
|
alter table t2 algorithm=inplace, drop foreign key nosuchfk;
|
|
|
|
alter table t2 algorithm=inplace, drop foreign key fkname2;
|
|
|
|
alter table t3 algorithm=inplace, drop foreign key fkname3;
|
|
|
|
drop table t1, t2, t3;
|
|
|
|
create table t1 (
|
|
a int, primary key using hash (a)
|
|
) engine = ndb;
|
|
|
|
create table t2 (
|
|
a int, primary key using hash (a)
|
|
) engine = ndb;
|
|
|
|
alter table t2 add constraint fkname2 foreign key (a) references t1 (a);
|
|
|
|
create table t3 (
|
|
a int, primary key using hash (a)
|
|
) engine = ndb;
|
|
|
|
alter table t3 add constraint fkname3 foreign key (a) references t2 (a);
|
|
|
|
--error ER_CANT_DROP_FIELD_OR_KEY
|
|
alter table t2 algorithm=copy, drop foreign key nosuchfk;
|
|
|
|
alter table t2 algorithm=copy, drop foreign key fkname2;
|
|
|
|
alter table t3 algorithm=copy, drop foreign key fkname3;
|
|
|
|
drop table t1, t2, t3;
|
|
|
|
--echo # bug#18824753 - NDB_RESTORE FAILS WITH FKS WITH SAME NAME
|
|
|
|
create table t1 (a int primary key) engine=ndb;
|
|
create table t2 (a int primary key) engine=ndb;
|
|
create table t3 (a int primary key) engine=ndb;
|
|
create table t4 (a int primary key) engine=ndb;
|
|
|
|
alter table t2
|
|
add constraint myfk2 foreign key (a) references t1 (a);
|
|
alter table t4
|
|
add constraint myfk4 foreign key (a) references t3 (a);
|
|
|
|
--error ER_CANNOT_ADD_FOREIGN
|
|
alter table t4
|
|
add constraint `11/22/myfk4` foreign key (a) references t3 (a);
|
|
show warnings;
|
|
|
|
--echo # run backup
|
|
--source include/ndb_backup.inc
|
|
|
|
drop table t2, t1;
|
|
drop table t4, t3;
|
|
|
|
--echo # run restore meta
|
|
--exec $NDB_RESTORE -b $the_backup_id -n 1 -m $NDB_BACKUPS-$the_backup_id >> $NDB_TOOLS_OUTPUT
|
|
|
|
--disable_warnings
|
|
show create table t2;
|
|
show create table t4;
|
|
--enable_warnings
|
|
|
|
drop table t2, t1;
|
|
drop table t4, t3;
|
|
--source suite/ndb/include/backup_restore_cleanup.inc
|
|
|
|
--echo # bug18825966 - ADDING FK TO TABLE WITH DATA GIVES INTERNAL ERROR 208
|
|
|
|
create table t1 (
|
|
c0 int,
|
|
c1 int,
|
|
primary key using hash (c1)
|
|
) engine=ndb;
|
|
|
|
create table t2 (
|
|
c0 int,
|
|
primary key using hash (c0)
|
|
) engine=ndb;
|
|
|
|
insert into t1 (c1) values (1);
|
|
insert into t2 (c0) values (1);
|
|
|
|
alter table t2 add constraint foreign key fk (c0) references t1 (c1);
|
|
|
|
drop table t2, t1;
|
|
|
|
create table t1 (
|
|
c0 int,
|
|
c1 int,
|
|
c2 int,
|
|
c3 int,
|
|
c4 int,
|
|
c5 int,
|
|
primary key (c2,c5)
|
|
) engine=ndb;
|
|
|
|
create table t2 (
|
|
c0 int,
|
|
c1 int,
|
|
c2 int,
|
|
c3 int,
|
|
c4 int,
|
|
c5 int,
|
|
primary key (c1,c4)
|
|
) engine=ndb;
|
|
|
|
insert into t1 (c2,c5) values (1,10),(2,20),(3,30);
|
|
insert into t2 (c1,c4) values (1,10),(2,20),(3,30);
|
|
|
|
alter table t2 add constraint fk foreign key (c1,c4) references t1 (c2,c5);
|
|
|
|
--error ER_NO_REFERENCED_ROW_2
|
|
insert into t2 (c1,c4) values (4,40);
|
|
|
|
drop table t2, t1;
|
|
|
|
create table t1 (
|
|
c0 int,
|
|
c1 int,
|
|
c2 int not null,
|
|
c3 int,
|
|
c4 int,
|
|
c5 int not null,
|
|
primary key (c0),
|
|
unique key using hash (c2,c5)
|
|
) engine=ndb;
|
|
|
|
create table t2 (
|
|
c0 int,
|
|
c1 int,
|
|
c2 int,
|
|
c3 int,
|
|
c4 int,
|
|
c5 int,
|
|
primary key (c1,c4)
|
|
) engine=ndb;
|
|
|
|
insert into t1 (c0,c2,c5) values (1,1,10),(2,2,20),(3,3,30);
|
|
insert into t2 (c1,c4) values (1,10),(2,20),(3,30);
|
|
|
|
alter table t2 add constraint fk foreign key (c1,c4) references t1 (c2,c5);
|
|
|
|
--error ER_NO_REFERENCED_ROW_2
|
|
insert into t2 (c1,c4) values (4,40);
|
|
|
|
drop table t2, t1;
|
|
|
|
--echo #
|
|
--echo # BUG#21308928 : CANNOT ADD UNIQUE CONSTRAINT ON SET OF FIELDS WITH EXISTING FOREIGN KEY
|
|
--echo #
|
|
|
|
--echo #Create the tables
|
|
create table t1 (
|
|
a int not null primary key
|
|
) engine ndb;
|
|
|
|
create table t2 (
|
|
a int primary key,
|
|
b int not null,
|
|
c int,
|
|
constraint fk1 foreign key (a) references t1(a),
|
|
constraint fk2 foreign key (b) references t1(a),
|
|
constraint fk3 foreign key (c) references t1(a)
|
|
) engine ndb;
|
|
|
|
--echo #Add a unique constraint to the foreign key column
|
|
alter table t2 add constraint uniquet2a unique (a);
|
|
alter table t2 add constraint uniquet2b unique (b);
|
|
alter table t2 add constraint uniquet2c unique (c);
|
|
|
|
--disable_warnings
|
|
show create table t2;
|
|
--enable_warnings
|
|
|
|
--echo #Drop tables
|
|
drop table t2, t1;
|
|
|
|
--echo #
|
|
--echo # Bug#27930382 : FOREIGN KEY CONSTRAINT VIOLATIONS INCORRECTLY REPORTED
|
|
--echo
|
|
|
|
# Foreign key constraint which self referrence its own table,
|
|
# incorrectly reported constraint violation. Root cause was
|
|
# that the key constraints were checked before the (unique) indexes
|
|
# had been updated:
|
|
#
|
|
# (Test case based on the API test 'testFK')
|
|
|
|
|
|
create table t1 (
|
|
kol1 int primary key,
|
|
kol2 int not null,
|
|
kol3 int not null,
|
|
kol4 int not null,
|
|
kol5 int not null,
|
|
unique key uq1(kol2),
|
|
foreign key fk1(kol3) references t1(kol2) on update restrict on delete restrict
|
|
) engine = ndb;
|
|
|
|
insert into t1 values (1,1,1,1,1);
|
|
delete from t1 where kol1=1;
|
|
drop table t1;
|
|
|
|
create table t1 (
|
|
kol1 int primary key,
|
|
kol2 int not null,
|
|
kol3 int not null,
|
|
kol4 int not null,
|
|
kol5 int not null,
|
|
foreign key fk1(kol3) references t1(kol1) on update restrict on delete restrict
|
|
) engine = ndb;
|
|
|
|
insert into t1 values (1,1,1,1,1);
|
|
delete from t1 where kol1=1;
|
|
drop table t1;
|
|
|
|
#
|
|
# There were also a race condition due to local backup replicas
|
|
# being read when verifying that there were no references to
|
|
# a deleted row. This allowed the read to race the delete of the
|
|
# index row, such that the deleted row appered to still being referred.
|
|
# FIX: Always read primary replicas when verifying a constraint.
|
|
create table t1 (
|
|
kol1 int primary key,
|
|
kol2 int not null,
|
|
kol3 int not null,
|
|
kol4 int not null,
|
|
kol5 int not null,
|
|
unique key uq1(kol2),
|
|
unique key uq2(kol3),
|
|
foreign key fk1(kol3) references t1(kol2) on update restrict on delete restrict
|
|
) engine = ndb;
|
|
|
|
insert into t1 values (1,1,1,1,1);
|
|
delete from t1 where kol1=1;
|
|
|
|
insert into t1 values (2,1,1,1,1);
|
|
delete from t1 where kol1=2;
|
|
|
|
insert into t1 values (3,1,1,1,1);
|
|
delete from t1 where kol1=3;
|
|
|
|
insert into t1 values (4,1,1,1,1);
|
|
delete from t1 where kol1=4;
|
|
|
|
|
|
insert into t1 values (1,0,0,0,0);
|
|
insert into t1 values (2,1,1,1,1);
|
|
insert into t1 values (3,2,2,2,2);
|
|
insert into t1 values (4,3,3,3,3);
|
|
delete from t1;
|
|
|
|
drop table t1;
|
|
--remove_file $NDB_TOOLS_OUTPUT
|