By kzeng on

I've changed my hosting provider recently. I got my MySQL data from my old hosting company. When I attempted to import them into the MYSQL server of my new hosting company, I got a big headache: my old hosting provider is using MYSQL ver 4.1 while my new hosting provider is using MySQL ver 4.0. I cannot directly import the data. I have shell access from my new hosting provider. Do anybody have some ideas about what I should do in order to migrate the data? Thanks a lot! I really need some help. Otherwise my Drupal site is dying. Thanks again!

Comments

ezheidtmann’s picture

ezheidtmann commented

Can you provide the specific error messages for the commands the 4.0 server is choking on?

kzeng’s picture

kzeng commented

Thanks for your reply! I got the following message:

ERROR 1064 at line 20: 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' at line 7

It seems that the error was caused by the syntax:
ENGINE=MyISAM DEFAULT CHARSET=latin1;

Thanks a lot!

--------------------------
http://www.kzeng.info

ezheidtmann’s picture

ezheidtmann commented

Because it's early in the file, I assumed it was a CREATE TABLE query. The documentation for this command's syntax says:

As of MySQL 4.1, character column definitions can include a CHARACTER SET attribute to specify the character set and, optionally, a collation for the column. For details, see Chapter 10, Character Set Support. CHARSET is a synonym for CHARACTER SET.

Look at the mysqldump utility for MySQL 4.1 and see if it will support suppressing the character set in table definitions. If it doesn't, try removing all references to CHARSET within CREATE TABLE queries. If there are more syntax errors after that, apply the same strategy to solve them: Identify the type of query, look up the docs, and see what information there is on what commands were added when. Come back here if you can't solve it yourself.

Good luck,
Evan

kzeng’s picture

kzeng commented

Thanks a lot for your reply. Yes, it is caused by CHARSET. I installed a MySQL 4.1 in my local machine and then import the data and then dumped them out in a MySQL-4.0-compatible format. I know this method is somehow stupid but I solve the problem in this way:-) Thanks again for your kind help!

--------------------------
http://www.kzeng.info