Description:
Unable to skip replication error "Query caused different errors on master and slave." on slave with GTID Replication.
With GTID Enabled server and replication, it's unable to skip transaction cause following error:
Last_SQL_Error: Query caused different errors on master and slave. Error on master: message (format)='Your password does not satisfy the current policy requirements' error code=1819; Error on slave:actual message='no error', error code=0. Default database:''. Query:'CREATE USER IF NOT EXISTS 'rpluser'@'%' IDENTIFIED WITH 'mysql_native_password' AS '<secret>''
Try to resolve this error by skipping transaction as follows but still replication fails to start because of the same error.
Method 1: Skip errored transaction using SET GTID_NEXT option.
method 2: Start replication from specific GTID position using set global GTID_PURGED option.
Will not get this error if we start replication with binlog file and position options.
How to repeat:
Slave conf:
# Replication config
gtid-mode=on
enforce-gtid-consistency=1
log-slave-updates=1
log-bin
binlog_format=row
relay-log=relay146
relay_log_recovery= on
master_info_repository = TABLE
relay_log_info_repository = TABLE
Steps:
On Master: Create user as follows,
mysql> create user if not exists 'rpluser'@'%' identified by 'Rpluse';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> create user if not exists 'rpluser'@'%' identified by 'Rpluser@123';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave, replication client on *.* to 'rpluser'@'%';
Query OK, 0 rows affected (0.00 sec)
On Slave:
CHANGE MASTER TO MASTER_HOST='192.168.2.101',MASTER_USER='rpluser',MASTER_PASSWORD='Rpluser@123',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.101
Master_User: rpluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: MYVM-100359085-bin.000001
Read_Master_Log_Pos: 864
Relay_Log_File: relay146.000002
Relay_Log_Pos: 381
Relay_Master_Log_File: MYVM-100359085-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 3002
Last_Error: Query caused different errors on master and slave. Error on master: message (format)='Your password does not satisfy the current policy requirements' error code=1819; Error on slave:actual message='no error', error code=0. Default database:''. Query:'CREATE USER IF NOT EXISTS 'rpluser'@'%' IDENTIFIED WITH 'mysql_native_password' AS '<secret>''
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 1291
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 3002
Last_SQL_Error: Query caused different errors on master and slave. Error on master: message (format)='Your password does not satisfy the current policy requirements' error code=1819; Error on slave:actual message='no error', error code=0. Default database:''. Query:'CREATE USER IF NOT EXISTS 'rpluser'@'%' IDENTIFIED WITH 'mysql_native_password' AS '<secret>''
Replicate_Ignore_Server_Ids:
Master_Server_Id: 145
Master_UUID: d922486f-065d-11e7-9dd6-00505605b8f9
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 170325 04:43:18
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: d922486f-065d-11e7-9dd6-00505605b8f9:1-3
Executed_Gtid_Set: d922486f-065d-11e7-9dd6-00505605b8f9:1
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
Skipping errored Transaction:
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GTID_NEXT="d922486f-065d-11e7-9dd6-00505605b8f9:2";
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GTID_NEXT="AUTOMATIC";
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.101
Master_User: rpluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: MYVM-100359085-bin.000001
Read_Master_Log_Pos: 864
Relay_Log_File: relay146.000002
Relay_Log_Pos: 381
Relay_Master_Log_File: MYVM-100359085-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 3002
Last_Error: Query caused different errors on master and slave. Error on master: message (format)='Your password does not satisfy the current policy requirements' error code=1819; Error on slave:actual message='no error', error code=0. Defaul t database:''. Query:'CREATE USER IF NOT EXISTS 'rpluser'@'%' IDENTIFIED WITH 'mysql_native_password' AS '<secret>''
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 1812
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 3002
Last_SQL_Error: Query caused different errors on master and slave. Error on master: message (format)='Your password does not satisfy the current policy requirements' error code=1819; Error on slave:actual message='no error', error code=0. Defaul t database:''. Query:'CREATE USER IF NOT EXISTS 'rpluser'@'%' IDENTIFIED WITH 'mysql_native_password' AS '<secret>''
Replicate_Ignore_Server_Ids:
Master_Server_Id: 145
Master_UUID: d922486f-065d-11e7-9dd6-00505605b8f9
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 170325 05:14:09
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: d922486f-065d-11e7-9dd6-00505605b8f9:1-3
Executed_Gtid_Set: d922486f-065d-11e7-9dd6-00505605b8f9:1-2
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> reset slave all;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status;
Empty set (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST='192.168.2.101',MASTER_USER='rpluser',MASTER_PASSWORD='Rpluser@123',MASTER_PORT=3306,MASTER_LOG_FILE='MYVM-100359085-bin.000001',MASTER_LOG_POS=864;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.101
Master_User: rpluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: MYVM-100359085-bin.000001
Read_Master_Log_Pos: 864
Relay_Log_File: relay146.000002
Relay_Log_Pos: 327
Relay_Master_Log_File: MYVM-100359085-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 864
Relay_Log_Space: 527
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 145
Master_UUID: d922486f-065d-11e7-9dd6-00505605b8f9
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:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
Suggested fix:
There should be a way to skip such error transactions.
Description: Unable to skip replication error "Query caused different errors on master and slave." on slave with GTID Replication. With GTID Enabled server and replication, it's unable to skip transaction cause following error: Last_SQL_Error: Query caused different errors on master and slave. Error on master: message (format)='Your password does not satisfy the current policy requirements' error code=1819; Error on slave:actual message='no error', error code=0. Default database:''. Query:'CREATE USER IF NOT EXISTS 'rpluser'@'%' IDENTIFIED WITH 'mysql_native_password' AS '<secret>'' Try to resolve this error by skipping transaction as follows but still replication fails to start because of the same error. Method 1: Skip errored transaction using SET GTID_NEXT option. method 2: Start replication from specific GTID position using set global GTID_PURGED option. Will not get this error if we start replication with binlog file and position options. How to repeat: Slave conf: # Replication config gtid-mode=on enforce-gtid-consistency=1 log-slave-updates=1 log-bin binlog_format=row relay-log=relay146 relay_log_recovery= on master_info_repository = TABLE relay_log_info_repository = TABLE Steps: On Master: Create user as follows, mysql> create user if not exists 'rpluser'@'%' identified by 'Rpluse'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> create user if not exists 'rpluser'@'%' identified by 'Rpluser@123'; Query OK, 0 rows affected (0.00 sec) mysql> grant replication slave, replication client on *.* to 'rpluser'@'%'; Query OK, 0 rows affected (0.00 sec) On Slave: CHANGE MASTER TO MASTER_HOST='192.168.2.101',MASTER_USER='rpluser',MASTER_PASSWORD='Rpluser@123',MASTER_PORT=3306,MASTER_AUTO_POSITION=1; mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.101 Master_User: rpluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: MYVM-100359085-bin.000001 Read_Master_Log_Pos: 864 Relay_Log_File: relay146.000002 Relay_Log_Pos: 381 Relay_Master_Log_File: MYVM-100359085-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 3002 Last_Error: Query caused different errors on master and slave. Error on master: message (format)='Your password does not satisfy the current policy requirements' error code=1819; Error on slave:actual message='no error', error code=0. Default database:''. Query:'CREATE USER IF NOT EXISTS 'rpluser'@'%' IDENTIFIED WITH 'mysql_native_password' AS '<secret>'' Skip_Counter: 0 Exec_Master_Log_Pos: 154 Relay_Log_Space: 1291 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 3002 Last_SQL_Error: Query caused different errors on master and slave. Error on master: message (format)='Your password does not satisfy the current policy requirements' error code=1819; Error on slave:actual message='no error', error code=0. Default database:''. Query:'CREATE USER IF NOT EXISTS 'rpluser'@'%' IDENTIFIED WITH 'mysql_native_password' AS '<secret>'' Replicate_Ignore_Server_Ids: Master_Server_Id: 145 Master_UUID: d922486f-065d-11e7-9dd6-00505605b8f9 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 170325 04:43:18 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: d922486f-065d-11e7-9dd6-00505605b8f9:1-3 Executed_Gtid_Set: d922486f-065d-11e7-9dd6-00505605b8f9:1 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) Skipping errored Transaction: mysql> reset master; Query OK, 0 rows affected (0.00 sec) mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> SET GTID_NEXT="d922486f-065d-11e7-9dd6-00505605b8f9:2"; Query OK, 0 rows affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> SET GTID_NEXT="AUTOMATIC"; Query OK, 0 rows affected (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.101 Master_User: rpluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: MYVM-100359085-bin.000001 Read_Master_Log_Pos: 864 Relay_Log_File: relay146.000002 Relay_Log_Pos: 381 Relay_Master_Log_File: MYVM-100359085-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 3002 Last_Error: Query caused different errors on master and slave. Error on master: message (format)='Your password does not satisfy the current policy requirements' error code=1819; Error on slave:actual message='no error', error code=0. Defaul t database:''. Query:'CREATE USER IF NOT EXISTS 'rpluser'@'%' IDENTIFIED WITH 'mysql_native_password' AS '<secret>'' Skip_Counter: 0 Exec_Master_Log_Pos: 154 Relay_Log_Space: 1812 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 3002 Last_SQL_Error: Query caused different errors on master and slave. Error on master: message (format)='Your password does not satisfy the current policy requirements' error code=1819; Error on slave:actual message='no error', error code=0. Defaul t database:''. Query:'CREATE USER IF NOT EXISTS 'rpluser'@'%' IDENTIFIED WITH 'mysql_native_password' AS '<secret>'' Replicate_Ignore_Server_Ids: Master_Server_Id: 145 Master_UUID: d922486f-065d-11e7-9dd6-00505605b8f9 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 170325 05:14:09 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: d922486f-065d-11e7-9dd6-00505605b8f9:1-3 Executed_Gtid_Set: d922486f-065d-11e7-9dd6-00505605b8f9:1-2 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> reset master; Query OK, 0 rows affected (0.01 sec) mysql> reset slave all; Query OK, 0 rows affected (0.00 sec) mysql> show slave status; Empty set (0.00 sec) mysql> CHANGE MASTER TO MASTER_HOST='192.168.2.101',MASTER_USER='rpluser',MASTER_PASSWORD='Rpluser@123',MASTER_PORT=3306,MASTER_LOG_FILE='MYVM-100359085-bin.000001',MASTER_LOG_POS=864; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.101 Master_User: rpluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: MYVM-100359085-bin.000001 Read_Master_Log_Pos: 864 Relay_Log_File: relay146.000002 Relay_Log_Pos: 327 Relay_Master_Log_File: MYVM-100359085-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 864 Relay_Log_Space: 527 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 145 Master_UUID: d922486f-065d-11e7-9dd6-00505605b8f9 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: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) Suggested fix: There should be a way to skip such error transactions.