transfer vbulletin database

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • morny
    Member
    • Jun 2008
    • 34

    transfer vbulletin database

    Hello there,

    I have one problem. I exported the vbulletin database (MySQL) and i want to import it on another server, the only problem no software ( on windows ) can import it because the vb_attachaments table contains EXTENDED INSERTS queries and thats why i receive error at importing.

    Can anybody tell me how to handle this? Or what software to use (on windows) for importing the MySQL database.
  • George L
    Former vBulletin Support
    • May 2000
    • 32996
    • 3.8.x

    #2
    what versions of mysql server are on both existing and destination servers ? you can just import the mysql sql file into windows server using DOS/shell prompt on windows machine and mysql command line exactly like you do on linux machine just the path to mysql sql backup file is different for windows versus linux.
    :: Always Back Up Forum Database + Attachments BEFORE upgrading !
    :: Nginx SPDY SSL - World Flags Demo [video results]
    :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

    Comment

    • morny
      Member
      • Jun 2008
      • 34

      #3
      I actually did that this way and it worked but at one point it crashes:

      mysql -u root -p
      use database;
      source vbulletin.sql

      My database is 200Mb and it imported 141 MB after i receive this errors :

      ERROR 1231 (42000): Variable 'sql_mode' can't be set to the value of 'NULL'
      ERROR 1231 (42000): Variable 'foreign_key_checks' can't be set to the value of '
      NULL'
      ERROR 1231 (42000): Variable 'unique_checks' can't be set to the value of 'NULL'

      ERROR 1231 (42000): Variable 'character_set_client' can't be set to the value of
      'NULL'
      Query OK, 0 rows affected (0.00 sec)

      ERROR 1231 (42000): Variable 'collation_connection' can't be set to the value of
      'NULL'
      ERROR 1231 (42000): Variable 'character_set_client' can't be set to the value of
      'NULL'
      mysql>

      Its about the fields type or the values from the database?
      Please let me know, i know im close to achieve this!

      Thanks

      Comment

      • George L
        Former vBulletin Support
        • May 2000
        • 32996
        • 3.8.x

        #4
        those variables are for mysql settings i think.. what version of mysql for old and new server ? as well as how did you specifically backup your sql file ? the exact command line used to backup to sql file ?

        tried

        mysql -u root -p < X:\path\to\vbulletin.sql

        where X is drive letter
        :: Always Back Up Forum Database + Attachments BEFORE upgrading !
        :: Nginx SPDY SSL - World Flags Demo [video results]
        :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

        Comment

        • morny
          Member
          • Jun 2008
          • 34

          #5
          Nope i didnt tried with that command, because that one give MySQL server has gone away error!

          I used

          "USE database;"
          "SOURCE X:\path\vbulletin.sql"

          This way it imported without going the server away, i only got those variables error.

          the old server its using MySQL version 5.0 and the new server its using MySQL version 5.1.

          The versions could be a problem?

          Thanks for your reply

          Comment

          • morny
            Member
            • Jun 2008
            • 34

            #6
            At the beginning of the .sql file it's this :

            /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
            /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
            /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
            /*!40101 SET NAMES utf8 */;

            /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
            /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
            /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


            I think here some values needs to be modified!

            Comment

            • morny
              Member
              • Jun 2008
              • 34

              #7
              At the end is this :


              /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
              /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
              /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
              /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
              /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
              /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
              /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;


              So this is the part, the new variables are defined as the OLD variables which are declared at the beginning.

              Comment

              • George L
                Former vBulletin Support
                • May 2000
                • 32996
                • 3.8.x

                #8
                1. do you still have access to old server to do another attempt for mysql database backup ?
                2. sounds like new server mysql configuration isn't setup properly to handle imports if you get mysql gone away errors. Can you post your my.cnf and my.ini from both old and new servers and it would help if you posted the full config and info particularly question #8 from http://www.vbulletin.com/go/tweak for both old and new servers

                basic command i use to backup on linux

                mysqldump --opt -Q -u root -p vbdatabasename > vbulletinbackup.sql

                to restore

                mysql -u root -p newvbdatabasename < vbulletinbackup.sql
                :: Always Back Up Forum Database + Attachments BEFORE upgrading !
                :: Nginx SPDY SSL - World Flags Demo [video results]
                :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

                Comment

                • George L
                  Former vBulletin Support
                  • May 2000
                  • 32996
                  • 3.8.x

                  #9
                  Also i ask mysql versions because some older versions of mysql might have a bug http://bugs.mysql.com/bug.php?id=26670 so knowing your specific mysql 5.0.xx and 5.1.xx versions would be helpful.
                  :: Always Back Up Forum Database + Attachments BEFORE upgrading !
                  :: Nginx SPDY SSL - World Flags Demo [video results]
                  :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

                  Comment

                  • morny
                    Member
                    • Jun 2008
                    • 34

                    #10
                    Ive put the value "" for every variable. And this is what i get now :

                    1. Unknown collation ''
                    2. unique_checks can't bet set to value of ''
                    3. foreign_key_checks can't bet set to the value of ''

                    So it seems 2 errors are passed now. I just need to figure it out what are the default values for this fields.

                    Comment

                    • morny
                      Member
                      • Jun 2008
                      • 34

                      #11
                      The new server has version 5.1.34 and the old server has version is 5.0.51b. Hope that would be helpful

                      Comment

                      • morny
                        Member
                        • Jun 2008
                        • 34

                        #12
                        Ive backup the database with the software MySQL GUI Administrator, but now im dumping the database with mysqldump and then give it another try.

                        Its hard to give the cnf files because im connected through remote desktop to the both servers and its moving very very slow!

                        Comment

                        • morny
                          Member
                          • Jun 2008
                          • 34

                          #13
                          I generated with mysqldump and no errors when i imported!!! Great

                          Thanks a lot!!!

                          Comment

                          • George L
                            Former vBulletin Support
                            • May 2000
                            • 32996
                            • 3.8.x

                            #14
                            Originally posted by morny
                            I generated with mysqldump and no errors when i imported!!! Great

                            Thanks a lot!!!
                            Excellent good to hear
                            :: Always Back Up Forum Database + Attachments BEFORE upgrading !
                            :: Nginx SPDY SSL - World Flags Demo [video results]
                            :: vBulletin hacked forums: Clean Up Guide for VPS/Dedicated hosting users [ vbulletin.com blog summary ]

                            Comment

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