Something I came across recently when installing WordPress gave me headaches. Everything seemed to work properly except when selecting posts by category no results were returned.

I debugged the problem by looking at the SQL-queries performed by WordPress. One query returned an error :

SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE 1=1  AND ( wp_term_relationships.term_taxonomy_id IN (1) ) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10

Because the MySQL server was configured to honor ONLY_FULL_GROUP_BY it gave the error “‘test.wpposts.post_author’ isn’t in GROUP BY”.

I could not disable ONLY_FULL_GROUP_BY serverwide so I had to insert it in the WordPress-code.

The best place to do this was in the wp-includes/wp-db.php. Look for the function db_connect() and add the code below as the last line of the function.

mysql_query( " SET sql_mode='ANSI,TRADITIONAL' ", $this->dbh);

Note that every time you perform an upgrade of WordPress you’ll have to add this line back to the source code.

UPDATE

After implementing this change I was unable to post new posts or pages through the WordPress interface. Doing Quick Posts (through the dashboard) and update via XML-RPC works without a problem.