[Maria-discuss] Character set & BLOBS on MySQL
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
On Wed, 7 Nov 2018 13:29:09 +0000
Andrew Wood
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?
BLOBS don't have a character set, TEXT and varchar do. You don't need to worry.
participants (2)
-
Andrew Wood
-
Daniel Black