vB 3.7 error with database when generating sitemap

First of all, Gert and the guys have been great to work with and VERY pacient with me Thank you.

They installed vBSEO on my site and helped me through some issues with the database. I hate to make any changes at all because I'm afraid of losing data, but I can't generate a sitemap.

When I try and generate a sitemap manually I get a database error:

[create sitemap file] filename: sitemap_897.xml.gz, number of urls: 100 [12,432.1Kb mem used] [3,007s (+3s)]
[create sitemap file] filename: sitemap_898.xml.gz, number of urls: 100 [12,443.0Kb mem used] [3,007s (+0s)]
[create sitemap file] filename: sitemap_899.xml.gz, number of urls: 100 [12,454.3Kb mem used] [3,008s (+1s)]

Database errorThe Discussion Forum database has encountered a problem.Please try the following:

• Load the page again by clicking the Refresh button in your web browser.
• Open the www.domain.com home page, then try to open another page.
• Click the Back button to try another link.

The FFCars.com forum technical staff have been notified of the error, though you may contact them if the problem persists.
We apologise for any inconvenience.



Here is the source code:

Database error in vBulletin 3.7.3:

Invalid SQL:

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

MySQL Error : MySQL server has gone away
Error Number : 2006
Request Date : Friday, July 23rd 2010 @ 01:50:20 PM
Error Date : Friday, July 23rd 2010 @ 01:53:22 PM
Script : http://www.mydomain.com/forums/vbseo...eo_sitemap.php
Referrer : http://www.mydomain.com/forums/vbseo...php?rlist=true
IP Address : 65.13.1.244
Username : Unregistered
Classname : vB_Database
MySQL Version :

MySQL Error : MySQL server has gone away

That would be something that I would contact your host about.

I don't know anything about MySQL. The forum is working. Could it work OK with this type of error code? I'll call my hosting company, but I don't want them to mess anything up with the database.

This runs once daily, and needs to run to generate your sitemaps. If your host is going away, you should contact them to have them increase some limits.

Which limits? I want to be clear with these guys so they don't mess anything up.

Thanks again.

Bill

The problem is possibly related to your mysql wait_timeout setting. Try to increase the wait_timeout setting.

900 sitemap files is absurd. You should have about 5 per million posts on your board.

See:
vBSEO Sitemap Generator : Quality vs Quantity - vBulletin SEO Forums

Delete all your xml.gz files in the vbseo_sitemap/data/ folder
go into the sitemap options and turn off everything except showthread and forum display (and cms if using it)
re-run the sitemap manually.

I asked our hosting company to give us more resources and they said it is already at the max.

As Brian suggested I deleted all the xml.gz files and turned everything off except showthread and forum display. I re-ran it manually and it still gives me the same problem. What's also weird is that even though I deleted the xml.gz files when I tried to do it again is started at 1,100.

I do have 2,200,000+ posts. Is that the problem?

Any ideas?

Thanks,

Bill

There is a file called something like progress (Forget the actual name) in the data folder. Delete that as well as your sitemaps and regenerate.

Originally Posted by Michael Biddle

There is a file called something like progress (Forget the actual name) in the data folder. Delete that as well as your sitemaps and regenerate.

Thanks. I did that and it now started at 1 again. But even after turning everything off except showthread and forum display, I still got a database error.

Bill

Make sure you're killed everything at the same time.

progress.dat
all xml.gz files

then re-run.

Originally Posted by Brian Cummiskey

Make sure you're killed everything at the same time.

progress.dat
all xml.gz files

then re-run.

I did it three times and got the same results.

When I contacted with my hosting company yesterday, they made these changes: In your mysql configuration file the "wait_timeout was commented out. With that being said it defaulted to 28800 (8 hours). The configuration file was set to 300 which is 5 minutes. I un commented out the original setting and the only thing left to do is restart mysql.

Thanks for the ideas. What should I try now?

- Bill

When you get the SQL error, right click the page and view source. What is the exact sql error?

Originally Posted by Michael Biddle

When you get the SQL error, right click the page and view source. What is the exact sql error?

Database error in vBulletin 3.7.3:

Invalid SQL:

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

MySQL Error : MySQL server has gone away
Error Number : 2006
Request Date : Saturday, July 24th 2010 @ 10:55:07 PM
Error Date : Saturday, July 24th 2010 @ 10:56:30 PM
Script : http://www.domain.com/forums/vbseo_s...eo_sitemap.php
Referrer : http://www.domain.com/forums/vbseo_sitemap/index.php
IP Address : 65.XX.X.XXX
Username : Unregistered
Classname : vB_Database
MySQL Version :

Again, that would still need to be something for them to fix.