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:

DATABASES = {
'default': {
'ENGINE':'django.db.backends.mysql',
'OPTIONS': {'charset': 'utf8mb4'},
}
}

view raw
settings.py
hosted with ❤ by GitHub

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:

SELECT id, name
FROM table
WHERE name IN (9, 8, 1, 2, 7, 3)
ORDER BY FIELD(id, 9, 8, 1, 2, 7, 3)

view raw
query.sql
hosted with ❤ by GitHub

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.

ids = [9, 8, 1, 2, 7, 3]
results = Model.objects.filter(id__in=ids).extra(
select={'manual': 'FIELD(id,%s)' % ','.join(map(str, ids))},
order_by=['manual']
)

view raw
query.py
hosted with ❤ by GitHub

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.

How To View Live MYSQL Queries

Here’s a simple trick for viewing MySQL queries as they hit your server – enable query logging to a file and then tail -f the file:

mysql> SHOW VARIABLES LIKE "general_log%";
+—————-+———————————–+
| Variable_name | Value |
+—————-+———————————–+
| general_log | OFF |
| general_log_file | /opt/local/var/db/mysql56/out.log |
+—————-+———————————–+
2 rows in set (0.00 sec)
mysql> SET GLOBAL general_log = 'ON';
Query OK, 0 rows affected (0.01 sec)

view raw
log_on.sql
hosted with ❤ by GitHub

tail -f /opt/local/var/db/mysql56/out.log

view raw
tail.sh
hosted with ❤ by GitHub

Just be sure to turn it off when you’re done:

mysql> SET GLOBAL general_log = 'OFF';

view raw
log_off.sql
hosted with ❤ by GitHub

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…

# the following assumes a typical MacPorts install to /opt/local
# create a directory to hold local ports
sudo mkdir /opt/custom
# edit /opt/local/etc/macports/sources.conf and add the following line before rsync://rsync.macports.org/release/tarballs/ports.tar
# file:///opt/custom
# create the directories to hold the port file
sudo mkdir -p /opt/custom/databases/mysql56/files/
# grab the original portfile and save it to /opt/custom/databases/mysql56/Portfile
# https://trac.macports.org/browser/trunk/dports/databases/mysql56/Portfile
# also grab the 5 supporting files and save them in /opt/custom/databases/mysql56/files/
# https://trac.macports.org/browser/trunk/dports/databases/mysql56/files
# update the Portfile with the following changes:
# Line 6:
# OLD: name mysql56
# NEW: name mysql56-custom
# Line 97:
# OLD: -DWITH_SSL:STRING=bundled
# NEW: -DWITH_SSL:STRING=bundled \
# -DWITH_INNODB_MEMCACHED=ON
# index the new port
cd /opt/custom
sudo portindex
# verify the port was added
port search mysql56-custom
# you should see:
# mysql56-custom @5.6.15 (databases)
# mysql56-custom-server @5.6.15
# install
sudo port install mysql56-custom-server

view raw
localport.sh
hosted with ❤ by GitHub