Frequent (2 or 3 times a day) SQL Corruption

[Hakur]

Well, I first posted this information in the bug area (http://www.vbulletin.com/forum/bugs....view&bugid=397) and I was told it was a problem with the version of mySQL I was running. I spent many hours on the phone and finally got my provider to upgrade to version 4.0.14. The problem is still there.

Here's the problem:

atabase error in vBulletin 3.0.0 Beta 5:

Invalid SQL:
### COUNT THREADS ###
SELECT COUNT(*) AS threads,
SUM(IF(lastpost>=1061211137 AND open<>10,1,0)) AS newthread
FROM thread AS thread
LEFT JOIN deletionlog AS deletionlog ON(thread.threadid = deletionlog.primaryid AND type = 'thread')
WHERE forumid = 5
AND sticky = 0
AND visible = 1

AND lastpost >= 1050844468

AND deletionlog.primaryid IS NULL

mysql error: Can't open file: 'thread.MYI'. (errno: 145)

mysql error number: 1016

Date: Monday 18th of August 2003 09:14:29 AM
Script: http://www.swgmedics.com/forums/foru...&daysprune=120
Referer: http://www.swgmedics.com/forums/forumdisplay.php?f=5
Username: Unregistered
IP Address: 213.171.63.59

This error effectively takes down the whole site. Any thread or post that a user clicks on produces this same message (which is then emailed to me). This morning I had 1291 messages containing this error report over a 9 hour period.

To fix the error I am running the Repair/Optimize Tables and in each case it has fixed the problem.

Any idea what's causing it? Let me know if you need more information or if you would like access to mySql or Admin on the board.

Thanks,

-Rob

[Steve Machol]

AFAIK the upgrade to 4.0.14 works for some people but not for everyone. Here is a 'quick fix' that eva2000 posted that seems to help a lot of people with this problem:

Quick fix:

Try using mysqldump to create a sql backup your database - mysqldump guide at http://www.sitepoint.c
om/article/678

Then create a new empty database name and import the mysql sql backup back into the empty database,
and edit config.php to point to the new database

Also take a read of

http://www.mysql.com/doc/en/Corrupted_MyISAM_tables.html

and

http://www.vbulletin.com/forum/showt...threadid=69803

[wacnstac]

This is ridiculous and a bit dicey for the average user of your product. I too have upgraded to SQL to 4.0.14 and still have this problem at least weekly on a LARGE forum. This is not good. HELP, vbulletin!!! Automate this "fix".

[Scott MacVicar]

this isn't an issue with vBulletin.

It affects any program that heavily uses MyISAM tables.

The only guaranteed solution is to downgrade to 3.23 series and hope that the people as MySQL resolve this problem.

[wacnstac]

So you are saying that even the "quick fix" may not work?

Scott and Steve, I am in no way saying this is a vbulletin problem. I'm knowledgable enough to ascertain that, but keep in mind that those of us with limited SQL skill may be a bit overwhelmed by the "quick fix" repair especially if it MAY not fix the problem.

We know this is not a vbulletin problem but perhaps vb with it's huge user base who are experiencing this problem could put pressure on SQL to fix the problem. There are many of us who could not explain this problem to the SQL people and this is where vbulletin could help.

[Scott MacVicar]

the problem is hard to identify due to the large amount of configurations, we also are unable to produce the problem on any of the vB staff servers.

Ask your host to shutdown mysqld
run myisamchk -e /path/to/database_dir/*.MYI

the path being the path to your database directory this will do a thorough check on all files and may take a long time depending on size of the message board.

Is your host running cpanel as this appears to be a common problem with these servers due to the way it was set up.

[wacnstac]

the problem is hard to identify due to the large amount of configurations, we also are unable to produce the problem on any of the vB staff servers.

Ask your host to shutdown mysqld
run myisamchk -e /path/to/database_dir/*.MYI

the path being the path to your database directory this will do a thorough check on all files and may take a long time depending on size of the message board.

Is your host running cpanel as this appears to be a common problem with these servers due to the way it was set up.

Yes we do run Cpanel. The only reason why this may be common is that until recently, cpanel didn't support the upgrade to SQL 4.0.14. It now does and the problem still occurs. I will the myisamchk by my host manager.

[Scott MacVicar]

ok just done some research.

Get your host to recompile PHP
from shell run
/scripts/easyapache

then run
myisamchk -e /path/to/database_dir/*.MYI

as long as your running 4.0.14 with latest MySQL libraries bundled with PHP it appears to resolve the problem.

Information on MySQL regarding the bug http://bugs.mysql.com/bug.php?id=563

[wacnstac]

Thanks for the research Scott. Will try to get my host manager to do just that ASAP.

[wacnstac]

Pathetic

Had my host manager perform the steps above last night and got another 1016 (this time the sessions table) corrupted less than 24 hours later. HELP! I'm held hostage waiting for the next occurance of this error.

[Scott MacVicar]

this shouldn't happen with the session table since there is no actual file to corrupt.

Can you provide phpmyadmin access so I can check your table types. session should be a HEAP table not MyISAM.

[wacnstac]

this shouldn't happen with the session table since there is no actual file to corrupt.

Can you provide phpmyadmin access so I can check your table types. session should be a HEAP table not MyISAM.

Just sent you a PM.

[Acutus]

Just found that thread using search.

I am having right the same issues. I have called my host today and asked to downgrade to 3.xx . However, I have shared webspace so it's not possible to downgrade the mysql server.

I must admit, that I am quite a bit disappointed, because a vBulletin license costs good money and therefore it should run smooth without any problems. I understand it, if some open source software or other freeware is having problems, but I can not accept that software which I have paid for does not run.

If it's a MySQL bug, then you should put some pressure on the MySQL devs to get this issue solved, because as it is now, it's not acceptable.

btw, I am hosted at www.hosteurope.de who are not using cpanel as far as I know.

[Scott MacVicar]

its out with our control and its not just vBulletin that has the problems.

Invision, vBulletin and any other software that relies heavily on MyISAM indexes is having the corruption. Its harder to track down an error in a compiled program than an open source software as logging has to be in built.
Since none of the staff here are experiencing the problem then its hard for us to identify the source.

Reading the bug report http://bugs.mysql.com/bug.php?id=563 the last post is by an Invision user with the same problem.

If you can turn on query logging and provide them with a copy of the corrupted data file and the log i'm sure they'd be much appreciated.