A serious risk when MySQL uses both order by and limit queries--loss of data

I often use order by and limit to do data paging and sorting, and have never found any problems. However, these two days lack of a serious problem, in the time stamp in ascending order and the limit to read the data in batches, but found that in some records are lost, the table is clearly some records are not read. The study of the majority of days finally found the problem, record it to prevent forgetting, but also for everyone to mention a wake up.

Problem replay tool and raw material database:

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, indexed
Flag	tinyint (1)	Type identification, for classification filtering

Data

About 5,000 data, most of the recorded flag is equal to the 0,pay_time field timestamp format is correct

Demand

Filter out flag=0 records and read all data in ascending order of Pay_time.

Processing mode

Use limit to read data in batches, such as:
select id, pay_time from order_customer_new where flag=0 order by pay_time asc, id asc limit 250, 10;

Discover problems

In the process of reading the data, it is found that there is a time stamp equal to the record, two times read out, you may lose a record. See, Id=465 's record is lost.

Problem analysis and speculation

When the sorted values are equal, their sequence is indeterminate. Here I guess: when 465 and 466 are at the end of limit 466 is in front, and when at the beginning of the limit, 466 is missing to the back. Therefore 465 were lost, 466 appeared two times.
When the sorted values are equal, the uncertainty of their order should be that their results are not predictable. However, a certain number of rules should be taken to determine the unique sort results, i.e., even if there are equal sort values, the results of multiple orders should be the same. From previous experience, MySQL is doing this. But the problem this time seems to indicate that MySQL is not like this. I do not know that MySQL is the case, or a bug.

Solutions

Since this problem is assumed to be caused by ordering uncertainty due to the equivalence of sorted values, try increasing the ordering criteria so that their ordering results are deterministic and unique. A try is OK, as shown, 465 out.

Request Support

I'm not very familiar with MySQL's underlying implementation and database principles, and I don't understand why MySQL is having this problem. If any friend can explain one or two, I appreciate it!

A serious risk when MySQL uses both order by and limit queries--loss of data