Can’t restore mySQL backup file.

  • I’ve tried the plugin available for 1.5, as well as phpMyAdmin to get this working, and I get the same error everytime. I follow the instructions on the wiki, and do the dump from my old database to move it to the new database (I’m switching hosts very soon), and the new host won’t take the database information from the SQL statement of the old dbase.

    I get this in response:

    the comment lines for ID

    CREATE TABLE blacklist (
    id int( 11 ) NOT NULL AUTO_INCREMENT ,
    regex varchar( 200 ) NOT NULL default '',
    regex_type enum( 'ip', 'url', 'rbl', 'option', 'auto-url', 'auto-ip', 'regex-url', 'regex-ip' ) NOT NULL default 'url',
    PRIMARY KEY ( id ) ,
    KEY id ( id ) ,
    FULLTEXT KEY regex ( regex )
    ) TYPE = MYISAM AUTO_INCREMENT =3279

    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 'id int(11) NOT NULL auto_increment,
    ????    regex` varchar(200) NOT

    I don’t fully understand what’s going on here, as I am *not* a mySQL guru by any stretch of the imagination. The new host is using 4.0.23, and the old host is using 4.0.18. I can’t imagine there would be a serious syntax change from one version to the other.

    Does anyone know if there’s something specific I’m doing wrong here? I would really appreciate the help, since I’d like to get moved to the new host ASAP in order to get rolling and stop getting billed for my old hosting.

Viewing 4 replies - 1 through 4 (of 4 total)
  • Thread Starter Ryan Markel

    (@markel)

    It took all the backquotes out of the code – doh!

    I can always send you the text cut somehow else if it would help some generous stranger to figure this out for me…

    Unfortunately, I am having a similar problem. I am moving hosts and so I backed up my SQL database to a ‘.sql’ file and then tried to import them again on MyPhpAdmin on my new host. I have followed all Wiki/Codex instructions to the letter and have repeated the process about four times. Always I get the mySQL error:

    #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=23" at line 28

    Now, I am not a mySQL guru either, but I can’t see for the life of me what in line 28 is causing a problem. For info, the first 30 lines of my ‘.sql’ file are here.

    Line 28 is

    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;

    Oddly, I can use my new host’s cPanel/Fantastico to install a new copy of WP1.2.2 which works fine: except that it hasn’t got my previous posts. If it is not possible to import these through SQL because of this error, perhaps I could get them in some other way? Would the one-click restore plugin help? Can I import from CSV or XML?

    My new host is running PHP version 4.3.10 / MySQL version 4.0.23-standard / cPanel Build 10.0.0-CURRENT 52 / phpMyAdmin 2.6.1-rc1 .

    Thanks in advance.

    Be sure both mysql server are the same version. I had the same problem and installed the lastest release and the problem was gone ??

    My old host has MySQL 4.1.7 running (compared to the new host’s 4.0.23-standard) so that could be an issue. It’s a damn shame if it means that the export/import is affected though, because aside from imploring my new host to upgrade their mySQL, there is very little that I can do about it. Does anyone know of a work-around?

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Can’t restore mySQL backup file.’ is closed to new replies.