Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6
-
None
Description
--source include/have_innodb.inc
|
|
CREATE TABLE t1 (f1 INT, f2 INT, KEY(f1), KEY(f2)) ENGINE=InnoDB; |
|
CREATE TABLE t2 (a INT, b INT, KEY(b)) ENGINE = InnoDB; |
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY (a) REFERENCES t1(f1) ON DELETE SET NULL; |
|
SET foreign_key_checks= OFF; |
ALTER TABLE t2 MODIFY a CHAR(1) NOT NULL; |
SET foreign_key_checks= ON; |
|
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY (b) REFERENCES t1 (f2); |
|
# Cleanup
|
DROP TABLE t2, t1; |
10.2 54d7ba96 |
mysqltest: At line 12: query 'ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY (b) REFERENCES t1 (f2)' failed: 1050: Table './test/t2' already exists
|
Reproducible on 10.2-10.6.
It happens even if we also modify t1.f1 to CHAR(1) NOT NULL after the first ALTER, to restore the consistency between column definitions. It still leaves the foreign key invalid though, because it can't really set NULL to a non-NULL column; so, some error is probably expected, but ER_TABLE_EXISTS_ERROR is clearly a wrong choice.
Attachments
Issue Links
- is duplicated by
-
MDEV-31086 MODIFY COLUMN can break FK constraints, and lead to unrestorable dumps
- Closed
- relates to
-
MDEV-25620 InnoDB: Failing assertion: id != 0 or Assertion `dict_table_is_file_per_table(this)' failed in dict_table_t::rename_tablespace
- Closed