Web lists-archives.com

Character set & BLOBS on MySQL




I am in the process of moving some tables from MySQL 5.1 on Squeeze to MariaDB 10.1 on Buster. One of the tables stores binary data (either a PDF or JPEG) as a LONGBLOB. The code that reads & writes to the table is PHP and hasnt changed other than having the url of the database server altered.

No errors are thrown on reading or writing but JPEGs are coming out corrupted, where as PDFs show fine. I had a hunch it was due to the charset/collation settings even though both tables are identical (the new one was created with the output from mysqldump on the old server).

Having run SHOW SESSION VARIABLES LIKE 'character\_set\_%'; on both the old and new server there is a difference but I want to understand why this might be causing the problem before I alter anything.


New server:
MariaDB [(none)]> SHOW SESSION 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         |
+------------------------------------+-------------+
7 rows in set (0.00 sec)

Old server:
mysql> SHOW SESSION VARIABLES LIKE 'character\_set\_%';
+------------------------------------+----------+
| Variable_name                      | Value  |
+------------------------------------+----------+
| character_set_client            | latin1 |
| character_set_connection  | latin1|
| character_set_database      | utf8   |
| character_set_filesystem   | binary|
| character_set_results         | latin1 |
| character_set_server          | latin1 |
| character_set_system         | utf8   |
+------------------------------------+---------+
7 rows in set (0.00 sec)


Also is there a way for the client to specify the correct settings for reading/writing BLOBS on a per connection basis as it might not always be possible to alter the server?

Thanks