Having covered the preparation and character set options of performing a latin1 to utf8 MySQL migration, just how do you perform the migration correctly.
Complete story again at Migrating MySQL latin1 to utf8 – The process .
Migrating MySQL latin1 to utf8 – Character Set Options
Continuing on from preparation in our MySQL latin1 to utf8 migration let us first understand where MySQL uses character sets. MySQL defines the character set at 4 different levels for the structure of data.
Migrating MySQL latin1 to utf8 – Preparation
Before undertaking such migration the first step is a lesson in understanding more about how latin1 and utf8 work and interact in MySQL. latin1 in a common and historical character set used in MySQL. utf8 first available in MySQL Version 4.1 is an encoding supporting multiple bytes and is the system default in MySQL 5.0
Jayson Minard: Yes, I Crashed the Site!
Jayson Minard wrote a very good article on upgrading a production site and what can go wrong and what we can learn from it.
Yesterday, I performed an upgrade to a third-party package used with Zend Developer Zone. It has an automated schema update system which silently performs actions on the database that had a large impact on ZDZ and related sites causing an outage. So, there are good lessons from my post-mortem that I would like to share with the community.
The Start of the Problem
First, let us look at the actual list of actions that started the issue:
1. The upgrade does a schema check on first load
2. The upgrade then corrects the schema to be valid for the new release (performing table changes via DDL)
3. The upgrade then may modify large amounts of existing data, or delete large amounts of old data
These schema and data updates can cause huge potential issues when the database and tables are used concurrently by the online site. First, the DDL changes will lock the affected tables. And for some storage engines (i.e. MyISAM in MySQL) the modifications or deletes will also cause table locks, and in other engines they could cause contention on locked rows, and in other engines cause things like rollback segments to overflow. The online site then waits behind the locks or contention causing threads in the web server to be held until no more threads are left to serve actual user requests. No more threads, no more site.
Learn from the problem; via Yes, I Crashed the Site!.