[Forum] MySQL Database Errors Galore

[metalguy639]

I have some mysql issues on my vBulletin 4.2 P3 forum. I'm wondering if there is a possible fix for the issues that we are having on the site. Here is the database error I get when I try to fix the unique indexes on my database:

Database error in vBulletin 4.2.0:

Invalid SQL:
SHOW KEYS FROM vbcontenttype;

MySQL Error   : Table 'forum1_forums.vbcontenttype' doesn't exist
Error Number  : 1146
Request Date  : Monday, April 1st 2013 @ 05:36:36 PM
Error Date    : Monday, April 1st 2013 @ 05:36:37 PM
Script        : http://xxxxxxx.com/forums/admincp/repair.php?do=fixunique
Referrer      : http://xxxxxxx.com/forums/admincp/repair.php?do=list
IP Address    : 71.226.100.133
Username      : user
Classname     : vB_Database
MySQL Version : 5.1.68-cll

In addition we are getting errors saying that certain tables no longer are there. The tables that are not there are:



vbcontenttype


vbsearchgroup


vbsearchlog


vbtag


vbtagcontent

Will I have to reinstall or would it be possible to run the upgrade script to see if these tables cannot be replaced. I looked at the database tables and these tables are indeed missing. Thanks for your help.

[snakes1100]

I assume you imported the db to a new server that isnt running innodb, thats a typical issue when its not running in mysql.

If the db wasnt touched and you lost the tables, did yo uverify that inndob is in fact ON?

You can simply recreate those tables if you want, yo ucan rebuild the search & tags tables.

The CMS table, if you had content in it, its gone, but you can simply create it as well.

You can find all the table queries in the mysql-schema.php file in your install directory.

[metalguy639]

I assume you imported the db to a new server that isnt running innodb, thats a typical issue when its not running in mysql. If the db wasnt touched and you lost the tables, did yo uverify that inndob is in fact ON? You can simply recreate those tables if you want, yo ucan rebuild the search & tags tables. The CMS table, if you had content in it, its gone, but you can simply create it as well. You can find all the table queries in the mysql-schema.php file in your install directory.

This is not a recent impex or import. The site has been up for a few months and there were no problems and then poof the mysql server started having issues out of no where. I will check with the host and see if they can enable innodb. Thanks. I'll post back soon with an update.

[metalguy639]

Ok we have had the host enable the inndob in the server, but we still have serious errors. No one can make a post or thread on the forum. Also whenever I try to recount any of the stats in the admincp I get a database error. I got the following errors in the forum:

Database error in vBulletin 4.2.0:

Invalid SQL:

            (SELECT 'package' AS classtype, package.packageid AS typeid, package.packageid AS packageid,
                package.productid AS productid, if(package.productid = 'vbulletin', 1, product.active) AS enabled,
                package.class AS class, -1 as isaggregator
            FROM vbpackage AS package
            LEFT JOIN vbproduct AS product
                    ON product.productid = package.productid
            WHERE product.active = 1
                OR package.productid = 'vbulletin'
            )

            UNION

            (SELECT 'contenttype' AS classtype, contenttypeid AS typeid, contenttype.packageid AS packageid,
                1, 1, contenttype.class AS class  ,  contenttype.isaggregator
            FROM vbcontenttype AS contenttype
            INNER JOIN vbpackage AS package ON package.packageid = contenttype.packageid
            LEFT JOIN vbproduct AS product ON product.productid = package.productid
            WHERE product.active = 1
            OR package.productid = 'vbulletin'  );

MySQL Error   : Table 'xxxx_xxxx.vbcontenttype' doesn't exist
Error Number  : 1146
Request Date  : Tuesday, April 2nd 2013 @ 08:08:31 PM
Error Date    : Tuesday, April 2nd 2013 @ 08:08:31 PM
Script        : http://xxxx.com/forums/admincp/misc.php?do=updatethread
Referrer      : http://xxxx.com/forums/admincp/misc.php
IP Address    : xxxxxxxx
Username      : xxxxxxx
Classname     : vB_Database
MySQL Version : 5.1.68-cll

And when I try to create a new thread or make a post:

Database error in vBulletin 4.2.0:

Invalid SQL:

          SELECT t.tagtext, p.tagtext as canonicaltagtext
            FROM vbtag t JOIN
                vbtag p ON t.canonicaltagid = p.tagid
            WHERE t.tagtext IN ('');

MySQL Error   : Table 'xxxx_xxxx.vbtag' doesn't exist
Error Number  : 1146
Request Date  : Tuesday, April 2nd 2013 @ 08:07:45 PM
Error Date    : Tuesday, April 2nd 2013 @ 08:07:46 PM
Script        : http://xxxx.com/forums/newthread.php?do=postthread&f=24
Referrer      : http://xxxx.com/forums/newthread.php?do=newthread&f=24
IP Address    : xxxx
Username      : xxx
Classname     : vB_Database
MySQL Version : 5.1.68-cll

Please someone help me get this sorted. I fear that a possibiity might be to make a back up of the forum, reinstate and old backup that had everything and worked just fine and then impex the backup I just made into the old forum backup that worked just fine. Would that be possible to do to get this error gone?

[Zachery]

You're missing tables in the database, do you have a backup?
You're not going to be able to impex the backup.

How many other database errors do you have? The two tables that you're showing here could likely be restored separately, without causing major headaches. I just don't know what else is missing.

[metalguy639]

These are the only tables that are missing to my knowledge.

vbcontenttype
vbsearchgroup
vbsearchlog
vbtag
vbtagcontent

The old backup I have has all those tables in it but its from quite awhile ago, like many many months ago or close to a year. If I were to use the old backup I'd have to make a test forum with the old database as it was vb 3 and then upgrade it to vb4, and then see if its possible to impex the new database that we just backed up into the upgraded clean database to get the users, posts, threads etc. Or maybe I could just reinstall vb4 and then impex the new backup into the new install? Is there a way to just restore the missing tables? That would be optimum and easier for us.

[tim.liton]

1. backup the new databaase.
2. copy the old database somewhere.
3. upload the old database in a test forum and delete all tables that you dont need. Just let the tables that are missing in your new database.
4. Export.
5. Check one more time if you have backup of the new database.
6. import the tables from the old database that you have just deleted the tables that you dont need.

I hope this will help.

Good luck.

[snakes1100]

First you need to find out what happened to the missing tables.

Also, there is no need delete all the tables from the old backup you have, simply export what you need, but i wouldnt import them until you find out whats going on.

[metalguy639]

Thanks all I'm going to try these suggestions and post back.

[metalguy639]

Ok when I tried to add the tables manually I get this message:

#1050 - Table 'vbcontenttype' already exists

I took a screenshot in phpmyadmin. That table is NOT there even though it says its there. Any ideas?

[da_prez]

Sorry, wrong thread.

[snakes1100]

Is this a dedicated/vps server metalguy?

[metalguy639]

Is this a dedicated/vps server metalguy?

Good question. I'm not exactly sure I'll check with the other admins and see.

[metalguy639]

Is this a dedicated/vps server metalguy?

It appears to be shared hosting I believe.

[snakes1100]

If thats the case & they cant/wont repair/recover the table via innodb recovery, then there is not much you can do.

You can create a new db & import from a back, then copy over the new table, but you may have a issue as the current corrupt table, mysql/phpmyadmin isnt going to allow you to delete it, you'd have to ask your host to delete at the server level, its also possible that the table isnt corrupt & is simply owned by the wrong user on the system as well, there are many possibilities.