| Bug #81486 | ERROR 1452 throws as ERROR 1062 if query has ON DUPLICATE stament | ||
|---|---|---|---|
| Submitted: | 18 May 2016 11:43 | Modified: | 19 May 2016 9:35 |
| Reporter: | Алексей Солодкий | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.6.30, 5.5.49 | OS: | Debian |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[18 May 2016 11:43]
Алексей Солодкий
[18 May 2016 11:47]
Алексей Солодкий
In 5.6.24-log version it's correct to.
[18 May 2016 12:30]
MySQL Verification Team
Hello! Thank you for the report and test case. Observed that 5.6.30/5.5.49 builds are affected. Thanks, Umesh
[18 May 2016 12:30]
MySQL Verification Team
-- 5.5.49 - affected
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.5.49: bin/mysql -uroot -S/tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.49-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test
Database changed
mysql> CREATE TABLE `table2` (
-> `id` INT(10) UNSIGNED NOT NULL,
-> PRIMARY KEY (`id`)
-> )
COLLATE='utf8_general_ci'
ENGINE=InnoDB
-> COLLATE='utf8_general_ci'
-> ENGINE=InnoDB
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TABLE `table1` (
-> `id` INT(10) UNSIGNED NOT NULL,
-> `fk` INT(10) UNSIGNED NOT NULL,
-> PRIMARY KEY (`id`),
-> INDEX `FK_table1_table2` (`fk`),
-> CONSTRAINT `FK_table1_table2` FOREIGN KEY (`fk`) REFERENCES `table2` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
-> )
-> COLLATE='utf8_general_ci'
-> ENGINE=InnoDB
-> ;
INSERT INTO `table1`
(id, fk) VALUES (1, 2)
ON DUPLICATE KEY UPDATE id=1,fk=3;Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO `table1`
-> (id, fk) VALUES (1, 2)
-> ON DUPLICATE KEY UPDATE id=1,fk=3;
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql>
[18 May 2016 12:31]
MySQL Verification Team
-- 5.6.30 - affected
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.6.30: bin/mysql -uroot -S/tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.30-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test
Database changed
mysql> CREATE TABLE `table2` (
-> `id` INT(10) UNSIGNED NOT NULL,
-> PRIMARY KEY (`id`)
-> )
-> COLLATE='utf8_general_ci'
-> ENGINE=InnoDB
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TABLE `table1` (
-> `id` INT(10) UNSIGNED NOT NULL,
-> `fk` INT(10) UNSIGNED NOT NULL,
-> PRIMARY KEY (`id`),
-> INDEX `FK_table1_table2` (`fk`),
-> CONSTRAINT `FK_table1_table2` FOREIGN KEY (`fk`) REFERENCES `table2` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
-> )
-> COLLATE='utf8_general_ci'
-> ENGINE=InnoDB
-> ;
ble1`
(id, fk) VALUES (1, 2)
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> INSERT INTO `table1`
-> (id, fk) VALUES (1, 2)
-> ON DUPLICATE KEY UPDATE id=1,fk=3;
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql>
[18 May 2016 12:31]
MySQL Verification Team
-- 5.7.12 - seems OK
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.12: bin/mysql -uroot -S/tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.12-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Database changed
mysql> CREATE TABLE `table2` (
-> `id` INT(10) UNSIGNED NOT NULL,
-> PRIMARY KEY (`id`)
-> )
-> COLLATE='utf8_general_ci'
-> ENGINE=InnoDB
;
-> CREATE TABLE `table1` ;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TABLE `table1` (
-> `id` INT(10) UNSIGNED NOT NULL,
-> `fk` INT(10) UNSIGNED NOT NULL,
-> PRIMARY KEY (`id`),
-> INDEX `FK_table1_table2` (`fk`),
-> CONSTRAINT `FK_table1_table2` FOREIGN KEY (`fk`) REFERENCES `table2` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
-> )
-> COLLATE='utf8_general_ci'
-> ENGINE=InnoDB
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO `table1`
-> (id, fk) VALUES (1, 2)
-> ON DUPLICATE KEY UPDATE id=1,fk=3;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`table1`, CONSTRAINT `FK_table1_table2` FOREIGN KEY (`fk`) REFERENCES `table2` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql>
[18 May 2016 12:32]
MySQL Verification Team
-- 5.6.24 - seems OK
[umshastr@hod03]/export/umesh/server/binaries/mysql-5.6.24: bin/mysql -uroot -S/tmp/mysql_ushastry.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.24 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test
Database changed
mysql> CREATE TABLE `table2` (
-> `id` INT(10) UNSIGNED NOT NULL,
-> PRIMARY KEY (`id`)
-> )
-> COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
-> ENGINE=InnoDB
-> ;
CREATE TABLE `table1` (
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TABLE `table1` (
-> `id` INT(10) UNSIGNED NOT NULL,
-> `fk` INT(10) UNSIGNED NOT NULL,
INDEX `FK_table1_table2` (`fk`),
-> PRIMARY KEY (`id`),
-> INDEX `FK_table1_table2` (`fk`),
-> CONSTRAINT `FK_table1_table2` FOREIGN KEY (`fk`) REFERENCES `table2` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
-> )
-> COLLATE='utf8_general_ci'
-> ENGINE=InnoDB
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> INSERT INTO `table1`
(id, fk) VALUES (1, 2)
O -> N DUP (id, fk) VALUES (1, 2)
-> ON DUPLICATE KEY UPDATE id=1,fk=3;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`table1`, CONSTRAINT `FK_table1_table2` FOREIGN KEY (`fk`) REFERENCES `table2` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
mysql>
[19 May 2016 9:35]
Nisha Padmini Gopalakrishnan
Pushed into repo mysql branch mysql-5.5 5.5.50 (hash:d36cd341d121bfd9a68f90fce6dfd2526fa9a8c8 committer:nisha.gopalakrishnan@oracle.com)
[19 May 2016 9:39]
MySQL Verification Team
Per 5.5.50, 5.6.31, 5.7.13 changelogs. INSERT with ON DUPLICATE KEY UPDATE and REPLACE on a table with a foreign key constraint defined failed with an incorrect "duplicate entry" error rather than a foreign key constraint violation error.
[24 May 2016 18:51]
MySQL Verification Team
Bug #81578 marked as duplicate of this
