[MDEV-23387] dict_load_foreign() fails to load the table during alter Created: 2020-08-03  Updated: 2020-10-19  Resolved: 2020-10-19

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB, Virtual Columns
Affects Version/s: 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.2.35, 10.3.26, 10.4.16, 10.5.7

Type: Bug Priority: Major
Reporter: Thirunarayanan Balathandayuthapani Assignee: Thirunarayanan Balathandayuthapani
Resolution: Fixed Votes: 0
Labels: rr-profile-analyzed


 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.

 Comments   
Comment by Thirunarayanan Balathandayuthapani [ 2020-08-05 ]

InnoDB do allow FK on virtual column. Problem exist in commit_inplace_alter_table(), it allows the table to be closed and open it using table name to
update the dict_table_t::v_cols.

Comment by Marko Mäkelä [ 2020-10-19 ]

OK to push.

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