Change MySQL 5.5 default character-set to UTF8

December 18th, 2013 | 0 Comments | mysql

Last night cPanel upgraded the version of MySQL on one of our servers to the latest MySQL 5.5. Unfortunately this results in over 4000 emails from that server, crying that various websites couldn't connect to the MySQL database. The error found in /var/log/mysqld.log was

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 26214400 bytes!
131218 23:06:21 [ERROR] Plugin 'InnoDB' init function returned error.
131218 23:06:21 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
131218 23:06:21 [ERROR] /usr/sbin/mysqld: unknown variable 'default-character-set=utf8'
131218 23:06:21 [ERROR] Aborting

Interstingly enough, the first error:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes

Was a side effect of the second error:

/usr/sbin/mysqld: unknown variable 'default-character-set=utf8'

Initially we had set the Innodb log file size to 25M in our my.cnf file. When cPanel had issues with the upgrade, at some point our log files reverted to the MySQL default 5M size. A fast fix for both these issues is to mv /etc/my.cnf to /etc/my.cnf.old. Restart with MySQL with "/scripts/restartsrv mysql". This gets us up and running, but doesn't fix any of the actual errors.

Be sure to make backups of all your databases before doing any of these steps.

Fixing the "default-character-set=utf8" error

To fix the default-character-set error, we found that MySQL deprecated this variable in MySQL 5.0.

Doing a quick search in Google found this great post with the proper way to change the default character set in MySQL 5.5. Simply put, edit /etc/my.cnf with your favourite editor:

Remove this line wherever it may appear:

default-character-set=utf8

Add under [mysqld] the following code snippet:

init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

Restart MySQL with /etc/init.d/mysqld restart or on cPanel using /scripts/restartsrv mysql. Now let's verify that our default character set is UTF8:

mysql
show variables like '%character%';
show variables like '%collation%'; 
mysql> show variables like '%character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql> show variables like '%collation%'; 
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

Looks good!

To fix the "/ib_logfile0 is of different size" error, we simply shut down MySQL, move the ib_logfile0 and ib_logfile1 to ib_logfile0.old and ib_logfile1.old, then restart MySQL.

Be sure to make backups of all your databases before doing any of these steps.