Open Stack

Forgot the links:

[1] https://bugs.mysql.com/bug.php?id=79317
[2] https://github.com/PyMySQL/PyMySQL/issues/644

On Wed, Mar 14, 2018 at 11:53 AM, Michael Bayer <mbayer at redhat.com> wrote:
> 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.