UTF8MB4 issue with MySQL 8 and PyMySQL

Issue #4283 resolved
Danqing Liu
created an issue

Using MySQL 8 and PyMySQL 0.8.1 with SQLAlchemy 1.2.8.

e = create_engine('mysql+pymysql://account@localhost:3306/db?charset=utf8mb4')
e.connect()

~/lib/python3.6/site-packages/pymysql/cursors.py:170: Warning: (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.")
  result = self._query(query)

This doesn't happen with MySQL 5. I'm already using utf8mb4, so I shouldn't really see this.

Thanks!

Comments (7)

  1. Michael Bayer repo owner

    Am travelling this week, however SQLAlchemy passes this encoding right through to the driver so you should likely report this upstream to pymysql.

    On the SQLAlchemy side we will have to add MySQL 8.0 to CI to figure out what else goes wrong.

  2. Naoki INADA

    With Python's -Werror option, I got this traceback:

    Traceback (most recent call last):
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1266, in _cursor_execute
        context)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 508, in do_execute
        cursor.execute(statement, parameters)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 172, in execute
        result = self._query(query)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 331, in _query
        self._do_get_result()
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 355, in _do_get_result
        self._show_warnings()
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 371, in _show_warnings
        warnings.warn(err.Warning(*w[1:3]), stacklevel=4)
    pymysql.err.Warning: (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.")
    
    The above exception was the direct cause of the following exception:
    
    Traceback (most recent call last):
      File "t.py", line 4, in <module>
        e.connect()
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2102, in connect
        return self._connection_cls(self, **kwargs)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 90, in __init__
        if connection is not None else engine.raw_connection()
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2188, in raw_connection
        self.pool.unique_connection, _connection)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
        return fn()
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/pool.py", line 345, in unique_connection
        return _ConnectionFairy._checkout(self)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/pool.py", line 791, in _checkout
        fairy = _ConnectionRecord.checkout(pool)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/pool.py", line 532, in checkout
        rec = pool._do_get()
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/pool.py", line 1196, in _do_get
        self._dec_overflow()
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
        compat.reraise(exc_type, exc_value, exc_tb)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 187, in reraise
        raise value
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/pool.py", line 1193, in _do_get
        return self._create_connection()
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/pool.py", line 350, in _create_connection
        return _ConnectionRecord(self)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/pool.py", line 477, in __init__
        self.__connect(first_connect_check=True)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/pool.py", line 684, in __connect
        exec_once(self.connection, self)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/event/attr.py", line 274, in exec_once
        self(*args, **kw)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/event/attr.py", line 284, in __call__
        fn(*args, **kw)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 1334, in go
        return once_fn(*arg, **kw)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/engine/strategies.py", line 183, in first_connect
        dialect.initialize(c)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/dialects/mysql/base.py", line 1913, in initialize
        default.DefaultDialect.initialize(self, connection)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 278, in initialize
        self.returns_unicode_strings = self._check_unicode_returns(connection)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/dialects/mysql/mysqldb.py", line 131, in _check_unicode_returns
        connection, additional_tests)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 345, in _check_unicode_returns
        results = {check_unicode(test) for test in tests}
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 345, in <setcomp>
        results = {check_unicode(test) for test in tests}
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 316, in check_unicode
        connection._cursor_execute(cursor, statement, parameters)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1273, in _cursor_execute
        context)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
        exc_info
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
        reraise(type(exception), exception, tb=exc_tb, cause=cause)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 186, in reraise
        raise value.with_traceback(tb)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1266, in _cursor_execute
        context)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 508, in do_execute
        cursor.execute(statement, parameters)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 172, in execute
        result = self._query(query)
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 331, in _query
        self._do_get_result()
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 355, in _do_get_result
        self._show_warnings()
      File "/Users/inada-n/tmp/pymysql-utf8-warning/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 371, in _show_warnings
        warnings.warn(err.Warning(*w[1:3]), stacklevel=4)
    sqlalchemy.exc.StatementError: (pymysql.err.Warning) (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.") [SQL: "SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1"]
    

    AS CHAR CHARACTER SET utf8 in the query causing this warning.

  3. Michael Bayer repo owner

    Use utf8mb4 (or utf8mb3) for all things MySQL

    Fixed bug in MySQLdb dialect and variants such as PyMySQL where an additional "unicode returns" check upon connection makes explicit use of the "utf8" character set, which in MySQL 8.0 emits a warning that utf8mb4 should be used. This is now replaced with a utf8mb4 equivalent. Documentation is also updated for the MySQL dialect to specify utf8mb4 in all examples. Additional changes have been made to the test suite to use utf8mb3 charsets and databases (there seem to be collation issues in some edge cases with utf8mb4), and to support configuration default changes made in MySQL 8.0 such as explicit_defaults_for_timestamp as well as new errors raised for invalid MyISAM indexes.

    Change-Id: Ib596ea7de4f69f976872a33bffa4c902d17dea25 Fixes: #4283 Fixes: #4192

    → <<cset c99345ee9994>>

  4. Michael Bayer repo owner

    Use utf8mb4 (or utf8mb3) for all things MySQL

    Fixed bug in MySQLdb dialect and variants such as PyMySQL where an additional "unicode returns" check upon connection makes explicit use of the "utf8" character set, which in MySQL 8.0 emits a warning that utf8mb4 should be used. This is now replaced with a utf8mb4 equivalent. Documentation is also updated for the MySQL dialect to specify utf8mb4 in all examples. Additional changes have been made to the test suite to use utf8mb3 charsets and databases (there seem to be collation issues in some edge cases with utf8mb4), and to support configuration default changes made in MySQL 8.0 such as explicit_defaults_for_timestamp as well as new errors raised for invalid MyISAM indexes.

    Change-Id: Ib596ea7de4f69f976872a33bffa4c902d17dea25 Fixes: #4283 Fixes: #4192 (cherry picked from commit c99345ee9994c3ea2a5e6536cc3365f18d017cc1)

    → <<cset ecd84ef096c3>>

  5. Log in to comment