Skip to content

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

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
zzzeek opened this issue Mar 12, 2018 · 9 comments
Closed

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

zzzeek opened this issue Mar 12, 2018 · 9 comments

Comments

@zzzeek
Copy link
Contributor

zzzeek commented Mar 12, 2018 •
edited
Loading

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
Copy link
Contributor Author

zzzeek commented Mar 12, 2018

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
Copy link
Member

methane commented Mar 12, 2018

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
Copy link
Contributor Author

zzzeek commented Mar 12, 2018

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

@zzzeek
Copy link
Contributor Author

zzzeek commented Mar 12, 2018

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
Copy link
Contributor Author

zzzeek commented Mar 12, 2018

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

@methane
Copy link
Member

methane commented Mar 13, 2018

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
Copy link
Contributor Author

zzzeek commented Mar 13, 2018

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 zzzeek closed this as completed Mar 13, 2018
@zzzeek
Copy link
Contributor Author

zzzeek commented Mar 13, 2018

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
Copy link
Member

methane commented Mar 13, 2018

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.

@sqlalchemy-bot sqlalchemy-bot mentioned this issue Nov 27, 2018
Closed
@spl0k spl0k mentioned this issue Feb 16, 2019
Closed
@github-actions github-actions bot locked as resolved and limited conversation to collaborators Aug 2, 2021
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@zzzeek @methane