Longtext Column Values getting hex encoded by jdbc driver
Posted by: Zac Burke
Date: April 14, 2023 05:27PM

Hello,

I was developing a simple Java application to save some data to a mysql database using the latest version of the jdbc connector mysql-connector-j-8.0.32 , and jdk 17.

I have enabled query logging on the database as follows:

SET global general_log_file='/tmp/mysql.log';
SET global log_output = 'file';
SET global general_log = on;

While my java application (JDK17) was running and inserting data to the relevant table, I noticed that the jdbc driver is hex encoding all values for columns of type longtext.

I could see this via the inserts in the query log, which I was tailing as the program was running.

As I was using many frameworks (hibernate / spring) I wrote a simple main line program just with the jdbc driver jar as a dependency to see if it was any of the other
frameworks causing the problem.

However, I still see this behaviour of the longtext values getting hex encoded by the jdbc driver, with just the jdbc dependency

I have a simple main line program which is using the mysql jdbc driver on its own and a simple table called book.
create table book
(
id bigint auto_increment primary key,
author varchar(255) null,
extract_a longtext null,
extract_b longtext null,
extract_c longtext null,
num_pages int not null,
title varchar(200) null
);


Using a standard prepared statement and normal jdbc code I insert a simple row into the database. The values I send down via java code are:

'Stephen King','Extract A.','Extract B.','Extract C.',200,'The Shining'

The query log shows me the following:


Query INSERT INTO book (author, extract_a, extract_b, extract_c, num_pages,title)
VALUES ('Stephen King', x'4578747261637420412e', x'4578747261637420422e', x'4578747261637420432e', 200, 'The Shining')

It can be clearly seen that it is sending down the values as hex encoded strings x followed by 4578747261637420412e, which is the value 'Extract A' as a hex encoded string. The same goes for the other longtext columns,

It's worth noting that this appears only to have been introduced from version 8.0.29 of the jdbc driver. Versions before this were not hex-encoding values of longtext.


My question is:

Is this a deliberate change (i.e) That all longtext columns are now hex encoded by the jdbc driver? and is there no way of turning this off ?
It's just that with hex encoding the amount of data sent down to the server will be in most cases twice as much.

The values I have chosen in my example are quite small just to show my point, but my real-world values will be quite large, and I'd like to understand if this overhead of hex encoding the data will always be there.

I had a look to see if this was called out anywhere in the release notes , but was unable to find any such information.

Thanks for taking the time to read this far !

-Zac.

My Version of MySQL Server is
Server version: 8.0.32 MySQL Community Server - GPL Running on Mac OS

MySQL [(none)]> show variables like '%char%';
+--------------------------+-------------
| Variable_name | Value |
+--------------------------+-------------
| character_set_client | utf8mb3 |
| character_set_connection | utf8mb3 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb3 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3

The my.cnf has nothing out of the ordinary in it , I'm just setting the basedir, datadir and tmp dir values.

Options: ReplyQuote


Subject
Written By
Posted
Longtext Column Values getting hex encoded by jdbc driver
April 14, 2023 05:27PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.