Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
-
None
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.
Attachments
Issue Links
- relates to
-
MDEV-23876 event_scheduler acquires Backup lock and does not release upon completion
- Closed
-
MDEV-24532 Table corruption ER_NO_SUCH_TABLE_IN_ENGINE or ER_CRASHED_ON_USAGE after ALTER on table with foreign key
- Closed