Any Wordpress gurus out there? I need SERIOUS help.

[Dave]

Okay, someone please explain this one to me, because I'm completely lost here.

I've been putting together my new Wordpress-based site on my own computer using xampp as a localhost server. My database is on my laptop, and I'm trying to export it in phpMyAdmin. I'm following the instructions on backing up a database here:

http://codex.wordpress.org/Backing_Up_Your_Database

When I attempt to import the resulting database to my web host, though, I'm getting an error that makes absolutely no sense to me:

Error

SQL query:
CREATE TABLE `wp_categories` ( `cat_ID` bigint( 20 ) NOT NULL AUTO_INCREMENT ,
`cat_name` varchar( 55 ) COLLATE latin1_general_ci NOT NULL default '',
`category_nicename` varchar( 200 ) COLLATE latin1_general_ci NOT NULL default '',
`category_description` longtext COLLATE latin1_general_ci NOT NULL ,
`category_parent` bigint( 20 ) NOT NULL default '0',
`category_count` bigint( 20 ) NOT NULL default '0',
`link_count` bigint( 20 ) NOT NULL default '0',
`posts_private` tinyint( 1 ) NOT NULL default '0',
`links_private` tinyint( 1 ) NOT NULL default '0',
PRIMARY KEY ( `cat_ID` ) ,
KEY `category_nicename` ( `category_nicename` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 COLLATE = latin1_general_ci AUTO_INCREMENT =6;


MySQL said: https://login.halley.lunarpages.com/3rdparty/phpMyAdmin/themes/original/img/b_help.png (http://dev.mysql.com/doc/mysql/en/error-messages-server.html)
#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 'collate latin1_general_ci NOT NULL default '',
`category_nice

Right. Can anyone at all out there tell me what I'm doing wrong?

Thanks in advance.

-Dave

[SFEley]

Hi Dave,

It looks to me like some flavor of configuration incompatibility between the MySQL server you exported from, and the server you're importing to. My wild bet would be that you're on different versions and that the collation syntax has changed.

The first thing I'd suggest would be to make sure the MySQL server version you're importing to is the same as or newer than the one you're exporting from. I wouldn't be too surprised if XAMPP was using an obsolete version of MySQL. (I'm not a fan of XAMPP anyway -- I've had all kinds of problems with it on other people's machines, because it puts things in bizarre places and many of its configurations are wrong.) You're probably best off installing MySQL direct from their Web site (http://www.mysql.org/), and the same for Apache, etc.

If that doesn't resolve the problem, check to make sure your target MySQL server supports MyISAM. (It really should, that's virtually the default nowadays.) Then make sure your characters sets are the same. Though again, I don't see how that could be a problem if the MySQL version is right.

Finally, you could open up a good text editor and strike all the extraneous stuff with a search-and-replace -- in particular, that COLLATE latin1_general_ci clause that isn't really important to you anyway. (All that does is specify what character set should be used for that column when determining alphabetical order. You're never gonna change it, so...) If that doesn't do it, strike the backticks (` marks) -- Wordpress doesn't use multi-word column names, so they're unnecessary. It's possible that your transfer from one machine to the other may have done some weird transliteration on those.

And so forth. But really, I have a hunch that checking the versions will solve your problems. Good luck.

[theandyman]

Okay, someone please explain this one to me, because I'm completely lost here.

I've been putting together my new Wordpress-based site on my own computer using xampp as a localhost server. My database is on my laptop, and I'm trying to export it in phpMyAdmin. I'm following the instructions on backing up a database here:

http://codex.wordpress.org/Backing_Up_Your_Database

When I attempt to import the resulting database to my web host, though, I'm getting an error that makes absolutely no sense to me:

Error

SQL query:
CREATE TABLE `wp_categories` ( `cat_ID` bigint( 20 ) NOT NULL AUTO_INCREMENT ,
`cat_name` varchar( 55 ) COLLATE latin1_general_ci NOT NULL default '',
`category_nicename` varchar( 200 ) COLLATE latin1_general_ci NOT NULL default '',
`category_description` longtext COLLATE latin1_general_ci NOT NULL ,
`category_parent` bigint( 20 ) NOT NULL default '0',
`category_count` bigint( 20 ) NOT NULL default '0',
`link_count` bigint( 20 ) NOT NULL default '0',
`posts_private` tinyint( 1 ) NOT NULL default '0',
`links_private` tinyint( 1 ) NOT NULL default '0',
PRIMARY KEY ( `cat_ID` ) ,
KEY `category_nicename` ( `category_nicename` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 COLLATE = latin1_general_ci AUTO_INCREMENT =6;


MySQL said: https://login.halley.lunarpages.com/3rdparty/phpMyAdmin/themes/original/img/b_help.png (http://dev.mysql.com/doc/mysql/en/error-messages-server.html)
#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 'collate latin1_general_ci NOT NULL default '',
`category_nice

Right. Can anyone at all out there tell me what I'm doing wrong?

Thanks in advance.

-Dave

Hi Dave,

It looks like the 2 MySql's are not the same version...not even the same planet... I don't have the latest version of MySql running but I think all the character set stuff is barfing on your target computer...

Try the following mysql on the target and see if this gets the table created.

Hopefully once the table is there, the data will import A-OK!

good Luck

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 bigint( 20 ) NOT NULL default 0,
category_count bigint( 20 ) NOT NULL default 0,
link_count bigint( 20 ) NOT NULL default 0,
posts_private tinyint( 1 ) NOT NULL default 0,
links_private tinyint( 1 ) NOT NULL default 0,
PRIMARY KEY ( cat_ID ),
KEY category_nicename ( category_nicename )
) TYPE = MyISAM ;

Enjoy!

[Dave]

It looks to me like some flavor of configuration incompatibility between the MySQL server you exported from, and the server you're importing to. My wild bet would be that you're on different versions and that the collation syntax has changed.

The first thing I'd suggest would be to make sure the MySQL server version you're importing to is the same as or newer than the one you're exporting from.

This may be the issue, actually. XAMPP is currently running MySQL 5.0.27, but my web host uses MySQL 4.1.10. Are there any special instructions out there for exporting a SQL 5 database into a format that's compatible with SQL 4.1?

-Dave