mirror of
https://github.com/MariaDB/server.git
synced 2025-07-29 22:50:47 +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
131 lines
6.8 KiB
Text
131 lines
6.8 KiB
Text
#
|
|
# MDEV-31086 MODIFY COLUMN can break FK constraints, and
|
|
# lead to unrestorable dumps
|
|
#
|
|
CREATE TABLE t1(
|
|
id SERIAL,
|
|
msg VARCHAR(100) CHARACTER SET utf8mb3,
|
|
KEY(msg))ENGINE=InnoDB;
|
|
CREATE TABLE t2(
|
|
id SERIAL,
|
|
msg varchar(100) CHARACTER SET utf8mb4,
|
|
CONSTRAINT fk_t1 FOREIGN KEY (msg) REFERENCES t1 (msg))ENGINE=InnoDB;
|
|
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
|
|
CREATE TABLE t2(
|
|
id SERIAL,
|
|
msg varchar(100) CHARACTER SET utf8mb3,
|
|
msg_1 varchar(100) CHARACTER SET utf8mb3,
|
|
INDEX (msg_1),
|
|
INDEX (msg),
|
|
CONSTRAINT fk_t1 FOREIGN KEY (msg) REFERENCES t1 (msg)
|
|
ON DELETE CASCADE)ENGINE=InnoDB;
|
|
SET FOREIGN_KEY_CHECKS=1;
|
|
ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(200) character set utf8mb3, ALGORITHM=COPY;
|
|
ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'fk_t1'
|
|
ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(200) character set utf8mb3, ALGORITHM=INPLACE;
|
|
ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'fk_t1'
|
|
SET FOREIGN_KEY_CHECKS=0;
|
|
ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(200) character set utf8mb3, ALGORITHM=COPY;
|
|
ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'fk_t1'
|
|
ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(400) character set utf8mb3, ALGORITHM=INPLACE;
|
|
ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'fk_t1'
|
|
SET FOREIGN_KEY_CHECKS=1;
|
|
ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(100) CHARACTER SET utf8mb4, ALGORITHM=COPY;
|
|
ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'fk_t1'
|
|
ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(100) CHARACTER SET utf8mb4, ALGORITHM=INPLACE;
|
|
ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'fk_t1'
|
|
SET FOREIGN_KEY_CHECKS=0;
|
|
ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(100) CHARACTER SET utf8mb4,ALGORITHM=COPY;
|
|
ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'fk_t1'
|
|
ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(100) CHARACTER SET utf8mb4,ALGORITHM=INPLACE;
|
|
ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'fk_t1'
|
|
SET FOREIGN_KEY_CHECKS=1;
|
|
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(msg_1) REFERENCES t1(msg),MODIFY COLUMN msg_1 VARCHAR(100) CHARACTER SET utf8mb4, ALGORITHM=COPY;
|
|
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
|
|
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(msg_1) REFERENCES t1(msg),MODIFY COLUMN msg_1 VARCHAR(100) CHARACTER SET utf8mb4, ALGORITHM=INPLACE;
|
|
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY
|
|
SET FOREIGN_KEY_CHECKS=0;
|
|
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(msg_1) REFERENCES t1(msg),MODIFY COLUMN msg_1 VARCHAR(100) CHARACTER SET utf8mb4, ALGORITHM=COPY;
|
|
ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
|
|
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(msg_1) REFERENCES t1(msg),MODIFY COLUMN msg_1 VARCHAR(100) CHARACTER SET utf8mb4, ALGORITHM=INPLACE;
|
|
ERROR HY000: Cannot change column 'msg_1': used in a foreign key constraint '0'
|
|
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY(msg_1) REFERENCES t1(msg),MODIFY COLUMN msg_1 VARCHAR(200) CHARACTER SET utf8mb3, ALGORITHM=INPLACE;
|
|
ERROR HY000: Cannot change column 'msg_1': used in a foreign key constraint '0'
|
|
SET FOREIGN_KEY_CHECKS=1;
|
|
ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb3, ALGORITHM=COPY;
|
|
ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'fk_t1' of table 'test.t2'
|
|
ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb3, ALGORITHM=INPLACE;
|
|
ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'fk_t1' of table 'test/t2'
|
|
ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb4, ALGORITHM=COPY;
|
|
ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'fk_t1' of table 'test.t2'
|
|
ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb4, ALGORITHM=INPLACE;
|
|
ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'fk_t1' of table 'test/t2'
|
|
SET FOREIGN_KEY_CHECKS=0;
|
|
ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb3, ALGORITHM=COPY;
|
|
ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'fk_t1' of table 'test.t2'
|
|
ALTER TABLE t1 MODIFY msg VARCHAR(400) CHARSET utf8mb3, ALGORITHM=INPLACE;
|
|
ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'fk_t1' of table 'test/t2'
|
|
ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb4, ALGORITHM=COPY;
|
|
ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'fk_t1' of table 'test.t2'
|
|
ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb4, ALGORITHM=INPLACE;
|
|
ERROR HY000: Cannot change column 'msg': used in a foreign key constraint 'fk_t1' of table 'test/t2'
|
|
SET FOREIGN_KEY_CHECKS=0;
|
|
ALTER TABLE t2 DROP FOREIGN KEY fk_t1, MODIFY msg VARCHAR(200) CHARSET utf8mb4, ALGORITHM=COPY;
|
|
ALTER TABLE t1 MODIFY msg VARCHAR(200) CHARSET utf8mb4, ALGORITHM=COPY;
|
|
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY (msg) REFERENCES t1(msg), aLGORITHM=INPLACE;
|
|
SET FOREIGN_KEY_CHECKS=1;
|
|
DROP TABLE t2, t1;
|
|
#
|
|
# MDEV-31869 Server aborts when table does drop column
|
|
#
|
|
CREATE TABLE t (a VARCHAR(40), b INT, C INT) ENGINE=InnoDB;
|
|
ALTER TABLE t MODIFY a VARCHAR(50), DROP b;
|
|
DROP TABLE t;
|
|
#
|
|
# MDEV-32060 Server aborts when table doesn't
|
|
# have referenced index
|
|
#
|
|
SET SESSION FOREIGN_KEY_CHECKS = OFF;
|
|
CREATE TABLE t1 (a VARCHAR(16) KEY, FOREIGN KEY(a) REFERENCES t2(b)) ENGINE=InnoDB;
|
|
CREATE TABLE t2 (b VARCHAR(8)) ENGINE=InnoDB;
|
|
SET SESSION FOREIGN_KEY_CHECKS = ON;
|
|
ALTER TABLE t2 MODIFY b VARCHAR(16), ADD KEY(b);
|
|
DROP TABLE t1, t2;
|
|
#
|
|
# MDEV-32337 Assertion `pos < table->n_def' failed
|
|
# in dict_table_get_nth_col
|
|
#
|
|
CREATE TABLE t (a INT, va INT AS (a), b INT, vb INT AS (b),
|
|
c INT, vc INT AS (c), vf VARCHAR(16) AS (f),
|
|
f VARCHAR(4)) ENGINE=InnoDB;
|
|
ALTER TABLE t MODIFY f VARCHAR(8);
|
|
ALTER TABLE t MODIFY vf VARCHAR(18);
|
|
ERROR HY000: This is not yet supported for generated columns
|
|
DROP TABLE t;
|
|
#
|
|
# MDEV-32527 Server aborts during alter operation
|
|
# when table doesn't have foreign index
|
|
#
|
|
CREATE TABLE t1 (f1 INT NOT NULL, INDEX(f1)) ENGINE=InnoDB;
|
|
CREATE TABLE t2(f1 INT NOT NULL, f2 VARCHAR(100) DEFAULT NULL,
|
|
INDEX idx(f1, f2),
|
|
FOREIGN KEY(f1) REFERENCES t1(f1))ENGINE=INNODB;
|
|
SET SESSION FOREIGN_KEY_CHECKS = OFF;
|
|
ALTER TABLE t2 DROP INDEX idx;
|
|
ALTER TABLE t2 MODIFY f2 VARCHAR(1023);
|
|
SET SESSION FOREIGN_KEY_CHECKS = ON;
|
|
DROP TABLE t2, t1;
|
|
#
|
|
# MDEV-32638 MariaDB crashes with foreign_key_checks=0
|
|
# when changing a column and adding a foreign
|
|
# key at the same time
|
|
#
|
|
CREATE TABLE t1(f1 VARCHAR(2) NOT NULL, PRIMARY KEY(f1))ENGINE=InnoDB;
|
|
CREATE TABLE t2(f1 INT NOT NULL PRIMARY KEY,
|
|
f2 VARCHAR(10) NOT NULL DEFAULT '')ENGINE=InnoDB;
|
|
SET SESSION FOREIGN_KEY_CHECKS = OFF;
|
|
ALTER TABLE t2 CHANGE COLUMN f2 f3 VARCHAR(20) NOT NULL,
|
|
ADD CONSTRAINT t2_fk FOREIGN KEY(f3) REFERENCES t1(f1);
|
|
DROP TABLE t2, t1;
|
|
SET SESSION FOREIGN_KEY_CHECKS = ON;
|
|
# End of 10.4 tests
|