Announcement

Collapse
No announcement yet.

MySQL Database Errors Galore

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • [Forum] MySQL Database Errors Galore

    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:

    Code:
    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.



  • #2
    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.


    Gentoo Geek

    Comment


    • #3
      Originally posted by snakes1100 View Post
      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.

      Comment


      • #4
        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:

        Code:
        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:

        Code:
        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?

        Comment


        • #5
          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.


          Comment


          • #6
            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.

            Comment


            • #7
              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.

              Comment


              • #8
                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.
                Gentoo Geek

                Comment


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

                  Comment


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

                    Code:
                    #1050 - Table 'vbcontenttype' already exists
                    I took a screenshot in phpmyadmin. That table is NOT there even though it says its there. Any ideas?

                    Comment


                    • #11
                      Sorry, wrong thread.

                      Comment


                      • #12
                        Is this a dedicated/vps server metalguy?
                        Gentoo Geek

                        Comment


                        • #13
                          Originally posted by snakes1100 View Post
                          Is this a dedicated/vps server metalguy?
                          Good question. I'm not exactly sure I'll check with the other admins and see.

                          Comment


                          • #14
                            Originally posted by snakes1100 View Post
                            Is this a dedicated/vps server metalguy?
                            It appears to be shared hosting I believe.

                            Comment


                            • #15
                              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.
                              Gentoo Geek

                              Comment

                              Related Topics

                              Collapse

                              Working...
                              X