Bug #12083 Error 'Duplicate entry' on REPLACE INTO on replication slave
Submitted: 21 Jul 2005 14:31 Modified: 22 Jul 2005 10:23
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.20 OS:Windows (XP, Debian, SuSE)
Assigned to: CPU Architecture:Any

[21 Jul 2005 14:31] [ name withheld ]
Description:
Every once in a while a slave stops with an error like 

Error 'Duplicate entry '33' for key 1' on query. Default database: 'xxx'. Query: 'REPLACE INTO ...

This is awkward since 'REPLACE INTO' is triggered by a duplicate key error

Master is Suse 8.x, Slaves are Debian and a local copy runs on XP

How to repeat:
no idea
[21 Jul 2005 17:47] Heikki Tuuri
Hi!

Are the tables MyISAM or InnoDB?

If MyISAM, have you run CHECK TABLE?

In InnoDB, a LOAD DATA INFILE ... REPLACE bug was fixed a month ago.

Regards,

Heikki
[21 Jul 2005 17:50] [ name withheld ]
engine is MyISAM, CHECK TABLE says OK
[21 Jul 2005 18:34] MySQL Verification Team
Is the above table InnoDB ??
[21 Jul 2005 18:40] [ name withheld ]
no InnoDB tables on this system, version 4.0.20 on all machines
[21 Jul 2005 19:29] MySQL Verification Team
There were few bugs fixed interim.

But most probably, this is caused by wrong CHANGE MASTER or by copy data to / from master or slave.
In any case, we require fully repeatable test case.
[21 Jul 2005 19:47] [ name withheld ]
Sorry, no CHANGE MASTER or copy data to / from master or slave. 

What puzzles me is the error description. How can a duplicate key error be raised with REPLACE INTO? Same with SELECT which I had as well on occasion, but very rarely. This doesn't make sense at all logically.

By the way, some of these machines are hundreds of kilometers apart, both production and stage, the local machine is on DSL, and everything runs absolutely smooth as a rule.

Let's see if I can produce a test case next time. Right now I can't, things run fine. It wasn't possible so far, but maybe I can find out more. Let's close the case for now. Thanks a lot!
[23 Jul 2005 14:25] [ name withheld ]
Here is some new information:

1. the error occurs
2. check table says ok
3. put the query to the database through phpMyAdmin - fine
4. same with mysql client - well, some peculiarity, but certainly not this error

Note that the query contains a comment /* ... */ and within this comment a '#' which causes the client to treat the rest of the line after the # as a comment - definitely a bug in the client. Therefore the client needs the rest of the line on a new line, which is treated as the rest of the comment as expected, to finally get the ';' to send the query.

You might raise the hypotheses that this '#' is what causes the error - but no, this comment is given in each and every query, select or other; it shows which machine issued the query and which role took care of it - master or slave. Of course, here only master-queries can show. If this '#' within a /*...*/ would be a cause, nothing could ever move.

5. Frankly, I have no idea how to set up a clean test case. This error occurred on my local XP machine, the other slaves didn't produce an error at all. But this isn't a problem of machines or operation systems. 

I recall that I had this with a Debian system as well. I didn't investigate into this, but rather decided that the table in question wasn't worth the trouble and took that out of replication as a workaround.

Here is the output:
----------------------------------------------------------------------------
1. 

error

MySQL on MAX ver 4.0.20 instance 1>SHOW SLAVE STATUS\G
*************************** 1. row ***************************
          Master_Host: pz-server1.xxx.com
          Master_User: repl
          Master_Port: 3306
        Connect_retry: 60
      Master_Log_File: pz-server1-bin.426
  Read_Master_Log_Pos: 64626911
       Relay_Log_File: max-relay-bin.007
        Relay_Log_Pos: 39649626
Relay_Master_Log_File: pz-server1-bin.426
     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
      Replicate_do_db:
  Replicate_ignore_db: servcontrol
           Last_errno: 1062
           Last_error: Error 'Duplicate entry '296852' for key 1' on query. Defa
ult database: 'xxx'. Query: 'REPLACE INTO menuPage
                        (idEdMp, keyMp, itemMp)
                        VALUES ('303', 1, 'Der \"alte Mann\"') /* Link_ID Resour
ce id #13 | Host_Master pz-server1.xxx.com | SERVER_ADDR 127.0.0.1*/'
         Skip_counter: 1
  Exec_master_log_pos: 38288379
      Relay_log_space: 65988248
1 row in set (0.00 sec)

----------------------------------------------------------------------------
2.

check table

MySQL on MAX ver 4.0.20 instance 1>check table menuPage;
+------------------------+-------+----------+----------+
| Table                  | Op    | Msg_type | Msg_text |
+------------------------+-------+----------+----------+
| xxx.menuPage      | check | status   | OK       |
+------------------------+-------+----------+----------+
1 row in set (0.27 sec)

----------------------------------------------------------------------------
3.

phpMyAdmin

insert_id: 296871 Inserted rows: 1 
REPLACE INTO menuPage (idEdMp, keyMp, itemMp) VALUES ('303', 1, 'Der "alte Mann"') /* Link_ID Resource id #13 | Host_Master pz-server1.xxx.com | SERVER_ADDR 127.0.0.1*/'

----------------------------------------------------------------------------
4.

mysql client

1. try: replace '#' with 'No.'

MySQL on MAX ver 4.0.20 instance 2>REPLACE INTO menuPage (idEdMp, keyMp, itemMp)
 VALUES ('303', 1, 'Der \"alte Mann\"') /* Link_ID Resource id No. 13 | Host_Mas
ter pz-server1.xxx.com | SERVER_ADDR 127.0.0.1*/;
Query OK, 2 rows affected (0.00 sec)

2. try: original query

MySQL on MAX ver 4.0.20 instance 2>REPLACE INTO menuPage (idEdMp, keyMp, itemMp)
 VALUES ('303', 1, 'Der \"alte Mann\"') /* Link_ID Resource id # 13 | Host_Maste
r pz-server1.xxx.com | SERVER_ADDR 127.0.0.1*/;
    -> 13 | Host_Master pz-server1.xxx.com | SERVER_ADDR 127.0.0.1*/;
Query OK, 2 rows affected (0.05 sec)

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

Interresting, somehow. Any ideas?
[24 Jul 2005 6:15] [ name withheld ]
Something new. This morning, I had the same problem both with my Office XP system and the Debian client, but at different offsets of the master log (i.e. different statements, but of the same type, but using a different table from yesterday). The SuSE client had no problems at all.

As the test yesterday evening revealed already, this is not a problem of the tables - otherwise the "REPLACE INTO" should have failed with the other methods as well. Now this experience shows that the phenomenon doesn't even depend on the original data given by the master, else the problem should have appeared likewise on all slaves.

Another thing: The tables involved today and yesterday are updated at the same time, so yesterday one type produced trouble, the other not, today the other way around.

Very peculiar.