WordPress.org

Make WordPress Core

Opened 9 years ago

Closed 9 years ago

Last modified 9 years ago

#21367 closed defect (bug) (invalid)

Mysql 5.5.24 syntax error using SQL_CALC_FOUND_ROWS

Reported by: benjaoming Owned by:
Milestone: Priority: normal
Severity: normal Version:
Component: General Keywords:
Focuses: Cc:

Description

MySQL has changed in 5.5.

This query is no longer OK as it results in a syntax error:

SELECT SQL_CALC_FOUND_ROWS FROM wp_posts WHERE 1=1 LIMIT 0, 5

 [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM wp_posts WHERE 1=1 LIMIT 0, 5' at line 1]

It has to be something like this:

SELECT SQL_CALC_FOUND_ROWS * FROM wp_posts WHERE 1=1 LIMIT 0, 5

Please correct in the following files:

./wp-includes/user.php: $this->query_fields = 'SQL_CALC_FOUND_ROWS * ' . $this->query_fields;
./wp-includes/query.php: $found_rows = 'SQL_CALC_FOUND_ROWS *';

Change History (6)

#1 @nacin
9 years ago

The issue is the lack of the *, right? Both of those file references clearly have a *.

#2 @nacin
9 years ago

25% of all WordPress installs use 5.5 — this does not seem like an issue in core. http://wordpress.org/about/stats/

#3 @dd32
9 years ago

In the example given, it seems that something is causing $this->query_fields to be NULL, as it should contain * or a set of fields already.

Just checking, MySQL 5.5/5.6 documentation doesn't include the * in their examples. If MySQL is chocking on the query (check it via PHPMyAdmin or CLI Mysql?) then knowing if there's any special mysql configurations would be helpful too (just for reference)

#4 @benjaoming
9 years ago

  • Resolution set to invalid
  • Status changed from new to closed

Thanks for answering and DAMN! Nevermind. I have checked my MySQL logs etc, made printouts and everything. I believed the error to be due to running MySQL 5.5 (Ubuntu 12.04) since an identical code base runs fine on an older server (Ubuntu 10.04).

Turns out the error disappears when I disable this plugin: http://wpcal.firetree.net/

Gotta stop using these crappy 3rd party apps :)

Most likely, it messes with lines such as this one.

                        $fields         = apply_filters_ref_array( 'posts_fields_request',              array( $fields, &$this ) );

...since the $fields is the variable that should not be empty, but is.

Curiously, though, my 10.04 does not issue any statements containing SQL_CALC_FOUND_ROWS but only "SELECT FOUND_ROWS()" (running MySQL 5.0).

#5 @SergeyBiryukov
9 years ago

  • Milestone Awaiting Review deleted

#6 @SergeyBiryukov
9 years ago

  • Severity changed from blocker to normal
Note: See TracTickets for help on using tickets.