Description:
Missing records in the parent table generate duplicate key entry errors (1062) instead of foreign key constraint failures (1452), when ON DUPLICATE KEY UPDATE is specified for an INSERT.
The bug can be reproduced in MySql 5.5.49, while MySql 5.5.48 works as expected.
How to repeat:
docker run --name mysql_5_5_49 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:5.5.49
docker exec -it mysql_5_5_49 bash -c '
mysql -u root -pmy-secret-pw <<EOF
CREATE DATABASE test;
USE test;
DROP TABLE IF EXISTS Child;
DROP TABLE IF EXISTS Parent;
CREATE TABLE Parent(
parent_id INT UNSIGNED NOT NULL PRIMARY KEY,
value INT UNSIGNED
) ENGINE=InnoDB;
CREATE TABLE Child(
parent_id INT UNSIGNED NOT NULL,
child_id INT UNSIGNED NOT NULL,
value INT UNSIGNED,
PRIMARY KEY (parent_id, child_id),
CONSTRAINT CHILD_PARENT_FK FOREIGN KEY (parent_id)
REFERENCES Parent (parent_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
) ENGINE=InnoDB;
INSERT INTO Child (parent_id, child_id, value) VALUES (1,1,1)
ON DUPLICATE KEY UPDATE value = value + 1;
EOF
'
# prints out:
ERROR 1062 (23000) at line 23: Duplicate entry '1-1' for key 'PRIMARY'
# instead of:
ERROR 1452 (23000) at line 23: Cannot add or update a child row: a foreign key constraint fails (`test`.`Child`, CONSTRAINT `CHILD_PARENT_FK` FOREIGN KEY (`parent_id`) REFERENCES `Parent` (`parent_id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
Suggested fix:
MySql should return ERROR 1452