Shulou(Shulou.com)05/31 Report--

This article mainly introduces the mysql GTID error report how to do, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

1. Delete a record on master, but cannot find it on slave.

2: the second kind: the primary key is repeated. The record already exists in slave, and the same record is inserted on master.

3: update a record on master, but cannot find it on slave, and data is lost.

The relay log relay-bin of 4:slave is corrupt.

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: *

Master_User: repl_user

Master_Port: *

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 1140

Relay_Log_File: relay-bin.000003

Relay_Log_Pos: 882

Relay_Master_Log_File: mysql-bin.000006

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: 1062

Last_Error: Worker 3 failed executing transaction '6ee9b003-f1a7-11e1-9ffamur141877405c37failed executing transaction 20' at master log mysql-bin.000006, end_log_pos 1109; Could not execute Write_rows event on table rentcar.hjl; Duplicate entry' 1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 1109

Skip_Counter: 0

Exec_Master_Log_Pos: 835

Relay_Log_Space: 1559

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: 1062

Last_SQL_Error: Worker 3 failed executing transaction '6ee9b003-f1a7-11e1-9ffamur141877405c37failed executing transaction 20' at master log mysql-bin.000006, end_log_pos 1109; Could not execute Write_rows event on table rentcar.hjl; Duplicate entry' 1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 1109

Replicate_Ignore_Server_Ids:

Master_Server_Id: 21

Master_UUID: 6ee9b003-f1a7-11e1-9ffa-141877405c37

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: 160410 20:37:35

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 6ee9b003-f1a7-11e1-9ffa-141877405c37:18-20

Executed_Gtid_Set: 6ee9b003-f1a7-11e1-9ffa-141877405c37:1-19

Dae6c1bc-fa6a-11e5-ae74-1418773c2aed:1

Auto_Position: 1

1 row in set (0.00 sec)

ERROR:

No query specified

Mysql > stop

-> slave

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from hjl

+-+ +

| | id | name |

+-+ +

| | 1 | hjl |

+-+ +

1 row in set (0.00 sec)

Mysql > delete table hjl where id = 1

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table hjl where id = 1' at line 1

Mysql > delete from hjl where id = 1

Query OK, 1 row affected (0.00 sec)

Mysql > start slave

Query OK, 0 rows affected, 1 warning (0.01 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 10.0.10.70

Master_User: repl_user

Master_Port: 5370

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 1140

Relay_Log_File: relay-bin.000004

Relay_Log_Pos: 448

Relay_Master_Log_File: mysql-bin.000006

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: 1140

Relay_Log_Space: 1682

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: 21

Master_UUID: 6ee9b003-f1a7-11e1-9ffa-141877405c37

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 the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 6ee9b003-f1a7-11e1-9ffa-141877405c37:18-20

Executed_Gtid_Set: 6ee9b003-f1a7-11e1-9ffa-141877405c37:1-20

Dae6c1bc-fa6a-11e5-ae74-1418773c2aed:1-2

Auto_Position: 1

1 row in set (0.00 sec)

After the log is damaged (in this case, the SLAVE is down or shut down illegally, such as power failure, motherboard burning, etc., resulting in damage to the relay log and synchronous shutdown)

The following is an example of the above four situations:

(1) Delete a record on master, but cannot find it on slave

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 530

Relay_Log_File: relay-bin.000003

Relay_Log_Pos: 361

Relay_Master_Log_File: mysql-bin.000006

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: 1051

Last_Error: Worker 3 failed executing transaction''at master log mysql-bin.000006, end_log_pos 361; Error 'Unknown table' rentcar.hjl'' on query. Default database: 'rentcar'. Query: 'DROP TABLE `hjl` / * generated by server * /'

Skip_Counter: 0

Exec_Master_Log_Pos: 191

Relay_Log_Space: 1072

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: 1051

Last_SQL_Error: Worker 3 failed executing transaction''at master log mysql-bin.000006, end_log_pos 361; Error 'Unknown table' rentcar.hjl'' on query. Default database: 'rentcar'. Query: 'DROP TABLE `hjl` / * generated by server * /'

Replicate_Ignore_Server_Ids:

Master_Server_Id: 21

Master_UUID: 6ee9b003-f1a7-11e1-9ffa-141877405c37

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: 160410 20:30:13

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 6ee9b003-f1a7-11e1-9ffa-141877405c37:17-18

Executed_Gtid_Set: 6ee9b003-f1a7-11e1-9ffa-141877405c37:1-16

Auto_Position: 1

1 row in set (0.00 sec)

The above situation will happen.

Solution:

Mysql > stop slave

Query OK, 0 rows affected (0.00 sec)

Mysql > reset master

Query OK, 0 rows affected (0.00 sec)

Mysql > reset slave

Query OK, 0 rows affected (0.01 sec)

Mysql > set global gtid_purged='6ee9b003-f1a7-11e1-9ffa-141877405c37:1-17'

Query OK, 0 rows affected (0.00 sec)

Skip the wrong place

Mysql > start slave

Query OK, 0 rows affected, 1 warning (0.00 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 10.0.10.70

Master_User: repl_user

Master_Port: 5370

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 530

Relay_Log_File: relay-bin.000003

Relay_Log_Pos: 577

Relay_Master_Log_File: mysql-bin.000006

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: 530

Relay_Log_Space: 949

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: 21

Master_UUID: 6ee9b003-f1a7-11e1-9ffa-141877405c37

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 the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 6ee9b003-f1a7-11e1-9ffa-141877405c37:18

Executed_Gtid_Set: 6ee9b003-f1a7-11e1-9ffa-141877405c37:1-18

Auto_Position: 1

1 row in set (0.00 sec)

ERROR:

No query specified

(2) duplicate primary key

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: *

Master_User: repl_user

Master_Port: *

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 1140

Relay_Log_File: relay-bin.000003

Relay_Log_Pos: 882

Relay_Master_Log_File: mysql-bin.000006

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: 1062

Last_Error: Worker 3 failed executing transaction '6ee9b003-f1a7-11e1-9ffamur141877405c37failed executing transaction 20' at master log mysql-bin.000006, end_log_pos 1109; Could not execute Write_rows event on table rentcar.hjl; Duplicate entry' 1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 1109

Skip_Counter: 0

Exec_Master_Log_Pos: 835

Relay_Log_Space: 1559

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: 1062

Last_SQL_Error: Worker 3 failed executing transaction '6ee9b003-f1a7-11e1-9ffamur141877405c37failed executing transaction 20' at master log mysql-bin.000006, end_log_pos 1109; Could not execute Write_rows event on table rentcar.hjl; Duplicate entry' 1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000006, end_log_pos 1109

Replicate_Ignore_Server_Ids:

Master_Server_Id: 21

Master_UUID: 6ee9b003-f1a7-11e1-9ffa-141877405c37

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: 160410 20:37:35

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 6ee9b003-f1a7-11e1-9ffa-141877405c37:18-20

Executed_Gtid_Set: 6ee9b003-f1a7-11e1-9ffa-141877405c37:1-19

Dae6c1bc-fa6a-11e5-ae74-1418773c2aed:1

Auto_Position: 1

1 row in set (0.00 sec)

ERROR:

No query specified

Mysql > stop

-> slave

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from hjl

+-+ +

| | id | name |

+-+ +

| | 1 | hjl |

+-+ +

1 row in set (0.00 sec)

Mysql > delete from hjl where id = 1

Query OK, 1 row affected (0.00 sec)

Mysql > start slave

Query OK, 0 rows affected, 1 warning (0.01 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 10.0.10.70

Master_User: repl_user

Master_Port: 5370

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 1140

Relay_Log_File: relay-bin.000004

Relay_Log_Pos: 448

Relay_Master_Log_File: mysql-bin.000006

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: 1140

Relay_Log_Space: 1682

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: 21

Master_UUID: 6ee9b003-f1a7-11e1-9ffa-141877405c37

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 the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 6ee9b003-f1a7-11e1-9ffa-141877405c37:18-20

Executed_Gtid_Set: 6ee9b003-f1a7-11e1-9ffa-141877405c37:1-20

Dae6c1bc-fa6a-11e5-ae74-1418773c2aed:1-2

Auto_Position: 1

1 row in set (0.00 sec)

3. Update a piece of data on master. Cannot update on slave.

Last_SQL_Error: Could not execute Update_rows event on table hcy.t1

Can't find record in 't1'

Error_code: 1032

Handler error HA_ERR_KEY_NOT_FOUND

The event's master log mysql-bin.000010, end_log_pos 794

Solution:

On master, use mysqlbinlog to analyze what the wrong binlog log is doing.

/ usr/local/mysql/bin/mysqlbinlog-- no-defaults-v-v-- base64-output=DECODE-ROWS mysql-bin.000010 | grep-A '10' 794

# 120302 12:08:36 server id 22 end_log_pos 794 Update_rows: table id 33 flags: STMT_END_F

# UPDATE hcy.t1

# WHERE

# @ 1room2 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2roombbc' / * STRING (4) meta=65028 nullable=1 is_null=0 * /

# SET

# @ 1room2 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2BTV'/ * STRING (4) meta=65028 nullable=1 is_null=0 * /

# at 794

# 120302 12:08:36 server id 22 end_log_pos 821 Xid = 60

Com _ MIT _ blank /

DELIMITER

# End of log file

ROLLBACK / * added by mysqlbinlog * /

/ *! 50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/

On slave, look for the updated record, which should not exist.

Mysql > select * from T1 where id=2

Empty set (0.00 sec)

Then go to master to check.

Mysql > select * from T1 where id=2

+-+ +

| | id | name |

+-+ +

| | 2 | BTV |

+-+ +

1 row in set (0.00 sec)

Fill in the missing data on slave and skip the error report.

The relay log relay-bin of 4:slave is corrupt.

Solution: find the synchronized GTID replication transaction ID and re-synchronize so that you can have a new relay day value.

Thank you for reading this article carefully. I hope the article "how to report errors in GTID in mysql" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.