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:
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.