Opened 18 years ago

Closed 15 years ago

Last modified 15 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: dev
Severity: normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

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

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@…> 17 years ago.
fix-for-mysql-case-sensitivity.diff (972 bytes ) - added by anonymous 17 years ago.
2170-fix-with-test.diff (1.7 KB ) - added by Philippe Raoult 17 years ago.
added a regression test

Download all attachments as: .zip

Change History (19)

comment:1 by Simon G. <dev@…>, 18 years ago

Triage Stage: UnreviewedAccepted

by Matt McClanahan <cardinal@…>, 17 years ago

Attachment: 2170-1.diff added

comment:2 by Matt McClanahan <cardinal@…>, 17 years ago

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 by James Bennett, 17 years ago

Description: modified (diff)

Fixing formatting in description.

comment:4 by saintsjd@…, 17 years ago

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.

by anonymous, 17 years ago

comment:5 by Philippe Raoult, 17 years ago

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.

by Philippe Raoult, 17 years ago

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

added a regression test

comment:6 by Marc Fargas, 16 years ago

milestone: 1.0

comment:7 by Malcolm Tredinnick, 16 years ago

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

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')
>>> 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 by arne, 16 years ago

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 by Karen Tracey <kmtracey@…>, 16 years ago

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 by Malcolm Tredinnick, 16 years ago

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

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 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 by Russell Keith-Magee, 16 years ago

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 by Brandon Carl, 15 years ago

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 by Karen Tracey, 15 years ago

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

comment:16 by Brandon Carl, 15 years ago

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