Database migration failing.
Posted by: Martin Haworth
Date: June 02, 2011 04:28AM

Apologies for the length of this post, but I suspect I don’t quite know of there is a problem here.

I’m VERY new to mysql, so forgive me if the question seems trivial.

I’m taking over a project which is to migrate a bunch of mysql databases from one server to another. The project has already started, and it looks like the process is also upgrading the version, going from “Source distribution 5.1.25-rc “ to “MySQL Community Server (GPL) 5.5.8”.

The method that was going to be used to get the databases across was by using mysqldump. This appears to work apart from one. During the import of one database, there is an error message of “ERROR 1253 (42000) at line 2055: COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'”.

Looking at the results it looks like the target database has a lot less tables in it than the source database.

So I did a bit of poking around in the source database. It looks like there are three tables that are using this “'utf8_general_ci'” clause, and each of those tables has a view hanging off it.

I’m suspecting that it is trying to import the tables in alphabetic order, as from the target database, it looks like as soon as it hit’s the first table that uses 'utf8_general_ci', I think it aborts the process, as no further tables appear alphabetically in the target database after that point.

So, to try and prove a point that it was those three tables that were causing the issue, I edited the sql file that came out of the mysqldump process, and removed the three offending tables from the import. Performed the import process again and the target database now has six objects fewer than the source, which is what I expected, as there were three suspect tables, and each had a view hanging off it.

So……..back to the source database, and if I try to do a describe of the table structure or “select count(*)” from any of the three tables, I get the same error as above.

I don’t think there’s anything worthwhile in these tables, as I would have thought if I can’t select from them like that, they are useless. Or have I misread what has happened?

My theory would be to drop the three offending tables and views, and I’m almost certain that I’ll get a clean migration. My problem is that I have no backout of this.

The author of the application concerned, no longer works for us, so I cannot refer to them, and I can find no system documentation. Not good, and something I will get someone to address in the future.

So in short, am I right in my assumption that these three tables are garbage and unusuable, or have I misread the situation?

Many thanks,


Options: ReplyQuote

Written By
Database migration failing.
June 02, 2011 04:28AM
June 06, 2011 10:06PM
June 07, 2011 02:49PM
June 07, 2011 08:27PM
June 08, 2011 10:44AM
June 08, 2011 09:00PM
June 09, 2011 09:59AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.