Incorrect SQL query in class-sitemap.php

Hi team,

I noticed the sitemap on the WordPress I’m using was no longer working. After looking at the PHP FPM logs I found the trace below.

Versions I’m using:
– WordPress 5.3.1
– Rankmath 1.0.37.2 (which seems up-to-date)
– MySQL 8

I found the query mentioned in the logs defined in includes/modules/sitemap/class-sitemap.php on line 256.

Solution:
1) Patching the ORDER BY with “ORDER BY MAX(post_modified_gmt)”
2) Go through the reset sequence of the sitemap plugin https://support.rankmath.com/ticket/502-bad-gateway-when-accessing-sitemap-not-logged-in/#post-29170

Hopefully that helps someone else and get this patched faster.

Thanks,
Flo

2020/02/01 22:32:31 [error] 5211#5211: *21387715 FastCGI sent in stderr: “PHP message: WordPress database error Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘blog.wp_posts.post_modified_gmt’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by for query
SELECT post_type, MAX(post_modified_gmt) AS date
FROM wp_posts
WHERE post_status IN (‘publish’,’inherit’)
AND post_type IN (‘post’,’page’,’attachment’)
GROUP BY post_type
ORDER BY post_modified_gmt DESC

made by require(‘wp-blog-header.php’), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, WP_Query->parse_query, do_action_ref_array(‘parse_query’), WP_Hook->do_action, WP_Hook->apply_filters, RankMath\Sitemap\Router->request_sitemap, RankMath\Sitemap\Sitemap_XML->__construct, RankMath\Sitemap\Sitemap_XML->output, RankMath\Sitemap\Sitemap_XML->has_sitemap_in_cache, RankMath\Sitemap\Sitemap_XML->build_sitemap, RankMath\Sitemap\Generator->get_output, RankM…PHP message: WordPress database error Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘blog.wp_posts.post_modified_gmt’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by for query
SELECT post_type, MAX(post_modified_gmt) AS date
FROM wp_posts
WHERE post_status IN (‘publish’,’inherit’)
AND post_type IN (‘post’,’page’,’attachment’)
GROUP BY post_type
ORDER BY post_modified_gmt DESC made by require(‘wp-blog-header.php’), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts, WP_Query->parse_query, do_action_ref_array(‘parse_query’), WP_Hook->do_action, WP_Hook->apply_filters, RankMath\Sitemap\Router->request_sitemap, RankMath\Sitemap\Sitemap_XML->__construct, RankMath\Sitemap\Sitemap_XML->output, RankMath\Sitemap\Sitemap_XML->has_sitemap_in_cache, RankMath\Sitemap\Sitemap_XML->build_
2020/02/01 22:32:31 [error] 5211#5211: *21387715 upstream sent too big header while reading response header from upstream, client: 162.243.217.88, server: blog.com, request: “GET /blog/sitemap_index.xml HTTP/1.0”, upstream: “fastcgi://unix:/var/run/php/php7.3-fpm.sock:”, host: “blog.com”