1

Mysql Slave replication was working fine, but suddenly it has crashed due to duplicate entries error The slave SQL thread stops due to duplicate entries in MySQL DB, which I have kept in binlog_ignore_db on both Master and slave. I have skipped these errors number of times, but keep getting on different tables in MySQL DB(which is default DB). Following are some of the errors, which I got after using SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

Last_Error: Error 'Duplicate entry 'pma_test_host-mysql-pma_test_user' for key 'PRIMARY'' on query. Default database: ''. Query: 'INSERT INTO `mysql`.`db`(`host`, `Db`, `User`) VALUES("pma_test_host", "mysql", "pma_test_user")'

Last_Error: Error 'Duplicate entry 'pma_test_host-mysql-pma_test_user-' for key 'PRIMARY'' on query. Default database: ''. Query: 'INSERT INTO `mysql`.`tables_priv`(`host`, `Db`, `User`, `Table_name`) VALUES("pma_test_host", "mysql", "pma_test_user", "")'

Last_SQL_Error: Error 'Duplicate entry 'pma_test_host-mysql-pma_test_user' for key 'PRIMARY'' on query. Default database: ''. Query: 'INSERT INTO `mysql`.`db`(`host`, `Db`, `User`) VALUES("pma_test_host", "mysql", "pma_test_user")'

Last_SQL_Error: Error 'Duplicate entry 'pma_test_host-mysql-pma_test_user--' for key 'PRIMARY'' on query. Default database: ''. Query: 'INSERT INTO `mysql`.`columns_priv`(`host`, `Db`, `User`, `Table_name`, `Column_name`) VALUES("pma_test_host", "mysql", "pma_test_user", "", "")'

Last_SQL_Error: Error 'Duplicate entry 'pma_test_host-mysql-pma_test_user--PROCEDURE' for key 'PRIMARY'' on query. Default database: ''. Query: 'INSERT INTO `mysql`.`procs_priv`(`host`, `Db`, `User`, `Routine_name`, `Routine_type`) VALUES("pma_test_host", "mysql", "pma_test_user", "", "PROCEDURE")

Last_SQL_Error: Error 'Duplicate entry 'pma_test_host-mysql-pma_test_user' for key 'PRIMARY'' on query. Default database: ''. Query: 'INSERT INTO `mysql`.`db`(`host`, `Db`, `User`) VALUES("pma_test_host", "mysql", "pma_test_user")'

Last_SQL_Error: Error 'Duplicate entry 'pma_test_host-mysql-pma_test_user--' for key 'PRIMARY'' on query. Default database: ''. Query: 'INSERT INTO `mysql`.`columns_priv`(`host`, `Db`, `User`, `Table_name`, `Column_name`) VALUES("pma_test_host", "mysql", "pma_test_user", "", "")

Last_SQL_Error: Error 'Duplicate entry 'pma_test_host-mysql-pma_test_user-' for key 'PRIMARY'' on query. Default database: ''. Query: 'INSERT INTO `mysql`.`tables_priv`(`host`, `Db`, `User`, `Table_name`) VALUES("pma_test_host", "mysql", "pma_test_user", "")'

Last_SQL_Error: Error 'Duplicate entry 'pma_test_host-mysql-pma_test_user--PROCEDURE' for key 'PRIMARY'' on query. Default database: ''. Query: 'INSERT INTO `mysql`.`procs_priv`(`host`, `Db`, `User`, `Routine_name`, `Routine_type`) VALUES("pma_test_host", "mysql", "pma_test_user", "", "PROCEDURE")'

Last_SQL_Error: Error 'Duplicate entry 'pma_test_host-mysql-pma_test_user' for key 'PRIMARY'' on query. Default database: ''. Query: 'INSERT INTO `mysql`.`db`(`host`, `Db`, `User`) VALUES("pma_test_host", "mysql", "pma_test_user")'

Last_SQL_Error: Error 'Duplicate entry 'pma_test_host-mysql-pma_test_user--' for key 'PRIMARY'' on query. Default database: ''. Query: 'INSERT INTO `mysql`.`columns_priv`(`host`, `Db`, `User`, `Table_name`, `Column_name`) VALUES("pma_test_host", "mysql", "pma_test_user", "", "")

Last_SQL_Error: Error 'Duplicate entry 'pma_test_host-mysql-pma_test_user-' for key 'PRIMARY'' on query. Default database: ''. Query: 'INSERT INTO `mysql`.`tables_priv`(`host`, `Db`, `User`, `Table_name`) VALUES("pma_test_host", "mysql", "pma_test_user", "")'

Last_SQL_Error: Error 'Duplicate entry 'pma_test_host-mysql-pma_test_user--PROCEDURE' for key 'PRIMARY'' on query. Default database: ''. Query: 'INSERT INTO `mysql`.`procs_priv`(`host`, `Db`, `User`, `Routine_name`, `Routine_type`) VALUES("pma_test_host", "mysql", "pma_test_user", "", "PROCEDURE")

My question is why is the slave copying the Database named MySQL, while I have explicitly kept it in ignore list and I have also explicitly mentioned the name of DB which I want to replicate.

How should I resolve this issue? I don't want to use slave-skip-errors = 1062 in MySQL settings as it may lead to inconsistency.

Slave configuration settings shown below:

server-id               = 2
binlog_format           = 'MIXED'
log_bin                 = /var/log/mysql/mysql-bin.log
relay-log               = /var/log/mysql/mysql-relay-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = vi*******n
binlog_ignore_db        = phpmyadmin
binlog_ignore_db        = mysql
read_only               = on

Master configuration settings shown below:

server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_format           = 'MIXED'
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = ******
binlog_ignore_db        = mysql
  • What version of MySQL are the master and slaves? – Shaulinator Mar 28 '18 at 15:13
  • Slave : mysql Ver 14.14 Distrib 5.5.53, for debian-linux-gnu (x86_64) using readline 6.3 Master :mysql Ver 14.14 Distrib 5.7.21, for Linux (x86_64) It has been working fine for 3 months, so i think version shouldn't matter. – jugadengg Mar 28 '18 at 17:30
  • I have the exact same issue, I wanted to compare versions to see if we were in the same boat. Are you using MyIsam at all? – Shaulinator Mar 28 '18 at 17:35
  • @Shaulinator No, I'm not using that. – jugadengg Mar 29 '18 at 6:53
  • @Shaulinator did you find any solution? – jugadengg Mar 29 '18 at 7:04
0

binlog-ignore-db is only a master-side setting, it tells the Master not to log changes taking place on the listed DB.

replicate-ignore-db is only a slave-side setting, it tells the Slave to ignore incoming log information related to the listed DB

@jugadengg in your case please use replicate-ignore-db instead in your Slave configuration.

  • I would also like to know why the error is occuring, as the particular DB is put in ignore list on master side. – jugadengg Apr 2 '18 at 19:32
  • @jugadengg i assume that the statement were already in binary log before you apply the ignore on the master and also you were using binlog_ignore_db instead of replicate-ignore-db on the slave that also can explain why the mysql schema filter was not working. To avoid the crash going forward, you can temporary use code slave-skip-errors = 1062 code and once all the slave is synced to the master remove the slave-skip-errors in your config file to avoid inconsistency – Mikael HOUNDEGNON Apr 4 '18 at 15:06
  • By gut feelings I did the same and removed skip-erros=1062 when sync was complete. TIll now for almost a week, no problem has been encountered. Thanks for all the help. – jugadengg Apr 6 '18 at 12:49
  • @jugadengg, i'm glad everything is fine, when you have a chance please marked the correct answer then – Mikael HOUNDEGNON Apr 27 '18 at 18:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.