Not logged when using jdbc driver

[noxomu]

Hi,

I have a web application (tomcat) running with jdbc connector.
I'd like to log select statement between tomcat and mysql, but not logged.
What variables should I set?

In addition, when using mysql console session (/usr/bin/mysql) and Navicat are both ok. (select statement is logged)

Regards,
Noxomu

mysql> status

mysql Ver 14.14 Distrib 5.1.66, for redhat-linux-gnu (i386) using readline 5.1

Connection id: 6
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.1.66-log Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: cp932
Db characterset: cp932
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql2/mysql2.sock
Uptime: 13 min 47 sec

Threads: 2 Questions: 104 Slow queries: 0 Opens: 50 Flush tables: 1 Open tables: 43 Queries per second avg: 0.125

nm /usr/lib/mysql/plugin/libaudit_plugin.so | grep audit_plugin_version

0003ac80 r audit_plugin_version_1_0_9_585

AUDIT Plugin system variables

plugin-load=AUDIT=libaudit_plugin.so
audit_json_file=1
audit_json_log_file=/var/log/mysql2/mysqld-audit-log.json
audit_json_socket=0
audit_json_file_sync=0
audit_record_cmds=select
audit_record_objs=*

[glicht]

Try removing: audit_record_cmds=select

If you don't set audit_record_cmds all commands will be logged. After removing this you should see all activity logged. Examine what activity is actually coming form tomcat.

[noxomu]

Thank you for your reply.

After removing audit_record_cmds, nothing was logged. (except header)

I need more time to examine actual activity coming from tomcat.
I will confirm that early next week and let you know.

[noxomu]

I confirmed tcpdump.

Navicat's select statement use COM_QUERY (0x3), and was logged.
Tomcat's select statement use COM_STMT_PREPARE (0x16), and was not logged.

In case using COM_STMT_PREPARE, bool ThdSesData::startGetObjects() always return false.
So tomcat's select statement was not logged, I think.

Is this by design?
I'm afraid mysql-audit plugin need consider other than COM_QUERY.

[glicht]

Thanks for the detailed info. We are investigating this and will update soon.

[glicht]

Hi,

It seems that when using prepared statements the objects are not populated. We are still checking this out to see what versions are affected by this.

Since you have the following setting: audit_record_objs=* statements which don't contain an object (as in the case of prepared statements) are not logged.

Try removing the setting: audit_record_objs=*. You should then be getting the statements from Tomcat, only without the objects list.

[noxomu]

Thanks for investigating.

After removing audit_record_objs, tomcat's select statement was logged.
However, I'd like to log specified tables, actually. (e.g., audit_record_objs=database.table1,database.table2,database.table3)

To simplify, I used audit_record_objs=*.

My confirmation result is below. Prepared statement was logged when version is 1.0.3.

mysql-audit plugin version	prepared statement (tomcat)
audit-plugin-mysql-5.1-1.0.3-371	logged
audit-plugin-mysql-5.1-1.0.4-459 audit-plugin-mysql-5.1-1.0.5-479 audit-plugin-mysql-5.1-1.0.6-491 audit-plugin-mysql-5.1-1.0.7-509 audit-plugin-mysql-5.1-1.0.8-527 audit-plugin-mysql-5.1-1.0.9-585 audit-plugin-mysql-5.1-1.1.0-606	not logged

[KurtMar]

Hi,

I'd like to say that I too have been hit by this issue. Prepared statements are not logged when audit_record_objs is set. When it is not set and I include "Execute" in audit_record_cmds, they are logged twice. An easy way to test this is to run an update query with MySQL Workbench.

This seems like the perfect tool for us, but we really need to filter by object, so would you be so kind as to address this issue?

MariaDB 10.1.17, Audit plugin 1.0.9-585, MySQL Workbench 6.3.5

[aharonrobbins]

We've been working on this issue and think we have it fixed. Please check the latest dev-snapshot release.

Thanks.

[aharonrobbins]

We just made a release. The released plugin should handle JDBC connections properly. I'm closing the issue. Thanks.