The Bounty:
Amount: $30 via PayPal, unless other arrangements are made.
Task: A short, numerated, tutorial instructing a user how to migrate an existing Drupal database from a server running MySQL 4.0 (Latin1 charset) to a server running MySQL 4.1 (UTF8).
Due Date: Friday, November 16th 2007 (I may be able to give 24 more hours).
Pretty easy $30--and it would be a great assistance to many Drupal site owners and be a great addition to the Drupal Handbook.
Interested? Here are some more specifics.
I am offering a small bounty ($30) to anyone who will write a tutorial for the Drupal Handbook in regards to the proper way to migrate an existing Drupal website (4.7.x and 5.x) from a server running MySQL 4.0 to a server running MySQL 4.1.
The Drupal CMS has two database types (for 4.7) for MySQL when starting a new site: 4.0 and 4.1. One of the major differences is that MySQL 4.0 uses Latin1 for the character set while MySQL 4.1 uses UTF8.
Dumping a Drupal site from a server running MySQL 4.0 and repopulating the database on a server running MySQL 4.1 results in garbled text and (from what I hear) query issues due to incorrect and conflicting collation table data.
I know this first hand because I am in the position where my hosting provider has migrated to MySQL 4.1. Sadly, I am not the only one with the issue. This is a recurring issue as can be seen by using the Drupal search feature and Google. Frequently, requests for support go one of two routes: either unanswered or get conflicting information that often doesn't appear to resolve the issue.
With more and more hosts moving to MySQL 4.1 & 5.0 this issue is going to become even more common as existing Drupal sites are forced to migrate away from MySQL 4.0 servers. Currently there is no Drupal-endorsed correction or instructions to site maintainers to this issue--so this would be a HUGE help to the hundreds (!!) of site operators who have posted on Drupal.org and have yet to receive any advice about this issue.
I will test the tutorial out myself for "ease of use". I may need the assistance of a Drupal savvy developer to confirm the database is correct. e.g. Some have suggest dumping the database with "add-drop-table --default-character-set=latin1" and repopulating with "--default-character-set=utf8" and this does remove the garbled text issues, but the Collation fields in the database tables are still latin1_swedish_ci (instead of utf8_general_ci, which the default Drupal 4.1 database shows when I browsed it) and I was told this would cause query issues and could cause the site to cease functioning. A running discussion on the issue, with a number of links (including some to threads discussing an [unsupported??] Drupal core module intended to deal with this can be found here:
You can contact me at Joshua at Pluswebdesign dot com if you are interested in this bounty.
Comments
Bounty Increase
I am increasing the bounty to $70.
I know this isn't a lot of money (my wife's site is pretty simple), but the additional documentation to the Drupal Handbook would help a lot of users who have had this problem (including myself).
Some additional notes that may be helpful:
• As mentioned in the Documentation request thread linked above, running the following code removes the garbled text (but doesn't *appear, to my limited knowledge* to resolve the collation field issues):
Step #1: Do a mysqldump on the 4.0 server as follows:
mysqldump -uusername -ppassword --add-drop-table --default-character-set=latin1 database > backup41.sqlStep #2: Populate the empty database on the 4.1 server as follows:
mysql -uusername -ppassword --default-character-set=utf8 database < backup41.sql• Wim Mostry's blog has a discussion and some sample code from Heini in regards to this very issue.
• There appears to be an unsupported (?) module to address this issue. http://aymanh.com/mysql-4-0-to-4-1-encoding-problem-and-solution Do you run this before or after you move to the new server? Does it completely resolve all the table and field issues? (He does not that the Drupal team hasn't offered a definitive answer to this problem at this point).
• Interestingly, while the Drupal Handbook lacks documentation on how to migrate from 4.0 to 4.1/5.0, it does include documentation to degrade from 4.1/5.0 to 4.0: http://drupal.org/node/71516 This may be of some help?
• And more links here: http://drupal.org/node/187689
Google searches and Drupal searches both pull up a lot of posts on this issue, many unaswered.
So the documentation will be very well received.
I wish I knew enough about MySQL myself to confirm if any of these solutions completely rectify the issue. I have tried a few of the other suggestions (which didn't work), and the one mentioned above (about dropping the Latin1 charset from the MySQL commandline) only appears to be a partial solution.
Hence I am asking a Drupal/MySQL expert to confirm the correct way to resolve this, as well as offer step-by-step instructions for us not-so-expert users :)
Thanks for your time.
Ps- uber brownie points for instructions pointing out how to salvage a site that migrated an existing Drupal site from a 4.0 server to a 4.1 server and began adding context :D
Hey Joshua, I'm very willing
Hey Joshua, I'm very willing to aid you, I'll contact you by mail about this.
I will contribute to Bounty for this
I don't know where this ended up, but I have 'stepped in it' myself at this point and would contribute to the bounty if you can include instructions for salvaging a site as per last sentence in above code....
Any insight into this upgrade is appreciated!
Cheers!
Jon Paul
http://www.harostreetmedia.com
Documentation is ready
Hey Joshua,
Here is the documentation: MySQL 4.0 -> 4.1 migration. With this you helped a lot of people :)
It was my pleasure really, I had a really great time working with Jirka and I learned a lot from the experience. There's no need to pay the bounty but you can always make a small donation to the Drupal Association instead:
http://association.drupal.org/donate
Thanks again for all this!
Wim