[MDEV-11277] REPLACE query leads to 'duplicate error for key PRIMARY' mesage, but must be 'foreign key constraint fail'. Created: 2016-11-13  Updated: 2016-11-17  Resolved: 2016-11-17

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Trivial
Reporter: Vasiliy Pyatykh Assignee: Unassigned
Resolution: Cannot Reproduce Votes: 0
Labels: None


 Description   

Consider the following example. We just create two tables, second with the foreign key, and our foreign key is also a part of compound primary key of the second table:

CREATE TABLE t
 
    (
 
      `id` int,
 
      `productName` varchar(70),
 
      primary key (id)
 
    );
 
 
 
CREATE TABLE t2
 
    (`id` int, `title` varchar(70), `fk_t` int,
 
     primary key (id, fk_t),
 
     constraint `t2_to_t_fkz` foreign key (`fk_t`) references `t`(`id`));
 
   
insert into t values(1, 'product 1');
 
replace into t2(title, fk_t) values('incorrect fk, replace syntax', 9999);

Last replace is an error, because the foreign key called fk_t leads to nowhere and thats why new row can't be inserted. But the server behavior varies on MySQL and MariaDB.

MySQL says:

Cannot add or update a child row: a foreign key constraint fails (`db_9_63d471`.`t2`, CONSTRAINT `t2_to_t_fkz` FOREIGN KEY (`fk_t`) REFERENCES `t` (`id`))

and that is correct error message.

But MariaDB says:

Error Code: 1062. Duplicate entry '0-9999' for key 'PRIMARY'

What? Duplicate entry? But i am using REPLACE query, you should just delete a row and insert again in such a case. The error messsage is wrong and confusing.

 Comments   
Comment by Elena Stepanova [ 2016-11-16 ]

vpyatykh,

Which MariaDB version are you using? I can't reproduce the problem on any of recent 5.5-10.2.

Could you please paste the full unabridged output (everything, from CREATE TABLE to SELECT @@version from the client similar to below, showing the wrong error message?

MariaDB [test]> CREATE TABLE t
 
    ->     (
 
    ->       `id` int,
 
    ->       `productName` varchar(70),
 
    ->       primary key (id)
 
    ->     );
 
Query OK, 0 rows affected (0.36 sec)
 
 
 
MariaDB [test]>  
MariaDB [test]> CREATE TABLE t2
 
    ->     (`id` int, `title` varchar(70), `fk_t` int,
 
    ->      primary key (id, fk_t),
 
    ->      constraint `t2_to_t_fkz` foreign key (`fk_t`) references `t`(`id`));
 
Query OK, 0 rows affected (0.41 sec)
 
 
 
MariaDB [test]>    
MariaDB [test]> insert into t values(1, 'product 1');
 
Query OK, 1 row affected (0.05 sec)
 
 
 
MariaDB [test]> replace into t2(title, fk_t) values('incorrect fk, replace syntax', 9999);
 
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_to_t_fkz` FOREIGN KEY (`fk_t`) REFERENCES `t` (`id`))
 
MariaDB [test]> select @@version;
 
+-----------------------+
 
| @@version             |
 
+-----------------------+
 
| 10.1.20-MariaDB-debug |
 
+-----------------------+
 
1 row in set (0.00 sec)

Comment by Vasiliy Pyatykh [ 2016-11-17 ]

sorry for confusion, i have just messed up my db install, it is actually a bug in Percona XtraDB Cluster

Generated at Thu Feb 08 07:48:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.