Contrib.social

Getting Error MySQL Server Has Gone Away During Sitemap Generation

Open on Drupal.org →
Created on 26 September 2024, about 2 months ago

Problem/Motivation

After several generations of a sitemap (~8) I'm getting the following error:

An AJAX HTTP error occurred.
HTTP Result Code: 500
Debugging information follows.
Path: /batch?id=182&op=do_nojs&op=do
StatusText: error
ResponseText: The website encountered an unexpected error. Try again later.Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away: SELECT 1 AS "expression"
FROM
"key_value" "key_value"
WHERE ("name" = :db_condition_placeholder_0) AND ("collection" = :db_condition_placeholder_1); Array
(
[:db_condition_placeholder_0] => queue_stashed_results
[:db_condition_placeholder_1] => simple_sitemap
)
in Drupal\Core\KeyValueStore\DatabaseStorage->doSet() (line 136 of core/lib/Drupal/Core/KeyValueStore/DatabaseStorage.php).

I have even updated my AWS EC2 server from a T3 Small (2G RAM) to T3 Medium (4G RAM) and it did help reduce the errors a bit, but they're still happening. I am using a T3 Small for the AWS RDS DB (perhaps 2G isn't enough for the DB? to use this module?). The site is quite large with many entities and images and I have tried with remove duplicates On and Off, and while Off it does use less memory for the generation when checking using the performance script, but issue still occurs. My sitemap settings are:

1. Regenerate the sitemaps during cron runs: Off (have tried On as well)
2. Add styling and sorting: On
3. Hide branding: Off
4. Default Sitemap
5. Default base URL: not filled in as only one URL
6. Exclude duplicate Links: On, but have tried Off as well
7. Maximum links in a sitemap: 20000 (tried lower and higher numbers too)
8. Sitemap generation max duration: 15 (have tried 30, 60, 200 - still get error)
9. Entities per queue item: 15 (tried 30, 60, 100 no change)
10. Regenerate all sitemaps after hitting Save: Off

I have also turned off run during Cron and have tried manually resuming generation, but same issue occurs.

Steps to reproduce

Proposed resolution

Remaining tasks

User interface changes

API changes

Data model changes

🐛 Bug report
Status

Active

Version

4.0

Component

Code

Created by

josephgut

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Comments & Activities

  • Issue created by @josephgut
  • Comment about 2 months ago →
    🇩🇪Germany gbyte Berlin

    We usually don't get these kind of reports which indicates the module is quite scalable.

    I have even updated my AWS EC2 server from a T3 Small (2G RAM) to T3 Medium (4G RAM) and it did help reduce the errors a bit, but they're still happening. I am using a T3 Small for the AWS RDS DB (perhaps 2G isn't enough for the DB? to use this module?).

    4GB RAM isn't much but it should be enough. Maybe PHP is running out of memory and so the database query does not finish? How much memory is PHP allowed to use in your case? You can check it by visiting /admin/reports/status

    7. Maximum links in a sitemap: 20000 (tried lower and higher numbers too)

    Try to keep this low - for the sake of testing maybe 200

    8. Sitemap generation max duration: 15 (have tried 30, 60, 200 - still get error)

    That might be a problem, you want a lower value to save memory. Try 5 for the sake of the test and go from there.

    There are also mysql configuration options that you could look at, but I personally never had to change those... Example:

    [mysqld]
wait_timeout
max_allowed_packet

    Maybe there is some strange query error because of a broken data set? Can you try and regenerate the sitemap with different content?

    You could also look at mysql logs.

contrib.social Blog FAQ Discussions
Production build 0.71.5 2024