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 <[email protected]> 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: [email protected]?subject:unsubscribe http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
