"20180205" MySQL 1032 and 1062 Skip error summary

# # # #MySQLSkip 1032 1062 Error
# # # #Traditional replication situation

1. 
   

   Slave_exec_mode (global level) idempotent or STRICT

   
   

   
   

   Controls how a Slave thread resolves conflicts and errors during replication. Idempotent mode causes suppression of Duplicate-key and no-key-found errors; STRICT means no such suppression takes place.

   
   

   
   

   Idempotent mode is intended-use with multi-master replication, circular replication, and some other special replication Scenarios for NDB Cluster Replication. (See section 21.6.10, "NDB Cluster replication:multi-master and Circular Replication", and sections 21.6.11, "NDB Cluster Replication Conflict Resolution ", for more information.) NDB Cluster ignores any value explicitly set for Slave_exec_mode, and always treats it as idempotent.

   
   

   
   

   In MySQL Server 5.7, STRICT mode is the default value.

   
   

   
   

   or storage engines other than NDB, idempotent mode should is used only if you were absolutely sure that Duplicate-key err ORS and Key-not-found errors can safely be ignored. It is meant to being used in fail-over scenarios for NDB Cluster where multi-master replication or circular replication is EM Ployed, and is not a recommended for use with other cases.

   
   

   • in case of idempotent (idempotent) mode (3 SQL inside a transaction, 1062 or 1032 error occurs in the middle of the SQL statement under strict (STRICT) mode)
     • The effect of this parameter for traditional or Gtid replication is the same as
     • error for 1062 primary key violations, whether Binlog_format is in row or statement format, The value of a row that has a primary key violation from the library is overwritten by the data that is synchronized by the main library, which is considered to be performing a replace operation from the library.
     • for errors that are not found for 1032 rows, regardless of whether Binlog_format is in row or statement format, the SQL statement is ignored locally from the library, not executed, except for the other SQL that executes the transaction without error.
3. Sql_slave_skip_counter (global level)
   • This parameter is valid for traditional replication only, and only Gtid_next is used for Gtid replication.
   • in the case of the row format of the Binlog_format, in the case of 1032 or 1062, and the table has a self-strengthening primary key, and do not specify the primary key when the insert operation on Master, this time need to pay attention to the primary key key value information, It is possible that the primary key and the next key value for both master and slave may be inconsistent after a 1032 or 1062 error has been skipped. So the operation that needs attention is delete,insert,truncate. The unit that the
   • parameter is for is statment or row. For statement, if you are executing multiple statement within a transaction, there are three cases when you encounter a 1032 or 1062 error:
     • the entire transaction will be skipped and will not be executed. What has been done in front of this statement will also be rolled back.
     • Just skips the statement of the error, and statement will continue to follow. The
     • skips the statement of the error and all subsequent statement that have not been executed, but the statement that were previously executed successfully will not be rolled back.
   • in the case where the format of Binlog_format is row, either 1032 or 1062 sets sql_slave_skip_counter=1, which skips the entire transaction.
   • in the case of the Binlog_format format is statement, Sql_slave_skip_counter=1 and is a 1062 error, which skips the entire transaction. The format of
   • Binglog_format is statement, in the case of a 1032 error, the entire transaction occurs in the modification of a main library exists, but from the library does not exist in the row data, in the library is not error, And the other statements of this transaction can be executed successfully.

5. Slave_skip_errors (global level)

   • This parameter is valid for Gtid and traditional replication, and the results are the same for both. The SQL statement for the error is skipped, but the rest of the SQL is still executing normally.
   • Set slave_skip_errors=1062 or 1032 in the case of Binlog_format is statement, the entire transaction will only skip 1062 or 1032 error SQL, do not execute this SQL, the rest of SQL is normal.
   • Setting slave_skip_errors=1062 or 1032 in the case where Binlog_format is row, the entire transaction will only skip the 1062 or 1032 error SQL, and do not perform the rest of the SQL normally.
7. Summarize:

# # # #GTID复制

1. Gtid_next (Session level)
   • Gtid mode of the next
   • Get Gtid execution information at master:

mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                            |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------------+
| mysql-bin.000026 |     4347 |              |                  | 834f1e16-fa69-11e7-b271-000c291f2799:1-1013108,
b7737e22-e469-11e7-b6b9-000c29a80f41:1-94000 |
+------------------+----------+--------------+------------------+----------------------------------------------------------------------------------------------+

• Get non-executed Gtid information in slave: mainly observing the values of Retrieved_gtid_set and Executed_gtid_set

mysql> show slave status \G
......
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 73306
                  Master_UUID: 834f1e16-fa69-11e7-b271-000c291f2799
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 834f1e16-fa69-11e7-b271-000c291f2799:1013102-1013108
            Executed_Gtid_Set: 069020a4-d346-11e7-a9e6-000c2920ceb4:1-123487,
69d937df-dbfe-11e7-84e5-000c291f2799:1-2,
834f1e16-fa69-11e7-b271-000c291f2799:1-1013107,
b7737e22-e469-11e7-b6b9-000c29a80f41:1-94000,
f1542f99-0015-11e8-a7c3-000c29a80f41:1-6
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
......

• Set an empty Gtid value in slave

[email protected] 16:12:  [percona]> show variables like ‘%next%‘;
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| gtid_next     | AUTOMATIC |
+---------------+-----------+
1 row in set (0.00 sec)
[email protected] 16:14:  [percona]> set gtid_next=‘834f1e16-fa69-11e7-b271-000c291f2799:1013108‘;
Query OK, 0 rows affected (0.00 sec)

[email protected] 16:14:  [percona]> begin;commit;
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.02 sec)

[email protected] 16:14:  [percona]> set gtid_next=‘AUTOMATIC‘;
Query OK, 0 rows affected (0.00 sec)

• Restart slave

"20180205" MySQL 1032 and 1062 Skip error summary