The Ultimate Guide To UTF-8 and MySQL

How character encodings work in MySQL seem to continue baffle people, at least based on the number of questions posted on Stackoverflow. Read on if you have an application that still outputs funny text like “señor” or “se�or” where “señor” is expected.

The truth about text in MySQL

Strings in MySQL consist of an encoding marker and a list of bytes. The CHARSET function reads the encoding while HEX can be used to read bytes:

SELECT CHARSET("€"), HEX("€")
=> latin1, 80

This enables MySQL to use different encodings while working on a query. You can write a query that compares text columns in different encodings and returns the results where the text is the same, rather than the bytes that are stored.

I have everything set to utf8 and still the database gives me garbage!

That’s because of the MySQL client encoding. One query can return text in several different encodings, it’s unreasonable to expect the user software to check the encoding of each individual string. So all text returned by the MySQL client is encoded in only one encoding.

The fast and easy solution to encoding problems: set the MySQL client encoding.

How this is done depends on what you use to connect. If you work in PHP and the old MySQL functions you should use mysql_set_charset("utf8"). If you have an old version try mysql_query("SET NAMES utf8"). If you use MySQLi it would be mysqli_set_charset($link, "utf8") (or $link->set_charset("utf8") if you use the OO API).

What are the encodings in the tables good for, then?

When you create a table you can specify a character encoding for a column. It means how the MySQL server will convert text like “abc123” to bytes that can be written to disk. The encoding affects two things:

  1. the range of characters that can be stored,  and
  2. the number of bytes the text will occupy on disk.
Popular encodings for storage include latin1 and utf8:
  • latin1: the characters that you can store cover most European languages, and the text occupies 1 byte per character.
  • utf8: you can store any(*) Unicode characters, and the text occupies from 1 to 3 bytes per character, depending on the character.

Other encodings exist, of course. For example latin2 has characters used in Eastern European languages. If you use ucs2 you can store any Unicode characters and the text occupies 2 bytes per character. If you are storing a lot of text in Japanese you may want to use ucs2 instead of utf8 because you are saving a third in the storage.

You can also specify a collation like utf8_general_ci for a column. It means how MySQL will sort the data. This affects indices and ORDER BY clauses. Cultures have different rules for alphabetic order: for example in Swedish Ä is the second to last letter of the alphabet, while in English it’s equivalent to A. So with Swedish collation you get a < b < ä and with English collation you get a = ä < b. Use the ordering your users expect. You can read more about collations in my other post.

What if you don’t specify an encoding and collation for the column? Then MySQL will use the default specified for the table, the database, or the server.

Converting encodings

Strings in MySQL consist of an encoding marker and a list of bytes. To convert strings from one encoding to another you can use the CONVERT(… USING …) function.  What CONVERT actually does is change both the marker and the bytes so that the resulting string is the same as the original; not very useful for fixing broken text.

The solution is stripping the encoding marker from the string before passing it to CONVERT. This is done by converting the string to “binary” first:

CONVERT(BINARY broken_text USING utf8)

I’ve written a detailed case study on fixing a particularly complicated MySQL encoding problem in another post.

Practical demonstration

Let’s create a table with columns in different encodings and fill it with some data.

CREATE TABLE test_encoding (
  utf8   varchar(4) CHARACTER SET utf8,
  latin1 varchar(4) CHARACTER SET latin1,
  latin2 varchar(4) CHARACTER SET latin2
);
INSERT INTO test_encoding (utf8,latin1,latin2) values ('A','A','A');
INSERT INTO test_encoding (utf8,latin1,latin2) values ('Ñ','Ñ','Ñ');
INSERT INTO test_encoding (utf8,latin1,latin2) values ('Ŕ','Ŕ','Ŕ');
INSERT INTO test_encoding (utf8,latin1,latin2) values ('☺','☺','☺');
INSERT INTO test_encoding (utf8,latin1,latin2) values ('€','€','€');

When you select the data from this table with select * from test_encoding you get the following results:

+------+--------+--------+
| utf8 | latin1 | latin2 |
+------+--------+--------+
| A    | A      | A      |
| Ñ    | Ñ      | ?      |
| Ŕ    | ?      | Ŕ      |
| ☺    | ?      | ?      |
| €    | €      | ?      |
+------+--------+--------+

Even though each column is encoded differently everything is still readable. This is thanks to the MySQL client encoding. The characters that could not be encoded are replaced with question marks: there is no Ñ in latin2, nor is there a Ŕ in latin1. The smiley face is not present in either.

The last line is interesting: the euro sign can be encoded in latin1 although technically it’s not really a latin1 character. This shows that MySQL’s latin1 is not really the standard ISO-8859-1 encoding, commonly known as “ISO latin 1”. In reality it is Windows-1252, the “Western European” encoding that also has curly quotes and other niceties where ISO-8859-1 has unprintable control characters.

To see that MySQL really is storing the columns in different encodings you could do this:

mysql> select utf8,hex(utf8),hex(latin1) from test_encoding;
+------+-----------+-------------+
| utf8 | hex(utf8) | hex(latin1) |
+------+-----------+-------------+
| A    | 41        | 41          |
| Ñ    | C391      | D1          |
| Ŕ    | C594      | 3F          |
| ☺    | E298BA    | 3F          |
| €    | E282AC    | 80          |
+------+-----------+-------------+

What happens when we compare two columns encoded differently?

mysql> select utf8,latin1 from test_encoding where utf8=latin1;
+------+--------+
| utf8 | latin1 |
+------+--------+
| A    | A      |
| Ñ    | Ñ      |
| €    | €      |
+------+--------+

MySQL knows that the character repertoire of latin1 is a subset of utf8, so it converts the latin1 column into unicode so that it can be compared. If however you try to compare latin1 with latin2 this is what happens:

mysql> select latin1,latin2 from test_encoding where latin1=latin2;
ERROR 1267 (HY000): Illegal mix of collations (latin1_bin,IMPLICIT) and (latin2_bin,IMPLICIT) for operation '='

Since neither latin1 and latin2 is a subset of the other, MySQL cannot compare the columns. To be able to compare them you have to convert at least one to unicode, e.g. by using CONVERT(.. USING ..):

select latin1, latin2 from test_encoding
where convert(latin1 using utf8)=latin2;

I hope this information is enough to solve any character encoding problems you may have with MySQL once and for all.

(*) Did I say utf8 can store any Unicode characters? That’s not really true. MySQL’s utf8 and ucs2 only store unicode from the basic multilingual plane (BMP), which includes “only” the characters U+0000 through U+FFFF. If you have MySQL 5.5 or later you have additional encodings to choose from: utf16, utf32, utf8mb4. These encodings You can read the details, as usual, in the official MySQL documentation.

Grouping data

Suppose you are faced with the task of taking a list of things and grouping them by some attribute, like turning the following list country-city pairs…

input=[("USA", "New York"), ("USA", "San Francisco"), ("USA", "Los Angeles"),
      ("UK", "London"), ("UK", "Manchester"), ("UK", "Edinborough"),
      ("Spain", "Madrid"), ("Spain", "Barcelona"), ("Spain", "Granada"),
      ("Finland", "Helsinki"), ("Finland", "Oulu"), ("Finland", "Kuopio")]

into lists of cities grouped by country like this:

output=[("USA", ["New York", "San Francisco", "Los Angeles"]),
	("UK", ["London", "Manchester", "Edinborough"]),
	("Spain", ["Madrid", "Barcelona", "Granada"]),
	("Finland", ["Helsinki", "Oulu", "Kuopio")]

Also suppose that the application that you are working on is about preparing data for reports and that a lot of the application will be dedicated to creating such groupings. Often the data is accumulated by summing numbers instead of building lists. Sometimes groups have to be divided into subgroups, like regions within countries. You had better have some kind of a generic pattern for this grouping algorithm instead of inventing a new way of doing each time, right?

One the worst ways of doing it I have seen is this:

country = None
isFirst = True
output  = []
cities = None

if len(input) > 0:
    for countryCity in input:
	if country != countryCity[0]:
	    if not isFirst:
		output.append((country, cities));

	    isFirst = False
	    country = countryCity[0]
	    cities = [countryCity[1]]
	else:
	    cities.append(countryCity[1])

    output.append((country, cities))

It gets the job done and didn’t take more than 5 minutes to write, but it’s brittle. The group is closed in two different places. It’s not immediately obvious what to change to group by multiple levels. If the processing logic was any longer doing this is like leaving a time bomb in the code base.

What I have found clearest in these cases is a nested loop like this:

while there is more input:
    open new group
    while there is more input and we're in the group:
	accumulate data for the group
	get next input
    close the group

An example in Python for the problem at hand would be something like this:

output = []
inputIter = iter(input)
countryCity = next(inputIter, None)
while countryCity != None:
    # open new group
    country = countryCity[0]
    cities = []

    while countryCity != None and country == countryCity[0]:
	# accumulate data for group
	cities.append(countryCity[1])
	countryCity = next(inputIter,None)

    # close group
    output.append((country, cities))

Here it’s pretty clear where a group is opened, where the data is accumulated, and where it is closed. Also it’s clear what to do if a second level of grouping is needed: the loop that accumulates data has to have this same structure inside. For example:

while countryCity != None:
    country = countryCity[0]
    regions = []
    # group by country
    while countryCity != None and country == countryCity[0]:
	region = countryCity[1]
	cities = []
	# group by region
	while countryCity != None and region == countryCity[1]:
	    cities.append(countryCity[2])
	    countryCity = next(inputIter,None)
	regions.append((region, cities))
    output.append((country, regions))

Crazy fast AJAX search suggest in CakePHP

The fastest AJAX call is the one that is never made.

Network latency makes using AJAX applications like Google instant search, or even the common autocomplete field, feel like running in treacle*. Having the browser run to the server for more data every time the user pushes a key just doesn’t work well. Many times the data doesn’t even change that often, so one solution is to let the browser cache it. This is how you do it in CakePHP.

One way to implement the search suggest feature is with the Scriptaculous AJAX autocomplete field. While you type it sends frequent requests to the server. In a high-latency setting you may have to wait seconds to obtain the response. Worse still, sometimes the responses arrive out of order.

You get much better responsiveness if you preload the data and use Autocomplete.Local. It works a little differently from AJAX autocomplete in that the data has to be a Javascript array. If you create a controller action /search/suggestData that returns all data as JSON array, you can load the “local” autocomplete data with AJAX. So you could add this to your default layout:

<input id="search" autocomplete="off"/>
<div id="search_suggest" style="display:none;"></div>
<script type="text/javascript">
new Ajax.Request('<?php echo $html->url('/search/suggestData') ?>', {
    method: 'get', // Important! Only GET requests are cached.
    onSuccess: function(response) {
	var suggestData = response.responseJSON;
	new Autocompleter.Local('search_autocomplete', 'search_suggest', suggestData, { });
    }
});
</script>

But that’s not the full story. A search suggest feature should have thousands of suggestions to be useful. Adding all of that for every single page view amounts to significant increase in bandwidth use. Since the data doesn’t change that often, you can make the browser store it in its local cache.

Basically all you need is the Expires HTTP header and make sure you make GET requests. POST requests are not cached. The next time the call is made the browser will serve it from disk and it will be ultra fast. For example, to generate the JSON necessary for the search suggest feature you could use this controller action and view:

// app/controllers/search_controller.php
class SearchController extends AppController {
    function suggestData() {
        header('Expires: '.date('r', strtotime('+1 day')));
        header('Content-type: application/json; charset=utf-8');
        // The top 5000 search words as an array()
        $this->set('data', $this->Search->top5kSearches());
    }
}

// app/views/search/suggestData.ctp
<?php echo json_encode($data) ?>

If the data doesn’t change at all you can set the cache expiry date a lot further into the future. Here it’s set to 1 day.

How many search suggestions should you include, then? As many as possible, but bear in mind that Autocomplete.Local finds its matches by doing a linear search through the data array. With 5000 entries in the array you still get decent performance. It’s not hard to replace the search logic to use e.g. a binary search though.

I have observed this trick to shorten response times from several seconds to about zero for users that have a slow link to the web server. It makes a world of difference for autocomplete fields.

*) Oh, by the way. Apparently it’s easier to swim in treacle than in water. Go figure.

5 cool things you can do with SSH besides logging in

You probably already know ssh and that you can use it to log on to remote machines with “ssh user@example.com.” Type in the password and It Just Works™.

But that’s not all you can do. You can also…

Transfer files. The OpenSSH client comes with the file transfer programs sftp and scp. They use the SSH protocol so no additional software is needed on the server. With sftp you can browse the files on the remote system and upload and download files interactively. Scp is for one-off transfers, and works even with two remote hosts. For example:

sftp user@remote
scp user@remote:/path/to/source destination

# Copy ~/.bashrc from remote1 to remote2
scp user1@remote1:.bashrc user2@remote2:

Log in without typing the password. Generate a pair of “public” and “private” keys. Upload the public key to remote machines, and keep the private key secret. The next time you log in ssh checks the private key against the public key instead of asking for a password. Sounds complicated? It’s really easy:

ssh-keygen -t rsa                 # Generate public/private key pair
ssh-copy-id user@remote           # Upload public key to remote
ssh user@remote                   # Look ma, no password!

Run remote console programs. If all you want to do is execute one command you can save a step and have SSH run the command instead of a shell. If the command needs a terminal (text editors, IRC, …) use the -t switch. For example to execute irssi:

ssh user@remote -t irssi

Run remote GUI programs. If you enable “X11 redirection” with the -X switch you can use GUI programs that run on the remote machine. Other useful switches are -C (enable compression) and -f (run in background after logging in). For example, to execute virt-manager, the libvirt virtual machine manager, on a remote host:

ssh user@remote -XCf virt-manager

You’re not limited to running random applications. You can also get a “desktop in a window” on the remote machine, kind of like VNC or Windows Remote Desktop Connection. You only need an X server in a window, such as Xephyr, and start the desktop on the remote machine:

Xephyr :1 -ac &
DISPLAY=:1 ssh user@remote -XCf gnome-session

Use remote services through local ports and the other way around. Is the remote network behind a NAT or firewall that blocks access? No problem; make the SSH client listen on a local port and forward connections from there to a host and port on the remote machine’s network. For example, this makes MySQL at dbhost look like it runs locally:

ssh user@remote -L 3306:dbhost:3306

You can use -R to make reverse connections. For example, CUPS the printer daemon listens for connections on the TCP port 631. To print to local printers from the remote machines:

ssh user@remote -R 631:localhost:1631

# Once you are logged in:
export CUPS_SERVER=localhost:1631

Bonus: clean up known_hosts with sed. This isn’t really an SSH tip, but here goes. When an SSH server’s host key changes, like when the machine is reinstalled, the client notices that something fishy is going on. It prints an error message like below and doesn’t let you continue.

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@    WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED!     @
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
[...snip...]
Offending key in /home/joni/.ssh/known_hosts:28
[...snip...]

To correct the situation the easiest way is to remove the offending line 28 from known_hosts:

sed -i 28d ~/.ssh/known_hosts

Introducing nc – the network “cat”

No, not lolcat, netcat. Ever wanted to

  • see how exactly a web server responds to a HTTP request,
  • intercept and study the network traffic of a client-server program, or
  • transfer files over a network?

Well, you’ll love netcat. You give it a hostname and a port, and it connects stdin and stdout to the socket connection. You are left talking with the server:

$ nc www.example.com 80
...now talking with the server...

Some common uses

Testing a web server:

echo -en 'GET / HTTP/1.0\r\n\r\n' | nc www.google.com 80 -q 5

(The “-q 5” makes nc wait 5s for the server’s reply)

Copying a bunch of files from one machine to another: listen on port 1234 of the receiver, and connect from the sender.

recvr$ nc -l 1234 | tar xzv
          ...
sendr$ tar cz file1 file2 | nc recvr 1234

(If you use the old “netcat” program you need the -p switch in the first command to listen on a port.)

Intercepting the traffic between a client and a server gets a bit more involved. You might think that you could do something like this to spy a the Windows remote desktop connection to “remotehost”:

nc -l 3389 | tee rdesktop.in | nc remotehost 3389 #broken

It won’t work because the pipes only work in one direction. Data from the client is passed to “remotehost,” but the data from the remote side is printed to the terminal. To solve this problem we’ll have to use named pipes:

mkfifo pin
mkfifo pout
nc -l 3389 <pin | tee rdesktop.in >pout
nc remotehost 3389 <pout | tee rdesktop.out >pin

Now the data read from the client is saved to rdesktop.in, and the data written to the client is saved to rdesktop.out.