| Bug #33753 | can not replicate update row event to slave after to dupli-key error. | ||
|---|---|---|---|
| Submitted: | 9 Jan 2008 2:00 | Modified: | 21 Oct 2008 2:58 |
| Reporter: | li pickup (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Row Based Replication ( RBR ) | Severity: | S2 (Serious) |
| Version: | 5.1.20 | OS: | Linux |
| Assigned to: | Andrei Elkin | CPU Architecture: | Any |
| Tags: | dupli-key error, row_based replication, update_rows_event | ||
[10 Jan 2008 14:47]
Susanne Ebrecht
Many thanks for writing a bug report. MySQL 5.1.20 is quiet old. Please try our newest version: MySQL 5.1.22-rc and let us know, if the error still occurs.
[15 Jan 2008 1:30]
li pickup
Thanks for reply. I have test the case in 5.1.22, and the phenomenon is still alive.
[28 Jan 2008 8:12]
li pickup
I am sorry to tell that the bug may not be repeated in your MySQL server version as in bug 33880. My MySQL server version of test environment is 5.1.22-rc-log. And the repeat step is: master> select version()\G *************************** 1. row *************************** version(): 5.1.22-rc-log 1 row in set (0.00 sec) master> show variables like 'binlog_format'\G *************************** 1. row *************************** Variable_name: binlog_format Value: ROW 1 row in set (0.00 sec)] master> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+ 3 rows in set (0.00 sec) master> create database king1; Query OK, 1 row affected (0.00 sec) master> use king1; Database changed master> create table testGeo (id int(11) NOT NULL, a008 float(12) unsigned, a027 point,PRIMARY KEY (id)); Query OK, 0 rows affected (0.00 sec) master> show tables; +-----------------+ | Tables_in_king1 | +-----------------+ | tb16timestamp | +-----------------+ 1 row in set (0.00 sec) master> insert into tb16timestamp (tt16) values ('2007-01-11 11:11:11'); Query OK, 1 row affected (0.00 sec) master> insert into tb16timestamp (tt16) values ('2007-02-16 11:11:11'); Query OK, 1 row affected (0.00 sec) master> insert into tb16timestamp (tt16) values ('2007-02-17 11:11:11'); Query OK, 1 row affected (0.00 sec) master> update tb16timestamp set tt16=replace(tt16, 02, 10); ERROR 1062 (23000): Duplicate entry '0000-00-00 00:00:00' for key 'PRIMARY' master> update tb16timestamp set tt16=replace(tt16, 02, 01); ERROR 1062 (23000): Duplicate entry '0000-00-00 00:00:00' for key 'PRIMARY' master> update tb16timestamp set tt16=replace(tt16, '02', '01'); Query OK, 2 rows affected (0.00 sec) Rows matched: 3 Changed: 2 Warnings: 0 master> select * from tb16timestamp; +---------------------+-----------+ | tt16 | server_id | +---------------------+-----------+ | 2007-01-11 11:11:11 | 1 | | 2007-01-16 11:11:11 | 1 | | 2007-01-17 11:11:11 | 1 | +---------------------+-----------+ 3 rows in set (0.00 sec) on the slave, the data is not replicated to slave now: slave> select version()\G *************************** 1. row *************************** version(): 5.1.22-rc-log 1 row in set (0.00 sec) slave> use king1; Database changed slave> select * from tb16timestamp; +---------------------+-----------+ | tt16 | server_id | +---------------------+-----------+ | 2007-01-11 11:11:11 | 1 | | 2007-02-16 11:11:11 | 1 | | 2007-02-17 11:11:11 | 1 | +---------------------+-----------+ 3 rows in set (0.00 sec) when we insert a row on the master, it will replicated to slave. on the master: master> insert into tb16timestamp (tt16) values ('2007-02-19 11:11:11'); Query OK, 1 row affected (0.00 sec) master> select * from tb16timestamp; +---------------------+-----------+ | tt16 | server_id | +---------------------+-----------+ | 2007-01-11 11:11:11 | 1 | | 2007-01-16 11:11:11 | 1 | | 2007-01-17 11:11:11 | 1 | | 2007-02-19 11:11:11 | 1 | +---------------------+-----------+ 4 rows in set (0.00 sec) but the row on the slave is not the same: slave> select * from tb16timestamp; +---------------------+-----------+ | tt16 | server_id | +---------------------+-----------+ | 2007-01-11 11:11:11 | 1 | | 2007-02-16 11:11:11 | 1 | | 2007-02-17 11:11:11 | 1 | | 2007-02-19 11:11:11 | 1 | +---------------------+-----------+ 4 rows in set (0.00 sec) I hope the bug was fix in your version of MySQL server. Many thanks for your attention and kind help.
[31 Jan 2008 17:17]
Susanne Ebrecht
Unfortunately, you are right. I can't reproduce this.
master> create table t(tst timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, i integer, primary key(tst));
master> insert into t values ('2007-01-11 11:11:11',1);
master> insert into t values ('2007-02-16 11:11:11',2);
master> insert into t values ('2007-02-17 11:11:11',3);
slave> select * from t;
+---------------------+------+
| tst | i |
+---------------------+------+
| 2007-01-11 11:11:11 | 1 |
| 2007-02-16 11:11:11 | 2 |
| 2007-02-17 11:11:11 | 3 |
+---------------------+------+
master> update t set tst=replace(tst, '02', '01');
Query OK, 2 rows affected (0.00 sec)
Rows matched: 3 Changed: 2 Warnings: 0
master> select * from t;
+---------------------+------+
| tst | i |
+---------------------+------+
| 2007-01-11 11:11:11 | 1 |
| 2007-01-16 11:11:11 | 2 |
| 2007-01-17 11:11:11 | 3 |
+---------------------+------+
3 rows in set (0.00 sec)
slave> select * from t;
+---------------------+------+
| tst | i |
+---------------------+------+
| 2007-01-11 11:11:11 | 1 |
| 2007-01-16 11:11:11 | 2 |
| 2007-01-17 11:11:11 | 3 |
+---------------------+------+
This looks similiar for me.
Tested with:
version(): 5.1.22-rc-debug
Same here with this bug. I need the exact information of your operating system.
[1 Feb 2008 2:16]
li pickup
Many thanks for your attention. I am sorry to hear that you can not repeat it.
I notice that you have not issued:
master> update tb16timestamp set tt16=replace(tt16, 02, 10);
ERROR 1062 (23000): Duplicate entry '0000-00-00 00:00:00' for key 'PRIMARY'
master> update tb16timestamp set tt16=replace(tt16, 02, 01);
ERROR 1062 (23000): Duplicate entry '0000-00-00 00:00:00' for key 'PRIMARY'
Before committed a correct update query.
May be it is the key because the data would be identified between slave and master in my environment if I did not issue the two wrong update queries before.
My system is centos 4.4 (Redhat linux). It is a 64 bit version.
master:
[root@host26 ~]# uname -a
Linux host26 2.6.16-xen #1 SMP Fri Jul 14 10:02:29 CST 2006 x86_64 x86_64 x86_64 GNU/Linux
slave:
[root@host25 ~]# uname -a
Linux host25 2.6.16-xen #1 SMP Fri Jul 14 10:02:29 CST 2006 x86_64 x86_64 x86_64 GNU/Linux
and the sync_binlog flag is:
master> show variables like "sync_binlog"\G
*************************** 1. row ***************************
Variable_name: sync_binlog
Value: 1
1 row in set (0.00 sec)
[19 Feb 2008 6:31]
li pickup
I am sorry to tell you that I can repeat this bug on version 5.1.23-rc-debug in our environment.
[19 Feb 2008 7:11]
li pickup
I am sorry to tell you that I can repeat this bug on version 5.1.23-rc-debug in our environment.
[10 Mar 2008 5:48]
li pickup
my.cnf of my environment
Attachment: my.cnf.5.1.23 (text/plain), 1.99 KiB.
[15 Oct 2008 4:06]
Valeriy Kravchuk
Please, try to repeat with a newer version, 5.1.28, and inform about the results.
[21 Oct 2008 2:58]
li pickup
I am glad to find it can not repeat in the version of 5.1.28. Congratulations!
Description: The update query can not be replicated to slave after two dupli-key error. How to repeat: the binlog_format was set to row in the master. 1, create a table like this: CREATE TABLE tb16timestamp ( tt16 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, server_id int(11) DEFAULT '1', PRIMARY KEY (tt16)); 2, insert 3 rows to the table: insert into tb16timestamp (tt16) values ('2007-01-11 11:11:11'); insert into tb16timestamp (tt16) values ('2007-02-16 11:11:11'); insert into tb16timestamp (tt16) values ('2007-02-17 11:11:11'); 3, issue two wrong update query to master: update tb16timestamp set tt16=replace(tt16, 02, 10); update tb16timestamp set tt16=replace(tt16, 02, 01); the errmsg return as: "ERROR 1062 (23000): Duplicate entry '0000-00-00 00:00:00' for key 'PRIMARY'" 4, commit a correct update query to master: update tb16timestamp set tt16=replace(tt16, '02', '01'); it return that: "Query OK, 2 rows affected (0.00 sec) Rows matched: 3 Changed: 2 Warnings: 0" The master data is changed correctly: mysql> select * from tb16timestamp; +---------------------+-----------+ | tt16 | server_id | +---------------------+-----------+ | 2007-01-11 11:11:11 | 1 | | 2007-01-16 11:11:11 | 1 | | 2007-01-17 11:11:11 | 1 | +---------------------+-----------+ 3 rows in set (0.00 sec) but it is not replicated to slave at that moment in our environment. so I commit a insert query to synchronize it. 5, commit a insert query to master: insert into tb16timestamp (tt16) values ('2007-02-19 11:11:11'); the data in the master is: mysql> select * from tb16timestamp; +---------------------+-----------+ | tt16 | server_id | +---------------------+-----------+ | 2007-01-11 11:11:11 | 1 | | 2007-01-16 11:11:11 | 1 | | 2007-01-17 11:11:11 | 1 | | 2007-02-19 11:11:11 | 1 | +---------------------+-----------+ 4 rows in set (0.00 sec) but the data in the slave is: mysql> select * from tb16timestamp; +---------------------+-----------+ | tt16 | server_id | +---------------------+-----------+ | 2007-01-11 11:11:11 | 1 | | 2007-02-16 11:11:11 | 1 | | 2007-02-17 11:11:11 | 1 | | 2007-02-19 11:11:11 | 1 | +---------------------+-----------+ 4 rows in set (0.00 sec) two row update in the master was not replicated to slave. Suggested fix: I can not fix it.