Repository navigation
INSERT into json column on MySQL 8 with binary_prefix fails (succeeds for pymysql) #494
Description
hey there -
one of my users found this, there's no pressure at all for this to be fixed if it's not possible, I just wanted to get it logged as a known issue in case other people hit it as I did not see it here. Using the "binary_prefix" feature with mysqlclient on MySQL 8 seems to break JSON values with MySQLdb._exceptions.OperationalError: (3144, "Cannot create a JSON value from a string with CHARACTER SET 'binary'."), example below which compares the same operation succeeding with pymysql.
MySQL version: Server version: 8.0.21 MySQL Community Server - GPL
MySQLClient: 2.0.3final0
reproduction:
import MySQLdb
import pymysql
def test(dbapi):
conn = dbapi.connect(
user="scott",
password="tiger",
host="mysql80",
db="test",
binary_prefix=True,
)
cursor = conn.cursor()
cursor.execute("DROP TABLE IF EXISTS test")
cursor.execute(
"""
CREATE TABLE test (
id INTEGER NOT NULL AUTO_INCREMENT,
json JSON NOT NULL,
PRIMARY KEY (id)
)CHARSET=utf8
"""
)
cursor.executemany(
"INSERT INTO test (id, json) VALUES (%(id)s, %(json)s)",
({"id": 1, "json": "{}"}, {"id": 2, "json": "{}"}),
)
conn.rollback()
test(pymysql) # succeeds
# MySQLdb._exceptions.OperationalError: (3144, "Cannot create a JSON value from a string with CHARACTER SET 'binary'.")
test(MySQLdb) # failshave a nice day!
Activity
Add test for PyMySQL#494
Fix executemany with binary prefix (PyMySQL#605)