Support » Fixing WordPress » phpMyAdmin exported database screwie

  • Maybe this is really a phpMyAdmin problem – or a problem with my soon to be former hosting service… but I figured I’d try here.
    I’m moving hosts so today I exported my database using phpMyAdmin’s export tab. I followed the directions from here and all seemed fine.
    I figured I might as well try to import it into my own local machine (PowerBook) through my own installation of phpMyAdmin to make sure it worked.
    So I created a database and clicked on the SQL tab, selected the .sql file and clicked ‘go’ and got this result…
    —-
    ERROR 1064 at line 2: 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 int(4) NOT NULL auto_increment,
    cat_name varchar(55
    ----
    I tried doing it directly through the command line and got the same message.
    From my very limited knowledge of MySQL, it's telling me something is wrong near line 2. Here's what's at the top of the file if it means anything to anyone
    ----
    -- phpMyAdmin SQL Dump
    -- version 2.6.0-pl3
    -- http://www.phpmyadmin.net
    --
    -- Host: localhost
    -- Generation Time: Dec 17, 2004 at 07:44 PM
    -- Server version: 4.0.20
    -- PHP Version: 4.3.10
    --
    -- Database:
    dparmetDB3
    --
    -- --------------------------------------------------------
    --
    -- Table structure for table
    wp_categories
    --
    CREATE TABLE
    wp_categories (
    cat_ID int(4) NOT NULL auto_increment,
    cat_name varchar(55) NOT NULL default '',
    category_nicename varchar(200) NOT NULL default '',
    category_description text NOT NULL,
    category_parent int(4) NOT NULL default '0',
    PRIMARY KEY (
    cat_ID),
    UNIQUE KEY
    cat_name (cat_name),
    KEY
    category_nicename (category_nicename`)
    ) TYPE=MyISAM AUTO_INCREMENT=14 ;
    ——
    does this make sense to anyone?
    thanks
    david parmet

Viewing 9 replies - 1 through 9 (of 9 total)
  • My code looks like this:
    DROP TABLE IF EXISTS wp_categories;
    CREATE TABLE
    wp_categories (
    cat_ID int(4) NOT NULL auto_increment,
    cat_name varchar(55) NOT NULL default '',
    category_nicename varchar(200) NOT NULL default '',
    category_description text NOT NULL,
    category_parent int(4) NOT NULL default '0',
    PRIMARY KEY (
    cat_ID),
    UNIQUE KEY
    cat_name (cat_name),
    KEY
    category_nicename (category_nicename)
    ) TYPE=MyISAM AUTO_INCREMENT=36 ;

    But that is from my complete last backup, and the first of those lines above is actually line 859 of the file.
    I have had problems before when exporting a db that had a lot of stats data in, but getting rid of that sorted out any issues in every case. If you want to send me the sql file, I’ll have a look and see what I can do ? (I wrote the above guide so I feel bad it’s not working for you.)
    tamba2 at gmail dot com

    Moderator James Huff

    (@macmanx)

    Volunteer Moderator

    Since it is important to clear out your stat database from time to time, it may be good practice to empty your stats databases right before backing up.

    Thread Starter dparmet

    (@dparmet)

    Stats database? Where would I find what I have to empty? In the database itself?
    Podz… I think I’ll take you up on that offer….
    david

    Error possibly caused by the database being exported in compressed format.
    Uncompressed backup file, problems gone 🙂

    I just tried this, the database downloads (uncompressed, zipped and gzipped) all produced the errors mentioned when trying to import them through phpmyadmin 2.6 on my ibook. Using the ‘backup database’ option on my hosts cpanel made a file that works though.

    Moderator James Huff

    (@macmanx)

    Volunteer Moderator

    Are you using StatTraq?

    Thread Starter dparmet

    (@dparmet)

    A tip of the hat to Podz for solving the problem — actually the file was unzipped when I tried to import it but the trick was cutting and pasting it into phpMyAdmin – sans the first handfull of comments / lines.
    thanks!
    david

    Just a bump to say that I was having exactly this problem, until I found this post (by doing a Google search on “‘cat_ID int(4) NOT NULL auto_increment,cat_name` varchar(55 “)

    The trick was to cut-and-paste the SQL export (minus the first line, which was trying to create a new database).

    dpartmet – annoying isn’t it? I’ve had to do that as well when brining the sql down from my remote to my local machine. I did find that if I do it in batches, doing some of the tables each time, I get better results. For me the problem was a) statTraq and b) when some of the table get to a certain size.

    Tg

Viewing 9 replies - 1 through 9 (of 9 total)
  • The topic ‘phpMyAdmin exported database screwie’ is closed to new replies.