maria-discuss team mailing list archive

[Andrew Wood <andrewjameswood@xxxxxxxxx>]

I am in the process of moving some tables from MySQL 5.1 (Linux) MariaDB 
10.1 also on Linux.


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