_mysql_exceptions.OperationalError: (1366, '') when running tests against MySQL

[JA Robson]

hi, I'm on OSX (Sierra), am running a local MySQL, version 5.7.17, running django '2.0.dev20170122010200', and python mysqlclient 1.3.9 and 1.3.1 (I tried both versions).

when I try to run the tests, it consistently fails with this stack trace

Traceback (most recent call last):

  File "/Users/test/projects/django-hack/django/django/db/backends/utils.py", line 63, in execute

    return self.cursor.execute(sql, params)

  File "/Users/test/projects/django-hack/django/django/db/backends/mysql/base.py", line 88, in execute

    return self.cursor.execute(query, args)

  File "/Users/test/.virtualenvs/djangodev/lib/python3.6/site-packages/MySQLdb/cursors.py", line 221, in execute

    self.errorhandler(self, exc, value)

  File "/Users/test/.virtualenvs/djangodev/lib/python3.6/site-packages/MySQLdb/connections.py", line 38, in defaulterrorhandler

    raise errorclass(errorvalue)

_mysql_exceptions.OperationalError: (1366, '')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):

  File "./runtests.py", line 466, in <module>

    options.exclude_tags,

  File "./runtests.py", line 270, in django_tests

    extra_tests=extra_tests,

  File "/Users/test/projects/django-hack/django/django/test/runner.py", line 601, in run_tests

    old_config = self.setup_databases()

  File "/Users/test/projects/django-hack/django/django/test/runner.py", line 546, in setup_databases

    self.parallel, **kwargs

  File "/Users/test/projects/django-hack/django/django/test/utils.py", line 180, in setup_databases

    serialize=connection.settings_dict.get('TEST', {}).get('SERIALIZE', True),

  File "/Users/test/projects/django-hack/django/django/db/backends/base/creation.py", line 68, in create_test_db

    run_syncdb=True,

  File "/Users/test/projects/django-hack/django/django/core/management/__init__.py", line 128, in call_command

    return command.execute(*args, **defaults)

  File "/Users/test/projects/django-hack/django/django/core/management/base.py", line 327, in execute

    output = self.handle(*args, **options)

  File "/Users/test/projects/django-hack/django/django/core/management/commands/migrate.py", line 224, in handle

    self.verbosity, self.interactive, connection.alias, apps=post_migrate_apps, plan=plan,

  File "/Users/test/projects/django-hack/django/django/core/management/sql.py", line 51, in emit_post_migrate_signal

    **kwargs

  File "/Users/test/projects/django-hack/django/django/dispatch/dispatcher.py", line 179, in send

    for receiver in self._live_receivers(sender)

  File "/Users/test/projects/django-hack/django/django/dispatch/dispatcher.py", line 179, in <listcomp>

    for receiver in self._live_receivers(sender)

  File "/Users/test/projects/django-hack/django/django/contrib/auth/management/__init__.py", line 79, in create_permissions

    Permission.objects.using(using).bulk_create(perms)

  File "/Users/test/projects/django-hack/django/django/db/models/query.py", line 438, in bulk_create

    ids = self._batched_insert(objs_without_pk, fields, batch_size)

  File "/Users/test/projects/django-hack/django/django/db/models/query.py", line 1079, in _batched_insert

    self._insert(item, fields=fields, using=self.db)

  File "/Users/test/projects/django-hack/django/django/db/models/query.py", line 1056, in _insert

    return query.get_compiler(using=using).execute_sql(return_id)

  File "/Users/test/projects/django-hack/django/django/db/models/sql/compiler.py", line 1094, in execute_sql

    cursor.execute(sql, params)

  File "/Users/test/projects/django-hack/django/django/db/backends/utils.py", line 63, in execute

    return self.cursor.execute(sql, params)

  File "/Users/test/projects/django-hack/django/django/db/utils.py", line 93, in __exit__

    six.reraise(dj_exc_type, dj_exc_value, traceback)

  File "/Users/test/projects/django-hack/django/django/utils/six.py", line 683, in reraise

    raise value.with_traceback(tb)

  File "/Users/test/projects/django-hack/django/django/db/backends/utils.py", line 63, in execute

    return self.cursor.execute(sql, params)

  File "/Users/test/projects/django-hack/django/django/db/backends/mysql/base.py", line 88, in execute

    return self.cursor.execute(query, args)

  File "/Users/test/.virtualenvs/djangodev/lib/python3.6/site-packages/MySQLdb/cursors.py", line 221, in execute

    self.errorhandler(self, exc, value)

  File "/Users/test/.virtualenvs/djangodev/lib/python3.6/site-packages/MySQLdb/connections.py", line 38, in defaulterrorhandler

    raise errorclass(errorvalue)

django.db.utils.OperationalError: (1366, '')

after much poking around, I believe the problem is the escaping in the first three VALUES of the following statement:

INSERT INTO `auth_permission` (`name`, `content_type_id`, `codename`) VALUES ('Can add \xc3\x83\xc2\x85ngstr\xc3\x83\xc2\xb6m\\'s Articles', 941, 'add_article'), ('Can change \xc3\x83\xc2\x85ngstr\xc3\x83\xc2\xb6m\\'s Articles', 941, 'change_article'), ('Can delete \xc3\x83\xc2\x85ngstr\xc3\x83\xc2\xb6m\\'s Articles', 941, 'delete_article'), ... snip ..., ('Can delete model3', 951, 'delete_model3')

... note that the backslash preceding the single-quote is getting escaped, not the single-quote. if I run the statement in mysql client directly it fails until I remove the extra slash.

this is happening in 

~/.virtualenvs/djangodev/lib/python3.6/site-packages/MySQLdb/cursors.py

specifically you can see my two print statements:

        if args is not None:

            print(args)

            if isinstance(args, dict):

                args = dict((key, db.literal(item)) for key, item in args.iteritems())

            else:

                args = tuple(map(db.literal, args))

            if not PY2 and isinstance(query, bytes):

                query = query.decode(db.unicode_literal.charset)

            query = query % args

        if isinstance(query, unicode):

            query = query.encode(db.unicode_literal.charset, 'surrogateescape')

        try:

            r = None

            print('cursors.py query=%s' % query)

            r = self._query(query)

the former print statement shows what's getting sent from the Django test code:

("Can add Ã\x85ngström's Articles", 941, 'add_article', "Can change Ã\x85ngström's Articles", 941, 'change_article', "Can delete Ã\x85ngström's Articles", 941, 'delete_article', ... snip ... ', 'Can delete model3', 951, 'delete_model3')

so this is clearly not an issue with Django specifically, but I'm a little stumped why it's happening at all, since this is the official mysqlclient module and all. also seems like someone would have mentioned it by now if it wasn't some kind of strange one-off issue.

any insight you can give would be greatly appreciated. Thanks, James

[Tim Graham]

Try adding this to both entries in your DATABASES test settings:

'TEST': {
    'CHARSET': 'utf8',
    'COLLATION': 'utf8_general_ci',
},

[JA Robson]

Ah, thank you. That didn't exactly solve it, but it certainly pointed me in the right direction. ++

I added that to my settings, and it still failed. I took a look at VARIABLES and discovered the 'character_set_server' in my servers was latin1

SHOW VARIABLES LIKE 'character_set%';

+--------------------------+----------------------------------+

| Variable_name            | Value                            |

+--------------------------+----------------------------------+

| character_set_client     | utf8                             |

| character_set_connection | utf8                             |

| character_set_database   | latin1                           |

| character_set_filesystem | binary                           |

| character_set_results    | utf8                             |

| character_set_server     | latin1                           |

| character_set_system     | utf8                             |

| character_sets_dir       | /usr/local/mysql/share/charsets/ |

+--------------------------+----------------------------------+  

I fixed (temporarily) by just doing

set global character_set_server=utf8;

on the server. not preserved on restarts, unfortunately, but this command-line (or my.cnf corollaries) does get it setup properly

mysqld \

    --datadir "$DIR/data" \

    --lc-messages-dir "$DIR/msg" \

    --skip-innodb-file-per-table \

    --collation-server=utf8_general_ci \

    --init-connect='SET NAMES utf8' \

    --character-set-server=utf8&