SQL errors during upgrade process

[hoax]

I recently went to upgrade a clean install of 3.6.8 Patch Level 2 to 3.7.0 Beta 3. All went well until the end of the upgrade when I got this error:

Step 2) Import latest admin help
Importing vbulletin-adminhelp.xml

Database error in vBulletin 3.7.0 Beta 3:

Invalid SQL:

DELETE FROM phrase
WHERE product IN ('vbulletin', '')
AND fieldname = 'cphelptext'
AND languageid = -1;

MySQL Error : Table './Removed/phrase' is marked as crashed and should be repaired
Error Number : 145
Date : Thursday, January 3rd 2008 @ 11:07:57 PM
Script : http://removed.com/talk/install/finalupgrade.php?step=2 (https://www.vbulletin.com/link.php?u=http%3A%2F%2Fkinktalk.com%2Ftalk%2Finst all%2Ffinalupgrade.php%3Fstep%3D2)
Referrer : http://removed.com/talk/install/finalupgrade.php?step=1 (https://www.vbulletin.com/link.php?u=http%3A%2F%2Fkinktalk.com%2Ftalk%2Finst all%2Ffinalupgrade.php%3Fstep%3D1)
IP Address : 75.22.43.201
Username :
Classname : vB_Database
MySQL Version : 5.0.45-community
I tried doing the upgrade again after repairing several times in phpMyAdmin but got the same error each time. When I try to login to the Admin panel I now get this error:


Database error in vBulletin 3.7.0 Beta 3:

Invalid SQL:

SELECT text, languageid, special
FROM phrase AS phrase
LEFT JOIN phrasetype USING (fieldname)
WHERE phrase.fieldname = 'error'
AND varname = 'security_alert_x_still_exists' AND languageid IN (-1, 0, 1);

MySQL Error : Table './removed/phrase' is marked as crashed and should be repaired
Error Number : 145
Date : Friday, January 4th 2008 @ 10:21:18 AM
Script : http://removed.com/talk/arkmin1/
Referrer :
IP Address : 75.22.43.201
Username : Removed
Classname : vB_Database
MySQL Version : 5.0.45-community
This same SQL error also comes up when I go into the boards and attempt to post now. I've tried repairing the mySQL table via phpMyAdmin but two tables come up with different messages from the other tables each time.

removed.phrase repair status Table is already up to date
and

removed.session repair note The storage engine for the table doesn't support r...

What really gets me is I had this exact same phrase errors when I revently upgraded the blog addon on my 3.6.8 Patch Level 2 instillation. When it happened there I attempted many repairs (both the built in vBulletin many times and the phpMyAdmin repairs) but the same phrase table would always stay stuck and spew errors. I eventually gave up and reverted back to my backup. This is a problem I am having on both my vBulletin sites when I attempt to upgraded anything. I've gotten to the point I am afraid to touch anything.

I think this may be a problem with my mySQL version or configuration but I am not sure. What is odd is I have this error on both my boards on the same server.

It may also be important to point out that I recently upgraded to PHP5 from PHP4 and had my mySQL version upgraded to the latest. I'm guessing something is going wrong now as a result of this.

Any advice or ideas?

[Wayne Luke]

Run Repair/Optimize in the Admin CP. You may need to run it more than once to fix all errors:

Admin Control Panel -> Import & Maintenance -> Repair/Optimize Tables -> Check All -> Continue

If you cannot access the Admin CP, then you can use phpMyAdmin to repair tables.

[hoax]

I've run the repair in phpMyAdmin many times and still nothing. Each time it just says "Table is already up to date" on the phrase table. All other tables just say "OK". I've run the repair 15 times now.

[Steve Machol]

Are you still getting any errors? If so, what are they?

[hoax]

I'm still getting the errors listed in my first post each time I attempt the upgrade. I don't think the repair is working. :(

[Steve Machol]

I don't see how based on your pic, but if you still have this problem, then check out this thread for other ways to repair a db:

http://www.vbulletin.com/forum/showthread.php?s=&threadid=44909

[hoax]

I'll try that out but the problem is more why this same database error occurs independently on my two different installs for different reasons. Shutting down my server and repairing the database each time I upgrade vBulletin or my vBulletin blog seems silly. There has to be something else at work here.

Anyways, I'll try the steps in that link and post my results.

[Wayne Luke]

The actual error comes from the MYSQL server. It is what is telling you the file needs to be repaired. vBulletin just displays it to you. You should talk to your hosting provider about this to make sure there are no hardware errors, that your pointing to the proper databases and so forth.

[PossumX]

When you upgraded MySQL to 5.0.45, did you run the mysql_upgrade command from a SSH or Telnet session? It is nearly a mandate when coming from any 4.x invocation to the 5.x tree.

syntax is:

mysql_upgrade -u username -p

hit enter, and you will be prompted for the password. Make sure the username is the parent for any database you have setup (or is setup as default for any control panel), otherwise you will need to run this command for each database centrically.

I had a similar issue when I cam up from early 4.0.x to current (and was corrected once I recalled the upgrade command needed to be executed), and am now on 5.0.54, running the mysql_upgrade command after every upgrade installed (not really necessary in the 5.0.x tree, but better safe that sorry).

Here is the direct link to the procedure:

http://dev.mysql.com/doc/refman/5.0/...l-upgrade.html (http://dev.mysql.com/doc/refman/5.0/en/mysql-upgrade.html)

[hoax]

Thanks a million PossumX!

Your advice hit the problem square on the head. After I upgraded the tables I then ran a repair while the mySQL server was stopped (http://www.vbulletin.com/forum/showthread.php?s=&threadid=44909) and all my mySQL errors went away.

Many many thanks :)