Warning: (1300, "Invalid utf8mb4 character string" occurring with Python 3 on PyMySQL 0.8.0 but not 0.7.1

[zzzeek]

This seems to have appeared as of 0.8.0. reproduces against:

• MySQL 5.6 or 5.7, not MariaDB
• Python 3, I'm using 3.6.4 here, not Python 2.7 (unless I incorrectly omit pymysql.Binary)
• charset must be specified with connect(), does not occur if not
• PyMySQL 0.8.0, not 0.7.1

import pymysql
import uuid

conn = pymysql.connect(
    user="scott", passwd="tiger", host="mysql56",
    db="test", charset="utf8mb4")
cursor = conn.cursor()
cursor.execute("""
    CREATE TABLE IF NOT EXISTS `profiles` (
      `id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
      `city` blob NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
""")
cursor.execute(
    "INSERT INTO profiles (id, city) VALUES (%(id)s, %(city)s)",
    {
        'id': uuid.uuid4().hex,
        'city': pymysql.Binary(
            b'z\xf9\x87jS?\xd4i\xa5\xa3\r\xa7\x1e\xed\x16\xe0\xb5\x05R\xa4\xec\x16\x8f\x06\xb5\xea+\xaf<\x00\\\x94I9A\xe0\x82\xa7\x13\x0c\x8c'
        )
    }
)

output produces a warning:

/home/classic/.venv3/lib/python3.6/site-packages/pymysql/cursors.py:165: Warning: (1300, "Invalid utf8mb4 character string: 'F9876A'")
  result = self._query(query)

Per one of my users In MySQL this is issue: https://bugs.mysql.com/bug.php?id=79317

however, PyMySQL 0.7.1 doesn't have the problem. So can we do whatever 0.7.1 is doing here?

[zzzeek]

since we are using pymysql.Binary(), it might be a better idea just to use the _binary prefix when passing to the DB. I have a feeling this is how things go with Postgresql /psycopg2 as well (e.g. special syntaxes are added around bound parameters).

[methane]

since we are using pymysql.Binary(), it might be a better idea just to use the _binary prefix

That was we have. But when user adds _binary prefix thmeselves, the prefix duplicated.

#610
#549

[zzzeek]

make it a connection-level option "prefix_binary" or something like that.

[zzzeek]

because otherwise now everyone has to duplicate Django's "fix" which IMO this is a driver issue. the drivers handle database typing issues as much as they can.

[zzzeek]

although this issue is going to be in mysqlclient, mysql-connector also, right?

[methane]

make it a connection-level option "prefix_binary" or something like that.

See https://github.com/PyMySQL/PyMySQL/blob/master/pymysql/connections.py#L562

although this issue is going to be in mysqlclient, mysql-connector also, right?

Yes, and it's very difficult to add binary_prefix option to mysqlclient.
I don't know much about Connector/Python.

[zzzeek]

it's too bad but it looks like all roads point to consuming projects have to do it, there are too many mysql drivers and none of them were ready for this stupid Oracle decision. see https://bitbucket.org/zzzeek/sqlalchemy/issues/4216/mysql-specific-binary-warnings-1300.

[zzzeek]

See https://github.com/PyMySQL/PyMySQL/blob/master/pymysql/connections.py#L562

OMG it's already there. Wow OK. I'll tell my user to use that for now, thanks!

[methane]

ref: https://bugs.mysql.com/bug.php?id=79317

Even mysqldump is got troubled by this warning.
https://bugs.mysql.com/bug.php?id=80150

I agree that's a silly change.