Restoring Database: SQL Syntax Error

  • Resolved rosiembanks

    (@rosiembanks)


    19 years ago

    Ms. Goober reporting in. I’m in the middle of changing webhosts. Downloaded all important files, backed up database according to the Codex instructions here. Made three backups, each in a different compression format, and tucked them safely into my hard drive.

    Tried ftping my WP files into new host, didn’t work, so I installed WordPress with Fantastico. Worked fine, but then I needed to restore my database into it.

    I tried to restore my backup database into the new database according to these Codex instructions.

    When that failed no matter which compression format file I used (got the “SQL Syntax Error”), I turned to Podz’s instructions here. I dumped the tables and then tried to restore, but got the dang error again:

    =======
    MySQL said:
    #1064 – You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near ‘cat_ID bigint(20) NOT NULL auto_increment,
          cat_name` varcha
    =========

    I found this thread, but the error message appears to be a little different from mine.

    So here I sit with dumped tables, hoping somebody can set me straight. Please tell me what I need to do. And don’t use big words.

    Thanks!

Viewing 13 replies - 1 through 13 (of 13 total)
  • Mark (podz)

    (@podz)

    19 years ago

    Have you tried restoring one table at a time ?
    This will help isolate the actual issue.

    Open the .sql file in notepad (or similar)
    Ignore the commented stuff at the top.
    Scroll down and you will see a set of instructions which starts “create table”. Copy JUST that section of instructions, and paste it into the SQL tab of the phpmyadmin screen.
    Do you get an error ?

    Thread Starter rosiembanks

    (@rosiembanks)

    19 years ago

    Thank you, god, I mean Podz, for helping me out here.

    I did as you said and got the message that the query was executed successfully. I now have a table called wp_categories.

    Mark (podz)

    (@podz)

    19 years ago

    Okay ..

    This is tedious but it works:
    Paste the next section in. This will insert the data into the created table. Then repeat – chunk by chunk.

    You MUST create the table before inserting data (obviously) and in cases such as this I literally go from start to end in strict order.

    Before you start though, COPY the .sql file so that you are working from a backup.

    If it keeps throwing errors, email me the .sql file and I’ll see what I can do. It can be a number of little errors that cause this, and each has a different way of working round it – which is why I can’t write it down in any definitive manner.

    Thread Starter rosiembanks

    (@rosiembanks)

    19 years ago

    All right. Tedious I can handle. Tedious is easy.

    But then after I create all the tables, I do the same thing with the INSERT stuff? Some of these INSERT sections are awfully big. I can understand the function of the tables, but what are the INSERT thingies?

    Thread Starter rosiembanks

    (@rosiembanks)

    19 years ago

    Oh, I see. they are my actual data. Duh!

    Okay, so I do the same thing with the INSERT stuff after I create the tables.

    Mark (podz)

    (@podz)

    19 years ago

    Yup:

    Create table
    Insert data

    Yes, they are huge, but if any of them are to do with blacklists / stats then don’t bother – it’s easier to reinstall the plugins.

    (I restored a 59meg db with a combination of methods, this being one, so it’s very doable 🙂 )

    Thread Starter rosiembanks

    (@rosiembanks)

    19 years ago

    Okay. Hang in there with me, please, because I’m still teetering here.

    I inserted the INSERTS, just two of them: the posts and the wp_users. Then I checked my blog site and got an error.

    It starts with: “Warning: Invalid argument supplied for foreach() in /home/bonnie/public_html/more/wp-includes/functions.php on line 366”.

    And goes on for miles, saying the same thing over and over.

    Did I insert the INSERTS incorrectly? Or do I need to continue and insert it all before I look at the blog site? I inserted them at the top, I think, of my tables. I didn’t click on the tables and try inserting them there.

    Thread Starter rosiembanks

    (@rosiembanks)

    19 years ago

    I just got this informative message from my host in response to my asking why I wasn’t able to restore the database file:

    ==========
    It seems to be due to mysql and php version conflict with versions at your old host. The version of php running on server is PHP 4.3.10 and that of mysql is Mysql 4.0.22-standard.

    If you have any more problems or questions please let us know.
    =========

    Lord knows I have no idea what they’re talking about. Perhaps this means my backup database file isn’t correctly formatted?

    Mark (podz)

    (@podz)

    19 years ago

    The host ? .. sounds odd. Like they don;t know what they are on about.

    The problem ? Until all the data is there, you will get errors I’m afraid.

    Thread Starter rosiembanks

    (@rosiembanks)

    19 years ago

    And lo, she did what Podz said, and it was good.

    Whew. Thank you so much for walking me through this.

    Now I’m off for another swipe of anti-perspirant. What a workout!

    Mark (podz)

    (@podz)

    19 years ago

    Excellent !!

    girlboheme

    (@girlboheme)

    18 years, 7 months ago

    Oh My freaking goodness. I had the same problem and this totally worked like a total champ! podz and Rosie. I bow down to you. Heavens. I was quite nervous. Yay! It’s working!

    pianodude363

    (@pianodude363)

    18 years, 5 months ago

    I dont know anything about mysql…so how exactly would I do this…and how long will it take…should I just start fresh…my site has been up for 5 months and has about 150 semi meaningless posts…?

Viewing 13 replies - 1 through 13 (of 13 total)
  • The topic ‘Restoring Database: SQL Syntax Error’ is closed to new replies.