Opened 6 years ago

Closed 6 years ago

Last modified 6 years ago

#29678 closed Bug (invalid)

inspectdb fails on MySQL 8 with " 'utf8' is currently an alias for the character set UTF8MB3"

Reported by: SuilAmhain Owned by: nobody
Component: Core (Management commands) Version: 2.1
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

Hi,

I have created a bunch of tables in MySQL 8.0.12 that I wished to import into Django.

But I consistently receive this error when attempting to do so:

# The error was: (3719, "3719: 'utf8' is currently an alias for the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.", None)

The problem is that the information_schema that is queried for table information uses UTF8, which is an an alias of UTF8MB3.

MySQL has introduced a warning from MySQL 8 to encourage you to use UTF8MB4 explicitly. inspectdb cannot deal with that warning.

I appreciate this may be regarded as a MySQL feature/bug.

Django is running these queries:

2018-08-15T23:25:41.500932Z	   43 Query	SELECT column_name, referenced_table_name, referenced_column_name FROM information_schema.key_column_usage WHERE table_name = 'django_content_type' AND table_schema = DATABASE() AND referenced_table_name IS NOT NULL AND referenced_column_name IS NOT NULL
2018-08-15T23:25:41.511166Z	   43 Query	SHOW WARNINGS

The show warnings is the problem. I am very new to Django and cannot workaround it.

Attachments (2)

base.py (13.5 KB ) - added by SuilAmhain 6 years ago.
Django 2.1 django/db/backends/mysql/base.py
2018-08-16 23_28_15-Exploration [C__Users_user01_Documents_Open University_TM470_TM470PyCharmProject.png (129.6 KB ) - added by SuilAmhain 6 years ago.
Fully Worked Example in Python Shell without Django wrapping MySQL Connector

Download all attachments as: .zip

Change History (9)

comment:1 by Tim Graham, 6 years ago

You're using Django 2.1? As of 5bcd292098b4de7bb03ef778e24d9e2f433d0dae (Django 1.8) Django doesn't promoted MySQL warnings to exceptions so I'm a bit puzzled if this happens without that.

comment:2 by SuilAmhain, 6 years ago

What can I provide as further evidence?

I am using a number of MySQL 8 features related to JSON so downgrading to a lower version is not viable. I am not explicitly tied to Django, but I really wanted to learn it.

by SuilAmhain, 6 years ago

Attachment: base.py added

Django 2.1 django/db/backends/mysql/base.py

comment:3 by Tim Graham, 6 years ago

Resolution: worksforme
Status: newclosed

I can't reproduce the failure with MySQL 8. I see the warning but it's not an exception. I guess there might be some code in your environment like what was removed in the commit I referenced above. If you're able to debug further and find that Django is at fault, please reopen with an explanation.

comment:4 by Tim Graham, 6 years ago

Summary: MySQL 8.0.12 manage.py inspectdb failsinspectdb fails on MySQL 8 with " 'utf8' is currently an alias for the character set UTF8MB3"
Type: UncategorizedBug

comment:5 by SuilAmhain, 6 years ago

I have found a workaround of sorts.
For the record:

  • mysql-connector-python=8.0.12
  • I have just moved the MySQL instance from 8.0.11 to 8.0.12
  • django 2.1

I have tried various settings passed to the database via settings.py but getting nowhere with them, for example:

DATABASES = {
    'default': {
        'ENGINE': 'mysql.connector.django',
#        'ENGINE': 'django.db.backends.mysql',
        'NAME': '<value>',
        'USER': '<value>',
        'PASSWORD': '<value>',
        'HOST': '<value>',
        'CHARSET': 'utf8mb4',
        'COLLATION': 'utf8mb4_unicode_ci',
        'PORT': '3309',
        'OPTIONS': {'sql_mode': 'TRADITIONAL', 'use_pure': True, 'use_unicode': True, 'charset': 'utf8mb4',
                    'collation': 'utf8mb4_general_ci','get_warnings':False},
    }

MySQL connector docs state that raise_on_warnings defaults to False. It is however getting set to true somewhere in the code.

In site-packages -> mysql -> connector -> cursor.py I changed the last few lines of the execute function to:

        if multi:
            self._executed_list = []
            return self._execute_iter(self._connection.cmd_query_iter(stmt))
        exlcude_schema=b'information_schema'         # New COS Workaround
        if exlcude_schema in stmt:                   # New COS Workaround
            print(self._connection.get_warnings)     # New COS Workaround
            self._connection.get_warnings = False    # New COS Workaround
        try:
            self._handle_result(self._connection.cmd_query(stmt))
        except errors.InterfaceError:
            if self._connection._have_next_result:  # pylint: disable=W0212
                raise errors.InterfaceError(
                    "Use multi=True when executing multiple statements")
            raise
        return None

It is not very elegant, but I am rushing for a college deadline and well time is ticking.

comment:6 by Tim Graham, 6 years ago

Resolution: worksformeinvalid

Thanks for investigating. So it seems to be an issue with MySQL Connector/Python and Django isn't at fault.

by SuilAmhain, 6 years ago

Fully Worked Example in Python Shell without Django wrapping MySQL Connector

comment:7 by SuilAmhain, 6 years ago

If I connect to the DB from python shell and execute basically the same commands, I can see that Mysql connector is defaulting
raise_on_warnings to False

If I manually set raise_on_warnings to True I then get the same error as from within inspectdb.

I honestly do not know where the responsibility lies for the bug or where raise_on_warnings is getting changed within the Django stack.

Anyways hopefully this note helps somebody somewhere at some stage :-)

Thank you

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