Opened 9 years ago

Closed 5 years ago

Last modified 5 years ago

#2170 closed defect (wontfix)

db lookup methods not case sensitive on mysql

Reported by: derelm Owned by: PhiR
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 ubernostrum)

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

OPERATOR_MAPPING = {
    '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@…> 8 years ago.
fix-for-mysql-case-sensitivity.diff (972 bytes) - added by anonymous 8 years ago.
2170-fix-with-test.diff (1.7 KB) - added by PhiR 7 years ago.
added a regression test

Download all attachments as: .zip

Change History (19)

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

  • Triage Stage changed from Unreviewed to Accepted

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

comment:2 Changed 8 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 8 years ago by ubernostrum

  • Description modified (diff)

Fixing formatting in description.

comment:4 Changed 8 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 8 years ago by anonymous

comment:5 Changed 7 years ago by PhiR

  • Owner changed from nobody to PhiR
  • Status changed from new to assigned
  • Triage Stage changed from Accepted to Ready 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 7 years ago by PhiR

added a regression test

comment:6 Changed 7 years ago by telenieko

  • milestone set to 1.0

comment:7 Changed 7 years ago by mtredinnick

  • Resolution set to fixed
  • Status changed from assigned to closed

(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 7 years ago by arne

  • Resolution fixed deleted
  • Status changed from closed to reopened

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')
>>> x.save()
>>> 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/manager.py", line 81, in get
    return self.get_query_set().get(*args, **kwargs)
  File "/Library/Python/2.5/site-packages/django/db/models/query.py", 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 7 years ago by arne

  • Resolution set to fixed
  • Status changed from reopened to closed

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 7 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 7 years ago by mtredinnick

(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 7 years ago by hrauch

  • milestone changed from 1.0 to 1.0 beta
  • Resolution fixed deleted
  • Status changed from closed to reopened

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

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

comment:13 Changed 7 years ago by russellm

  • milestone changed from 1.0 beta to 1.0
  • Resolution set to fixed
  • Status changed from reopened to closed

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 5 years ago by spleeyah

  • milestone changed from 1.0 to 1.2
  • Resolution fixed deleted
  • Status changed from closed to reopened
  • Triage Stage changed from Ready for checkin to Unreviewed

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 5 years ago by kmtracey

  • milestone 1.2 deleted
  • Resolution set to wontfix
  • Status changed from reopened to closed

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: http://code.djangoproject.com/ticket/2170#comment:11 and the other tickets linked.

comment:16 Changed 5 years ago by spleeyah

Just to note: this "fix" was reverted in http://code.djangoproject.com/changeset/8319
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