Open Stack

hey all -

Just looking to see if we think this will impact openstack.  MySQL 5.6
and 5.7, but not yet MariaDB, now emits an erroneous warning when you
try to send a binary value to the database, because it sees the client
connection is supposed to use the utf8 or utf8mb4 charsets, assumes
all data must be in that charset, then warns because the binary data
does not necessarily conform to utf8 (which it has no need to, it's
binary).

Sounds weird, right, to make it easier the demo looks just like this:

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'
        )
    }
)

when using PyMySQL 0.8.0 (not 0.7.1) you then get a warning

Warning: (1300, "Invalid utf8mb4 character string: 'F9876A'").


So, Oracle upstream clearly is never going to fix this if you look at
the typically dismal discussion at [1].   I poked the PyMySQL project
at [2] to see what we can do.   Long term is that SQLAlchemy will add
the special "_binary" prefix to binary-bound parameter tokens to avoid
the warning, however right now PyMySQL supports a flag "binary_prefix"
that will do it for us on the driver side.

For Openstack, i need to know if we are in fact passing binary data to
databases in some project or another.   What we can do is add the
supply of this flag to oslo.db so that it is present automatically for
the PyMySQL driver, as well as checking the PyMySQL version for
compatibility.

If folks are seeing this warning already or are using BLOB / binary
columns in their project please ping me and we will get this added to
oslo.db.