Make WordPress Core

Opened 13 years ago

Closed 13 years ago

Last modified 13 years ago

#21367 closed defect (bug) (invalid)

Mysql 5.5.24 syntax error using SQL_CALC_FOUND_ROWS

Reported by: benjaoming's profile 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
13 years ago

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

#2 @nacin
13 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
13 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
13 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
13 years ago

  • Milestone Awaiting Review deleted

#6 @SergeyBiryukov
13 years ago

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