3

I am using Running same source code and same database for WordPress, only the mysql version is different. It make my pagination post have duplication. Here is what I found out. Any idea how to solve it in higher version?

This is for Page 2, and Page 3 query output. Left-Hand side is MySql 5.5, Right-Hand side is MySql 5.6. up_posts.ID is unique.

SQL Statement:-

SELECT SQL_CALC_FOUND_ROWS up_posts.ID FROM up_posts  WHERE 1=1  AND up_posts.post_type = 'post' AND (up_posts.post_status = 'publish' OR up_posts.post_status = 'closed' OR up_posts.post_status = 'private' OR up_posts.post_status = 'hidden')  ORDER BY up_posts.post_date DESC LIMIT 10, 10

SELECT SQL_CALC_FOUND_ROWS up_posts.ID FROM up_posts  WHERE 1=1  AND up_posts.post_type = 'post' AND (up_posts.post_status = 'publish' OR up_posts.post_status = 'closed' OR up_posts.post_status = 'private' OR up_posts.post_status = 'hidden')  ORDER BY up_posts.post_date DESC LIMIT 20, 10

Page 2:- different is LIMIT 10,10 Page 2

Page 3:- different is LIMIT 20,10 Page 3 - as you can see ID:62728 & 62737 appear again in Page 3

Seem like MySQL 5.5 will sort by the ID, but 5.6 above does not have this ability.

4
  • 2
    Without proper ORDER BY there's no guaranteed output in a DBMS, MySQL seems to evolve :-) – dnoeth Apr 5 '17 at 8:07
  • @dnoeth arg... shouldn't it behaviour like 5.5 order by id to avoid this duplicated result... MySQL MySQL? can consider this is bug? – Shiro Apr 5 '17 at 8:40
  • 2
    Not a bug. This is expected. Non-deterministic ORDER BY produces non-deterministic results. – ypercubeᵀᴹ Apr 5 '17 at 8:59
  • You are asking why WP+MySQL fails to get the same get the same answer in two situations? Because WP has a bug. Not MySQL. File a bug report with them and cite this Q&A as a discussion of how it should be fixed in WP. – Rick James Apr 5 '17 at 16:06
2

Added ORDER BY up_posts.post_date DESC, up_posts.ID DESC no more duplicate result. Seem like we need to manually add this to avoid an unnecessary problem.

FYI. It applied to MySQL 5.6 and 5.7.

See 8.2.1.16 LIMIT Query Optimization

If you want to order by primary key, you have to say so explicitly.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.