mirror of
https://github.com/MariaDB/server.git
synced 2025-07-27 05:35:00 +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
102 lines
3.2 KiB
Text
102 lines
3.2 KiB
Text
CREATE TABLE parent (a INT PRIMARY KEY) ENGINE=InnoDB;
|
|
INSERT INTO parent SET a=1;
|
|
CREATE TABLE child (a INT PRIMARY KEY, FOREIGN KEY (a) REFERENCES parent(a)
|
|
ON UPDATE CASCADE)
|
|
ENGINE=InnoDB;
|
|
INSERT INTO child SET a=1;
|
|
TRUNCATE TABLE parent;
|
|
ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`child`, CONSTRAINT `1` FOREIGN KEY (`a`) REFERENCES `test`.`parent` (`a`))
|
|
TRUNCATE TABLE child;
|
|
INSERT INTO child SET a=1;
|
|
UPDATE parent SET a=2;
|
|
SELECT * FROM child;
|
|
a
|
|
2
|
|
connect dml,localhost,root;
|
|
SET DEBUG_SYNC='foreign_constraint_update_cascade SIGNAL fk WAIT_FOR go';
|
|
UPDATE parent SET a=3;
|
|
connection default;
|
|
SET DEBUG_SYNC='now WAIT_FOR fk';
|
|
SET lock_wait_timeout=1;
|
|
TRUNCATE TABLE child;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
SET DEBUG_SYNC='now SIGNAL go';
|
|
connection dml;
|
|
SELECT * FROM child;
|
|
a
|
|
3
|
|
SET DEBUG_SYNC='foreign_constraint_check_for_update SIGNAL fk WAIT_FOR go';
|
|
DELETE FROM parent;
|
|
connection default;
|
|
SET DEBUG_SYNC='now WAIT_FOR fk';
|
|
SET lock_wait_timeout=1;
|
|
TRUNCATE TABLE child;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
SET DEBUG_SYNC='now SIGNAL go';
|
|
connection dml;
|
|
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `1` FOREIGN KEY (`a`) REFERENCES `parent` (`a`) ON UPDATE CASCADE)
|
|
SELECT * FROM child;
|
|
a
|
|
3
|
|
INSERT INTO parent SET a=5;
|
|
SET DEBUG_SYNC='foreign_constraint_check_for_ins SIGNAL fk WAIT_FOR go';
|
|
INSERT INTO child SET a=5;
|
|
connection default;
|
|
SET DEBUG_SYNC='now WAIT_FOR fk';
|
|
SET foreign_key_checks=0, innodb_lock_wait_timeout=0;
|
|
TRUNCATE TABLE parent;
|
|
ERROR HY000: Lock wait timeout exceeded; try restarting transaction
|
|
SET DEBUG_SYNC='now SIGNAL go';
|
|
connection dml;
|
|
SELECT * FROM parent;
|
|
a
|
|
3
|
|
5
|
|
SELECT * FROM child;
|
|
a
|
|
3
|
|
5
|
|
disconnect dml;
|
|
connection default;
|
|
SET DEBUG_SYNC = RESET;
|
|
DROP TABLE child, parent;
|
|
#
|
|
# MDEV-24532 Table corruption ER_NO_SUCH_TABLE_IN_ENGINE or
|
|
# ER_CRASHED_ON_USAGE after ALTER on table with foreign key
|
|
#
|
|
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a)) ENGINE=InnoDB;
|
|
ALTER TABLE t1 ADD FOREIGN KEY (b) REFERENCES t1 (a) ON UPDATE CASCADE;
|
|
LOCK TABLE t1 WRITE;
|
|
TRUNCATE TABLE t1;
|
|
ALTER TABLE t1 ADD c INT;
|
|
UNLOCK TABLES;
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-26052 Assertion prebuilt->trx_id < table->def_trx_id failed
|
|
#
|
|
call mtr.add_suppression("InnoDB: In ALTER TABLE `test`\\.`t1` has or is");
|
|
CREATE TABLE t1 (pk INT, a INT, PRIMARY KEY (pk), KEY (a)) ENGINE=InnoDB;
|
|
SET FOREIGN_KEY_CHECKS=0;
|
|
ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t2 (a), ALGORITHM=COPY;
|
|
INSERT INTO t1 VALUES (1,1);
|
|
CREATE TABLE t2(f1 INT PRIMARY KEY)ENGINE=InnoDB;
|
|
LOCK TABLES t1 WRITE;
|
|
SET FOREIGN_KEY_CHECKS=1;
|
|
TRUNCATE t1;
|
|
ERROR HY000: Cannot add foreign key constraint for `t1`
|
|
INSERT INTO t1 VALUES (2,2);
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `1` FOREIGN KEY (`a`) REFERENCES `t2` (`a`))
|
|
SELECT * FROM t1;
|
|
pk a
|
|
1 1
|
|
UNLOCK TABLES;
|
|
INSERT INTO t1 VALUES (2,2);
|
|
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `1` FOREIGN KEY (`a`) REFERENCES `t2` (`a`))
|
|
SET FOREIGN_KEY_CHECKS=0;
|
|
INSERT INTO t1 VALUES (2,2);
|
|
SELECT * FROM t1;
|
|
pk a
|
|
1 1
|
|
2 2
|
|
DROP TABLE t2, t1;
|
|
# End of 10.6 tests
|