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”

Viewing 4 replies - 1 through 4 (of 4 total)
  • Hello,

    Thank you for contacting Rank Math and sorry for any inconvenience that might have been caused due to that.

    I have forwarded this issue to our Development team, they will further debug it and fix it in the upcoming updates. In the meantime, if there is anything else please let us know.

    Thank you.

    Hello,

    WordPress doesn’t support ONLY_FULL_GROUP_BY sql mode due to its DataBase structure. You’ll get similar errors in default Post query too. Please change the sql mode to the one which is supported by WordPress. That should fix the issue.

    I hope that helps. Thank you.

    Please delete this entry. I have opened a separate ticket.
    Hello,

    I’ve got an error to one of my (old) articles after installing rank math:

    502 Bad Gateway

    The same error also occurred with a newly published article today. The only way I could fix it was to delete the old article and create a new article with the same content.

    This is very annoying.

    I deleted the following option from My SQL database after I did an analysis:

    rank_math_seo_analysis_results

    But it doesn’t help.

    Best regards,
    Sabine

    • This reply was modified 2 weeks, 1 day ago by Sabine Gimm.

    HI Sabine,

    Thanks for getting in touch with us today.

    The 502 is likely as a result of using either the %seo_title% or %seo_description% on your meta title or meta description field.

    These variables are reserved for use on your schema settings and should not be used on the areas where they are generated from.

    You can easily fix this by replacing the use of these variables with either %title% or %excerpt%

    I hope this info helps. Thank you.

    Hello,

    Since we did not hear back from you for 15 days, we are assuming that you found the solution. We are closing this support ticket.

    If you still need assistance or any other help, please feel free to open a new support ticket, and we will be more than happy to assist.

    Thank you.

Viewing 4 replies - 1 through 4 (of 4 total)

The ticket ‘Incorrect SQL query in class-sitemap.php’ is closed to new replies.