A serious risk of mysql using both orderby and limit queries-data loss
I often use order by and limit to display and sort data by PAGE, and I have never found any problems. However, there was a serious problem during the past two days. When the data was read in ascending order by timestamp and in batches by limit, some records were lost, the records clearly in the table cannot be read. After studying it for a long time, I finally found out the problem. I recorded it to prevent forgetting it. I also reminded everyone.
Problem reproduction tools and raw material databases:
Ver 14.14 Distrib 5.6.11, for Linux (x86_64) using EditLine wrapper
Table Structure:
Field |
Type |
Description |
Id |
Int (10) |
Primary Key |
Pay_time |
Int (10) |
Timestamp with index |
Flag |
Tinyint (1) |
Type identifier for category Filtering |
Data
About 5000 data records, most of the records are flags equal to 0, and the timestamp format of the pay_time field is correct.
Requirement
Records with flag = 0 are filtered out, and all data is read in ascending order of pay_time.
Processing Method
Use limit to read data in batches, for example:
Select id, pay_time from order_customer_new where flag = 0 order by pay_time asc, id asc limit 250, 10;
Problems Found
When reading data, a record with the same timestamp is found. If the record is read twice, a record may be lost. As shown in, records with id = 465 are lost.
Problem Analysis and Prediction
When the sorting values are equal, the order is uncertain. Here I guess: When 465 and 466 are at the end of the limit, 466 is at the top, and when they are at the beginning of the limit, 466 is at the end. So 465 is lost, and 466 appears twice.
When the sorting values are equal, the uncertain order of the values should be unpredictable. However, certain rules should be adopted to determine the unique sorting result when sorting. That is to say, even if there are equal sorting values, the results of multiple sorting should be the same. From past experiences, mysql did this. However, the problem this time seems to indicate that mysql is not like this. I don't know whether mysql is like this or a bug.
Solution
Since we guess this problem is caused by the Order uncertainty caused by equal sorting values, we can try to add a sorting condition so that the sorting result is definite and unique. A try is OK, as shown in. 465 is displayed.