MySQL - Handling illegal mix of collations

[Erik Karulf]

Hi All,

Traceback (most recent call last):

 File "/www/www.penny-arcade.com/parts/django/django/core/handlers/base.py", line 92, in get_response
   response = callback(request, *callback_args, **callback_kwargs)

 File "/www/www.penny-arcade.com/parts/django/django/views/decorators/cache.py", line 30, in _cache_controlled
   response = viewfunc(request, *args, **kw)

 File "/www/www.penny-arcade.com/src/penny_arcade/views.py", line 190, in archive
   if len(tags) == 0:

 File "/www/www.penny-arcade.com/parts/django/django/db/models/query.py", line 81, in __len__
   self._result_cache = list(self.iterator())

 File "/www/www.penny-arcade.com/parts/django/django/db/models/query.py", line 238, in iterator
   for row in self.query.results_iter():

 File "/www/www.penny-arcade.com/parts/django/django/db/models/sql/query.py", line 287, in results_iter
   for rows in self.execute_sql(MULTI):

 File "/www/www.penny-arcade.com/parts/django/django/db/models/sql/query.py", line 2369, in execute_sql
   cursor.execute(sql, params)

 File "/www/www.penny-arcade.com/parts/django/django/db/backends/mysql/base.py", line 84, in execute
   return self.cursor.execute(query, args)

 File "/usr/local/lib/python2.6/site-packages/MySQLdb/cursors.py", line 166, in execute
   self.errorhandler(self, exc, value)

 File "/usr/local/lib/python2.6/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
   raise errorclass, errorvalue

OperationalError: (1270, "Illegal mix of collations (latin1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE), (utf8_general_ci,COERCIBLE) for operation ' IN '")


<WSGIRequest
GET:<QueryDict: {u'q': [u'news  /path\ufffdto\ufffdqnews/q-news.php?id=http://www.diakonia-jkt.sch.id/upload/id1???']}>,
POST:<QueryDict: {}>,
COOKIES:{},
META:{'DOCUMENT_ROOT': '/www/www.penny-arcade.com/public/',
 'GATEWAY_INTERFACE': 'CGI/1.1',
 'HTTP_HOST': 'www.penny-arcade.com',
 'HTTP_USER_AGENT': 'Mozilla/5.0',
 'HTTP_X_FORWARDED_FOR': '206.225.23.4',
 'HTTP_X_VARNISH': '499193426',
 'PATH': '/sbin:/bin:/usr/sbin:/usr/bin',
 'PATH_INFO': u'/archive/',
 'PATH_TRANSLATED': '/www/www.penny-arcade.com/bin/django.wsgi/archive/',
 'QUERY_STRING': 'q=news%20%20/path%A7to%A7qnews/q-news.php?id=http://www.diakonia-jkt.sch.id/upload/id1???',
 'REMOTE_ADDR': '172.17.16.98',
 'REMOTE_PORT': '52485',
 'REQUEST_METHOD': 'GET',
 'REQUEST_URI': '/archive/?q=news%20%20/path%A7to%A7qnews/q-news.php?id=http://www.diakonia-jkt.sch.id/upload/id1???',
 'SCRIPT_FILENAME': '/www/www.penny-arcade.com/bin/django.wsgi',
 'SCRIPT_NAME': u'',
 'SERVER_ADDR': '172.17.17.9',
 'SERVER_ADMIN': 'webmaster@penny-arcade.com',
 'SERVER_NAME': 'www.penny-arcade.com',
 'SERVER_PORT': '80',
 'SERVER_PROTOCOL': 'HTTP/1.1',
 'SERVER_SIGNATURE': '',
 'SERVER_SOFTWARE': 'Apache',
 'mod_wsgi.application_group': 'www.penny-arcade.com|',
 'mod_wsgi.callable_object': 'application',
 'mod_wsgi.listener_host': '',
 'mod_wsgi.listener_port': '80',
 'mod_wsgi.process_group': '',
 'mod_wsgi.reload_mechanism': '0',
 'mod_wsgi.script_reloading': '1',
 'mod_wsgi.version': (2, 5),
 'wsgi.errors': <mod_wsgi.Log object at 0x8035c6480>,
 'wsgi.file_wrapper': <built-in method file_wrapper of mod_wsgi.Adapter object at 0x80315a3f0>,
 'wsgi.input': <mod_wsgi.Input object at 0x8031e2730>,
 'wsgi.multiprocess': True,
 'wsgi.multithread': False,
 'wsgi.run_once': False,
 'wsgi.url_scheme': 'http',
 'wsgi.version': (1, 0)}>

[Alex Gaynor]

You've provided absolutely no information as to what we need to do to reproduce this, or what might have caused this error. Django is working just fine on MySQL for quite a lot of people so we're going to need a good bit more information to look into this. If no additional information is provided this will be closed as worksforme.

[Erik Karulf]

Sorry for the incomplete report, I pressed submit instead of preview. :)

I realize this is just a bot probing for SQL injections, but I can't think of a good way to catch this bug short of try/catching a MySQLdb OperationalError.

I'm not sure exactly what the correct solution is, but it seems like it would break the django's db abstraction to coerce data into the same collation at the application level.

Python 2.6.2
FreeBSD: 7.0
MySQL server: 5.1.22
MySQL client: 5.1.36
Django: 1.1
MySQLdb: 1.2.2

worksforme is an acceptable resolution, there was just not a ticket I could find on the matter.

[Karen Tracey]

There are a couple of ways you could avoid this problem. First, you could use a utf-8 encoding for the table on MySQL. Since Django always uses utf-8 to talk to the database you will then never run into trouble with illegal mixtures of collations. Alternatively, if you want to keep the table using latin1 encoding, you can ensure that any parameters you pass can be encoded in latin1. (Do not actually pass latin-1 encoded bytestrings, just verify that what you are sending can in fact be encoded in latin1.) So far as I've been able to determine, this illegal mix of collations error only crops up when you send data that cannot be encoded in the table's charset.

[AdamGilman]

Just wanted to add a comment here. I just bumped into this problem recently with user input as unicode being passed to the DB with a latin1 tableset. As ekarfulf mentioned catching OperationalError from MySQLdb would catch this error but, a problem we found is that OperationalError is way too broad.

From MySQLdb source

59 class OperationalError(DatabaseError):
   60 
   61     """Exception raised for errors that are related to the database's
   62     operation and not necessarily under the control of the programmer,
   63     e.g. an unexpected disconnect occurs, the data source name is not
   64     found, a transaction could not be processed, a memory allocation
   65     error occurred during processing, etc."""

This includes collation errors (like above) but, also DB connection failures, memory errors, etc that bubble up from MySQL to MySQLdb to Django. This really is a bug for MySQLdb for the OperationalError class being too broad.

In the end there's two possible solutions. One being what kmtracey mentioned which is altering your tables to UTF8 or if you know you will never need unicode in that table you can cast the user input into a string str(user_input) and then try to catch the UnicodeError that will be raised so you don't pass unicode to your DB.

Just wanted to add some more help to anyone who finds this, like I did while searching for solutions to this problem

[Karen Tracey]

if you know you will never need unicode in that table you can cast the user input into a string str(user_input) and then try to catch the UnicodeError? that will be raised so you don't pass unicode to your DB.

Note attempting to cast to str is considerably more restrictive than attempting to encode in latin1. The cast to str will fail if the data contains any non-ASCII characters. latin1 includes many non-ASCII characters, just not the full range of characters in all of Unicode. Thus str(x) failing doesn't necessarily imply MySQL would generate an Illegal mix of collation errors if handed x.