[MDEV-24552] ALTER causes discrepancy in FK information between server and engine, unexpected ER_CANNOT_ADD_FOREIGN Created: 2021-01-09  Updated: 2023-04-27

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

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Marko Mäkelä
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-23876 event_scheduler acquires Backup lock... Closed
relates to MDEV-24532 Table corruption ER_NO_SUCH_TABLE_IN_... Closed

 Description   

Possibly related to MDEV-24532.

--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (a INT, b INT, KEY(a), FOREIGN KEY(b) REFERENCES t1(a)) ENGINE=InnoDB;
 
SET FOREIGN_KEY_CHECKS= OFF;
 
ALTER TABLE t1 MODIFY a SMALLINT;
 
SHOW CREATE TABLE t1;
 
SELECT * FROM information_schema.innodb_sys_foreign;
 
SELECT UNIQUE_CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME FROM information_schema.referential_constraints;
 
TRUNCATE TABLE t1;
 
 
 
# Cleanup
 
DROP TABLE t1;

In the test case above, 10.2+ versions accept and execute ALTER TABLE, and the column type gets modified. However, after that

  • SHOW CREATE TABLE does not show the foreign key any longer;
  • information_schema.innodb_sys_foreign still has a record for the foreign key on t1, but information_schema.referential_constraints does not;
  • further TRUNCATE TABLE fails with ER_CANNOT_ADD_FOREIGN.

10.5 1b12e251

 
SHOW CREATE TABLE t1;
 
Table	Create Table
 
t1	CREATE TABLE `t1` (
 
  `a` smallint(6) DEFAULT NULL,
 
  `b` int(11) DEFAULT NULL,
 
  KEY `a` (`a`),
 
  KEY `b` (`b`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
SELECT * FROM information_schema.innodb_sys_foreign;
 
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
 
test/t1_ibfk_1	test/t1	test/t1	1	0
 
SELECT UNIQUE_CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME FROM information_schema.referential_constraints;
 
UNIQUE_CONSTRAINT_NAME	TABLE_NAME	REFERENCED_TABLE_NAME
 
TRUNCATE TABLE t1;
 
bug.trunc4 'innodb'                      [ fail ]
 
        Test ended at 2021-01-09 02:27:46
 
 
 
CURRENT_TEST: bug.trunc4
 
mysqltest: At line 9: query 'TRUNCATE TABLE t1' failed: 1215: Cannot add foreign key constraint for `t1`

Reproducible on 10.2-10.5.
On 10.1 and MySQL 5.7 ALTER TABLE fails with ER_ERROR_ON_RENAME, and no further damage is done.

 Comments   
Comment by Elena Stepanova [ 2021-01-09 ]

Here is another test case. I don't know if it's exactly the same issue, since there is no visible discrepancy here, just the unexpected error, but creating yet another JIRA report about TRUNCATE table with corrupt foreign keys, in addition to this one and MDEV-24532, seems way too excessive. Feel free to treat it any way you like.

--source include/have_innodb.inc
 
 
 
CREATE TABLE t1 (a INT, b INT, KEY ind1(a,b), KEY ind2(a), FOREIGN KEY(a) REFERENCES t1(a)) ENGINE=InnoDB;
 
ALTER TABLE t1 DROP KEY ind1;
 
TRUNCATE TABLE t1;
 
 
 
# Cleanup
 
DROP TABLE t1;

The test case is admittedly not very practical, but on the "positive" side it doesn't require disabling FOREIGN_KEY_CHECKS.
Unlike with the test case in the description, ALTER does not cause the FK to disappear from SHOW CREATE or I_S tables; however, TRUNCATE still causes ER_CANNOT_ADD_FOREIGN.

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