WordPress.org

Support

Support » Miscellaneous » [Resolved] Restoring Database: SQL Syntax Error

[Resolved] Restoring Database: SQL Syntax Error

  • 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)
  • 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 ?

    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.

    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.

    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?

    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.

    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 🙂 )

    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.

    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?

    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.

    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!

    Excellent !!

    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

    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 ‘[Resolved] Restoring Database: SQL Syntax Error’ is closed to new replies.
Skip to toolbar