Vbulletin MySQL error

[gkainz]

another board I visit is throwing an error and they have no support. I'm an Oracle DBA but have never touched MySQL. A quick google search shows options for Repair Table in MySQL - would that be the first obvious fix for something like this error?
--------------------------------------
vBulletin Database Error!

Database error in vBulletin 3.8.4:

Invalid SQL:
SELECT post.*,
IF(post.visible = 2, 1, 0) AS isdeleted,
editlog.userid AS edit_userid,
editlog.dateline AS edit_dateline,
editlog.reason AS edit_reason, editlog.hashistory
FROM post AS post
LEFT JOIN editlog AS editlog ON (editlog.postid = post.postid)
WHERE post.postid = 1624179;

MySQL Error : Table './diese3_vb/post' is marked as crashed and should be repaired
Error Number : 145

 

[murphey]

another board I visit is throwing an error and they have no support. I'm an Oracle DBA but have never touched MySQL. A quick google search shows options for Repair Table in MySQL - would that be the first obvious fix for something like this error?

Yes.

It's also common when there's a huge load on the system or one of the tables got corrupted. I've yet to find any software error messages that mean anything until you understand what caused the problem. a** Backwards and has been for decades.

Before repair, can you get a dump of the current contents of the table and examine it? It may be something simple as a data type conflict that snuck thru or worse case, the whole table is trash...er...corrupted.

Do you have the DDL that created the table? After attempting to repair, the next option would be to delete it (and hope there's a backup to reload the data) and re-create it.

 

[gkainz]

The vbulletin admin gave me access to the admin cp page (which appears to be the vbulletin panel, not any mysql admin).

Under Maintenance, Diagnostics, I see

post ... Table './diese3_vb/post' is marked as crashed and should be repaired
usercss ... Incorrect key file for table 'usercss'; try to repair it

Selecting Repair/Optimize Table
Table Data Len Index Len Overhead
--------- ----------- ------------ ------------
post 0 0 0
usercss 0 0 0

Optimize Tables: No
Repair Tables: Yes

Table Action Results
-------- ------- --------------------------------------------------------------
post Check Error: Table upgrade required. Please do "REPAIR TABLE `post`" or dump/reload to fix it!
post *
usercss Check Error: Incorrect key file for table 'usercss'; try to repair it
usercss Check Error: Corrupt
usercss *

So, apparently I need access to the mysql admin tool/page to go any further?

 

[jesse]

You need to run repair table with the appropriate permission level. You could also run myisamchk -r tablefile on the ssh console.

If they don't have the appropriate permission they'll need to open a support ticket with their hosting provider.

Switching the tables to the InnoDB engine instead of Myisam would prevent this from happening. I havent done this with PoA yet but intend to do so.

(written on mobile phone)

 

[gkainz]

Thanks, Jesse - will pass that along.

 

[gkainz]

this host is not (yet) supporting InnoDB, so stuck on myisam

got to the admincp and ran repair database, the two tables are still flagged as corrupt, status is flagged as "in use" on the admin screen. attempting to just repair those 2 tables doesn't repair them. Still showing the same status post-repair as before:

Table Action Results
-------- ------- --------------------------------------------------------------
post Check Error: Table upgrade required. Please do "REPAIR TABLE `post`" or dump/reload to fix it!
post *
usercss Check Error: Incorrect key file for table 'usercss'; try to repair it
usercss Check Error: Corrupt
usercss *

The repair was run from the mysql controlpanel (or myphpadmin cp, don't recall right now) but it doesn't appear like the hosting interface offers up a ssh connection. And again, I'm just poking around for the vbulletin board owner adhoc - not my db, and I've never looked under the vbulletin covers before this.

There are 3 databases; _forums, _vb and ... another ... is this typical vbulletin setup?

 

[jesse]

In the vBulletin control panel scroll down and click Maintenance. Then click Execute SQL Query and run: REPAIR TABLE usercss

If that doesn't fix it then open a support ticket with the host and have them repair the table(s). This won't be the first time that they've seen a MyISAM table get corrupted.

 

[gkainz]

thanks, I'll pass that on. Appears that the privs the owner has doesn't include "run query" - or the access he granted to my user.