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

Answer 1

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/*!*/

;