Tag Archives: duplicate entry

MySQL Replication Failed

I wanted to replicate a MySQL-Database using the integrated Master -> Slave mechanism.

Master-System:   openSUSE 10.2, MySQL 5.0.26
The Slave-Setup: openSUSE 11.1, MySQL 5.0.67

This is quite easy to setup usually. This time, I almost had a nervous breakdown.
On the slave side the replication just didn’t start. The logfile showed these errors:

090421 20:49:28 [ERROR] Slave: Error 'Duplicate entry '790233' for key 1' on query.
...
090421 20:49:28 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000008' position 30408893


As checking the database and making sure that there were no “duplicate entries”,
did not bring up any results, I found something curious (after hours and hours).

On the Master-System:

mysql> SHOW MASTER STATUS;
+- - - - - - - - + - - - - - + - - - - - - - + - - - - - - - - -+
| File           | Position  | Binlog_Do_DB  | Binlog_Ignore_DB |
+- - - - - - - - + - - - - - + - - - - - - - + - - - - - - - - -+
| mysql-bin.0001 |    10034  | mydb,mydb     |                  |
+- - - - - - - - + - - - - - + - - - - - - - + - - - - - - - - -+

Why is the database listed two times? In the configuration it’s only used one time:
 
binlog-do-db = mydb

 
After spending some time on google, I found the reason:
http://bugs.mysql.com/bug.php?id=20748
It’s a bug : / MySQL (in that version) is reading the configuration file twice.
 
If installing a newer version is not possible, you can use this workaround:
Start MySQL with the option --defaults-file=/etc/my.cnf
This advices MySQL to ONLY use this config file.