Ошибка 1032 mysql

Note: The old replication terms were «master-slave replication», consisting of a master and one or more slave servers. The new terminology uses «source-replica replication», consisting of a source and one or more replica servers.

Monitoring replication status

The information, that the replica was out of sync, was retrieved by our monitoring, using the moniotring plugin check_mysql_slavestatus in the background:

After an analysis what happened, it was decided to «clear» the replica and do a full data-sync from the source.

Full replication (re-)sync

Note: A very good guide can be found on Remi Bergsma’s article fixing an out-of-sync or corrupt MySQL slave.

Steps to do on the source server

On the source server, the replication state is reset and the tables must be locked to get a consistent state.

Save the information retrieved from the SHOW MASTER STATUS command. You’ll need this later.

Then the database(s) can be saved using mysqldump:

root@mysql01:/backup# mysqldump —max_allowed_packet=512M —all-databases > mysqldump.sql

If a database contains large data fields, the —max_allowed_packet parameter should be used.

The dump should contain the CREATE DATABASE command:

Now that the dump was successfully written, the tables can be unlocked:

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

Now transfer the dump file to the replica server. If the dump is big, you should consider zipping it first:

root@mysql01:/backup# gzip mysqldump.sql

Steps to do on the replica server

Now with the dump transferred to the replica server, the slave process needs to be stopped and reset:

To get a clean state and get rid of still remaining data corruption, I recommend to DROP all databases (except mysql internal databases information_schema, mysql, performance_schema and sys).

mysql> DROP DATABASE Jira;
Query OK, 0 rows affected (0.00 sec)

The database dump can now be loaded into the MySQL server:

root@mysql02:/backup# gunzip < mysqldump.sql.gz | mysql —max_allowed_packet=512M -u root

Once the dump was restored (without error), the replication settings need to be adjusted. Use the information retrieved from the master earlier:

In most cases, the replication should catch up since the master’s database was unlocked.

Error 1032 Could not execute Update_rows event on table

In some cases the replication can have a couple of hiccups. In this case an error 1032 showed up in the SHOW SLAVE STATUS command:

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Queueing master event to the relay log
                  Master_Host: mysql01
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 40254923
               Relay_Log_File: mysql02-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-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: 1032
                   Last_Error: Could not execute Update_rows event on table monitoring.monitoring; Can’t find record in ‘monitoring’, Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event’s master log mysql-bin.000001, end_log_pos 441
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 40255304
              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: 1032
               Last_SQL_Error: Could not execute Update_rows event on table monitoring.monitoring; Can’t find record in ‘monitoring’, Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event’s master log mysql-bin.000001, end_log_pos 441
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: e1e2a203-eb81-11ea-8f82-0050568d9dca
             Master_Info_File: /var/lib/mysql/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: 210423 07:27:58
               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)

If you search for that particular error, most results will simply tell you to skip the error and start the slave again. Although this is technically correct, you should first make sure that you understand the SQL error. In this case the error is caused by yet another monitoring plugin check_mysql_write, which updates a record in the monitoring table in the monitoring database every 10 seconds. 

As this is only monitoring related and temporary data, this error can be skipped and the slave process started again:

But as I mentioned, check_mysql_write updates the table every 10 seconds, so there are a ton of these errors to skip. This can be solved doing a loop, by making sure the error is related to the monitoring database:

root@mysql02:~# while true; do if [[ $(mysql -e «SHOW SLAVE STATUS\G» | grep «Last_SQL_Error:» | grep -c «monitoring») -gt 0 ]]; then mysql -e «STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE;»; else break; fi; sleep 1; done

This took several seconds until finally all errors related to the monitoring database were skipped and finally the replication was running again.

Exclude databases from replication

Certain databases don’t need to be replicated. Such databases can be excluded from the replication by using the binlog_ignore_db configuration parameter. The monitoring database is a perfect example:

root@mysql01:~# grep «binlog_ignore_db» /etc/mysql/mysql.conf.d/mysqld.cnf
binlog_ignore_db        = monitoring

Multiple databases can be specified by defining binlog_ignore_db several times:

root@mysql01:~# grep «binlog_ignore_db» /etc/mysql/mysql.conf.d/mysqld.cnf
binlog_ignore_db        = monitoring
binlog_ignore_db        = anotherdatabase

Note: The other way around (do not replicate anything, except this database) is also possible! By using the binlog_do_db parameter.

I asked this question yesterday on dba.stackexchange.com and didn’t get any responses, so I’m trying here.

I’m getting MySQL 1032 «Can’t find record in ‘person'» errors for some queries in my database, and I cannot resolve them.

Here’s the table:

CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `last_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `first_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `title` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `dob` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `person_full_idx` (`last_name`,`first_name`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=4448 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The query that’s failing is

SELECT * FROM person p0_
WHERE MATCH (p0_.last_name , p0_.first_name , p0_.title) AGAINST ('anne' IN BOOLEAN MODE) > 0.5
ORDER BY p0_.last_name ASC, p0_.first_name ASC, p0_.dob ASC;

If I take out any one of the order by clauses the query runs just fine. And If I change anne to anna the query runs just fine with all three order by clauses. There are some Annes in the table, about as many as there are Annas.

The MySQL error log has a bunch of these error messages each time the query fails:

2019-03-27T17:31:27.891405Z 9 [Warning] [MY-012853] [InnoDB] Using a
partial-field key prefix in search, index `FTS_DOC_ID_INDEX` of table 
`database`.`person`. Last data field length 8 bytes, key ptr now 
exceeds key end by 4 bytes. Key value in the MySQL format:
len 4; hex 05110000; asc     ;

I’m not using replication, and inserts, updates, and deletes are all successful for anne records. I dropped and recreated the fulltext index with no improvement. I dropped and reloaded the database and get the same error.

The query isn’t failing in production (mysql Ver 15.1 Distrib 10.1.37-MariaDB) with the same data. As far as I can tell, it’s only failing on my dev machine (mysql Ver 8.0.15 for osx10.14 on x86_64 (Homebrew)).

What should I try next?

The main cause of the 1032 error is that the main library update or the deleted record is not present on the slave library.
There are two general ideas for dealing with this type of error:
1. Skip the error execution statement directly
2, find the wrong execution statement, fix the data from the library
The first solution will have a hidden danger of master-slave inconsistency (delete statement can be skipped), and the second is to solve the problem fundamentally.

The statement skip operation method is as follows:

The 1032 error message is as follows:

Replicate_Wild_Ignore_Table: 
                 Last_Errno: 1032
                 Last_Error: Could not execute Update_rows event on table test.test; Can't find record in 'test', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql1-bin.000001, end_log_pos 2579
               Skip_Counter: 0

— traditional mode

mysql> stop slave;
 # indicates skip one step error, the following numbers are variable, (or N events, one by one)
mysql> set global sql_slave_skip_counter =1;
mysql> start slave;

 Then use mysql> show slave status/G to view:

mysql> show slave status/G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.20
                  Master_User: rep1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql1-bin.000001
          Read_Master_Log_Pos: 2610
               Relay_Log_File: cndba-relay-bin.000005
                Relay_Log_Pos: 321
        Relay_Master_Log_File: mysql1-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
 #  Skip all 1032 errors
 Change the my.cnf file and add it under Replication settings:
　　slave-skip-errors　＝　1032
 And restart the database, then start salve.
 Note: It is not recommended because you want to restart the database unless there are too many error events.

— GTID mode

mysql> stop slave;
 Find the Retrieved_Gtid_Set: 7800a22c-95ae-11e4-983d-080027de205a:12 by show slave status/G;
mysql> set GTID_NEXT='7800a22c-95ae-11e4-983d-080027de205a:12'
mysql> begin;commit;
mysql> set GTID_NEXT='AUTOMATIC';
mysql> start slave;

Simulate the 1032 scene:

Main library creation table

mysql> create table test(id int PRIMARY KEY ,name varchar(32));
Query OK, 0 rows affected (0.06 sec)
 Modify the parameter sql_log_bin to make the main library operation not sync to the slave library
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values (1,'aa');
Query OK, 1 row affected (0.02 sec)
mysql> insert into test values (2,'bb');
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values (3,'dd');
Query OK, 1 row affected (0.00 sec)
 Modify the parameter sql_log_bin to synchronize the main library operation statement to the slave library.
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values (4,'cc');
Query OK, 1 row affected (0.08 sec)
mysql> select * from test; 
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | dd   |
|  4 | cc   |
+----+------+
4 rows in set (0.00 sec)

View data synchronization from the library

You can see that only one data is synchronized from the library data.
mysql> select * from test; 
+----+------+
| id | name |
+----+------+
|  4 | cc   |
+----+------+
1 row in set (0.00 sec)

In the main library update table test

mysql> update test set name = 'abc' where id=1;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 At this point insert a piece of data:
mysql> insert into test values (5,'dd');
Query OK, 1 row affected (0.01 sec)

View master-slave status in the standby database

Found 1032 error, the reason for this error is because the library does not have id=1 data, resulting in updating the main library, the library has no corresponding data to modify.

mysql> show slave status/G;
......
                   Last_Errno: 1032
                   Last_Error: Could not execute Update_rows event on table test.test; Can't find record in 'test', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql1-bin.000001, end_log_pos 2037
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1792
              Relay_Log_Space: 2264
              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: 1032
               Last_SQL_Error: Could not execute Update_rows event on table test.test; Can't find record in 'test', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql1-bin.000001, end_log_pos 2037
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 20
......

Solution:

Find the missing data from the library according to the location of the master log and end_log_pos prompted in Last_Error
 Main library operation:
[root@cndba binlog]# mysqlbinlog --no-defaults -v --base64-output=decode-rows  --stop-position=2037 /data/mysql/binlog/mysql1-bin.000001 | tail -20
SET TIMESTAMP=1534626999/*!*/;
BEGIN
/*!*/;
# at 1934
#180819  5:16:39 server id 20  end_log_pos 1984 CRC32 0x666a3738     Table_map: `test`.`test` mapped to number 220
# at 1984
#180819  5:16:39 server id 20  end_log_pos 2037 CRC32 0x74a99c1f     Update_rows: table id 220 flags: STMT_END_F
### UPDATE `test`.`test`
### WHERE
###   @1=1
###   @2='aa'
### SET
###   @1=1
###   @2='abc'
ROLLBACK /* added by mysqlbinlog */ /*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

 After finding it, manually convert to an insert statement
 Execute the following statement from the library: 
mysql> insert into `test`.`test` values (1,'aa');
Query OK, 1 row affected (0.03 sec)
 Start replication
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
 View the status from the library:
mysql> show slave status/G;
......
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......
mysql> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | abc  |
|  4 | cc   |
|  5 | dd   |
+----+------+

Through the above results, we can see that the state is normal, and the data inserted after the error has passed to the slave library, we can conclude that repairing the 1032 error will not affect the data after the error occurs.