2

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
6
  • What version of MySQL are the master and slaves? Mar 28, 2018 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, 2018 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? Mar 28, 2018 at 17:35
  • @Shaulinator No, I'm not using that.
    – jugadengg
    Mar 29, 2018 at 6:53
  • @Shaulinator did you find any solution?
    – jugadengg
    Mar 29, 2018 at 7:04

1 Answer 1

1

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.

4
  • 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, 2018 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 Apr 4, 2018 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, 2018 at 12:49
  • @jugadengg, i'm glad everything is fine, when you have a chance please marked the correct answer then Apr 27, 2018 at 18:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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