Syntax error when restoring database

  • adeco2

    (@adeco2)


    20 years, 9 months ago

    when uploading the file that contains the backup from my old wordpress database (changing host), I get this message. I’ve got no clue what to do about it:

    SQL-query:

    CREATE TABLE wp_categories (

    cat_ID bigint( 20 ) NOT NULL AUTO_INCREMENT ,
    cat_name varchar( 55 ) NOT NULL default ”,
    category_nicename varchar( 200 ) NOT NULL default ”,
    category_description longtext NOT NULL ,
    category_parent int( 4 ) NOT NULL default ‘0’,
    PRIMARY KEY ( cat_ID ) ,
    KEY category_nicename ( category_nicename )
    ) ENGINE = MYISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =4
    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 ‘DEFAULT CHARSET=latin1 AUTO_INCREMENT=4’ at line 9

Viewing 9 replies - 1 through 9 (of 9 total)
  • MichaelH

    (@michaelh)

    20 years, 9 months ago

    If you’ve got access to your old WordPress blog, install the Database Manager from Lester ‘Gamerz’ Chan do a backup, install Database Manager on your new WordPress blog and restore.

    Assume old and new version of WordPress are the same???

    Kafkaesqui

    (@kafkaesqui)

    20 years, 9 months ago

    Try removing ” DEFAULT CHARSET=latin1 ” from the statement in your import file. I’ve known cases where that was an issue with certain MySQL setups. It’s also not necessary.

    Thread Starter adeco2

    (@adeco2)

    20 years, 9 months ago

    I removed that line and the result is that international characters are messed up. Alternative solution?

    Kafkaesqui

    (@kafkaesqui)

    20 years, 9 months ago

    Then try

    DEFAULT CHARACTER SET latin1

    in its place.

    I’m fairly certain DEFAULT CHARSET is a synonym for this, but as I mentioned I’ve seen problems using it. If this doesn’t work, what version of MySQL are you running, and what is the character set you’re using on the db (as in, is it Latin1 globally, or something else)?

    Thread Starter adeco2

    (@adeco2)

    20 years, 9 months ago

    A fairly knowledgeable person did that for me, and hopefully will explain to me how it was fixed. Except for a couple of outstanding issues.

    MySQL version is 4.1.11 in the old server and 4.0.24 in the new one.

    character set in the original was latin1_swedish_ci.
    the new one apparently uses utf-8, MySQL 4.0.24 doesn’t seem to have the functionality to edit charsets.

    Mark (podz)

    (@podz)

    20 years, 9 months ago

    I fixed it by doing the following:

    – using phpMyAdmin on Xampp, create a new database
    – I selected the charset utf8_general_ci
    – Then I did what is below and it was successful

    – Open the .sql file in Notepad++
    – Change the encoding to utf-8
    – Save the file
    – Using the SQL query tab, paste each ‘create table’ then the table contents into the query area, clicking Go after each one.
    – I ignored the comments at the top of the file.
    – 3.6meg file didn’t take long.

    That allowed me to import your database.

    Thread Starter adeco2

    (@adeco2)

    20 years, 9 months ago

    Podz,

    I used phpMyAdmin on Mozilla firefox (web version provided by host).

    I did not try to paste each table separately. I uploaded the file instead.

    Do you think that any of the above was the cause of the problem?

    By the way, using Firefox the character “à” shows as “?”. Not with explorer. Can anyone say what’s the reason for that?

    Mark (podz)

    (@podz)

    20 years, 9 months ago

    I tried multiple times to import the file as a whole and every time it gave an error. I could not find the error – and I did look and try various things.

    I had success when I imported using copy/paste with xampp, and I used the same method to put your database where it is now.

    I think the error was caused by the export. Your phpmyadmin (you sent me a screenshot) had an export option which I have not seen before and if I had been able to access that, I would have exported a copy with each option. I suspect selecting ‘None’ was the correct option.

    I don’t know what else to do now.

    As for Firefox, check the encoding it is using.

    Thread Starter adeco2

    (@adeco2)

    20 years, 9 months ago

    Firefox used ISO-8xxx-1
    Changed to utf-8, and the “à” still reads “‘?”, although this does not happen with new posts or comments. Only with old ones.

    Could that be caused by some minor difference between two character sets?

Viewing 9 replies - 1 through 9 (of 9 total)

The topic ‘Syntax error when restoring database’ is closed to new replies.

Tags