Record a real production environment accident, 200000 records were lost, and the MySQL delete command stepped on the pit, not intentionally deleting the database

Time:2020-10-25
--The main table before this launch, MD_ poc_ I didn't put in other fields of survey, which has nothing to do with the bug I'm going to talk about this time.
DROP TABLE IF EXISTS md_poc_survey;
CREATE TABLE `md_poc_survey` (
  `member_ survey_ id` int(10) NOT NULL AUTO_ Add comment 'member questionnaire self increment ID'
  PRIMARY KEY (`member_survey_id`)
) ENGINE=InnoDB AUTO_INCREMENT=207054 DEFAULT CHARSET=utf8;

--This release is to fix concurrent MD_ poc_ There are hundreds of duplicate data in the survey. Currently, there are 200000 formal data in this table

--Create a temporary table, store duplicate records, prepare for deletion, execute local Navicat, no problem
DROP TABLE IF EXISTS tmp_member_survey;
CREATE TABLE tmp_member_survey(
    member_survey int(10)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

--Insert member in duplicate data into temporary table_ survey_ If the ID is larger, the duplicate data will be inserted into the temporary table. There will be no problem in local execution, but there will be data insertion
INSERT tmp_member_survey SELECT member_survey_id FROM md_poc_survey WHERE member_id
          IN (
              SELECT a.member_id FROM md_poc_survey a INNER JOIN md_sales_survey_answer b ON a.member_survey_id=b.member_survey_id GROUP BY a.member_id HAVING COUNT(a.member_id) > 1
          ) AND member_survey_id NOT IN (
             SELECT MIN(a.member_survey_id) FROM md_poc_survey a INNER JOIN md_sales_survey_answer b ON a.member_survey_id=b.member_survey_id GROUP BY a.member_id HAVING COUNT(a.member_id) > 1
         );
         
--Delete MD_ poc_ Survey duplicate data. No error is reported during local Navicat execution

At about 10:30 in the morning, my colleagues also executed a section by section of SQL (I only have the permission to query the production environment). No errors were found. When the delete statement was executed, the query displayed affected rows: 200000 +, and the colleague refreshed the MD_ poc_ Survey table, all the data is gone, and the Department is fried in an instant. I was sitting opposite him, and the whole person was not well. I had the impulse to run.

At the back, the customer reported that the historical data was gone. We said that the system was unstable. It would be ok if we waited a moment. So the technical director kept on recovering the data. After tossing around until 8 p.m., the lost data was fully recovered. This is also a test of our system: the backup mechanism is not perfect, the operation is troublesome and time-consuming

OK, let’s talk about the problem and create TMP_ member_ What I want in survey is member_ survey_ I write the ID field as member_ Survey, creating a table and executing SQL is OK. When inserting data, it is a member_ Survey field, can insert data normally, no problem. Finally, when the delete statement is executed locally, no error will be reported. Even if I do not report an error in the main table MD, I will not report an error in the test environment_ poc_ Survey data has not been cleared!
Record a real production environment accident, 200000 records were lost, and the MySQL delete command stepped on the pit, not intentionally deleting the database
Separate execution

SELECT member_survey_id FROM tmp_member_survey AS X

Will report a mistake
Record a real production environment accident, 200000 records were lost, and the MySQL delete command stepped on the pit, not intentionally deleting the database

Why is the production environment main table MD_ poc_ Will survey be cleared?
Why carry out

DELETE FROM md_poc_survey WHERE member_survey_id IN (

    SELECT member_survey_id FROM tmp_member_survey AS X

);

Don’t you report the error directly?

Fortunately not fired, the next article summarizes the reasons and database backup methods!

Recommended Today

Layout of angular material (2): layout container

Layout container Layout and container Using thelayoutDirective to specify the layout direction for its child elements: arrange horizontally(layout=”row”)Or vertically(layout=”column”)。 Note that if thelayoutInstruction has no value, thenrowIs the default layout direction. row: items arranged horizontally.max-height = 100%andmax-widthIs the width of the item in the container. column: items arranged vertically.max-width = 100%andmax-heightIs the height of the […]