Database error on listing posts for category

Guessing you have some plugin that is causing that. Deactivate all plugins, change to the WordPress Default theme and see if the problems resolves.

We didn’t installed any plugins. Default theme shows the same problem with the bad SQL-query. Maybe this problem is better suited as bug-report ?

The “GROUP BY” in the reported bad query makes not much sense to me, but the wordpress code is a bit too big, wanting to fix this by ourselves.

What happens if you update your Permalink structure, via Administration > Settings > Permalinks, to the default setting?

Also, if using the WordPress Default theme, your categories are displayed in the sidebar–what happens when you click on that category there?

We didn’t changed the Permalinks (it’s the “standard” setting). We have a plain installation of 2.7.1 with 3 added articles and 2 categories (like described above). No plugins or gimmicks have been added. Different themes (including the default theme) always result in the same database error.

Also, if using the WordPress Default theme, your categories are displayed in the sidebar–what happens when you click on that category there?

Click on the “releases” category in the sidebar. What happens? What URL is displayed?

That’s exactly what i’ve posted in my first question:

term_id=4 with URL: https://our-server.de/our-blog-url/wordpress/?cat=4

Link to see problem please.

That’s not possible as i wrote in my initial post, because it’s only in our Intranet.

I know PHP quite well, I know SQL very well and I can tell, that when i press on the mentioned link, there’s a database-error (see my first post). That doesn’t change when i change the theme (default or other themes). I consider this a bug.

Okay sorry for missing that.

Why this bit of code, wp_posts.post_author = 8, happens on a category query is confusing.

This is the query I get when clicking on my category 4 in sidebar of the WordPress Default theme

SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) WHERE 1=1 AND wp_term_taxonomy.taxonomy = ‘category’ AND wp_term_taxonomy.term_id IN (‘4’) AND wp_posts.post_type = ‘post’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘private’) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10

That is very strange: If I copy your query into our mysql-client, i receive the same error as reported. The part of “wp_posts.post_author = 8” makes no difference in regard to the error.

From my SQL-knowledge I would say, the “GROUP BY wp_posts.ID” is the main-problem in combination with the fields selected “wp_posts.*”. In the table “wp_posts”, the next field after “ID” (which is included in the group-by is the “post_author”-field). I would say, that THIS is the source of the problem.

So either the “GROUP BY” is wrong or the selection of fields in the “SELECT”-part.
BTW, our database-backend is using a MySQL server version 5.0.58 under Linux.

Might check if your tables and indexes match the Database_Description.

Suppose you’ve tried repairing your tables…

I don’t think, the DDL of our database is the problem, but the built query by WordPress is bad. I’ve took a look around in the PHP-sources of WordPress now and was able to pinpointing the adding of the “GROUP BY”-clause to the file “wp-includes/query.php” (line #1792 in the section for the “Category stuff”, version2.7.1). There you can find the statement, that add’s the “GROUP BY”:

if ( !empty($q['category__in']) ) {
     $groupby = "{$wpdb->posts}.ID";
  }

This is used, when one or more categories are used (passed over the ‘cat’-argument in the URL in my example) from my original post: https://our-server.de/our-blog-url/wordpress/?cat=4

I think that this usage of the “GROUP BY” is a mis-use. First, this is a MySQL-specialty (and not allowed in standard SQL). Second, even the MySQL manual disregards the use of GROUP-BY in this situation:
-> see https://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html :

[…] Do not use this feature if the columns you omit from the GROUP BY part are not constant in the group. […]

When i comment out the adding of the “GROUP BY” in the file mentioned it works fine. However, I’m not sure, for what this “GROUP BY” is normally added in the query, so i ask you to find that out please.

That is very strange: If I copy your query into our mysql-client, i receive the same error as reported. The part of “wp_posts.post_author = 8” makes no difference in regard to the error.

I get no error using this statement in phpMyAdmin

SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) WHERE 1=1 AND wp_term_taxonomy.taxonomy = ‘category’ AND wp_term_taxonomy.term_id IN (‘4’) AND wp_posts.post_type = ‘post’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘private’) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10

If necessary, either DROP your database tables or change your $table_prefix in wp-config.php and install WordPress again to see if that solves.

My slow query log is also full of this same query:

(my table prefix is ‘ffc_’)

# Query_time: 7 Lock_time: 0 Rows_sent: 10 Rows_examined: 27282

SELECT SQL_CALC_FOUND_ROWS ffc_posts.* FROM ffc_posts INNER JOIN ffc_term_relationships ON (ffc_posts.ID = ffc_term_relationships.object_id) INNER JOIN ffc_term_taxonomy ON (ffc_term_relationships.term_taxonomy_id = ffc_term_taxonomy.term_taxonomy_id) WHERE 1=1 AND ffc_term_taxonomy.taxonomy = ‘category’ AND ffc_term_taxonomy.term_id IN (‘3’, ‘1308’, ‘1309’, ‘2260’) AND ffc_posts.post_type = ‘post’ AND (ffc_posts.post_status = ‘publish’) GROUP BY ffc_posts.ID ORDER BY ffc_posts.post_date DESC LIMIT 0, 10;

the query examines 27282 rows to return 10 results! What kind of madness is that? At first i suspected a plugin or theme, but soon realised that this query was native to wordpress.

I’m running 2.9.1

@jgaspar – did you find a way of removing the ‘GROUP BY’ from this query?

This is a real problem with wordpress. The more posts, categories, tags, etc. that you have the more it starts to lag, and slow queries start to build up rapidly! This slow query has occured about 5000 times in the last 2 days on one of my sites…

Im having the exact same problem, though with tags.

WordPress database error: [Lost connection to MySQL server during query]
SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) INNER JOIN wp_terms ON (wp_term_taxonomy.term_id = wp_terms.term_id) WHERE 1=1 AND wp_term_taxonomy.taxonomy = ‘post_tag’ AND wp_terms.slug IN (‘hopp’) AND wp_posts.post_type = ‘post’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘private’) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 10

This happens to all my WordPress sites on this server, its a server issue I believe. Its causing the MYSQL to crash.