Mysql error after upgrade to 2.0.1

[dxb]

i started getting these mysql errors after I upgraded to vb 2.0.1

Database error in vBulletin: Invalid SQL: 
SELECT
session.userid AS sessionuserid,
post.*,post.username AS postusername,post.ipaddress AS ip,user.*,userfield.*,icon.title as icontitle,icon.iconpath,
attachment.attachmentid,attachment.filename,attachment.visible AS attachmentvisible,attachment.counter
,avatar.avatarpath,NOT ISNULL(customavatar.avatardata) AS hascustomavatar,customavatar.dateline AS avatardateline
FROM
post
LEFT JOIN icon ON icon.iconid=post.iconid
LEFT JOIN user ON user.userid=post.userid
LEFT JOIN userfield ON userfield.userid=user.userid
LEFT JOIN avatar ON avatar.avatarid=user.avatarid
                       LEFT JOIN customavatar ON customavatar.userid=user.userid
LEFT JOIN attachment ON attachment.attachmentid=post.attachmentid
LEFT JOIN session ON (session.userid = user.userid
                                            AND session.userid > 0
                                            AND user.invisible = 0
                                            AND session.lastactivity>992343351)
WHERE
post.postid IN (0,80175)
ORDER BY dateline 

mysql error: Got error 28 from table handler
mysql error number: 1030
Date: Tuesday 12th of June 2001 05:25:51 AM
Script: /showthread.php?s=&postid=80175
Referer:

and again

Database error in vBulletin: Invalid SQL: 
SELECT
session.userid AS sessionuserid,
post.*,post.username AS postusername,post.ipaddress AS ip,user.*,userfield.*,icon.title as icontitle,icon.iconpath,
attachment.attachmentid,attachment.filename,attachment.visible AS attachmentvisible,attachment.counter
,avatar.avatarpath,NOT ISNULL(customavatar.avatardata) AS hascustomavatar,customavatar.dateline AS avatardateline
FROM
post
LEFT JOIN icon ON icon.iconid=post.iconid
LEFT JOIN user ON user.userid=post.userid
LEFT JOIN userfield ON userfield.userid=user.userid
LEFT JOIN avatar ON avatar.avatarid=user.avatarid
                       LEFT JOIN customavatar ON customavatar.userid=user.userid
LEFT JOIN attachment ON attachment.attachmentid=post.attachmentid
LEFT JOIN session ON (session.userid = user.userid
                                            AND session.userid > 0
                                            AND user.invisible = 0
                                            AND session.lastactivity>992256937)
WHERE
post.postid IN (0,72149,72167,78450,79041,79231,79233,79264,79273)
ORDER BY dateline 

mysql error: Got error 28 from table handler
mysql error number: 1030
Date: Monday 11th of June 2001 05:25:37 AM
Script: /showthread.php?s=&threadid=9041

and I noticed that somebody else had reported the same exact error in this post

http://161.58.84.213/forum/showthread.php?s=&threadid=20035&highlight=error

and you can notice it's error 28

what dose this error means and how to fix it

[Mike Sullivan]

That generally means table corruption -- you can fix it with the repair.php file located in your extras/ directory.

[dxb]

thanks Ed but I noticed one more thing

the timing of the occurance I have over 5 emails from different days


Date: Tuesday 12th of June 2001 05:25:51 AM

Date: Monday 11th of June 2001 05:25:37 AM

which is kind of weared because all of them happened with in the 25th minute with the same hour but different days

I was thinking about my programmed server restart timing ....could that couse it ....and if it is coused by the server daily restart dose it have any effect on the database

please help with this

[tubedogg]

You should close MySQL before restarting your server as it can cause major problems if you don't.

[ahc]

Originally posted by Ed Sullivan
That generally means table corruption -- you can fix it with the repair.php file located in your extras/ directory.

I have been getting the same error periodically (once a day or so) since installing vB 2.0.1 a week ago. The repair.php file reports all table statuses as OK--i.e., it doesn't change anything.

My ISP is looking into this (I gather from other posts that this error can be caused by a full MySQL partition, though I am not at all sure that's the problem here), and I understand that the code is clean per se, but obviously this is very problematic. What causes table corruption (if that's the problem--again, MySQL sees no problems with my tables) and what plans do you have to address it?

Yours,

Andy

[tubedogg]

As it's nothing that vB causes, there's really no way to address it.

[ahc]

Originally posted by tubedogg
As it's nothing that vB causes, there's really no way to address it.

Try to see it from my perspective. I have an installation of vBulletin that is, at the moment, the only thing using the mySQL database. It is, in fact, the only thing running on the server at the moment. As far as I'm concerned as a end user, vBulletin, mySQL, and PHP are a package. So if vBulletin can't actually run stably on mySQL, that's a problem for the developers of vBulletin, since I will abandon the product if it doesn't work. It's the same problem as a software developer that develops for an OS--of course you can tell your customers, "that's Microsoft's problem, not ours," but in the long run, they are going to buy a different program if yours fails to find a way to work around the OS's limitations.

Possible ways to address it:

* provide insights in the documentation as to what does cause these errors and pointers for fixing them in mySQL (that go beyond repairing tables, since that doesn't seem to exhaust the possible causes for this error)
* lobby the mySQL folks to fix a bug--and tell vB users that you're doing so and what the prognosis is
* make vB database-independent (I know this is in the works)--e.g. one has to wonder whether this database corruption results from some sort of ACID deficiency in mySQL
* make available tools to recover from these errors (ideally automatically, in the form of a cleanup script that administrators can enable to be run when this type of error occurs)

I am thrilled with vBulletin in every other way, and have nothing but the desire to use it and pay for it for years to come, but obviously I can't do so if it does not run stably!

Best wishes in handling what is obviously a subtle and hard-to-diagnose problem--and my ISP and I will continue to try to troubleshoot and share what we learn.

Yours,

Andy

[tubedogg]

Originally posted by ahc
As far as I'm concerned as a end user, vBulletin, mySQL, and PHP are a package.

But they aren't. They are made by 3 different groups/companies. When something goes wrong in PHP, you don't go to vB to get a fix, you go to the PHP site. The same with MySQL - if there's a problem there, you go to the MySQL site or the mailing lists.

So if vBulletin can't actually run stably on mySQL, that's a problem for the developers of vBulletin, since I will abandon the product if it doesn't work.

There are thousands of customers whose vB installation works without problem. For some reason on your particular installation it does not. I think it's safe to say it's been narrowed down to MySQL, as you seem to be having corruption problems. So on your particular system, with its' unique combination of hardware, software, and webserver, MySQL is corrupting tables - I still fail to see what can be done in general for vB users. Yes, users with your exact combination of hardware/software might benefit from the info, but what are the chances of many others having the exact same combo of stuff as you or anyone else?

* provide insights in the documentation as to what does cause these errors and pointers for fixing them in mySQL (that go beyond repairing tables, since that doesn't seem to exhaust the possible causes for this error)

There's not really anything that can be said - there's nothing really known that causes corruption, nor is there any real way to prevent it since it's not apparent that its' coming.

* lobby the mySQL folks to fix a bug--and tell vB users that you're doing so and what the prognosis is

What's the bug? That's the thing - there's no known bug that causes tables to just corrupt out of nowhere. There's nothing we can tell them, beyond "this user seems to be having an overabundance of corruption problems".

* make available tools to recover from these errors (ideally automatically, in the form of a cleanup script that administrators can enable to be run when this type of error occurs)

Currently there is a script to attempt to correct corruption problems. Other than that there's not a whole lot we can do except on a case-by-case basis. This falls under the responsibility of MySQL, IMHO.