Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6
Description
InnoDB fails to rename the foreign key constraint during rename table alter using
copy algorithm.
The following test case repeats the scenario:
--source include/have_innodb.inc
|
create table t1(f1 int not null, index(f1))engine=innodb;
|
|
create table t2(f1 int not null,
|
foreign key(f1) references t1(f1))engine=innodb;
|
|
alter table t1 rename to tm1, algorithm=copy;
|
|
show create table tm1;
|
Table Create Table
|
tm1 CREATE TABLE `tm1` (
|
`f1` int(11) NOT NULL,
|
KEY `f1` (`f1`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
|
show create table t2;
|
Table Create Table
|
t2 CREATE TABLE `t2` (
|
`f1` int(11) NOT NULL,
|
KEY `f1` (`f1`),
|
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1
|
|
select * from information_schema.innodb_sys_foreign;
|
ID FOR_NAME REF_NAME N_COLS TYPE
|
test/t2_ibfk_1 test/t2 test/t1 1 0
|
|
|
create table t1(f1 int not null)engine=innodb;
|
Error failed: 1215: Cannot add foreign key constraint
|
Analysis:
=========
Copy alter algorithm does the following steps:
1) Create a new table (#sql) and copies the row from old table
- Doesn't add anything in SYS_FOREIGN
2) Rename t1 to #sql2
- Rename the table name in InnoDB dictionary
- Rename the file name too
- Doesn't rename the constraint in SYS_FOREIGN tables
3) RENAME #sql to tm1
- Rename the table name in InnoDB dictionary
- Loads the foreign & referenced index from SYS_FOREIGN
using the table name(tm1). But InnoDB doesn't have any FK constraint
with the name (tm1)
4) DROP #sql2
- Does check whether the SYS_FOREIGN has the foreign key constraint using the table name.
Attachments
Issue Links
- relates to
-
MDEV-16417 Store Foreign Key metadata outside of InnoDB
- In Review
-
MDEV-34105 Failed to INSERT a proper value when no FOREIGN KEY violation
- Confirmed
-
MDEV-25642 Assertion `err != DB_DUPLICATE_KEY' failed in row_rename_table_for_mysql & ERROR 1050 (42S01): Table './test/t1' already exists for simple ALTER TABLE on optimized builds (Related to MDEV-25509)
- Closed