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-31086MODIFY COLUMN can break FK constraints, and lead to unrestorable dumps
Closed
relates to
MDEV-25620InnoDB: Failing assertion: id != 0 or Assertion `dict_table_is_file_per_table(this)' failed in dict_table_t::rename_tablespace
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.
Marko Mäkelä
added a comment - I think that the correct course of action would be to reject already the statement
ALTER TABLE t2 MODIFY a CHAR (1) NOT NULL ;
because it directly contradicts the previous definition
ALTER TABLE t2 ADD CONSTRAINT FOREIGN KEY (a) REFERENCES t1(f1) ON DELETE SET NULL ;
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.
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.
Elena Stepanova
added a comment - 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.
Thirunarayanan Balathandayuthapani
added a comment -
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.
Bug
Major
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.