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
-
-
- Closed
-
- relates to
-
-
- Closed
-
Activity
While at it, please adjust (or clarify) the description of foreign_key_checks variable to indicate what exactly it is meant to relax.
Currently it is:
If set to 1 (the default) foreign key constraints (including ON UPDATE and ON DELETE behavior) InnoDB tables are checked, while if set to 0, they are not checked. 0 is not recommended for normal use, though it can be useful in situations where you know the data is consistent, but want to reload data in a different order from that that specified by parent/child relationships. Setting this variable to 1 does not retrospectively check for inconsistencies introduced while set to 0.
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY (a) REFERENCES t1(f1) ON DELETE SET NULL;
|
Fails to load the foreign key constraint due to column mismatch. So consecutive alter
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY (b) REFERENCES t1 (f2); tries to insert the foreign
key constraint. In dict_create_foreign_constraints_low(), InnoDB tries to fetch the foreign set present
in the memory to find the maximum id. Since InnoDB doesn't load foreign key constraint in previous alter.
It leads to insertion of #sql_..._ibfk_1 in sys_foreign table. So while renaming the table, InnoDB
fails to rename the constraint in sys_foreign table as DB_DUPLICATE_KEY.
I think that the correct course of action would be to reject already the statement
because it directly contradicts the previous definition
of the same table. That is, the SET NULL that refers to t2.a clearly implies that t2.a must allow NULL values. The foreign_key_checks=OFF should only relax checks that are related to the referenced tables, i.e., if t1 did not exist, we should allow those statements to get through. But, whether or not t1 exists, I think that we must reject a table definition where SET NULL would be requested on a NOT NULL column.