MySQL v9.0 on AWS RDS + PyMSQL 0.10.1 on python 2.7

Question

I have a python 2.7 app (Edgewall Trac) that connects to a MySQL v5.7 db deployed on AWS RDS. That same db is also accessed by multiple PHP applications. AWS recently notified me that support for MySQL v5.7 will be discontinued, and I should switch to MySQL v8.0.

I upgraded the db without data loss, but had to set the character_set parameters to utf8 as described here:

character_set_client     -> "utf8"
character_set_connection -> "utf8"
character_set_server     -> "utf8"

After rebooting the db, the PHP app works fine; however the python 2.7 + PyMySQL 0.10.1 still cannot connect. It throws the same error as discussed here so it seems like the encoding is still not what PyMySQL expects. I tried setting all character encoding and collation parameters I could find like this:

character_set_database -> "utf8"
character_set_results  -> "utf8"
collation_connection   -> "utf8_unicode_ci"
collation_server       -> "utf8_unicode_ci"

However, after that change + db reboot, I still get the same error.

TimeoutError: Unable to get database connection within 0 seconds. (AttributeError: 'NoneType' object has no attribute 'encoding')

Any other suggestions that I could try, or is continued use of MySQL v5.7 pretty much the only option?

Answer 1

I was able to find a solution. The database schema was originally created using utf8/utf8-bin as the character set and collation. Converting it to utf8mb4/utf8mb4_bin made everything work again for pymysql as well without having to set any parameters in addition to what PHP required. I ran the following queries from MySQL Workbench:

ALTER DATABASE my_db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

And then to update the tables, run the query below, copy the output and run that as a query again:

SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET utf8mb4
   COLLATE utf8mb4_unicode_ci;   ",
   "ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4
   COLLATE utf8mb4_unicode_ci;  ") 
AS alter_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = "my_db_name";

(credit to the post in this discussion)