MySQL Bugs: #92471: Adding FK constraints depends on number of rows table has

Bug #92471	Adding FK constraints depends on number of rows table has
Submitted:	18 Sep 2018 6:37	Modified:	10 May 2019 12:26
Reporter:	Krunal Bauskar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.7.23	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:

Bug originates from the workaround that InnoDB (5.7) write_row has.
While executing ALTER with copy, InnoDB commits after every 10K rows.

Post this commit, transaction flags are cleared.

If user has set FOREIGN_KEY_CHECKS=0 then this flag is also cleared
post intermediate commit.
[BTW, all flags are cleared so issue can percolate to other use-cases too].

This means 10,000th insert to a temporary table (given alter algorithm=copy)
will execute with trx->check_foreigns=true when all rows from 1-9999 executed
with trx->check_foreigns=false.

[This is cause of the failure. So a table with < 10K rows can be altered
successfully but table > 10K rows will hit the issue on 10,000th row and ALTER
will fail].

How to repeat:
1. Start mysql server with secure-file-priv = "" since we are trying to do LDI.

2. run the attached script.

rm -rf /tmp/t1.csv && ./bin/mysql --user root < ./load.sql

Expected output:
@@global.secure_file_priv

COUNT(*)
9000
COUNT(*)
9000
Table	Create Table
child	CREATE TABLE `child` (\n  `ID` int(11) NOT NULL,\n  PRIMARY KEY (`ID`),\n  CONSTRAINT `CO4` FOREIGN KEY (`ID`) REFERENCES `parent` (`ID`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8

3. Now change the count in the script from 9000 to 11000

Expected output:

@@global.secure_file_priv

COUNT(*)
11000
COUNT(*)
11000
ERROR 1452 (23000) at line 60: Cannot add or update a child row: a foreign key constraint fails (`test`.`#sql-4ac8_2`, CONSTRAINT `CO4` FOREIGN KEY (`ID`) REFERENCES `parent` (`ID`))

----------------------

Script

shell> cat load.sql

select @@global.secure_file_priv;
SET FOREIGN_KEY_CHECKS = 0; 

drop database if exists test;
create database test;
use test;
drop table if exists t1;
CREATE TABLE `t1` (
  `ID` int NOT NULL,
  primary key `PK0` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

drop procedure if exists load_foo_test_data;

delimiter #
create procedure load_foo_test_data()
begin

declare v_max int unsigned default 9000;
declare v_counter int unsigned default 0;

  truncate table t1;
  start transaction;
  while v_counter < v_max do
    insert into t1 (ID) values (v_counter);
    set v_counter=v_counter+1;
  end while;
  commit;
end #
delimiter ;

call load_foo_test_data();
SELECT COUNT(*) FROM t1;
SELECT * FROM t1 INTO OUTFILE '/tmp/t1.csv';

## import
## Create an empty table

drop table if exists parent;
drop table if exists child;
CREATE TABLE `parent` (
  `ID` int NOT NULL,
  primary key `PK1` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#
#CREATE TABLE `child` (
#  `ID` int NOT NULL,
#  primary key `PK2` (`ID`),
#  CONSTRAINT `CO3` FOREIGN KEY (`ID`) REFERENCES `parent` (`ID`)
#) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `child` (
  `ID` int NOT NULL,
  primary key `PK2` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOAD DATA INFILE '/tmp/t1.csv' IGNORE INTO TABLE child;
SELECT COUNT(*) FROM child;
ALTER TABLE child add CONSTRAINT `CO4` FOREIGN KEY (`ID`) REFERENCES `parent` (`ID`), algorithm=copy;
SHOW CREATE TABLE child;

Suggested fix:
Post intermediate commit (from the write_row workaround), innodb should restore the flags that original transaction started with.

script to reproduce bug

Attachment: load.sql (application/sql, text), 1.41 KiB.

Hello Krunal,

Thank you for the report and test case.
Verified as described with 5.7.23 build.

regards,
Umesh

Posted by developer:
 
Hello!

Indeed the problem is repeatable with recent 5.7 version.
I have used simplified test case and 5.7.27-git:

CREATE TABLE child (id INT NOT NULL);
CREATE TABLE parent (id INT NOT NULL PRIMARY KEY);
DELIMITER |;
CREATE PROCEDURE load_foo_test_data()
BEGIN
DECLARE v_max INT UNSIGNED DEFAULT 11000;
DECLARE v_counter INT UNSIGNED DEFAULT 0;
  WHILE v_counter < v_max DO
    INSERT INTO child (id) VALUES (v_counter);
    SET v_counter = v_counter+1;
  END WHILE;
END |
DELIMITER ;|

CALL load_foo_test_data();
SELECT COUNT(*) FROM child;
#	COUNT(*)
#	11000

SET FOREIGN_KEY_CHECKS = 0;
# The below ALTER incorrectly fails with error 1452 "Cannot add or update
# a child row: a foreign key constraint fails (`test`.`#sql-369b_3`,
# CONSTRAINT `#sql-369b_3_ibfk_1` FOREIGN KEY (`id`) REFERENCES `parent`
# (`id`))".
ALTER TABLE child ADD FOREIGN KEY (id) REFERENCES parent (id), ALGORITHM=COPY;

However, the problem doesn't occur with recent 8.0 (I have used 8.0.17-git):

CREATE TABLE child (id INT NOT NULL);
CREATE TABLE parent (id INT NOT NULL PRIMARY KEY);
DELIMITER |;
CREATE PROCEDURE load_foo_test_data()
BEGIN
DECLARE v_max INT UNSIGNED DEFAULT 11000;
DECLARE v_counter INT UNSIGNED DEFAULT 0;
  WHILE v_counter < v_max DO
    INSERT INTO child (id) VALUES (v_counter);
    SET v_counter = v_counter+1;
  END WHILE;
END |
DELIMITER ;|

CALL load_foo_test_data();
SELECT COUNT(*) FROM child;
#	COUNT(*)
#	11000

SET FOREIGN_KEY_CHECKS = 0;
# The below ALTER succeeds as expected!
ALTER TABLE child ADD FOREIGN KEY (id) REFERENCES parent (id), ALGORITHM=COPY;
SHOW CREATE TABLE child;
#	Table  	Create Table
#	child  	CREATE TABLE `child` (
#		  `id` int(11) NOT NULL,
#		  KEY `id` (`id`),
#		  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`id`) REFERENCES `parent` (`id`)
#		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

This is because intermediate commit which is causing problem in 5.7
was removed by the fix for bug #17479594 "AVOID INTERMEDIATE COMMIT
WHILE DOING ALTER TABLE ALGORITHM=COPY" in 8.0.0.

So I am closing this bug as fixed in 8.0.0. Moving to documenting
state to let Documentation Team to decide if anything needs to
be added to Release Notes.

I saw latest comment
"So I am closing this bug as fixed in 8.0.0. ...."

right, the bug is fixed in 8.x series since the said patch has been worked with different logic but the said bug is still a valid 5.7 bug and since Oracle support 5.7 I presume it is a good idea to resolve the bug unless there is a workaround that justifies why it should not be solved in 5.7.

Posted by developer:
 
Fixed as of the 8.0.0 release, and here's the changelog entry:

An ALTER TABLE ... ALGORITHM=COPY operation that added a foreign key
constraint failed after due to an intermediate commit that occurred after
10000 rows were copied. The intermediate commit reset the foreign key
checks flag, causing the operation to fail. The intermediate commit no
longer occurs.