Most of the businesses using MySQL involve Replication, and master-slave mechanisms are often used for reading and writing separation,HA, hot backup, or incremental Replication.
However, in many cases, 1032 and 1205 errors are reported
1032 in the first place.
Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;
The root cause of the 1032 error was inconsistency in the master-slave database data, resulting in the synchronization operation not being performed on the slave library.
There are two kinds of situations I have encountered so far:
For Replication, I used master –binlog-do-db=db_name or slave — Replicate_do_db =db_name.
If two libraries pubs and test, ignore the test, the results have a SQL in the master test library implementation: insert into pubs. Tname values (XXXXX);
Then, depending on the configuration of the service, a 1032 error is raised if the master executes successfully and the master does not
2 TRIGGER and PROCEDURE version problem. If the master-slave version is inconsistent, for example, five pieces of data are written after the execution of a PROCEDURE on the master-slave version, while only one line of data is written after the execution of a PROCEDURE on the master-slave version, then a 1032 error is bound to occur
Solutions:
1. It doesn’t use –binlog-do-db and — Replicate_do_DB =db_name
Change from top — Replicate_wild_do_table =db_name.%
2 Ensure that the master-slave TRIGGER and PROCEDURE versions are consistent
Say again 1205:
This error is easy to understand. Typically, the primary operation connection is autocommit, and as a result, the runtime fails and an error is reported when synchronizing from the library.
Two ways to do it:
Set my.cnF Innodb_rollback_on_TIMEOUT =1 on the primary, rollback for timeout
2. Ignore 1052. My. CNF — slave-skipping-errors =1205 from above
1205 occurs on the main library, often because of lock timeouts. For example, using a transaction on a table results in one transaction not committing late, another transaction waiting for the previous transaction to commit, lock wait timeout, and the latter transaction hanging. A 1205 error occurred on the main library. The most common is when a table has a self-growing ID, and an INSERT initiated transaction is delayed for several reasons, so that subsequent transactions wait for the previous INSERT operation to commit when performing the insert operation on the table. These all require DBAs and R& D Cooperate to complete. The service performance is low CPU utilization, but load is unusually high. If you look innoDB status, you can catch lock conflicts randomly.
However, in many cases, 1032 and 1205 errors are reported
1032 in the first place.
Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;
The root cause of the 1032 error was inconsistency in the master-slave database data, resulting in the synchronization operation not being performed on the slave library.
There are two kinds of situations I have encountered so far:
For Replication, I used master –binlog-do-db=db_name or slave — Replicate_do_db =db_name.
If two libraries pubs and test, ignore the test, the results have a SQL in the master test library implementation: insert into pubs. Tname values (XXXXX);
Then, depending on the configuration of the service, a 1032 error is raised if the master executes successfully and the master does not
2 TRIGGER and PROCEDURE version problem. If the master-slave version is inconsistent, for example, five pieces of data are written after the execution of a PROCEDURE on the master-slave version, while only one line of data is written after the execution of a PROCEDURE on the master-slave version, then a 1032 error is bound to occur
Solutions:
1. It doesn’t use –binlog-do-db and — Replicate_do_DB =db_name
Change from top — Replicate_wild_do_table =db_name.%
2 Ensure that the master-slave TRIGGER and PROCEDURE versions are consistent
Say again 1205:
This error is easy to understand. Typically, the primary operation connection is autocommit, and as a result, the runtime fails and an error is reported when synchronizing from the library.
Two ways to do it:
Set my.cnF Innodb_rollback_on_TIMEOUT =1 on the primary, rollback for timeout
2. Ignore 1052. My. CNF — slave-skipping-errors =1205 from above
1205 occurs on the main library, often because of lock timeouts. For example, using a transaction on a table results in one transaction not committing late, another transaction waiting for the previous transaction to commit, lock wait timeout, and the latter transaction hanging. A 1205 error occurred on the main library. The most common is when a table has a self-growing ID, and an INSERT initiated transaction is delayed for several reasons, so that subsequent transactions wait for the previous INSERT operation to commit when performing the insert operation on the table. These all require DBAs and R& D Cooperate to complete. The service performance is low CPU utilization, but load is unusually high. If you look innoDB status, you can catch lock conflicts randomly.
[ERROR] ERROR reading packet from Server: Lost Connection to MySQL Server during Query (server_errno=2013)
In general, three situations will lead to 2013 errors
1 Reverse parsing
2 Max_allowed_packet is inconsistent
3. Network Problems
The solution
1 Skip-name-resolve is forbidden to reverse resolve
Configure master slave max_allowed_packet with the same value
3 Adjust the value of net_write_TIMEOUT
A friend of mine reported a mistake in 2013 when I was writing notes. None of the three schemes mentioned above worked, and Lost Connection to MySQL Server appeared very timely. Finally, I found out that the network agent controlled the connection for more than 30 minutes and automatically hung up…