Database migration failing.
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,
Martin.