Solution to the problem of data loss when using Replace operation in MySQL

Preface

The company's developers used the replace into statement when updating data. Due to improper use, a large amount of data was lost. This article analyzes how the data loss was caused.

1. Problem Description

The company's developers used the replace into statement when updating data. Due to improper use, a large amount of data was lost. The following is an analysis of how the data loss was caused.

2. Problem Analysis

a. REPLACE principle

The official explanation of the REPLACE INTO principle is:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

If the primary key or unique key of the newly inserted row already exists in the table, the original record will be deleted and the new row will be inserted; if it does not exist in the table, it will be inserted directly.

Address: https://dev.mysql.com/doc/refman/5.6/en/replace.html

b. Problem symptoms

The table structure of missing data is as follows:

CREATE TABLE `active_items` (

The replace statements executed are as follows (multiple):

REPLACE INTO active_items(ad_id,score) VALUES('XXXXXXX', 1800);

By querying binlog to find the execution record, some of them are as follows:

### UPDATE `items`.`active_items`
### @21=0 /* TINYINT meta=0 nullable=0 is_null=0 */

The ad_id being operated already exists, so it is deleted first and then inserted. It can be seen that except for the specified ad_id and score, other fields are changed to the default values, resulting in the loss of the original data (although it is converted to update in the log)

c. Comparative testing

Next I conducted the following tests:

• Data loss during MySQL Replace operation
• The left side uses the REPLACE statement, and the right side uses the DELETE + INSERT statement. The final result is exactly the same.
• The row with the original primary key id of 1 is deleted, the primary key id of the newly inserted row is updated to 4, and the default value is inserted into the field c without specified content
• A row of data was updated using REPLACE, and MySQL prompted that the number of affected rows was 2 rows

To sum up, it means that a row is deleted and a row is inserted.

3. Data Recovery

After data loss or data error, there are several ways to recover:

1. The business side writes its own script for recovery
2. Use MySQL binlog to find out the incorrect SQL and generate reverse SQL for data recovery (suitable for small SQL data volume)
3. Restore the data status to the moment before the erroneous operation through historical backup files + incremental binlog

4. Problem Extension

From the above analysis, we can find that REPLACE will delete the old row and insert the new row, but the binlog records it in the form of update, which brings another problem:

The self-growth value of the slave database is smaller than that of the master database

1. Testing

a. Master-slave consistency:

Main library:

mysql> show create table tG

From the library:

mysql> show create table tG

b. Main library REPLACE:

Main library:

mysql> replace into t (a,b)values(1,7);

From the library:

mysql> show create table tG

Note that the AUTO_INCREMENT values ​​of the master and slave tables are different now.

c. Simulate the upgrade from slave to master and perform INSERT in the slave database:

mysql> insert into t (a,b,c)values(4,4,4);

When inserting from the database, an error will be reported, and the primary key is repeated. After the error is reported, AUTO_INCREMENT will be +1, so the insertion can be successful if it is executed again.

2. Conclusion

This problem will not have any impact under normal circumstances, but:

If the master database uses a large number of REPLACE statements on a regular basis, causing the slave database's AUTO_INCREMENT value to lag far behind the master database, when the master-slave switch occurs, a large number of primary key duplication errors will appear in the new master database when data is inserted again, causing data to fail to be inserted.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.