Forum Moderators: open
Moving to MYSQL 4.0.25 (yes, *backwards* in version terms) and likely charset will therefore be LATIN1.
Anyone done this before? I've read through a variety of forums, and upgrade techniques, but essentially I am going backwards in version terms. When I do dummy runs of the migration, export/import using mysqldump/mysql command lines respectively, certain characters are not being inserted consistently.
Just need some pointers really for how to approach this.
I appreciate this is an unusual migration.
In the end I...
1) ... created a copy of the structure of my MYSQL 4.1 database, using phpmyadmin.
2) altered my cloned MYSQL 4.1 database tables to latin1 from utf-8...
ALTER TABLE mytable CONVERT TO CHARACTER SET latin1 COLLATE latin1_general_ci;
3) copied over the data from my existing MYSQL 4.1 database to my cloned MYSQL 4.1 database, using phpmyadmin (which does a INSERT ... SELECT)
4) used mysqldump to dump the data from the cloned MYSQL 4.1 database. Now here's the key point, which took a lot of digging. Regardless of the definitions/structure of your database, mysqldump always creates its dump file as utf-8 by default. So whilst the CREATE TABLE shows latin1 the file of INSERTS is actually utf-8. So you have to override this functionality. Here's what I went with, some of it may be supurfluous...
mysqldump -u[user] -p[password] [database] --skip-opt --create-options --default-character-set=latin1 --compatible=mysql40 > [output file]
5) using a normal mysql command line to load the resultant file's contents into the MYSQL 4.0 database.
Hope this is useful, I saw this problem reported literally hundreds of times and never a solution told, so if this helps just one more person from pulling their hair out...
