WordPress.org

Make WordPress Core

Opened 3 years ago

Closed 7 weeks ago

#40376 closed defect (bug) (duplicate)

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

Reported by: shiroamada Owned by:
Milestone: Priority: normal
Severity: normal Version:
Component: Query Keywords:
Focuses: Cc:

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 (4)

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

This ticket was mentioned in Slack in #core by noisysocks. View the logs.


7 weeks ago

#3 @talldanwp
7 weeks ago

  • Component changed from General to Database
  • Keywords needs-patch added
  • Milestone changed from Awaiting Review to Future Release

Hey @shiroamada. Thanks for reporting this, and apologies it took a while for a response.

This issue was discussed during a triage session in slack (https://wordpress.slack.com/archives/C02RQBWTW/p1590040313447300, link requires a Making WordPress slack account).

It was hard to test the issue because of the limited time, but there was an agreement that this change makes logical sense.

#4 @ocean90
7 weeks ago

  • Component changed from Database to Query
  • Keywords needs-patch removed
  • Milestone Future Release deleted
  • Resolution set to duplicate
  • Status changed from new to closed
  • Version 4.7.3 deleted

Closing as a duplicate of #44349 which has a few more details.

Note: See TracTickets for help on using tickets.