Django and MySQL + Emoticons

This has been documented elsewhere previously but for my own recollection, here it is again.

If you create a typical UTF-8 database (CHARACTER SET utf8 COLLATE utf8_unicode_ci), you’ll run into the following error if you attempt to save 4-byte characters to MySQL (i.e. emoticons). This is due to MySQL’s 3 byte limit on utf-8 characters.

To remedy this issue, you’ll need to make a couple of configuration changes:

1) Switch your MySQL database to the utf8mb4 character set (you’ll need MySQL 5.5 or later).

2) Update your Django database settings to use the utf8mb4 encoding:

One thing to watch out for, if you have a CharField with a max_length of 255 characters and it has an index on it (i.e. unique), you’ll need to reduce the max_length to 191 as utf8mb4 takes up 33% more space. More info can be found in this Django ticket.

Sort Django Query (Order By) Using Values Within IN()

In MySQL, you can use the FIELD() function to easily sort a result set by a list of ordered ids:

To accomplish this in Django, you can make use of the extra() QuerySet method to create an additional field in the SELECT statement which can then be using for sorting in the FIELD method.

MySQL 5.5 and 5.6 Vulnerability

Another day, another security vulnerability.

Today Oracle announced security vulnerabilities and associated software patches affecting MySQL 5.5 and 5.6:

http://www.oracle.com/technetwork/topics/security/cpuoct2014-1972960.html#AppendixMSQL

To address these vulnerabilities, your database instances will need to be upgraded to either MySQL 5.5.40 or 5.6.21.

If you’re on a hosted service such as RDS, expect to be upgraded during your next maintenance window. More info can be found on Amazon’s security site.

MacPorts, Mavericks & MySQL 5.6 with Memcached

If you’ve upgraded to Mavericks you’ve probably realized that MacPorts MySQL 5.6 would not build due to some issues with MySQL itself.

That issue has now been fixed with MySQL version 5.6.15 and this changeset which is now live in the port index. Simply install like usual:

sudo port install mysql56-server

One thing the portfile doesn’t contain is the flag to enable the new 5.6 InnoDB Memcached Plugin. If you’d like to enable it, you’ll need to create a local portfile with the following changes:

# change
name                mysql56
# to
name                mysql56-custom


# change
-DWITH_SSL:STRING=bundled
# to
-DWITH_SSL:STRING=bundled \
-DWITH_INNODB_MEMCACHED=ON

If you’ve never worked with local portfiles before, here’s a quick tutorial…