Mysql import/export problems

[barrysfarm]

Alright,

We're moving some files to a new web host, and I'm running into some trouble. In the old host in phpMyadmin I can export the database scructure as a file, but when I import it to the new one via phpMyadmin, it gives me 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 'access_id` int(11) NOT NULL auto_increment,
`access_cd` varchar(16) NOT NULL d' at line 1

I think it has something to do with different versions of mysql or different veresion of phpMyadmin. We're moving from mysql MySQL 3.23.58 / phpMyAdmin 2.6.0-pl3 to MySQL 5.0.24a / phpMyAdmin - 2.8.2.4

any ideas?

 

[4np]

hmm... perhaps the SQL syntaxis has changed a little bit between these versions. I'm not sure though since I mainly use MSSQL at work (and MySQL latest at home).

Have you tried the command line import / export utilities? Or don't you have shell access?

 

[barrysfarm]

i have ssh access to the new host, but not the old one. Do i need access to the old one if i have the exported file already? I've never used ssh.

 

[4np]

Probably not... you can try to ssh to the new server and import the database export you created with phpmyadmin using the command line utilities:

mysql -u username -ppassword database_name < FILE.sql

 

[barrysfarm]

Alright, I got to this part (with the correct info):
mysql -u username -ppassword database_name < FILE.sql

and then it tells me all of the options i have with the mysql command. It doesn't seem to process any info or anything, and the database is the same as it was. I did upload the file to the ftp root.

Any ideas?

 

[4np]

what is the actual create table statement which fails?

 

[barrysfarm]

Sorry about the delay in response.

Here's all it get's through:

CREATE TABLE `access` (
`access_id` int( 11 ) NOT NULL AUTO_INCREMENT ,
`access_cd` varchar( 16 ) NOT NULL default '',
`access_title` varchar( 64 ) NOT NULL default '',
`access_description` varchar( 255 ) NOT NULL default '',
`ins_user` varchar( 24 ) default NULL ,
`ins_datetime` datetime default NULL ,
`upd_user` varchar( 24 ) default NULL ,
`upd_datetime` datetime default NULL ,
PRIMARY KEY ( `access_id` ) ,
KEY `access_cd` ( `access_cd` )
) TYPE = MYISAM AUTO_INCREMENT =27;


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 'access_id` int(11) NOT NULL auto_increment,
`access_cd` varchar(16) NOT NULL d' at line 1

 

[barrysfarm]

Alright, I figured it out. In the sql file that was exported, it put in a bunch of comments saying what the table name was and the new version of mysql didn't like it. Once I removed them, it all worked.

Thanks!