MySQL charset hell

There are not a lot of paradises on this corner of the earth, but there's one sure hell: charsets with MySQL. MySQL has the peculiar idea of defaulting everything to latin1. Everything.

Everything should be utf-8 of course, just because it fucking works with every character on earth. And no, MySQL's utf8 cannot. Because they rushed it in I suppose. So if you want to be able to use every character on earth, you should use utf8mb4.

Now, let's assume you got the following error message somewhere in some obscure logs:

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

This means one thing: you're in MySQL charset hell. Welcome in. You're deep in. It's hot.

The road to heaven

First, you will need to edit your my.cnf file, to be found on linux in /etc/mysql/ and add/modify the following properties in the proper section:

[client] default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4 [mysqld] default-character-set = utf8mb4 // NOTE: Newer versions of mysql seems to choke on this one. collation-server = utf8mb4_unicode_ci init-connect='SET NAMES utf8mb4' character-set-server = utf8mb4

The goal is to see everything as UTF-8 while running the following:

show variables like 'char%';

The result you want is this:

Similarly for collation:

Now what?

Now, you're still in hell, but you just got the promise of not getting deeper. And you still need to get out, that is to change all your tables to store UTF-8. There you have several options, some consisting in dumping everything, recreating a new DB and reloading everything (see the second link below). I chose the path of the ALTER. Here is what you can do:

Migrate your dbs:

ALTER DATABASE YourDbName DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;

Locate the faulty tables:

SELECT table_schema,table_name, column_name, character_set_name FROM information_schema.`COLUMNS` C where character_set_name is not null;

Migrate table after table:

ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4;

This is the easiest solution by far and it will convert all char based columns. If for any reason you want to convert only a few columns, you will have to alter them by hand:

ALTER TABLE t MODIFY col1 CHAR(50) CHARACTER SET utf8mb4;

Of course, the CHAR(50) piece (and eventual other options such as NOT NULL) depends on the column. Good luck.

And remember: For every MySQL install you do from that point on, do this first. Less trouble for after.

Thanks:

Dos and Don'ts

Never do

CREATE DATABASE new_db;

Always do

CREATE DATABASE new_db DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;

Responses[2]

Posted by pieroxy on May 28, 2013 12:43:00 PM PDT #

lz-string: We'll try to make it faster

I've setup a small page where I share my findings in trying to optimize the implementation of lz-string. This work will continue in the coming weeks. You can leave a comment in this blog entry for any feedback.

Tags : compression, javascript, localstorage, lzw, web

Responses[4]

Posted by pieroxy on May 21, 2013 1:19:00 AM PDT #

Just released: lz-string

lz-string is a compression program for JavaScript in the browser, based on LZW. It is fast, meant for localStorage or any other string-based storage in JavaScript, and is particularly efficient on short strings.

All can be read on the home page: https://pieroxy.net/blog/pages/lz-string/index.html And a demo can be found below for live compression: https://pieroxy.net/blog/pages/lz-string/demo.html

Tags : compression, gzip, javascript, localstorage, lzma, lzw, news, web

Responses[26]

Posted by pieroxy on May 9, 2013 2:27:00 AM PDT #