MySQL with utf8_bin collation for case-sensitive comparison goodness requires MySQLdb 1.2.1
|Reported by:||dave AT avaragado.org||Owned by:||Adrian Holovaty|
|Severity:||normal||Keywords:||mysql mysqldb utf8 case|
|Cc:||Triage Stage:||Design decision needed|
|Has patch:||no||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||no|
By default, MySQL 4.1 (at least) uses a case-insensitive collation, utf8_general_ci. This means that checks for equality are, annoyingly, case-insensitive. You can set the collation for a database in the CREATE DATABASE command, or you can set it per-server with an option in the MySQL my.cnf file. You can also set it per-column if you want.
Sadly, using a collation like utf8_bin, which gives case-sensitive comparisons, causes unexpected results through the ORM.
- Edit your my.cnf file to include the line
collation-server = utf8_bin
- Restart mysqld
Run "python manage.py test" such that it tests this models.py:
from django.db import models class Dummy(models.Model): """ >>> d = Dummy(ok='ok', notok='not ok') >>> d.save() >>> d = Dummy.objects.all() >>> d.ok, d.notok ('ok', 'not ok') """ ok = models.CharField(maxlength=100) notok = models.TextField()
You'll get something like this (irrelevant info removed):
Failed example: d.ok, d.notok Expected: ('ok', 'not ok') Got: ('ok', array('c', 'not ok'))
Without the collation-server line in my.cnf, the tests pass. Even without that line the tests fail if the CREATE DATABASE line specifies utf8_bin as the collation (and I imagine other binary collations), but the test framework uses the server defaults for those.
My own scrabbling around discovered that MySQLdb (version 1.2.0, at least) includes a suspicious class in init.py:
def Binary(x): from array import array return array('c', x)
... but that's as far as my limited knowledge goes - I have no idea how it gets used.
Change History (6)
comment:2 Changed 10 years ago by
|Component:||Core framework → Documentation|
|Status:||closed → reopened|
|Summary:||MySQL with utf8_bin collation for case-sensitive comparison goodness causes TextFields to be returned as arrays → MySQL with utf8_bin collation for case-sensitive comparison goodness requires MySQLdb 1.2.1|
comment:3 Changed 10 years ago by
|Component:||Documentation → Core framework|
|Keywords:||mysql mysqldb utf8 case added|
|Triage Stage:||Unreviewed → Design decision needed|