↓ Archives ↓

Are you stuck in Debian/MySQL/Charset/Collation hell?

So while Debian still hasn’t changed the MySQL default caracter set and collation to utf8, we all know that the first thing to do on a vanilla Debian MySQL installation is to add the following utf8.cnf file to /etc/mysql/conf.d/:

[mysqld]
default-character-set=utf8
default-collation=utf8_unicode_ci

However, if for some reason you didn’t do that and have used software which hasn’t been consistently explicit about character sets and collations, you end up with a nice mess of character sets and collations.

There is a great post on serverfault which helps you out. It comes down to one command which will take some time based on the size of your database:

mysql -B -N --user=user --password=secret -e "SELECT DISTINCT \
CONCAT( 'ALTER TABLE \`', TABLE_SCHEMA, '\`.\`', TABLE_NAME, '\` CONVERT \
TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;' ) FROM \
information_schema.COLUMNS WHERE TABLE_SCHEMA != 'information_schema';" \
| mysql --user=user --password=secret

Update:

And of course you need to alter the defaults for existing databases as well:

mysql -B -N --user=user --password=secret -e "SELECT DISTINCT \
CONCAT( 'ALTER SCHEMA \`', SCHEMA_NAME, '\` CHARACTER SET utf8 COLLATE \
utf8_unicode_ci;' ) FROM information_schema.SCHEMATA where SCHEMA_NAME \
!= 'information_schema';" | mysql --user=user --password=secret

2 Comments

  • Apr 13th 201110:04
    by Christoph

    There is a typo in the second code, second line. It should be “ALTER SHEMA” instead of “ALER SHEMA”. But thanks for sharing anyway!

  • May 28th 201119:05
    by Jan Schulz-Hofen

    Thanks Christoph, that’s fixed now.

  • Leave a Reply