Bug #81578 INSERT ON DUPLICATE KEY UPDATE statement returning 1062 instead of 1452
Submitted: 24 May 2016 18:38 Modified: 24 May 2016 18:51
Reporter: Davide Romani Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.5.49 OS:Any
Assigned to: CPU Architecture:Any

[24 May 2016 18:38] Davide Romani
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
[24 May 2016 18:51] MySQL Verification Team
Hello David,

Thank you for the report.
This is most likely duplicate of Bug #81486, please see Bug #81486

Thanks,
Umesh