Mysql – Which error codes are safe for `slave_skip_errors` with MySQL Innodb

errorsinnodbMySQLreplication

Does anyone know which error codes are safe to ignore during replication, meaning the slave will remain consistent with the master?

For example

[mysqld]
slave-skip-errors=1007,1008,1050,1051,1054,1060,1061,1068,1094,1146

The example that I found omitted is 1213 (Deadlock found when trying to get lock; try restarting transaction, Error_code: 1213; handler error HA_ERR_LOCK_DEADLOCK), which seems safe to me?

I've found little advice on the matter so seems like a good one for stackoverflow…

Best Answer

To get the ball rolling, let us start with the manual

option_mysqld_slave-skip-errors

MySQL 5.7 supports an additional shorthand value ddl_exist_errors, which is equivalent to the error code list 1007,1008,1050,1051,1054,1060,1061,1068,1094,1146.

Which includes:

  1. Error: 1007 SQLSTATE: HY000 (ER_DB_CREATE_EXISTS)

    Message: Can't create database '%s'; database exists An attempt to create a database failed because the database already exists. Drop the database first if you really want to replace an existing database, or add an IF NOT EXISTS clause to the CREATE DATABASE statement if to retain an existing database without having the statement produce an error.

  2. Error: 1008 SQLSTATE: HY000 (ER_DB_DROP_EXISTS)

    Message: Can't drop database '%s'; database doesn't exist

  3. Error: 1050 SQLSTATE: 42S01 (ER_TABLE_EXISTS_ERROR)

    Message: Table '%s' already exists

  4. Error: 1051 SQLSTATE: 42S02 (ER_BAD_TABLE_ERROR)

    Message: Unknown table '%s'

  5. Error: 1054 SQLSTATE: 42S22 (ER_BAD_FIELD_ERROR)

    Message: Unknown column '%s' in '%s'

  6. Error: 1060 SQLSTATE: 42S21 (ER_DUP_FIELDNAME)

    Message: Duplicate column name '%s'

  7. Error: 1061 SQLSTATE: 42000 (ER_DUP_KEYNAME)

    Message: Duplicate key name '%s'

  8. Error: 1068 SQLSTATE: 42000 (ER_MULTIPLE_PRI_KEY)

    Message: Multiple primary key defined

  9. Error: 1094 SQLSTATE: HY000 (ER_NO_SUCH_THREAD)

    Message: Unknown thread id: %lu

  10. Error: 1146 SQLSTATE: 42S02 (ER_NO_SUCH_TABLE)

    Message: Table '%s.%s' doesn't exist

But in support of Dave's comment:

Normally, replication stops when an error occurs on the slave, which gives you the opportunity to resolve the inconsistency in the data manually. This option causes the slave SQL thread to continue replication when a statement returns any of the errors listed in the option value.

Do not use this option unless you fully understand why you are getting errors. If there are no bugs in your replication setup and client programs, and no bugs in MySQL itself, an error that stops replication should never occur. Indiscriminate use of this option results in slaves becoming hopelessly out of synchrony with the master, with you having no idea why this has occurred.

Related Question