Error when generating sitemap

This is a strange one. I had my sitemap crash this morning with the following email sent

Database error in vBulletin 3.8.1:

Invalid SQL:

SELECT count(*) as cnt
,max(views) as maxv,avg(views) as avgv
,max(replycount) as maxre,avg(replycount) as avgre
FROM thread
WHERE forumid = 202
AND visible = 1;

MySQL Error : MySQL server has gone away
Error Number : 2006
Request Date : Sunday, April 10th 2011 @ 02:19:16 AM
Error Date : Sunday, April 10th 2011 @ 02:26:58 AM
Script : http://www.thespainforum.com/cron.php?rand=1302416350
Referrer : http://www.thespainforum.com/es/f191/c%C3%B3mo-eliminar-la-niebla-de-una-foto-en-photoshop-283451/
IP Address : 90.168.42.130
Username : Unregistered
Classname : vB_Database
MySQL Version

I have just updated to the latest version of the sitemap, run it and got a similar error

Database error in vBulletin 3.8.1:

Invalid SQL:

SELECT count(*) as cnt
,max(views) as maxv,avg(views) as avgv
,max(replycount) as maxre,avg(replycount) as avgre
FROM thread
WHERE forumid = 202
AND visible = 1;

MySQL Error : MySQL server has gone away
Error Number : 2006
Request Date : Sunday, April 10th 2011 @ 09:38:38 AM
Error Date : Sunday, April 10th 2011 @ 09:42:18 AM
Script : http://www.thespainforum.com/vbseo_s...eo_sitemap.php
Referrer : http://www.thespainforum.com/vbseo_s...php?rlist=true
IP Address : 88.148.5.20
Username : Unregistered
Classname : vB_Database
MySQL Version

Any ideas on this one

You need to increase MySQL wait_timeout value to 175 and restart apache

Sorry I never replied yesterday Mert
I did the changes you suggested yesterday. I the guys I get my server from make the changes.
Same problem again today
The only thing I have been running new on the forums is the bad behavior mod
I dont know if this could be the cause of the sudden stopping of the sitemap generation.
Would lowering the amount of urls in each sitemap page from 20000 to say 15000 or 10000 lower the resources being used?

It was the same error again.
If I go into the sitemap control panel and run a manual generation, it carries on from where it stopped and completes the sitemap

Hello Lee ,

If you have too many tags on your site , that may cause the issue. Actually including tags inside sitemap files is not a good idea imo because they will add useless content and they will drive GoogleBots attention from your threads.

Hi Mert, thanks for getting back so quickly

I went through everything yesterday.
Killed all the tags being included in the sitemap
Its only forum display, show thread and blogs being included in the sitemap
I know I run a hack you guys hate on there (vbet) but I never went as far as integrating it into the sitemaps
I have a 15 second sleep between pages being generated as well

I will try lowering the amount of urls per sitemap page to see if that cures the problem
Or if that dont work, disable the bad behavior mod over night to pin point if thats at fault

If you have SSH access , you can add a crontab to your server that will generate sitemap files directly on server level. You can check readme.html file for an example

remove the sleep. that actually induces wait_timeout errors.

Just tried doing another generation since there is almost a full days junk added to the forums.

Tried turning off the wait time
10k urls per page

And it still happened

Database error in vBulletin 3.8.1:

Invalid SQL:

SELECT count(*) as cnt
,max(views) as maxv,avg(views) as avgv
,max(replycount) as maxre,avg(replycount) as avgre
FROM thread
WHERE forumid = 202
AND visible = 1;

MySQL Error : MySQL server has gone away
Error Number : 2006
Request Date : Monday, April 11th 2011 @ 06:13:56 PM
Error Date : Monday, April 11th 2011 @ 06:16:57 PM
Script : http://www.thespainforum.com/vbseo_s...eo_sitemap.php
Referrer : http://www.thespainforum.com/vbseo_sitemap/index.php
IP Address : 88.148.5.20
Username : Unregistered
Classname : vB_Database
MySQL Version :

Started it again once it crashed and it finished OK

Report:
============================
Click the following link for your vBSEO Google/Bing Sitemap Report:
http://www.thespainforum.com/vbseo_sitemap/

Summary:
============================
Forum Display: 3630
Show Thread: 219940
Show Post:
Member Profiles:
Poll Results:
Blog Entries: 334
Blog Tags:
Album URLs:
Social Groups URLs:
Tag URLs:
Archive: 0

Total Indexed URLs: 225404
Total Processing Time: 263.93 seconds

Google ping: Disabled.
Bing ping: Disabled.
Yahoo ping: Disabled.
Ask ping: Disabled.

I will try it again at some stage tomorrow afternoon with the bad behavior mod disabled
That way I can find if that mod is causing the problems and I only drop my guard for a few minutes when I do it.

Each time its the same forum ID thats reported in the error

Hello Lee,

"MySQL server has gone away" error message usually means lack of resources. The default value for the wait_timeout var in MySQL configuration file is 28880, so that you may want to increase it a bit more than 175. However it doesn't mean that the wait_timeout setting can troubleshoot the issue, as the "MySQL has gone..." error message can be caused by other different things.

What is the MySQL version you are running on your server?.

What is the value for the setting "max_execution_time" in your PHP config file?.

TOO high of a wait_timeout causes max_Clients issues though, so you need to find a happy medium.

Cheers for all the help on this

PHP version is 5.2.13

MYSQL Client API version 5.0.90

max_execution_time = 2500

What I will do is leave it until late noon here, disable the bad behavior mod and run it again
Todays sitemap crashed out. I run it manually to complete its generation, which went through OK

Run this two further times today
First time I went through the php.ini file and made sure all the settings were the same in my top level cpanel as above.
It still crashed

I have just done a run with the bad behavior mod disabled and it went OK
So it looks like the mod is hogging resources

Please follow what i suggested on Error when generating sitemap

Looks like I finally got it going
Had to go through and strip out any dead garbage lurking on the forums, disabled hacks etc
Tidied up my spiders list
Killed several corporate crawlers from hitting my forums, the likes of Radian, Spin3r etc
Increased the memory limit
And set it to run at the quiet period for my forums

Its been worth it just for the amount of bad traffic the add on stops, in my case over 400 bad ips a day

Thanks for all the help guys

Looks like I can go back to finding other methods of breaking my forums