Mysql Db Import Problems

[LATD Inc]

I'm trying to import a DB that is currently hosted at a company running MySQL 4.1.11. The people that built the CMS that uses this database have told me that my new hosting company (you guys) should have someone on staff that can convert my DB to run on MySQL 4.0.2X.

 

I've been dealing with your support staff for over a day now and no one has been able to help me with this issue. I keep bouncing back and forth between the front line and level 2 techs.

 

This is the last response I got from your staff:

I tried to recreate the database from the dumpfile but since the version that we have is

4.0.23 and you had created it from a higher MySQL version, there are some coflicts. It is showing up errors in syntax on the dumpfile. The current version cannot be used with your dumpfile. Please get back to us with a dumpfile without the field COLLATE latin1_swedish and we will try to install it for you.

Also check out our forums at http://www.totalchoicehosting.com/forums for more help in this regard.

 

I'm not aware HOW to get the file without the COLLATE line. I'm just dumping from my PHPmyAdmin account... using the MYSQL323 compatibility option.

 

If you don't have anyone on staff who can handle this, I need to know so I can find a new web host. This site needs to go live and it can't go live without the backend.

 

Thank you so much for your help.

[TweezerMan]

Welcome to the forums, LATD Inc!

 

I'm not aware HOW to get the file without the COLLATE line.  I'm just dumping from my PHPmyAdmin account... using the MYSQL323 compatibility option.

<{POST_SNAPBACK}>

I didn't know phpMyAdmin had such an option (never seen it on either TCH servers or my own local install of phpMyAdmin). It would appear that the MYSQL323 option did not work, otherwise the COLLATE clauses/fields would not be in the exported dump file.

 

I'm trying to import a DB that is currently hosted at a company running MySQL 4.1.11.  The people that built the CMS that uses this database have told me that my new hosting company (you guys) should have someone on staff that can convert my DB to run on MySQL 4.0.2X.

<{POST_SNAPBACK}>

They probably could if they had access to a MySQL server running 4.1.x. To my knowledge, all TCH servers are running MySQL 4.0.x. According to the MySQL documentation, the only way that data from a MySQL 4.1.x database can be downgraded and imported on a MySQL 4.0.x server is for the data to be exported from the MySQL 4.1.x server with the "--compatible=mysql40" option:

2.11.2. Downgrading to 4.0

The table format in 4.1 changed to include more and new character set information. Because of this, you must use mysqldump to dump any tables you have created with the newer MySQL server. For example, if all the tables in a particular database need to be dumped to be reverted back to MySQL 4.0 format, use this command:

>shell> mysqldump --create-options --compatible=mysql40 db_name > dump_file

If you don't have anyone on staff who can handle this, I need to know so I can find a new web host.  This site needs to go live and it can't go live without the backend.

<{POST_SNAPBACK}>

The data must be exported from the MySQL 4.1.11 server with the "--compatible=mysql40" option so anything relating to COLLATE is removed. Your best bet is to try dumping the file again with the correct conversion options so it can be imported on a MySQL 4.0.x server. If phpMyAdmin can't do it, you may need your host to do it for you.

 

Hope this helps...

[LATD Inc]

The data must be exported from the MySQL 4.1.11 server with the "--compatible=mysql40" option so anything relating to COLLATE is removed.  Your best bet is to try dumping the file again with the correct conversion options so it can be imported on a MySQL 4.0.x server.  If phpMyAdmin can't do it, you may need your host to do it for you.

 

In my PHPmyAdmin area, there is an option for export as MYSQL40 compatable. If I select that option it still has the collate.

 

What am I possibly doing wrong?

[TCH-Bruce]

Welcome to the forums LATD Inc!

[cajunman4life]

Welcome to the forums!

[TCH-Thomas]

Welcome to the forum, LATD Inc.

[TweezerMan]

I don't know that you're doing anything wrong. phpMyAdmin's inclusion of character set and collation data in database exports is advertised as a feature that I don't any way to disable.

 

Since phpMyAdmin doesn't appear to be able to export a MySQL 4.1.x database that can be imported into a MySQL 4.0.x server, I see two possible options:

 

1) Try having your host run the mysqldump command I indicated above manually.

 

2) Take the MySQL export file that you have now and open it in a text editor. Do a Search/Replace for:

>CHARACTER SET latin1 COLLATE latin1_swedish

...and replace all occurences with nothing (delete the text everywhere it occurs).

 

I don't know if the above text is exactly what's in your export file - you'll need to examine the file yourself and search for whatever text is there, similar to what I have above.

 

Save the file, then see if it can be successfully imported.

 

Hope this helps (again!)...