18

Is there a way to enable MySQL general query logging through JDBC? The closest thing I have found through my search is the ability to log slow queries through JDBC (http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-configuration-properties.html). Maybe I should do that and set the slow query threshold to 0 ms?

I would like to log all queries through MySQL in a human-readable format and would like to specify the location where the log file should be written. I know I will take a performance hit, but my application only has one user and is simple enough that I would be surprised if the performance hit was noticeable. I would like to try it out anyway to see.

I believe another option I have is to turn on binary logging and use mysqlbinlog to convert the binary logs to a human-readable format, but it sounds like the general query log would provide a simpler means of getting what I want.

alfredough
  • 385
  • 2
  • 4
  • 10

6 Answers6

33

You can enable logging in the JDBC URL like this:

jdbc:mysql://host/db?logger=com.mysql.jdbc.log.Log4JLogger&profileSQL=true

Other logging backends are available (CommonsLogger, Slf4jLogger, JDK14Logger). I believe direct Log4J logging was dropped at some point due to licencing issues so it might not work with your version of the JDBC driver.

Naturally, you'll need the relevant logging library's JAR in your classpath, and a configuration file (log4j.properties). I would set the root level to TRACE first to see what's happening and tighten it up by log level and category once you see what's being logged.

Further reading:

HTH

Anthony O.
  • 22,041
  • 18
  • 107
  • 163
Frans
  • 3,670
  • 1
  • 31
  • 29
  • Thanks! This is is really when using blackbox/closed source frameworks that don't easily support adding logging arguments – Ted Pennings Sep 16 '13 at 16:34
  • 1
    Note that the Log4JLogger is apparently no longer included with Connector/J due to licence incompatibilities, but Slf4JLogger (which has a capital J, not lower case as suggest above) is still available and will log through log4j if it is present. – Jules Apr 12 '18 at 10:46
  • 7
    The log package has moved to `com.mysql.cj.log` these days, eg `com.mysql.cj.log.Slf4JLogger` – Jonas Bergström Nov 21 '18 at 09:11
16

Add 'logger' and 'profileSQL' to the jdbc url:

&logger=com.mysql.jdbc.log.Slf4JLogger&profileSQL=true

Then you will get the SQL statement below:

2016-01-14 10:09:43  INFO MySQL - FETCH created: Thu Jan 14 10:09:43 CST 2016 duration: 1 connection: 19130945 statement: 999 resultset: 0
2016-01-14 10:09:43  INFO MySQL - QUERY created: Thu Jan 14 10:09:43 CST 2016 duration: 1 connection: 19130945 statement: 999 resultset: 0 message: SET sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'
2016-01-14 10:09:43  INFO MySQL - FETCH created: Thu Jan 14 10:09:43 CST 2016 duration: 1 connection: 19130945 statement: 999 resultset: 0
2016-01-14 10:09:43  INFO MySQL - QUERY created: Thu Jan 14 10:09:43 CST 2016 duration: 2 connection: 19130945 statement: 13 resultset: 17 message: select 1
2016-01-14 10:09:43  INFO MySQL - FETCH created: Thu Jan 14 10:09:43 CST 2016 duration: 0 connection: 19130945 statement: 13 resultset: 17
2016-01-14 10:09:43  INFO MySQL - QUERY created: Thu Jan 14 10:09:43 CST 2016 duration: 1 connection: 19130945 statement: 15 resultset: 18 message: select @@session.tx_read_only
2016-01-14 10:09:43  INFO MySQL - FETCH created: Thu Jan 14 10:09:43 CST 2016 duration: 0 connection: 19130945 statement: 15 resultset: 18
2016-01-14 10:09:43  INFO MySQL - QUERY created: Thu Jan 14 10:09:43 CST 2016 duration: 2 connection: 19130945 statement: 14 resultset: 0 message: update sequence set seq=seq+incr where name='demo' and seq=4602
2016-01-14 10:09:43  INFO MySQL - FETCH created: Thu Jan 14 10:09:43 CST 2016 duration: 0 connection: 19130945 statement: 14 resultset: 0

The default logger is:

com.mysql.jdbc.log.StandardLogger

Mysql jdbc property list: https://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html

imxylz
  • 7,847
  • 4
  • 28
  • 25
  • In case these are your only connection parameters, you should use "?logger=com.mysql.jdbc.log.Slf4JLogger&profileSQL=true" (otherwise JDBC code would concatenate the DB name with the rest of the line) – Northern Pole Feb 14 '19 at 08:48
  • and you need slf4j-api and slf4j-simple in your classpath. – JPT Mar 10 '20 at 09:07
3

If you're using Hibernate, and perform all data access through it, you can turn on logging by setting the property hibernate.show_sql to true. This will write parameterized statements (e.g. SELECT foo.id FROM foo WHERE foo.bar = ?), though. If you need parameter values, or don't use a tool like Hibernate, you might need to have MySQL write this log. See the MySQL documentation on the general query log.

FWIW, the MySQL binary log is a means to a different end; it records changes to the data, and is used for incremental backups and/or replication. SELECT statements aren't recorded in the binary log.

EDIT: I was able to get MySQL to write the general log by adding the following two lines to my.cnf (after confirming neither variable was already set), and restarting MySQL:


general_log = 1
general_log_file=/tmp/mysql-general.log
Eric R. Rath
  • 1,939
  • 1
  • 14
  • 16
  • Eric, I haven't seen a page of official documentation by MySQL that was comprehensible, ever, and I've been using it for 5 years. I can be very specific pointing out ambiguity on that page. Can you show an example query for turning the general query log on? I don't know if I use hibernate, either. If you could tell me how to check that, I could let you know, though. – Wolfpack'08 Jul 02 '12 at 00:46
3

I ended up finding a workaround. I enable MySQL general query logging through Java by modifying MySQL global system variables at runtime with the following SQL queries.

SET GLOBAL log_output="FILE"
SET GLOBAL general_log_file="Path/File"
SET GLOBAL general_log='ON'

I recommend using forward slashes in the general_log_file path. I could not get backslashes to work, even in a Windows environment.

I disable general query logging at runtime with the following SQL query.

SET GLOBAL general_log='OFF'
alfredough
  • 385
  • 2
  • 4
  • 10
  • 1
    It looks like that would log queries to the server. In cases where you don't have login access to the server that MySQL is running on, that wouldn't be helpful. – Frans Apr 18 '18 at 07:24
1

For the recent MySQL driver versions for Spring Boot add to the JDBC connection URL:

spring.datasource.url: jdbc:mysql://localhost:49172/INTEGRATION_DB?logger=com.mysql.cj.log.Slf4JLogger&profileSQL=true

It will use Slf4J logger. If there is not such logger a standard logger can be used: logger=com.mysql.cj.log.StandardLogger

Also It is possible to use a custom logger, just implement interface com.mysql.cj.log.Log and specify a new logger in the connection URL.

v.ladynev
  • 19,275
  • 8
  • 46
  • 67
0

Using MySQL along with the mysql-connector-java-8.0.13.jar I added the profileSQL=true to my JDBC connection URL. For example:

jdbc:mysql://${HOST}:${PORT}/${SCHEMA}?autoReconnect=true&profileSQL=true

You could also add &logger=com.mysql.cj.log.StandardLogger to the URL, but is not needed because it is the default value as indicated in the documentation.

Documentation reference:

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html

The logging output was annoying to read because it contains a stack trace indicating where exactly in the code the query was run. If anyone figures out a way to exclude the stack trace please let me know.

Gabe Gates
  • 902
  • 1
  • 14
  • 19
  • StandardLogger is default, you may omit it. And the first parameter needs a ? instead of & as with any URI. – JPT Mar 10 '20 at 08:55
  • But still doesn't work for me. throws an error from `com.mysql.cj.util.LogUtils.expandProfilerEventIfNecessary(LogUtils.java:53)`. any idea? – JPT Mar 10 '20 at 09:04
  • This is part of the stack trace I was talking about in my answer. If you look deeper in the stack trace it will tell you which line of code the query was executed in. I just tried it again and initially thought there were problems, but my app still ran, and it still outputs the queries being run. If there is a solution to omit the stack trace that would be ideal. – Gabe Gates Mar 10 '20 at 17:46