WordPress.org

Make WordPress Core

Opened 3 years ago

Last modified 3 years ago

#40376 new defect (bug)

WordPress Duplication Post when come to Pagination if using MySQL 5.6, 5.7 and above

Reported by: shiroamada Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: 4.7.3
Component: General Keywords:
Focuses: Cc:
PR Number:

Description

Here is the Database behaviour
http://dba.stackexchange.com/questions/169163/mysql-5-5-vs-5-6-above-same-database-same-query-but-different-output

When it comes to pagination, the bug is happening because we have a lot of same post_date result. In normal blog this will not happen.

URL: http://wordpress.dev/page/2, http://wordpress.dev/page/3

Here is the debug tools screenshot
Page 2
http://i.imgur.com/6bd8h6Q.png

Page 3
http://i.imgur.com/ORkIbHw.png

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

The reason it is because MySQL behaviour Non-deterministic, the ORDER BY produces non-deterministic results.

Can the team add the ORDER BY wp_posts.post_date DESC, wp_posts.ID DESC to make deterministic results.

The Fix SQL:

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

Change History (1)

#1 in reply to: ↑ description @shiroamada
3 years ago

I reported to MySQL for this issue - https://bugs.mysql.com/bug.php?id=85801

Replied by Developer:

This is not a bug, see discussion for https://bugs.mysql.com/bug.php?id=72076 and documentation at https://dev.mysql.com/doc/refman/5.6/en/limit-optimization.html

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

So I think WordPress need to be added in this requirement.

Note: See TracTickets for help on using tickets.