Announcement

Collapse
No announcement yet.

Getting mysql db errors after changing the server

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

  • [Forum] Getting mysql db errors after changing the server

    I recently switched from a shared hosting to a VPS. I set up everything and restored vBulletin's backup and forum seems to load fine. But since then no one is able to post or delete any new threads or replies on my forum because they cause a vbulletin database error like this:

    Code:
    Database error in vBulletin 4.1.1:
    
    Invalid SQL:
    
                SELECT searchcore.groupcontenttypeid, searchcore.groupid
                FROM myvb_searchcore AS searchcore
                WHERE contenttypeid = 1 AND
                    primaryid = 5732;
    
    MySQL Error   : Table 'myvbdb.myvb_searchcore' doesn't exist
    or :

    Code:
    Database error in vBulletin 4.1.1:
    
    Invalid SQL:
    INSERT INTO vbmq_searchgroup (username, contenttypeid, groupid, userid, dateline)
                VALUES ( '', 3, 4, 0, 0 )
                ON DUPLICATE KEY UPDATE  username = VALUES(username), contenttypeid = VALUES(contenttypeid), groupid = VALUES(groupid), userid = VALUES(userid), dateline = VALUES(dateline);
    
    MySQL Error   : Table 'myvbdb.myvb_searchgroup' doesn't exist
    Error Number  : 1146

    I tried all the maintenance options in admincp and none of them worked out.

  • #2
    I don't think everything was properly restored actually - you seem to be missing a table. Do you still have access to the old data?
    anders | vbulletin team | check out the new vbulletin facebook app
    Proudly vBulletin'ing since 2001
    Please be my friend!
    http://www.twitter.com/inetskunkworks
    vBulletin Performance Articles:
    Click here to read

    Comment


    • #3
      No unfortunately not. But I have tried dropping tables and restoring the sql again. which did not work. Is there a possibility that I can create those tables manually? Because it seems like all the user's data and topics are restored correctly

      Comment


      • #4
        Which table prefix are you using and what is the name of the last table in that db?
        Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
        Change CKEditor Colors to Match Style (for 4.1.4 and above)

        Steve Machol Photography


        Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


        Comment


        • #5
          the prefix is: vbmq_

          I attached a full list of tables from the db. Hope it helps

          tables.txt

          Comment


          • #6
            Thanks. Hopefully that is the only table missing. Try running this query to recreate that table:

            CREATE TABLE vbmq_ searchcore (
            searchcoreid INT UNSIGNED NOT NULL AUTO_INCREMENT,
            contenttypeid INT UNSIGNED NOT NULL,
            primaryid INT UNSIGNED NOT NULL,
            groupcontenttypeid INT UNSIGNED NOT NULL,
            groupid INT UNSIGNED NOT NULL DEFAULT 0,
            dateline INT UNSIGNED NOT NULL DEFAULT 0,
            userid INT UNSIGNED NOT NULL DEFAULT 0,
            username VARCHAR(100) NOT NULL,
            ipaddress INT UNSIGNED NOT NULL,
            searchgroupid INT UNSIGNED NOT NULL,
            PRIMARY KEY (searchcoreid),
            UNIQUE KEY contentunique (contenttypeid, primaryid),
            KEY groupid (groupcontenttypeid, groupid),
            KEY ipaddress (ipaddress),
            KEY dateline (dateline),
            KEY userid (userid),
            KEY searchgroupid (searchgroupid)
            );


            Then rebuild the search index:

            Admin CP -> Maintenance -> Update Counters -> Rebuild Search Index
            Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
            Change CKEditor Colors to Match Style (for 4.1.4 and above)

            Steve Machol Photography


            Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


            Comment


            • #7
              Thank you,

              but unfortunately, that is not the only table I am missing. I'm also missing the Table 'vbmq_tag' , 'vbmq_contenttype' , ' vbmq_searchgroup'
              , and probably a few more. Can I run this query for every table that is missing? Are the attributes the same?

              ----------
              EDIT:

              Ok, I just compared the current db with one of my old backups. And these are the tables I am missing:


              vbmq_tag
              vbmq_searchgroup
              vbmq_contenttype
              vbmq_searchlog
              vbmq_tag
              vbmq_tagcontent

              Thanks again
              Last edited by hermes3; Sat 12 Mar '11, 11:27am.

              Comment


              • #8
                Look in the /install/mysql-schema.php file for the queries to add those tables. However unfortunately you would need to restore the data for that tag and contentype tables. At this point I suggest you go back to the original database and make a new and complete backup.

                Note: The only consistently reliable method of backing up and restoring a database is with shell access via telnet or ssh. This is because backing up with a PHP script like that in the Admin CP or phpMyAdmin can result in PHP timeout errors and an incomplete backup file.

                Please see the instructions here:

                Backup:
                http://www.vbulletin.com/docs/html/m...nce_ssh_backup

                Restore:
                http://www.vbulletin.com/docs/html/m...ce_ssh_restore

                If you don't have telnet or SSH access, some people have reported success with these scripts:

                MySQLDumper:
                http://www.mysqldumper.net/

                MySQLHotxcopy:
                http://www.vbulletin.com/forum/showt...t=mysqlhotcopy
                Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
                Change CKEditor Colors to Match Style (for 4.1.4 and above)

                Steve Machol Photography


                Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


                Comment


                • #9
                  The problem is that I don't have access to the original database anymore. What I did was copying those 6 tables from one of my old backups and it worked! Forum is now functioning properly. My only concern is future issues. Am I going to be missing something that will bother me in the future?

                  Comment


                  • #10
                    Rebuild the search index. Other than that there is nothing you can do. Hopefully there will not be any problems.
                    Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
                    Change CKEditor Colors to Match Style (for 4.1.4 and above)

                    Steve Machol Photography


                    Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


                    Comment

                    widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                    Working...
                    X