Announcement

Collapse
No announcement yet.

moving servers... database import error

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

  • moving servers... database import error

    I'm currently running vb 2.2.9 on Win2k and am working towards moving my sites over to a Red Hat 7.2 / Apache server.

    When I try to import my vb database on the Linux box I get the following error:

    ----------------------------------------
    mysql> CREATE DATABASE forums;
    Query OK, 1 row affected (0.00 sec)
    mysql> exit
    Bye

    [[email protected] bin]# ./mysql -uroot -p forums < /home/John/022003vb.sql
    Enter password:
    ERROR 1064 at line 86270: You have an error in your SQL syntax near ''I wouldn\'t be so concerned about the \"appearance\" of the resume - folded or ' at line 1
    [[email protected] bin]#
    ----------------------------------------

    The database is around 680MB. The import runs for 5-10 minutes then the error comes up.

    Any help is appreciated.

    Thanks,

    John
    ...john2k...

  • #2
    It sounds like a bad backup file. How did you make it and can you make a new one?
    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


    • #3
      I made the backup from the Win2k server command prompt:

      mysqldump -uusername -ppasswd forums > /path/to/022003vb.sql

      My forums are still running on the Win2k server, so I can still make backups. I'm fairly new to Linux and am testing everything out a dev box before I try to do all this on a production machine. I'd like to try and resolve any possibly issues before working on the new Linux server.

      I tested the import command in my first post using two separate backup files, made 2 weeks apart using the same command above, and still get a similar error.

      This is the error from the other backup I tried:

      ----------------------------------------
      mysql> DROP DATABASE forums;
      Query OK, 48 rows affected (0.72 sec)

      mysql> CREATE DATABASE forums;
      Query OK, 1 row affected (0.00 sec)

      mysql> exit
      Bye

      [[email protected] bin]# ./mysql -uroot -p forums < /home/John/020603vb.sql
      Enter password:
      [color=red]ERROR 1064 at line 261622: You have an error in your SQL syntax near ''[QUOTE]Originally posted by Sharky \r\n[B]\r\n\r' at line 1
      [[email protected] bin]#
      ----------------------------------------
      Last edited by john2k; Sun 23 Feb '03, 10:01am.
      ...john2k...

      Comment


      • #4
        I don't understand why you're using the DROP and CREATE queries. Those aren't necessary to restore a database backup.

        I would run repair.php on the database then back it up and try to restore it again.
        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
          If I try to import without using those queries I get the following:

          ----------------------------------------
          [[email protected] bin]# ./mysql -uroot -p forums < /home/John/020603vb.sql
          Enter password:
          ERROR 1049: Unknown database 'forums'
          [[email protected] bin]#
          ----------------------------------------

          I was using the DROP command to basically clear out the previous failed import, then using the CREATE command to create the database. If I don't use CREATE when no db exists I get that error.
          ...john2k...

          Comment


          • #6
            Oh, my bad. I was thinking you were dropping and creating a table.
            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
              Before I made the 022003vb.sql backup I ran the following at the Win2k command prompt:

              myisamchk -r *.MYI

              Does that do the same as the repair.php script?
              ...john2k...

              Comment


              • #8
                As root, run:

                mysqlcheck -A --auto-repair

                This will repair all databases on the server.
                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
                  I ran the repair.php file and everything returned as OK or table already up to date for the optimizations. After doing that I backed up the database again and tried to import on the Linux box.

                  ----------------------------------------
                  [[email protected] bin]# ./mysql -uroot -p forums < /home/John/022303forums.sql
                  Enter password:
                  ERROR 1064 at line 356064: You have an error in your SQL syntax near ''[quote]\r
                  \nDemon Knight wrote on 06-22-2002 08:17 PM:\r\nHi, I recently read yo' at line
                  1

                  [[email protected] bin]#
                  ----------------------------------------

                  I'll try "mysqlcheck -A --auto-repair" next and will post details.
                  ...john2k...

                  Comment


                  • #10
                    I used the mysqlcheck -A --auto-repair command and the result for all the tables was in the form "forums.* OK"

                    The only odd table was the session table which reports that the handler for the table doesn't support check/repair.

                    I'm backing up again and will download the backup file tonight. Tomorrow morning I'll try the import again and will post the results here.

                    Thanks for all of your help, I really appreciate it.

                    Best,

                    John
                    ...john2k...

                    Comment


                    • #11
                      Well, it seems to be working so far. I don't know if it's the "mysqlcheck -A --auto-repair" command that fixed the problem, or my method of downloading the database as I did that differently this time as well.

                      With my other attempts to import the database, I downloaded the *.sql file via FTP from my Win2kServer to my WinXP local workstation, then copied the *.sql file over my local network to the Linux box via Samba. Each time the file size on the Linux box exactly matched the original on the Win2k server.

                      This time, instead of going that route I just had my Linux box download the *.sql file directly using the wget command.


                      The import is taking absolutely forever. Been running 3 1/2 hours so far. At least it hasn't had an error in the first 10 minutes like my previous tries. My linux development box is a dual xeon 500 PIII with 512 RAM. The *.sql file was around 680MB. It is normal for an import to take so long?
                      ...john2k...

                      Comment


                      • #12
                        I've never import a backup that large so I'm not sure if this is normal or not.
                        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


                        • #13
                          The import finally completed. Took around 5 hours. Everything looks good so far, I'm checking everything now to make sure there aren't any problems.

                          Thanks again for all the help.
                          ...john2k...

                          Comment

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