potential pagination bug - can anyone replicate this?

[Nicolaas Thiemen Francken]

Hi Everyone,

I have come across a potential bug, as summarised below. Is it possible for anyone else to replicate this and/or comment on the best way to fix this.  If it is really a bug then I am keen to write a fix for this as I feel it may have all sorts of unforeseen consequences. 

Pagination does not work well when the data is provided by MySQL 5.6 / 5.7 (php 7.1) where the data-set is sorted by a field (or fields) that are not enforced to be unique on a database level. The reason for this is that MySQL may return datasets in a random order (beyond what it has been asked to sort by).

You can possibly replicate this bug by yourself by using the tools listed below:

• https://github.com/sunnysideup/silverstripe-sort-test/: see the bug in action
• https://github.com/sunnysideup/silverstripe-dataintegritytests/blob/master/code/CheckForMysqlPaginationIssuesBuildTask.php: use this build task to analyse your own projects

Issues that may arise out of this bug are:

• any time you update datasets in batches (e.g. newsletters) - you may process some records twice and other ones not at all - if you are not careful.
• grid field pagination is broken (some items listed twice or more, on separate pages, others not at all)
• pagination on the front-end is broken (some items listed twice or more, on separate pages, others not at all)

There are still a lot of questions about this bug (e.g. is it restricted to MySQL, what versions, what determines random order of records, etc.. etc...), but what I do know is that I have been able to consistently demonstrate this error on several machines with clean installs of 3.6.1 and that one of my clients noticed it on a live site. 

I have lodged a bug report here: https://github.com/silverstripe/silverstripe-framework/issues/7249. 

Any help appreciated.

Nicolaas