INSERT into json column on MySQL 8 with binary_prefix fails (succeeds for pymysql)

[zzzeek]

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)  # fails

have a nice day!