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
Reply to: