MySQL data loss troubleshooting case

Preface

Recently, a friend suddenly contacted me on WeChat and told me that MySQL had experienced data loss. Without a doubt, for a DBA, this is undoubtedly the most stressful thing, no doubt about it. After hearing the news, I immediately started troubleshooting the problem.

On-site investigation

When I first heard the news, I was of course very nervous, but I quickly calmed myself down and started to investigate:

(1) Is the instance status normal? --After confirmation, the instance status is normal

(2) Which is the business library? Does it still exist? Has it been deleted? --It has been confirmed that the business inventory is

(3) Which table did the business access when reporting the error? Does the table exist? Has it been deleted? --After confirmation, the business table exists

(4) Are the application user's permissions normal? --It has been confirmed that the application user has all permissions of the business library

(5) What error message is reported during business access? --After confirmation, the business side reports errors when accessing certain pages

(6) At this point in the investigation, on the one hand, we suspect whether there is an abnormality in the application, and on the other hand, we suspect whether some records are lost. The development side and the operation and maintenance side are investigating at the same time. The idea for the operation and maintenance side to investigate here is whether the business table has a primary key? What is the correspondence between business-side access errors and business tables? Can you find the corresponding records?

(7) Further analysis revealed that the business table had a primary key, and the development side also provided a query record. After checking, the record existed and was not deleted by mistake. The development side checked the application, and the log did not clearly print out the error information.

(8) In this case, you can only ask if any changes/releases have been made that evening? --It has been confirmed that some table DDL changes were made that night

Continuing to investigate, we found that the DDL change that night involved operations on the business table. The change was to modify the field length, similar to alter table xxx modify column xxx char(x). At this point, we began to have ideas about the problem. Next, we began to check the sql_mode configuration and query the corresponding complete row records for confirmation by the developer. Finally, we confirmed that the DDL change caused the field to be truncated. In the end, we could only restore it through backup, and the problem was finally solved.

Case Reproduction

After reading the troubleshooting process just now, I believe many of you will have questions, why does modifying the field length cause the data to be truncated? Doesn't MySQL do data validation? Let’s continue to look down.

(1) Scenario 1

mysql> select * from sbtest2 limit 1;
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 1 | 3718516 | 08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977 | 63188288836-92351140030-06390587585-66802097351-49282961843 |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table sbtest2 modify column pad char(1);
ERROR 1265 (01000): Data truncated for column 'pad' at row 1

mysql> select * from sbtest2 limit 1;
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 1 | 3718516 | 08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977 | 63188288836-92351140030-06390587585-66802097351-49282961843 |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

(2) Scenario 2

mysql> select * from sbtest2 limit 1;
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k | c | pad |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 1 | 3718516 | 08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977 | 63188288836-92351140030-06390587585-66802097351-49282961843 |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table sbtest2 modify column pad char(1); Query OK, 100 rows affected, 100 warnings (0.06 sec)
Records: 100 Duplicates: 0 Warnings: 100

mysql> select * from sbtest2 limit 1;
+----+---------+-------------------------------------------------------------------------------------------------------------------------+------+
| id | k | c | pad |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+------+
| 1 | 3718516 | 08566691963-88624912351-16662227201-46648573979-64646226163-77505759394-75470094713-41097360717-15161106334-50535565977 | 6 |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+------+
1 row in set (0.00 sec)

Scenario 1 is more in line with our expectations, and directly reports the error "data truncated"; Scenario 2 is executed successfully, resulting in "partial data loss"; then, does MySQL not perform data verification? In fact, MySQL has data validation. However, in scenario 2, due to the problem with sql_mode configuration and the failure to set STRICT_TRANS_TABLES, MySQL did not prevent the operation from being executed, resulting in the "data loss" tragedy.

Summarize

At this point, the "data loss" tragedy can come to an end. The root cause is that sql_mode does not set STRICT_TRANS_TABLES. This case also reminds us that sql_mode is a very critical configuration and must not be set or modified casually. More information about sql_mode will be shared with you in the next article.

The above is the details of the MySQL data loss troubleshooting case. For more information about MySQL data loss troubleshooting, please pay attention to other related articles on 123WORDPRESS.COM!