This past week I converted the last remnants of badly encoded data from a 12 year old MySQL database to utf8mb4. Good riddance to latin1! The database migration itself went well. Alas, hours of my life were consumed battling the MySQL 8.0 ODBC Connector used to import shipping records to UPS Worldship. Googling revealed nothing helpful, so I post the following to help the next poor soul…
To give some background, the server is running MySQL 8.0.22. All records in every table have been fully converted to utf8mb4, so data is in perfect unicode:
mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database | utf8mb4_0900_ai_ci |
| collation_server | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)The good news: Endicia, UPS Worldship 2020-2021 and FedEx Ship Manager are all compatible with the MySQL ODBC Connector v8.0.23 driver (longstanding bugs in ODBC v5.x have finally be resolved in v8.0). Our utf8mb4 tables imported perfectly into Endicia and FedEx Ship Manager on the first try.
UPS shipping labels are limited to the latin1 character set. So, I expected Worldship to import valid latin1 characters successfully, and only reject characters beyond ASCII 0xFF. That is how FedEx, Endicia, and other ODBC apps behaved under the same ODBC connection. However, Worldship produced 100% mojibake. Some latin1 characters rendered as “?”, others were replaced by multiple, random characters, and some entire address fields imported blank/empty.
The solution: Leave “Character Set” blank in the ODBC Data Source Configuration.
I had selected utf8mb4, which worked fine in all other apps, but this field breaks Worldship for whatever reason.
Success!
