Hi All,
I'm trying to move my MySQL database on another server; however, I'm encountering some errors. Please note that I tried this exact code on another server, which copied the database correctly. I suspect this server has an older version of MySQL?


Here is the error:

Old server mysql version: 2.6.4-pl2
New server mysql version:


CREATE TABLE `dean_cats_pri` ( `id` int( 10 ) unsigned NOT NULL AUTO_INCREMENT ,
`name` varchar( 255 ) NOT NULL default '',
`keywords` varchar( 255 ) NOT NULL default '',
`description` text NOT NULL ,
`rating` float NOT NULL default '0',
PRIMARY KEY ( `id` ) ,
KEY `name` ( `name` ) ,
KEY `rating` ( `rating` )
) ENGINE = MYISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT =753


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 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=753' at line 28

2.6.4-pl2 is not a version of MySQL. Are you sure you are checking the right thing? Character set support was improved in 4.1, so if you are using something below that, you might have problems using that statement.

However, since the default character set is latin1 (iirc), you can safely omit that table option.

This kind of error commonly occurs when copying a table from MySQL 4.1 to 4.0.

2.6.4-pl2 sounds like a version of phpMyAdmin. That version has an option on its export page called "SQL export compatibility". It defaults to "NONE" but you will probably need to change it to "MYSQL 40" (if the MySQL version on the destination server is 4.0.x) or "MYSQL 323" (if 3.23).

FYI, phpMyAdmin lists the MySQL version on the first page after login. It usually looks something like this (green text is the MySQL version number):

Welcome to phpMyAdmin 2.6.4-pl2

MySQL 4.1.14-standard running on localhost as some_user@localhost