How to log the complete query statement including insert '?' parameters on mariaDB

Question

I've a mariadb:10.10.6 running under a docker container. I'm running some tests in a java microservice that uses that database and I would like to log the exact query that the java client is sending to database.

i've tried setting the config file as:

general_log = ON general_log_file = /var/log/mysql/general.log log_slow_verbosity = full

but still the geneeral log file comes with ? in the insert statement and i've many reasons to believe that it is compacting it:

something like

 254 Close stmt 254 Prepare insert into announce_images (announce_id, image) values (?, ?) 254 Execute insert into announce_images (announce_id, image) values (?, ?)

I'm trying to use the java persistence provider to make a batch insert and I would very much like to see a log showing

insert into announce_images (announce_id, image) values (?, ?) (?, ?) (?, ?) (?, ?) (?, ?) (?, ?) (?, ?) (?, ?) (?, ?)

so i can guarantee that the persistence provider is really making a batch insert

how can i get a more complete accurate log on mariadb

Would it work for you if you log your queries on application level and not database level? — Asgar
– Asgar, Commented Dec 14, 2023 at 13:57
No, because the way hibernate logs the queries doesn't exactly reflect in database — Rafael Lima
– Rafael Lima, Commented Dec 14, 2023 at 14:09
Why would they not reflect exactly? If you configure hibernate, show sql to true, it should work — Asgar
– Asgar, Commented Dec 14, 2023 at 14:26

Georg Richter · Accepted Answer · 2023-12-15 07:36:09Z

Like MariaDB Connector/Python the MariaDB Java Connector uses the binary protocol (prepared statements) for batch operations.

The use of binary protocol is up to 10 times faster by reducing the number of calls and subsequently the number of round trips to the database an SQL statement requires:

First it sends a COM_STMT_PREPARE command (containing the SQL statement) followed by COM_STMT_EXECUTE command (containing the number of rows, followed by all data). This method doesn't require a rewrite of INSERT statement (using VALUES (),(),() syntax) and works also for DELETE and UPDATE batch operations.

To check if the persistence provider is really making a batch insert there are two possible solutions:

Disable option useBulkStmts (Since version 3.0.0 useBulkStmts is enabled by default. ) if you don't care about performance loss.
Instead of checking the general log, check the binary log (row format) with mysqlbinlog command:

Example (batch with 2 rows):

#231215 7:51:09 server id 1 end_log_pos 10563 CRC32 0x1bc95e3a Annotate_rows: #Q> insert into t1 values (?) #231215 7:51:09 server id 1 end_log_pos 10608 CRC32 0xad5a2a5f Table_map: `test`.`t1` mapped to number 63 # at 10608 #231215 7:51:09 server id 1 end_log_pos 10651 CRC32 0x7776e429 Write_rows: table id 63 flags: STMT_END_F BINLOG ' Xfd7ZRMBAAAALQAAAHApAAAAAD8AAAAAAAEABHRlc3QAAnQxAAEDAAFfKlqt Xfd7ZRcBAAAAKwAAAJspAAAAAD8AAAAAAAEAAf/+AQAAAP4CAAAAKeR2dw== '/*!*/; # Number of rows: 2 # at 10651 #231215 7:51:15 server id 1 end_log_pos 10682 CRC32 0xd80bfbaf Xid = 315 COMMIT/*!*/

;

what should i expect to see inn the binlog if is batch and what i should see in a non batch?
As you can see: Number of rows: 2 = 2 rows inserted via batch.

Collectives™ on Stack Overflow

How to log the complete query statement including insert '?' parameters on mariaDB

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related