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

[SuilAmhain]

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.

[Tim Graham]

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.

[SuilAmhain]

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.

[SuilAmhain]

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

[Tim Graham]

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.

[SuilAmhain]

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.

• ​https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlconnection-raise-on-warnings.html

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.

[Tim Graham]

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

[SuilAmhain]

Fully Worked Example in Python Shell without Django wrapping MySQL Connector

[SuilAmhain]

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