[MDEV-25619] Bogus ER_TABLE_EXISTS_ERROR upon an attempt to add a foreign key Created: 2021-05-07  Updated: 2023-11-02  Resolved: 2023-11-02

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Thirunarayanan Balathandayuthapani
Resolution: Duplicate Votes: 1
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-31086 MODIFY COLUMN can break FK constraint... Closed
Relates
relates to MDEV-25620 InnoDB: Failing assertion: id != 0 or... Closed

 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.

 Comments   
Comment by Marko Mäkelä [ 2021-05-10 ]

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.

Comment by Elena Stepanova [ 2021-05-10 ]

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.

Comment by Thirunarayanan Balathandayuthapani [ 2021-05-18 ] 

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.

Comment by Thirunarayanan Balathandayuthapani [ 2023-11-02 ]

This issue should be fixed by MDEV-31086

Generated at Thu Feb 08 09:39:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.