I am working on some legacy mysql databases, with tables using blobs to store text information. I have this on two different servers. One is a mariadb instance, the other is a mysql instance.
When I run a "SELECT * from TABLE WHERE column1='value'" query, the mariadb instance displays all the blobs as the text, for example, "a string" (desirable behavior). On the other hand, the mysql instance shows them as hexadecimal strings, for example, "0x6120737472696e67" (not desirable; preferable to see the human readable version "a string" in the output).
I know that one can use "CONVERT(column1 using utf8)" kind of query to display, or even alter the table to convert the blobs to text type columns. However, the former is very cumbersome when multiple columns need to be converted within the query, and the latter is a last resort option for me. What I am trying to find out is why the difference exists, and if there is a way to change any settings (on the server or the client) to get the same output on the mysql instance as the mariadb instance.
Any help is much appreciated.
Here are the versions of the software in use:
MariaDB: "mysql Ver 15.1 Distrib 5.5.50-MariaDB, for Linux (x86_64) using readline 5.1" on Centos 7
MySQL: "mysql Ver 8.0.36 for Linux on x86_64 (Source distribution)" on Centos 9 Stream
PS: I have looked at the server settings and compared. MariaDB uses "latin1" as default encoding for character_set_database and character_set_server. MySQL uses "utf8mb4". Changing it to "latin1" on MySQL had no effect on the output of the select query - still displays hex.
This occurs due to "binary as hex" option being enabled by default in the MySQL client in versions greater than 8.0.19. Can be easily overcome by using --skip-binary-as-hex command line option.
Found this information in a different context from: