MySQL last_sql_errno:1062

I. Description of the Environment

1. CentOS 6.6

2.mysql 5.6.25

3. Gtid-based replication

Ii. Description of the exception

Mistakenly inserts a data from the node as the master node, the same data is inserted in the primary, from the node insert, causing the primary key conflict, slave thread exception.

Third, the processing process

1. View the error message

([email protected]) [(none)]> show Slave status\g;

1. Row ***************************

............... Slightly.............................

last_errno:1062

Last_error:could not execute Write_rows event on table user-center.t_dea_info; Duplicate entry ' 89465d5d792541459e38cabaf677c95e ' for key ' PRIMARY ', error_code:1062; Handler error Ha_err_found_dupp_key; The event ' s master Log mysql-bin.000005, End_log_pos 469856269

skip_counter:0

exec_master_log_pos:469855892

relay_log_space:470236370

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:could not execute Write_rows event on table user-center.t_dea_info; Duplicate entry ' 89465d5d792541459e38cabaf677c95e ' for key ' PRIMARY ', error_code:1062; Handler error Ha_err_found_dupp_key; The event ' s master Log mysql-bin.000005, End_log_pos 469856269

Replicate_ignore_server_ids:

master_server_id:157

master_uuid:77220b08-41ab-11e5-b9be-000ec4cac97a

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:170410 16:10:01

MASTER_SSL_CRL:

Master_ssl_crlpath:

retrieved_gtid_set:77220b08-41ab-11e5-b9be-000ec4cac97a:33417992-37560729

executed_gtid_set:1d7e2d03-084b-11e6-88f0-000ec4cac979:1-3236,

716feeae-41b4-11e5-82c2-000ec4cac979:1-2,

77220b08-41ab-11e5-b9be-000ec4cac97a:1-37559950,

99bcf840-22f5-11e6-b6d2-000ec4cac981:1-10

Auto_position:1

1 row in Set (0.00 sec)

2. Confirm that the gtid that need to be skipped is the key

Find this already executed Gtid, add 1

77220b08-41ab-11e5-b9be-000ec4cac97a:1-37559950,

3. Jump Gtid

([email protected]) [(none)]> stop slave;

([email protected]) [(None)]> SET @ @SESSION. gtid_next= ' 77220b08-41ab-11e5-b9be-000ec4cac97a:37559951 ';

Query OK, 0 rows Affected (0.00 sec)

([email protected]) [(none)]> BEGIN; COMMIT;

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

([email protected]) [(None)]> SET SESSION gtid_next = AUTOMATIC;

Query OK, 0 rows Affected (0.00 sec)

4. Restart the thread, check the status, confirm OK

([email protected]) [(None)]> START SLAVE;

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

([email protected]) [(none)]> show Slave status\g;

1. Row ***************************

......................... Slightly............................

master_log_file:mysql-bin.000005

read_master_log_pos:470459548

relay_log_file:relay-bin.000016

relay_log_pos:70709

relay_master_log_file:mysql-bin.000005

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

relay_log_space:114127

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

master_uuid:77220b08-41ab-11e5-b9be-000ec4cac97a

Master_Info_File:mysql.slave_master_info

sql_delay:0

Sql_remaining_delay:null

Slave_sql_running_state:slave have 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:77220b08-41ab-11e5-b9be-000ec4cac97a:33417992-37561196

executed_gtid_set:1d7e2d03-084b-11e6-88f0-000ec4cac979:1-3236,

716feeae-41b4-11e5-82c2-000ec4cac979:1-2,

77220b08-41ab-11e5-b9be-000ec4cac97a:1-37561196,

99bcf840-22f5-11e6-b6d2-000ec4cac981:1-10

Auto_position:1

1 row in Set (0.00 sec)

ERROR:

No query specified

Iv. Key points

1. Find the Gtid to skip, know why to add 1, understand the principle, the problem will be solved.

This article is from the "ROIDBA" blog, make sure to keep this source http://roidba.blog.51cto.com/12318731/1914604

MySQL last_sql_errno:1062