mirror of
https://github.com/MariaDB/server.git
synced 2025-07-09 21:08:16 +02:00
cffbb17480
Before MySQL 4.0.18, user-specified constraint names were ignored. Starting with MySQL 4.0.18, the specified constraint name was prepended with the schema name and '/'. Now we are transforming into a format where the constraint name is prepended with the dict_table_t::name and the impossible UTF-8 sequence 0xff. Generated constraint names will be ASCII decimal numbers. On upgrade, old FOREIGN KEY constraint names will be displayed without any schema name prefix. They will be updated to the new format on DDL operations. dict_foreign_t::sql_id(): Return the SQL constraint name without any schemaname/tablename\377 or schemaname/ prefix. row_rename_table_for_mysql(), dict_table_rename_in_cache(): Simplify the logic: Just rename constraints to the new format. dict_table_get_foreign_id(): Replaces dict_table_get_highest_foreign_id(). innobase_get_foreign_key_info(): Let my_error() refer to erroneous anonymous constraints as "(null)". row_delete_constraint(): Try to drop all 3 constraint name variants. Reviewed by: Thirunarayanan Balathandayuthapani Tested by: Matthias Leich
262 lines
6.9 KiB
Text
262 lines
6.9 KiB
Text
--source include/have_innodb.inc
|
|
--source include/not_embedded.inc
|
|
|
|
--echo #
|
|
--echo # Bug #18806829 OPENING INNODB TABLES WITH MANY FOREIGN KEY
|
|
--echo # REFERENCES IS SLOW/CRASHES SEMAPHORE
|
|
--echo #
|
|
|
|
create table t1 (f1 int primary key) engine=innodb;
|
|
insert into t1 values (5);
|
|
insert into t1 values (2882);
|
|
insert into t1 values (10);
|
|
|
|
let $fk_tables = 120;
|
|
|
|
--disable_query_log
|
|
let $i = $fk_tables;
|
|
while ($i)
|
|
{
|
|
eval create table fk_$i (f1 int primary key,
|
|
constraint pc$i foreign key (f1) references t1(f1)
|
|
on delete cascade on update cascade) engine=innodb;
|
|
eval insert into fk_$i values (5);
|
|
eval insert into fk_$i values (2882);
|
|
eval insert into fk_$i values (10);
|
|
dec $i;
|
|
}
|
|
--enable_query_log
|
|
|
|
--source include/restart_mysqld.inc
|
|
|
|
update t1 set f1 = 28 where f1 = 2882;
|
|
|
|
select * from fk_120;
|
|
select * from fk_1;
|
|
select * from fk_50;
|
|
|
|
--disable_query_log
|
|
let $i = $fk_tables;
|
|
while ($i)
|
|
{
|
|
eval drop table fk_$i;
|
|
dec $i;
|
|
}
|
|
--enable_query_log
|
|
|
|
drop table t1;
|
|
|
|
--echo #
|
|
--echo # Check if restrict is working fine.
|
|
--echo #
|
|
|
|
create table t1 (f1 int primary key) engine=innodb;
|
|
|
|
let $fk_tables = 30;
|
|
|
|
--disable_query_log
|
|
let $i = $fk_tables;
|
|
while ($i)
|
|
{
|
|
eval create table fk_$i (f1 int primary key,
|
|
constraint pc$i foreign key (f1) references t1(f1)
|
|
on delete restrict on update restrict) engine=innodb;
|
|
eval insert into t1 values ($i);
|
|
eval insert into fk_$i values ($i);
|
|
dec $i;
|
|
}
|
|
--enable_query_log
|
|
|
|
--source include/restart_mysqld.inc
|
|
|
|
--error ER_ROW_IS_REFERENCED_2
|
|
delete from t1 where f1 = 29;
|
|
select * from fk_29;
|
|
|
|
--disable_query_log
|
|
let $i = $fk_tables;
|
|
while ($i)
|
|
{
|
|
eval drop table fk_$i;
|
|
dec $i;
|
|
}
|
|
--enable_query_log
|
|
|
|
drop table t1;
|
|
|
|
#
|
|
# MDEV-7672: Crash creating an InnoDB table with foreign keys
|
|
#
|
|
|
|
CREATE TABLE t1 (
|
|
id int(11) NOT NULL AUTO_INCREMENT,
|
|
f1 int(11) DEFAULT NULL,
|
|
PRIMARY KEY (id),
|
|
CONSTRAINT fk1 FOREIGN KEY (f1) REFERENCES t1 (id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
|
|
--error 1005
|
|
CREATE TABLE t2 (
|
|
id int(11) NOT NULL AUTO_INCREMENT,
|
|
f2 int(11) NOT NULL,
|
|
f3 int(11) NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
CONSTRAINT fk2 FOREIGN KEY (f2) REFERENCES t1 (`id`) ON DELETE CASCADE,
|
|
CONSTRAINT fk3 FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
|
|
show warnings;
|
|
|
|
CREATE TABLE t2 (
|
|
id int(11) NOT NULL AUTO_INCREMENT,
|
|
f2 int(11) NOT NULL,
|
|
f3 int(11) NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
CONSTRAINT fk2 FOREIGN KEY (f2) REFERENCES t1 (`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
|
|
--error 1005
|
|
ALTER TABLE t2 ADD CONSTRAINT fk3 FOREIGN KEY (f3) REFERENCES t3 (id) ON DELETE CASCADE;
|
|
|
|
show warnings;
|
|
|
|
drop table t2;
|
|
drop table t1;
|
|
|
|
#
|
|
# MDEV-9142 :Adding Constraint with no database reference
|
|
# results in ERROR 1046 (3D000) at line 13: No database selected
|
|
#
|
|
CREATE DATABASE kg_test1;
|
|
CREATE DATABASE kg_test2;
|
|
|
|
CREATE TABLE `kg_test1`.`group` (
|
|
Id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
|
|
|
CREATE TABLE `kg_test1`.`person` (
|
|
`Id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
`Name` VARCHAR(50) NOT NULL,
|
|
PRIMARY KEY (`Id`),
|
|
CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`)
|
|
) ENGINE=INNODB DEFAULT CHARSET=utf8;
|
|
|
|
show create table `kg_test1`.`person`;
|
|
|
|
--error 1005
|
|
CREATE TABLE `kg_test2`.`person2` (
|
|
`Id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
`Name` VARCHAR(50) NOT NULL,
|
|
PRIMARY KEY (`Id`),
|
|
CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `group` (`Id`)
|
|
) ENGINE=INNODB DEFAULT CHARSET=utf8;
|
|
|
|
CREATE TABLE `kg_test2`.`person2` (
|
|
`Id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
`Name` VARCHAR(50) NOT NULL,
|
|
PRIMARY KEY (`Id`),
|
|
CONSTRAINT `fk_person_group` FOREIGN KEY (`Id`) REFERENCES `kg_test1`.`group` (`Id`)
|
|
) ENGINE=INNODB DEFAULT CHARSET=utf8;
|
|
|
|
show create table `kg_test2`.`person2`;
|
|
|
|
SHOW WARNINGS;
|
|
DROP DATABASE kg_test2;
|
|
DROP DATABASE kg_test1;
|
|
|
|
#
|
|
# MDEV-7627: Some symbols in table name can cause to Error Code: 1050 when created FK
|
|
#
|
|
|
|
CREATE TABLE `#departaments` (
|
|
`id_depart` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`id_depart`)
|
|
) ENGINE=INNODB DEFAULT CHARSET=utf8;
|
|
|
|
CREATE TABLE `#departaments_tree` (
|
|
`id_depart` INT(10) UNSIGNED NOT NULL,
|
|
`id_depart_in` INT(10) UNSIGNED NOT NULL,
|
|
PRIMARY KEY (`id_depart`,`id_depart_in`),
|
|
CONSTRAINT `#departaments_tree_ibfk_1` FOREIGN KEY (`id_depart`) REFERENCES `#departaments` (`id_depart`)
|
|
) ENGINE=INNODB DEFAULT CHARSET=utf8;
|
|
|
|
ALTER TABLE `#departaments_tree`
|
|
ADD FOREIGN KEY (`id_depart_in`) REFERENCES `#departaments`(`id_depart`);
|
|
|
|
SHOW CREATE TABLE `#departaments_tree`;
|
|
|
|
DROP TABLE `#departaments_tree`;
|
|
DROP TABLE `#departaments`;
|
|
|
|
CREATE TABLE `boroda` (
|
|
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`a` INT(11) UNSIGNED DEFAULT NULL,
|
|
`b` INT(11) UNSIGNED DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `a` (`a`),
|
|
CONSTRAINT `1` FOREIGN KEY (`a`) REFERENCES `boroda` (`id`)
|
|
) ENGINE=INNODB DEFAULT CHARSET=utf8;
|
|
|
|
ALTER TABLE `boroda`
|
|
ADD FOREIGN KEY (`b`) REFERENCES `boroda`(`id`);
|
|
|
|
ALTER TABLE `boroda` DROP FOREIGN KEY `2`;
|
|
|
|
RENAME TABLE `boroda` TO `#boroda`;
|
|
|
|
ALTER TABLE `#boroda`
|
|
ADD FOREIGN KEY (`b`) REFERENCES `#boroda`(`id`);
|
|
|
|
SHOW CREATE TABLE `#boroda`;
|
|
DROP TABLE `#boroda`;
|
|
|
|
CREATE TABLE `boroda` (
|
|
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`a` INT(11) UNSIGNED DEFAULT NULL,
|
|
`b` INT(11) UNSIGNED DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `a` (`a`),
|
|
CONSTRAINT `1` FOREIGN KEY (`a`) REFERENCES `boroda` (`id`)
|
|
) ENGINE=INNODB DEFAULT CHARSET=utf8;
|
|
|
|
RENAME TABLE `boroda` TO `bor#oda`;
|
|
|
|
ALTER TABLE `bor#oda`
|
|
ADD FOREIGN KEY (`b`) REFERENCES `bor#oda`(`id`);
|
|
|
|
SHOW CREATE TABLE `bor#oda`;
|
|
DROP TABLE `bor#oda`;
|
|
|
|
--echo #
|
|
--echo # MDEV-21127 Assertion `(size_t)(ptr - buf) < MAX_TEXT - 4' failed in key_text::key_text
|
|
--echo #
|
|
--error ER_CANT_CREATE_TABLE
|
|
CREATE TABLE t1 (
|
|
a012345678901234567890123456789012345678901 char(255),
|
|
b char(255),
|
|
FOREIGN KEY ( a012345678901234567890123456789012345678901, b ) REFERENCES tx (ax, bx)
|
|
) ENGINE=InnoDB;
|
|
|
|
--error ER_CANT_CREATE_TABLE
|
|
CREATE TABLE t1 (
|
|
a012345678901234567 int,
|
|
b int,
|
|
c0123456789012345678 int,
|
|
FOREIGN KEY (a012345678901234567,c0123456789012345678,b) REFERENCES tx (x1,x2,x3)
|
|
) ENGINE=InnoDB;
|
|
|
|
--echo #
|
|
--echo # MDEV-25642 InnoDB rename table copy DDL fails
|
|
--echo # while dropping the table
|
|
--echo #
|
|
call mtr.add_suppression("InnoDB: In ALTER TABLE `test`.`t1` has or is referenced in foreign key constraints which are not compatible with the new table definition.");
|
|
|
|
CREATE TABLE t1 (a VARCHAR(10) NOT NULL PRIMARY KEY) ENGINE=InnoDB;
|
|
CREATE TABLE t1_fk (a VARCHAR(40), KEY a (a), FULLTEXT KEY(a), CONSTRAINT fk FOREIGN KEY(a) REFERENCES t1 (a) ON UPDATE CASCADE) ENGINE=InnoDB;
|
|
ALTER TABLE t1 RENAME TO tm1, ALGORITHM=COPY;
|
|
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
|
|
# Enable SET FOREIGN_KEY_CHECKS after fixing MDEV-25885
|
|
SET FOREIGN_KEY_CHECKS=0;
|
|
CREATE TABLE t1 (c1 BIGINT NOT NULL, c2 BIGINT NOT NULL, PRIMARY KEY(c1), UNIQUE KEY(c2)) ENGINE=MEMORY;
|
|
ALTER TABLE t1 ENGINE=InnoDB, ALGORITHM=COPY;
|
|
DROP TABLE t1, tm1, t1_fk;
|