Support » Fixing WordPress » Moving Servers – DB Import/Export Problem

  • Hi. I’m moving from a Windows server to a Linux server and I’m having a problem moving over my blog’s database. I got myphpadmin installed on the Windows box (a battle itself) and exported the database into a .sql file. However, when I try to import this file using myphpadmin on the Linux box I get an 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 CHARACTER SET latin1 COLLATE latin1_swedish_ci’ at line

    There is a similar topic on the forum that suggested I just edit out the offending text, but when I did this a whole other crop of problems came up. Can someone help me? I’ve been working on this all day and am very frustrated.

    Thank you.

Viewing 10 replies - 1 through 10 (of 10 total)
  • I noticed this problem as well and I think this is probably a problem with phpMyAdmin, though I can’t be sure.

    To solve:
    What I did was in phpMyadmin I exported Data and Structure seperately.

    Then in the new host I created the tables one by one from the Structure file. Took time but creating together was causing the problem.
    If you still get errors then remove 'DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci'

    Then once all the tables are made import the Data .sql file

    Okay. I made two different .sql files and edited out everything that related to character sets. It imported the structure one.

    The data one is still being rejected.

    #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 ‘– phpMyAdmin SQL Dump
    — version 2.6.2-pl1

    Which line is it in exactly?

    I notice a '

    Check if that is correct?

    Try doing the data tables one by one.

    That is the first line. I try removing everything before USE alexblog; but it still finds that strange ‘ thing, even if there’s nothing before it.

    The error I get is:

    #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 ‘USE alexblog’ at line 1

    and the first part of the data .sql file is:

    USE alexblog;

    — Dumping data for table wp_categories

    INSERT INTO wp_categories

    and so on.

    As far as I can tell that stupid character doesn’t exist anymore, yet it’s still bitching about it.

    I’ll try doing the tables one by one, but if it’s finding an error on the USE alexblog; part, I doubt it will work.

    I can’t really figure out a way to do the tables one by one, the data file is much too big for me to really hand edit.

    Still no blog….

    Is there any other way of exporting the data?

    Mark (podz)


    Support Maven

    If you are not using it, then please download and use the (damn good) Notepad++
    That odd character is produced by the text editor you are currently using.

    How big is the .sql file ? I’ve hand imported a 59meg file before so although it’s very tedious, it does work.

    You don’t really need to handedit the data file.

    Just import the data of each table at a time.

    You can use Notepad++ to simplify your work.

    I’m going to try this notepad++ but I am having some troubles of my own. My blog is still reffering to the old files on the old server after I have followed all the steps. Any ideas of what it is?

    Mark (podz)


    Support Maven

    Yup – change both the values refered to here:

    If they are changed, then did your url change ?

    Hey all,

    I had the same problem. Here’s an easy fix, don’t know if it will work for everyone though.

    Use MySQL Administrator to backup (you can download from Start MySQL Administrator and login to your MySQL database as root. In the left pane click ‘Backup’ then click ‘New Project’ down in the bottom right. Name it whatever you want. Now, click on your wordpress database in the ‘Schemata’ pane then click the right arrow to add it to the backup project.

    Now, HERE’S THE KEY. Click on the ‘Advanced Options’ tab, and select ‘Compatability Mode’ under ‘Output File Options’. Now you can save your backup with ‘Save Project’ in bottom right or just backup right away with ‘Execute Backup Now’ also in bottom right.

    This will create the SQL file, move that to your linux box. Use whatever client you like (I used phpmyadmin) and restore this SQL file.

    Should work.

Viewing 10 replies - 1 through 10 (of 10 total)
  • The topic ‘Moving Servers – DB Import/Export Problem’ is closed to new replies.