MySQL - checking database

snowman

Member
Hello -

When I was trying to find the table name of the import table to resolve a direction issue, I used the "Checking Database" feature of MySQL. When it checked the xenForo database, I got many "Table upgrade required" errors. Here are some examples:

snowman_community.archived_import_log OK
snowman_community.xf_addon error : Table upgrade required. Please do "REPAIR TABLE `xf_addon`" or dump/reload to fix it!
snowman_community.xf_admin OK
snowman_community.xf_admin_navigation error : Table upgrade required. Please do "REPAIR TABLE `xf_admin_navigation`" or dump/reload to fix it! snowman_community.xf_admin_permission error : Table upgrade required. Please do "REPAIR TABLE `xf_admin_permission`" or dump/reload to fix it! snowman_community.xf_admin_permission_entry error : Table upgrade required. Please do "REPAIR TABLE `xf_admin_permission_entry`" or dump/reload to fix it! snowman_community.xf_admin_template error : Table upgrade required. Please do "REPAIR TABLE `xf_admin_template`" or dump/reload to fix it! snowman_community.xf_admin_template_compiled error : Table upgrade required. Please do "REPAIR TABLE `xf_admin_template_compiled`" or dump/reload to fix it! snowman_community.xf_admin_template_include OK

So it was a mixture of OK's and Errors.

Is this anything to worry about? My installation is from yesterday. If this is not accurate information insofar as any errors with the database, is there a tool we can use for xenForo to check these things more accurately?

Repair table wasn't able to repair most of them when I tried to run it.

Thanks!
 
Did your host or server person recently upgrade MySQL? From what version to what version? Upgrading MySQL can trigger this error for existing tables. Normally a repair would fix this, but INNODB tables don't support repair so this error doesn't make sense.

I did some searching and found this to be a bug:

http://bugs.mysql.com/bug.php?id=42563
http://bugs.mysql.com/bug.php?id=47205
http://lists.mysql.com/commits/132572

I see two fixes:

1) Recreate your database using a backup taken before MySQL was upgraded (assuming you made a backup before the upgrade).

2) Upgrade the tables using ALTER TABLE. I see these two forms suggested:

ALTER TABLE table ENGINE=innodb;
ALTER TABLE table FORCE;
 
Thanks for your response Jake -

Yes, the server was experiencing other issues related to the versions of PHP and MySQL and the library when it was compiled (Server error 246). So the host updated and recompiled PHP to 5.2.17 and MySQL to 5.1.56. Previously, MySQL was at 5.0.92 - Community.

This was a new install that imported from vBulletin. The original vBulletin database was created before the MySQL upgrade but that seems like a major undertaking of reinstalling everything from scratch again.

I would like to try to upgrade the tables I think using ALTER TABLE. What tool do I use to issue this command? I really don't know much about MySQL databases or how to work with them. Should I ask the host to do this?
 
Thanks again Jake ... as luck would have it, MyPHP for some reason will not load. I sent a note to my host but they have not responded yet. Will Navicat Lite be able to to run the query remotely? I never have much luck trying to do things in the shell.
 
My host performed the suggested "ALTER TABLE <table> FORCE" command on all tables that returned an error regarding upgrade. At this time, I am able to perform a MySQL Check on the xenForo database and receive all "OK" responses. So it appears this did resolve the issue. Thanks again for your help Jake!
 
Mind you that some tables are MEMORY (especially '*_view' ones), others do seem to be MyISAM (i think session* ones and search_index)
 
Top Bottom