Mysqllimit is a serious risk when mysql uses both order by and limit queries.

Mysqllimit is a serious risk when mysql uses both order by and limit queries.

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.

Request Support

I am not very familiar with the underlying implementation of mysql and the principles of the database. I have no idea why mysql has this problem. I am very grateful to anyone who can explain this!