Mysql Upgrade

[eileen1017]

I am new to mysql upgrade. I have a mysql server running version 3.23 on Red Hat linux machine. And I have a newer mysql server running version 4.0 on SuSe linux machine. My goal is to move all the database on the older version server to the newer version server. What should I do? I used mysqldump on the older version to get the dump file and then scp it to the newer version server and then import them through mysql client. However, whenever a database has a table that is deal with auto_increment in the create table command, the import will fail. It complains that the SQL syntax is wrong. I know that version 4.0 have some index changes. But how is it different with the auto_increment? Please let me know. Thanks.

 

[TonyGroves]

Version 4.0 should accept older syntax no problem. Can you show us a few lines of the text around where the syntax error is, and post the exact error message?

 

[eileen1017]

In the dump file I have a create table sql statement like below:

CREATE TABLE centers_copy12-15 (
id int(11) NOT NULL auto_increment,
region int(11) NOT NULL default '0',
name varchar(75) NOT NULL default '',
phone varchar(20) default NULL,
fax varchar(20) default NULL,
email varchar(75) default NULL,
website varchar(75) default NULL,
street varchar(100) default NULL,
suite varchar(200) default NULL,
city varchar(100) default NULL,
state char(2) default NULL,
zip varchar(15) default NULL,
type tinyint(4) NOT NULL default '0',
modified timestamp(14) NOT NULL,
created timestamp(14) NOT NULL,
notes text,
PRIMARY KEY (id),
KEY state (state,type),
KEY region (region)
) TYPE=MyISAM;

When I try to load it to the newer version server by issuing the following command:
mysql -uroot -p mrenaissance < /usr/tmp/dbmrenaissance.txt

It gives me the following error:
ERROR 1064 at line 11: 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 '-15 (
id int(11) NOT NULL AUTO_INCREMENT,
region int(11) NO

Please help. Thanks.

 

[TonyGroves]

The problem is the hyphen in "centers_copy12-15"; it's an illegal character in user-defined names. If you can't change the table name, then you need to enclose the table name in "backticks" (grave accent characters), and make sure any other uses of that table name are always enclosed in backticks.
[tt]
CREATE TABLE `centers_copy12-15` (
...
[/tt]