Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2, 10.3, 10.4, 10.5
Description
The following test case repeats the problem:
--source include/have_innodb.inc
|
set foreign_key_checks=0;
|
CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL, f3 int as (f2) virtual,
|
index(f3), index(f3, f2))engine=innodb;
|
ALTER TABLE t1 ADD CONSTRAINT r FOREIGN KEY(f3) REFERENCES t1(f3), LOCK=NONE;
|
ALTER TABLE t1 DROP IF EXISTS f3;
|
SHOW CREATE TABLE t1;
|
DROP TABLE t1;
|
InnoDB shouldn't allow foreign key on virtual column. Final alter drops the virtual column
and fail to load the index for the table entries in SYS_FOREIGN table.
Even other test case fails:
--source include/have_innodb.inc
|
set foreign_key_checks=0;
|
CREATE TABLE t0(f1 int not null, index(f1))engine=innodb;
|
CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL, f3 int as (f2) virtual,
|
index(f3), index(f3, f2))engine=innodb;
|
ALTER TABLE t1 ADD CONSTRAINT r FOREIGN KEY(f3) REFERENCES t0(f1), LOCK=NONE;
|
ALTER TABLE t1 DROP IF EXISTS f3;
|
SHOW CREATE TABLE t1;
|
DROP TABLE t1;
|
The above test case also aborts the server.
Error log contain the following:
2020-08-03 19:09:24 140332217923328 [Warning] InnoDB: Load table `test`.`t1` failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again.