[openstack-dev] [oslo.db] upcoming warnings in MySQL 5.6, 5.7 for BLOB columns

Jay Pipes jaypipes at gmail.com
Wed Mar 14 16:02:50 UTC 2018

Neither nova nor placement use any BLOB columns.


On 03/14/2018 11:53 AM, Michael Bayer 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.
> __________________________________________________________________________
> OpenStack Development Mailing List (not for usage questions)
> Unsubscribe: OpenStack-dev-request at lists.openstack.org?subject:unsubscribe
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

More information about the OpenStack-dev mailing list