Closed
Description
So it's not clear if this is actually a regression, because apparently PyMySQL has had this problem for awhile, but it just popped up on mysqlclient 1.4.4. Re-adding the "SET NAMES" mentioned in the changelog seems to fix it. I think MySQL 8.0 has a problem when SET NAMES is not called so if pymysql/mysqlclient won't add it back in I will likely have to add it to SQLAlchemy.
Test case:
import MySQLdb
conn = MySQLdb.connect(
user="scott", passwd="tiger", host="mysql80", db="test", charset="utf8mb4"
)
cursor = conn.cursor()
# uncomment to fix issue
#cursor.execute("SET NAMES utf8mb4")
for name in [
"engineers",
"managers",
]:
cursor.execute("DROP TABLE IF EXISTS %s" % name)
cursor.execute(
"""
CREATE TABLE engineers (
person_id INTEGER NOT NULL,
PRIMARY KEY (person_id)
)
"""
)
cursor.execute(
"""
CREATE TABLE managers (
person_id INTEGER NOT NULL,
manager_name VARCHAR(50),
PRIMARY KEY (person_id)
)
"""
)
cursor.execute("""
SELECT engineers.person_id, CAST(null AS CHAR(50)) AS manager_name
FROM engineers
UNION
SELECT managers.person_id, managers.manager_name FROM managers
""")
print(cursor.fetchall())
this is using MySQL 8.0.16 running in Centos docker:
MySQLdb._exceptions.OperationalError: (1271, "Illegal mix of collations for operation 'UNION'")
It doesn't like the "CAST(NULL AS CHAR(50))" in the UNION. suddenly. adding back the SET NAMES makes it go away. only on MySQL 8, not on 5.7, 5.6, mariadb, etc.
Sorry this issue kind of sucks.
Metadata
Metadata
Assignees
Labels
No labels