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