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.

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=utf8 [mysql] default-character-set=utf8 [mysqld] default-character-set = utf8 collation-server = utf8_unicode_ci init-connect='SET NAMES utf8' character-set-server = utf8

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

show variables like 'char%';

The result you want is this:

+--------------------------+----------------------------+ | 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/ | +--------------------------+----------------------------+

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 utf8 DEFAULT COLLATE utf8_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 = 'latin1';

Migrate column after column:

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

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:

Categories : General rambling

Add a comment

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

Categories : JavaScript

Add a comment

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: http://pieroxy.net/blog/pages/lz-string/index.html And a demo can be found below for live compression: http://pieroxy.net/blog/pages/lz-string/demo.html

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

Categories : News, JavaScript

Responses[3]

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

Tomcat, Java and https

So I recently acquired a free https certificate from StartSSL. All well and good, this was fairly easy. Now, I have 4 files at my disposal:

ca.pem pk.mydomain.com ssl.crt sub.class1.server.ca.pem

At that stage I'm fine. I'm about to configure https and will go on with my life. As it turned out, things aren't exactly going to turn out that way.

Tomcat needs a keystore. None of those files will make the trick of course, but none of the stupid attempts I was about to try made it any more. It's not about just throwing the three certs in a keystore. Oh no, it's a bit more than that.

As usual, Stackoverflow bootstrapped me in the right direction.

openssl rsa -in pk.mydomain.com -out out/ssl.key cat sub.class1.server.ca.pem ca.pem > out/intcacerts.pem openssl pkcs12 -export -in ssl.crt -inkey out/ssl.key -certfile out/intcacerts.pem -name "mydomain" -out out/keyandcerts.p12

And that's it. Now, in the server.xml you would find the following:

... keystoreFile="/path/to/my/keystore/file/keyandcerts.p12" keystoreType="PKCS12" ...

And that's all. If you have several domain names running on your Tomcat instance, you need a certificate that will accommodate them all, because to this day, Tomcat cannot use a different certificate for different hosts.

Tags : https, java, ssl, tomcat

Categories : Java

Add a comment

Posted by pieroxy on April 16, 2013 12:37:00 PM PDT #

Ruzzle - neat cheat

Ruzzle is a game on iPhone and Android involving small puzzles (4x4 grid of letters) and the ability of the player to find words in the grid. Of course, as soon as I saw this, I wrote a small program to cheat at it.

Here is the cheat program.

Of course I don't recommend to actually cheat at Ruzzle. But it can help you train.

Tags : cheat, news, ruzzle, solver

Categories : General rambling, News

Add a comment

Posted by pieroxy on April 8, 2013 2:56:00 PM PDT #

HTML5 - Also offline

Apps aren't the only things you can run whenever you are offline. Websites can too be run offline, but only if they were meant to.This nice little page, while not being exhaustive by any measure, shows you how to make that work: http://www.html5rocks.com/en/mobile/workingoffthegrid/

Tags : html, offline, web

Categories : html

Add a comment

Posted by pieroxy on April 4, 2013 4:08:00 AM PDT #