Opened 12 years ago

Closed 8 years ago

Last modified 8 years ago

#2170 closed defect (wontfix)

db lookup methods not case sensitive on mysql

Reported by: derelm Owned by: Philippe Raoult
Component: Database layer (models, ORM) Version: master
Severity: normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description (last modified by James Bennett)

i noticed that the methods field__exact and field__iexact match case insensitive on mysql
given you have a field (choices=(('a','small a'),('A','capital a'))) field__exact will always match both choices.

possible solution:
in django.db.backends.mysql.base

    'exact': '= BINARY %s',

oh and will iexact, icontains, istatswith, iendswith all match exactly the same rows anyways?

Attachments (3)

2170-1.diff (858 bytes) - added by Matt McClanahan <cardinal@…> 11 years ago.
fix-for-mysql-case-sensitivity.diff (972 bytes) - added by anonymous 11 years ago.
2170-fix-with-test.diff (1.7 KB) - added by Philippe Raoult 10 years ago.
added a regression test

Download all attachments as: .zip

Change History (19)

comment:1 Changed 11 years ago by Simon G. <dev@…>

Triage Stage: UnreviewedAccepted

Changed 11 years ago by Matt McClanahan <cardinal@…>

Attachment: 2170-1.diff added

comment:2 Changed 11 years ago by Matt McClanahan <cardinal@…>

Has patch: set

This came up in IRC today, when somebody noted that it's resulting in an exception if you do FooModel.objects.get(charfield='foo') and have both 'foo' and 'Foo' in that field (Two rows returned instead of one as expected).

comment:3 Changed 11 years ago by James Bennett

Description: modified (diff)

Fixing formatting in description.

comment:4 Changed 11 years ago by saintsjd@…

I just updated the patch to work with the latest SVN. I can confirm that this works for me and seems to solve case sensitivity issues in mysql.

Changed 11 years ago by anonymous

comment:5 Changed 10 years ago by Philippe Raoult

Owner: changed from nobody to Philippe Raoult
Status: newassigned
Triage Stage: AcceptedReady for checkin

Actually this is dependent on the field's collation but the default collation for most mysql installation is to be case-insensitive. So in short this is a real bug that has been undetected for a long time.

Changed 10 years ago by Philippe Raoult

Attachment: 2170-fix-with-test.diff added

added a regression test

comment:6 Changed 10 years ago by Marc Fargas

milestone: 1.0

comment:7 Changed 10 years ago by Malcolm Tredinnick

Resolution: fixed
Status: assignedclosed

(In [7798]) Fixed #2170 -- "exact" lookups in MySQL are now case-sensitive (the same as other backends).

This is a backwards incompatible change if you were relying on 'exact' being
case-insensitive. For that, you should be using 'iexact'.

comment:8 Changed 10 years ago by arne

Resolution: fixed
Status: closedreopened

Even though the patch fixes the original problems, it now fails to lookup keys with non-ASCII characters:

>>> from sprint.julian.models import Julian
>>> x = Julian(name=u'\xf6')
>>> y = Julian.objects.get(name=u'\xf6')
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/Library/Python/2.5/site-packages/django/db/models/", line 81, in get
    return self.get_query_set().get(*args, **kwargs)
  File "/Library/Python/2.5/site-packages/django/db/models/", line 300, in get
    % self.model._meta.object_name)
DoesNotExist: Julian matching query does not exist.

Omitting the BINARY in 'exact' allows such lookup.

Indeed, it appears that MySQL consistently fails to perform lookup of non-ASCII strings across various front-ends (tested with phpMyAdmin and mysql command line).

comment:9 Changed 10 years ago by arne

Resolution: fixed
Status: reopenedclosed

Follow-up: It turns out that MySQL *will* correctly lookup non-ASCII characters even for =BINARY, as long as the database character set is UTF-8 (i.e. the same as the connection character set). As the documentation already suggests to create MySQL databases with character set utf8, there probably is nothing more that can be done. Perhaps this point about using UTF-8 can be emphasized a bit more, explaining that it otherwise just won't work.

comment:10 Changed 10 years ago by Karen Tracey <kmtracey@…>

Actually the non-ASCII problem is really a dup of #8102 and I'm still somewhat hopeful that something can be done about it. It can work if Django will only specify a collation, not flat-out BINARY for the comparison.

comment:11 Changed 10 years ago by Malcolm Tredinnick

(In [8319]) Changed "exact" matches in MySQL to use the database's native collation.

This effectively reverses the change in [7798]. It was proving too difficult to
successfully manage all the side effects here and provide a satisfactory
solution for everybody. Many thanks to arne, Martin von Löwis and, particular,
Karen Tracey, for doing a lot of research and proto-patches here to establish what was possible and practical.

This is backwards incompatible if you were relying on the behaviour after
[7798]. The docs have been updated to indicate the solution.

Refs #2170, #7789, #8102.

comment:12 Changed 10 years ago by hrauch

milestone: 1.01.0 beta
Resolution: fixed
Status: closedreopened

As noted in the documentation, I tried to change the collation within my Django-database. So I dumped the database and changed "utf8_german_ci" to "utf8_bin". After restoring the data and trying to open one page, I'll get the following error:

[Fri Aug 15 08:52:23 2008] [error] [client] mod_python (pid=2259, interpreter='dms', phase='PythonHandler', handler='django.core.handlers.modpython'): Application error
[Fri Aug 15 08:52:23 2008] [error] [client] ServerName: ''
[Fri Aug 15 08:52:23 2008] [error] [client] DocumentRoot: '/srv/www/htdocs'
[Fri Aug 15 08:52:23 2008] [error] [client] URI: '/news/index.html'
[Fri Aug 15 08:52:23 2008] [error] [client] Location: None
[Fri Aug 15 08:52:23 2008] [error] [client] Directory: None
[Fri Aug 15 08:52:23 2008] [error] [client] Filename: '/srv/www/htdocs/news'
[Fri Aug 15 08:52:23 2008] [error] [client] PathInfo: '/index.html'
[Fri Aug 15 08:52:23 2008] [error] [client] Traceback (most recent call last):

[Fri Aug 15 08:52:23 2008] [error] [client] File "/usr/lib64/python2.5/site-packages/mod_python/", line 1537, in HandlerDispatch\n default=default_handler, arg=req, silent=hlist.silent)
[Fri Aug 15 08:52:23 2008] [error] [client] File "/usr/lib64/python2.5/site-packages/mod_python/", line 1229, in _process_target\n result = _execute_target(config, req, object, arg)
[Fri Aug 15 08:52:23 2008] [error] [client] File "/usr/lib64/python2.5/site-packages/mod_python/", line 1128, in _execute_target\n result = object(arg)
[Fri Aug 15 08:52:23 2008] [error] [client] File "/usr/local/lib64/python2.5/site-packages/django/core/handlers/", line 210, in handler\n return ModPythonHandler()(req)
[Fri Aug 15 08:52:23 2008] [error] [client] File "/usr/local/lib64/python2.5/site-packages/django/core/handlers/", line 183, in call\n response = self.get_response(request)
[Fri Aug 15 08:52:23 2008] [error] [client] File "/usr/local/lib64/python2.5/site-packages/django/core/handlers/", line 67, in get_response\n response = middleware_method(request)
[Fri Aug 15 08:52:23 2008] [error] [client] File "/usr/local/lib64/python2.5/site-packages/django/middleware/", line 16, in process_request\n language = translation.get_language_from_request(request)
[Fri Aug 15 08:52:23 2008] [error] [client] File "/usr/local/lib64/python2.5/site-packages/django/utils/translation/", line 97, in get_language_from_request\n return real_get_language_from_request(request)
[Fri Aug 15 08:52:23 2008] [error] [client] File "/usr/local/lib64/python2.5/site-packages/django/utils/translation/", line 351, in get_language_from_request\n lang_code = request.session.get('django_language', None)
[Fri Aug 15 08:52:23 2008] [error] [client] File "/usr/local/lib64/python2.5/site-packages/django/contrib/sessions/backends/", line 50, in get\n return self._session.get(key, default)
[Fri Aug 15 08:52:23 2008] [error] [client] File "/usr/local/lib64/python2.5/site-packages/django/contrib/sessions/backends/", line 147, in _get_session\n self._session_cache = self.load()
[Fri Aug 15 08:52:23 2008] [error] [client] File "/usr/local/lib64/python2.5/site-packages/django/contrib/sessions/backends/", line 19, in load\n return self.decode(s.session_data)
[Fri Aug 15 08:52:23 2008] [error] [client] File "/usr/local/lib64/python2.5/site-packages/django/contrib/sessions/backends/", line 80, in decode\n encoded_data = base64.decodestring(session_data)

comment:13 Changed 10 years ago by Russell Keith-Magee

milestone: 1.0 beta1.0
Resolution: fixed
Status: reopenedclosed

Please don't revert tickets that have been closed unless you are certain that the bug in question has not been resolved. If you want to help working out why an error message is occurring, please ask on the mailing list.

Marking for v1.0 beta won't help either - 1.0 beta has been shipped.

comment:14 Changed 8 years ago by Brandon Carl

milestone: 1.01.2
Resolution: fixed
Status: closedreopened
Triage Stage: Ready for checkinUnreviewed

This bug is somehow still present in the latest trunk release. (currently 1.2 beta 1)

The same bug fix that was applied before can be applied again. Don't know how the bug re-appeared, but it's really annoying hah.

comment:15 Changed 8 years ago by Karen Tracey

milestone: 1.2
Resolution: wontfix
Status: reopenedclosed

Yes, the fix for this was reverted, after much investigation and attempt to make it work. The problems it introduced were worse than the problem it fixed. So this bug is essentially wontfix. (Though if I'm remembering right there may be another one open to look for a better fix -- but the fix here is definitely not viable.) See: and the other tickets linked.

comment:16 Changed 8 years ago by Brandon Carl

Just to note: this "fix" was reverted in
To get the functionally correctly, change the Collation of the MySql database to latin1_swedish_cs or utf8_bin for case sensitive comparisons.

Note: See TracTickets for help on using tickets.
Back to Top