WordPress.org

Ready to get started?Download WordPress

Forums

Moving Servers - DB Import/Export Problem (11 posts)

  1. f4shionablecha0s
    Member
    Posted 8 years ago #

    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.
    -Alex

  2. Ajay
    Member
    Posted 8 years ago #

    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

  3. f4shionablecha0s
    Member
    Posted 8 years ago #

    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
    -- http://www.ph

  4. Ajay
    Member
    Posted 8 years ago #

    Which line is it in exactly?

    I notice a '

    Check if that is correct?

    Try doing the data tables one by one.

  5. f4shionablecha0s
    Member
    Posted 8 years ago #

    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.

  6. f4shionablecha0s
    Member
    Posted 8 years ago #

    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?

  7. Mark (podz)
    Support Maven
    Posted 8 years ago #

    If you are not using it, then please download and use the (damn good) Notepad++
    http://notepad-plus.sourceforge.net/uk/site.htm
    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.

  8. Ajay
    Member
    Posted 8 years ago #

    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.

  9. awefasdfa
    Member
    Posted 8 years ago #

    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?

  10. Mark (podz)
    Support Maven
    Posted 8 years ago #

    Yup - change both the values refered to here:
    http://www.tamba2.org.uk/wordpress/site-url/

    If they are changed, then did your url change ?

  11. kurmbox
    Member
    Posted 8 years ago #

    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 http://www.mysql.com/products/administrator/). 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.

Topic Closed

This topic has been closed to new replies.

About this Topic