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.

Improve this question

7 Answers 7

Reset to default
35

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks! This is is really when using blackbox/closed source frameworks that don't easily support adding logging arguments
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.
The log package has moved to com.mysql.cj.log these days, eg com.mysql.cj.log.Slf4JLogger
18

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

Improve this answer

2 Comments

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)
and you need slf4j-api and slf4j-simple in your classpath.
4

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'
Improve this answer

1 Comment

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.
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
Improve this answer

1 Comment

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.
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.

Improve this answer

Comments

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.

Improve this answer

3 Comments

StandardLogger is default, you may omit it. And the first parameter needs a ? instead of & as with any URI.
But still doesn't work for me. throws an error from com.mysql.cj.util.LogUtils.expandProfilerEventIfNecessary(LogUtils.java:53). any idea?
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.
0

If you are using log4j and want to write SQL logs to a separate log file sql.log, you can configure it as follows:

appender.connectorJdbc.name = ConnectorJdbcAppender
appender.connectorJdbc.type = RollingFile
appender.connectorJdbc.append = true
appender.connectorJdbc.fileName = /xxx/log/connectorJdbc.log
logger.connectorJdbc.name = MySQL
logger.connectorJdbc.level = INFO
logger.connectorJdbc.appenderRef.connectorJdbc.ref = ConnectorJdbcAppender

It's important to note that the log name here is MySQL.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.