On checking MySQL/MariaDB slave status we can see the following error:
|
1 2 3 4 |
mysql> SHOW SLAVE STATUS\G ... Last_SQL_Errno: 1062 Last_SQL_Error: Error 'Duplicate entry '146474' for key 'PRIMARY'' on query. Default database: 'db'. Query: 'INSERT INTO `options` (`name`, `value, autoload`) VALUES ('enable', 'true', 'true')' |
There are a few ways how to treat such kind of errors.
Repair MySQL table
|
1 |
mysql> REPAIR TABLE options; |
If repairement completed successfully check replication status again.
If you want to repair all tables in your database run the following script:
|
1 2 3 |
SELECT CONCAT('repair table ', table_name, ';') FROM information_schema.tables WHERE table_schema='YourDatabaseName'; |
Ignore MySQL error 1062
This is not recommended way but can back you in business quickly:
|
1 |
mysql> STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; |
This command will make MySQL/MariaDB to ignore error 1062 one time.
If you have such kind of errors regularily you should drill down to a root cause and fix your application.
If this error does not break anything you can ignore it constantly. In /etc/my.cnf on Slave server:
slave-skip-errors = 1062
You can also ignore problematic tables.
In /etc/my.cnf on Slave server:
|
1 |
replicate_wild_ignore_table = pets.% |
Ignore all tables from a database ‘pets‘.
You can specify multiple directives:
|
1 2 |
replicate_wild_ignore_table = pets.% replicate_wild_ignore_table = breed.dogs_% |
Also ignore all tables starting from dogs_ in database ‘breed‘
