;; for logging JDBC queries: https://p6spy.readthedocs.io/en/latest/install.html#generic-instructions
[p6spy/p6spy "3.9.1"]Talking to a relational database with Clojure is relatively easy. There’s a great library next.jdbc for that. In legacy applications, you may encounter an older library, clojure.java.jdbc.
But sometimes, you need to look at the data flowing into/from your database. Or you need to examine the queries your application is making to troubleshoot a performance problem.
p6spy is a great tool to intercept and log database queries. It logs all JDBC transactions for any Java/JVM application.
It is as easy to use from Clojure, as it’s from Java.
First, add p6spy as a dependency:
leiningen:
;; for logging JDBC queries: https://p6spy.readthedocs.io/en/latest/install.html#generic-instructions
[p6spy/p6spy "3.9.1"]or deps.edn:
;; for logging JDBC queries: https://p6spy.readthedocs.io/en/latest/install.html#generic-instructions
p6spy/p6spy {:mvn/version "3.9.1"},Second, use P6SpyDriver instead of your regular JDBC driver. This is how we do it:
(defn- spy-driver
"If db query logging is enabled, use p6spy driver instead of the original."
[original-driver]
(if (:codescene-log-db-query env)
"com.p6spy.engine.spy.P6SpyDriver"
original-driver))
;; See https://p6spy.readthedocs.io/en/latest/integration.html#connection-url-way
(defn- spy-url
"If db query logging is enabled, transforms the ordinary jdbc url like 'jdbc:mysql://...'
into the one handled by p6spy, e.g. 'jdbc:p6spy:mysql://...'."
[original-url]
(cond-> original-url
(:codescene-log-db-query env) (str/replace-first "jdbc:" "jdbc:p6spy:")))
#_(spy-url "jdbc:mysql://localhost:3306")
;; => "jdbc:p6spy:mysql://localhost:3306"
(defn get-db-spec []
(let [host (get-property-value :db-host)
port (get-property-value :db-port)
database (get-property-value :db-name)
engine-specs [{:db-type "h2"
:driver-class-name (spy-driver "org.h2.Driver")
:jdbc-url (spy-url (format "jdbc:h2:file:%s;MULTI_THREADED=1;MODE=MYSQL" (get-property-value :db-physical-path)))}
{:db-type "mysql"
:driver-class-name (spy-driver "com.mysql.cj.jdbc.Driver")
:jdbc-url (spy-url (format "jdbc:mysql://%s:%s/%s" host (or port "3306") database))}
;; maria-db left out snce it doesn't support p6spy
]
...To configure the logging, we need to create the spy.properties file in our "resources" folder.
Here’s a sample configuration file that I used:
# https://p6spy.readthedocs.io/en/latest/configandusage.html
# determines if property file should be reloaded
# Please note: reload means forgetting all the previously set settings (even those set during runtime - via JMX)
# and starting with the clean table (default is false)
reloadproperties=true
# determines how often should be reloaded in seconds (default is 60)
reloadpropertiesinterval=10
# driverlist isn't really needed for MySQL but it didn't work for H2 without it
# Note: mariadb doesn't work, even after listing it here.
driverlist=org.h2.Driver,com.mysql.jdbc.Driver,org.mariadb.jdbc.Driver
dateformat=dd-MM-yy HH:mm:ss:SS
logfile=codescene.db.log
logMessageFormat=com.p6spy.engine.spy.appender.CustomLineFormat
# Custom log message format used ONLY IF logMessageFormat is set to com.p6spy.engine.spy.appender.CustomLineFormat
# default is %(currentTime)|%(executionTime)|%(category)|connection%(connectionId)|%(sqlSingleLine)
customLogMessageFormat=%(currentTime)|%(executionTime)|%(category)|connection%(connectionId)|%(sqlSingleLine)
# prints a stack trace for every statement logged (default is false)
# very handy for debugging but very verbose
#stacktrace=trueYou can find the description of p6spy configuration online: Configuration and Usage
At the moment, it seems that p6spy doesn’t support MariaDB - at least, I couldn’t make it work:
Execution error at com.zaxxer.hikari.util.DriverDataSource/<init> (DriverDataSource.java:110).
Driver org.mariadb.jdbc.Driver claims to not accept jdbcUrl, jdbc:p6spy:mariadb://localhost:3307/codescene?allowMultiQueries=true&serverTimezone=UTCFor the CodeScene On-Prem product, you can enable DB query logging (powered by p6spy)
via CODESCENE_LOG_DB_QUERY=true.
See Logging database queries (MySQL or embedded H2 db)
You will then find the detailed DB query log in the codescene.db.log file.
Here’s a small excerpt from codescene.db.log (see section CodeScene DB Query Log)
22-12-22 06:59:51:317|0|statement|connection375|SELECT * FROM mail_server_configuration
22-12-22 06:59:51:318|0|statement|connection375|select id, project_id, plan, type, address, user, provider_id from report_configurationEnabling stacktraces in p6spy configuration can be very handy. You can then clearly see where do the queries come from.
You need to enable this in the spy.properties config file:
# prints a stack trace for every statement logged (default is false)
# very handy for debugging but very verbose
stacktrace=trueThen you can see a stacktrace for each logged query:
12-12-22 13:22:48:746|3|statement|connection0|SELECT * FROM codescene_enterprise2.DATABASECHANGELOG ORDER BY DATEEXECUTED ASC, ORDEREXECUTED ASC
java.lang.Exception
at com.p6spy.engine.common.P6LogQuery.doLog(P6LogQuery.java:126)
...
at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:111)
at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java)
at liquibase.executor.jvm.JdbcExecutor$QueryStatementCallback.doInStatement(JdbcExecutor.java:511)
...
at liquibase.Liquibase.validate(Liquibase.java:2324)
...
at clojure.java.jdbc$db_transaction_STAR_.invokeStatic(jdbc.clj:807)
...
at myapp.database.db$migrate.invokeStatic(db.clj:142)
...
at java.lang.Thread.run(Thread.java:748)Warning: Especially with stacktraces, the db log file can grow large very quickly. Make sure you disable the logging once you are done with investigation.
Most database come with their own facilities for detailed query logging. One such option is Slow Query Log for MySQL. Another one is The General Query Log
This is a very useful option and you can safely enable it in production for queries slower than ~1 second. The downside is that it may be hard to correlate the queries to the application code or to discover all the queries (the faster ones) that the app is doing.
On the other hand, it can give you additional information useful for understanding the performance of the queries. So it’s a good idea to use this as a complement to p6spy.
Clojure REPL is an invaluable tool and you can use it to manually replicate specific flow (perhaps a route handler)
in your application.
Capture the data you need in def-s, run the top-level function triggering the flow
and observe the generated queries via p6spy.
CodeScene On-Prem db logging: Logging database queries (MySQL or embedded H2 db)
MySQL Query Logging