Problem migrating a MySQL 4.0 database to HostGator

[Arkain]

I currently have a forum running on an outdated MySQL 4.0 database with GoDaddy, and migration to HostGator is difficult since that database contain many non-english characters (utf-8) that would become mumble-jumbo when brought over into HostGator's newer version of MySQL server (I'm guessing 4.1+ or 5.0)

Surely you have many customers who ran into this problem before, so can you safely move that MySQL 4 database to run with the newer MySQL server on HostGator without corrupting the non-english posts inside?

I found some info on the following website, but it seems like HostGator techs can take care of that issue much faster with administrative access.

The MySQL version 4.1 introduced some changes to character set handling, which are often giving us trouble if moving a Drupal site from one to the other (i.e. from 4.0 or below, to 4.1 or above). Basically, Drupal always uses utf-8 encoding, but MySQL 4.0 and lower doesn't support it, so utf-8 data are treated as default Latin 1 inside the MySQL 4.0 engine. It doesn't matter there, as MySQL output echoes the same data as sent in still, so Drupal works fine, but it matters a lot in the migration process, where MySQL might attempt to "convert" the data from Latin 1 to utf-8, not knowing that we have utf-8 in there already, and so the site gets broken then.


There are a few typical symptoms, and typical scenarios, (all discussed later in more detail):


- "Garbled text": Any special characters (non-english, advanced punctuation and the like) in your site's contents turned into seemingly random pairs of weird characters after moving the site to 4.1. (Typically, "ÅÃÄ" are frequently seen, always followed by some other non-english character).


- "Question marks": Your contents is more or less fine, but some special characters (non-english, advanced punctuation and the like) are replaced by question-marks after the move to 4.1.

-----


First, export your database (from the newly upgraded 4.1 server) in backwards-compatible mode: mysqldump --user=username --password=password --default-character-set=latin1 --compatible=mysql40 dbname > dump.sql

Then, empty your database, by removing all the tables, and change it's default encoding/collation to "utf8_general_ci": mysql --user=username --password=password --execute="DROP TABLE table_name, table_name...... ; ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;" Since specifying all the tables may be annoying, better create a brand new database instead (if you've permissions): mysql --user=username --password=password --execute="DROP DATABASE dbname; CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;" Both these ways should result in an empty database with default encoding/collation "utf8_general_ci".

Finally, import the file back into the database: mysql --user=username --password=password --default-character-set=utf8 dbname < dump.sql


Now, your database should be perfectly OK, "utf8_general_ci" set on all tables. You're done.



http://drupal.org/node/198184

[striddy]

Try using live chat as they can advise you the best course of action, which will likely be a support ticket.

[Goddess Dix]

i'd suggest opening a support ticket for assistance as well. i think that would probably be your quickest resolution.

[Arkain]

Nevermind, I managed to complete the transition after serveral hours of tinkering.