Caused by: java.sql.SQLException: JDBC4 Connection.isValid() method not supported

Question

My play-framework project is running well in my local but when I am trying to deploy it in heroku I am getting following error.

2015-07-05T06:24:10.456657+00:00 app[web.1]:    at com.google.inject.Guice.createInjector(Guice.java:73)
2015-07-05T06:24:10.456817+00:00 app[web.1]:    at play.api.inject.guice.GuiceApplicationBuilder.build(GuiceApplicationBuilder.scala:93)
2015-07-05T06:24:10.456702+00:00 app[web.1]:    at com.google.inject.Guice.createInjector(Guice.java:62)
2015-07-05T06:24:10.456746+00:00 app[web.1]:    at play.api.inject.guice.GuiceBuilder.injector(GuiceInjectorBuilder.scala:126)
2015-07-05T06:24:10.456863+00:00 app[web.1]:    at play.api.inject.guice.GuiceApplicationLoader.load(GuiceApplicationLoader.scala:21)
2015-07-05T06:24:10.456909+00:00 app[web.1]:    at play.core.server.ProdServerStart$.start(ProdServerStart.scala:52)
2015-07-05T06:24:10.456951+00:00 app[web.1]:    at play.core.server.ProdServerStart$.main(ProdServerStart.scala:27)
2015-07-05T06:24:10.456994+00:00 app[web.1]:    at play.core.server.ProdServerStart.main(ProdServerStart.scala)
2015-07-05T06:24:10.457407+00:00 app[web.1]:    at com.zaxxer.hikari.pool.BaseHikariPool.addConnection(BaseHikariPool.java:441)
2015-07-05T06:24:10.457496+00:00 app[web.1]:    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
2015-07-05T06:24:10.457369+00:00 app[web.1]: Caused by: java.sql.SQLException: JDBC4 Connection.isValid() method not supported, connection test query must be configured
2015-07-05T06:24:10.457602+00:00 app[web.1]:    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
2015-07-05T06:24:10.457451+00:00 app[web.1]:    at com.zaxxer.hikari.pool.BaseHikariPool$1.run(BaseHikariPool.java:413)
2015-07-05T06:24:10.457681+00:00 app[web.1]:    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
2015-07-05T06:24:10.457812+00:00 app[web.1]:    at java.lang.Thread.run(Thread.java:745)
2015-07-05T06:24:10.457768+00:00 app[web.1]:    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
2015-07-05T06:24:11.231007+00:00 heroku[web.1]: Process exited with status 255
2015-07-05T06:24:11.243795+00:00 heroku[web.1]: State changed from starting to crashed
2015-07-05T06:24:11.245013+00:00 heroku[web.1]: State changed from crashed to starting
2015-07-05T06:24:16.259302+00:00 heroku[web.1]: Starting process with command `target/universal/stage/bin/sazors -Dhttp.port=10803`
2015-07-05T06:24:17.542815+00:00 app[web.1]: Picked up JAVA_TOOL_OPTIONS: -Xmx384m -Xss512k -Dfile.encoding=UTF-8
2015-07-05T06:24:18.643391+00:00 app[web.1]: 06:24:18,391 |-INFO in ch.qos.logback.classic.LoggerContext[default] - Could NOT find resource [logback.groovy]
2015-07-05T06:24:18.643397+00:00 app[web.1]: 06:24:18,391 |-INFO in ch.qos.logback.classic.LoggerContext[default] - Could NOT find resource [logback-test.xml]

I am using PostgreSQL as my db and slick as database access library. I tried to google it and after some research I found out this error can be caused when provided url in configuration file is incorrect or it is returning null. I tried to fix it but I couldn't resolve this error.

Here is my Procfile:

web: target/universal/stage/bin/myapp -Dhttp.port=${PORT} -Dconfig.resource=${PLAY_CONF_FILE}

and Application.conf

slick.dbs.default.driver="slick.driver.PostgresDriver$"
slick.dbs.default.db.driver="org.postgresql.Driver"
slick.dbs.default.db.url="postgres://rycmvicwkkzofh:Xt2PWggm__FQEHOeq9EoMqTOtF@ec2-54-243-132-114.compute-1.amazonaws.com:5432/d5pr4h65obtb0s"
slick.dbs.default.db.user=rycmuicwkkzofh
slick.dbs.default.db.password="copiedfromheroku"

P.S: I am working on these revisions

play framework version : 2.4.0 slick version : 3.0

EDIT: If I add slick.dbs.default.db.connectionTestQuery="SELECT 1" in my application.conf it produces new type of nullPointerException Error.

That is gone but getting new error now:
2015-07-05T12:22:49.781133+00:00 app[web.1]:    at com.google.inject.Guice.createInjector(Guice.java:73)
2015-07-05T12:22:49.781201+00:00 app[web.1]:    at com.google.inject.Guice.createInjector(Guice.java:62)
2015-07-05T12:22:49.781283+00:00 app[web.1]:    at play.api.inject.guice.GuiceBuilder.injector(GuiceInjectorBuilder.scala:126)
2015-07-05T12:22:49.781361+00:00 app[web.1]:    at play.api.inject.guice.GuiceApplicationBuilder.build(GuiceApplicationBuilder.scala:93)
2015-07-05T12:22:49.781507+00:00 app[web.1]:    at play.core.server.ProdServerStart$.start(ProdServerStart.scala:52)
2015-07-05T12:22:49.781425+00:00 app[web.1]:    at play.api.inject.guice.GuiceApplicationLoader.load(GuiceApplicationLoader.scala:21)
2015-07-05T12:22:49.781599+00:00 app[web.1]:    at play.core.server.ProdServerStart$.main(ProdServerStart.scala:27)
2015-07-05T12:22:49.781669+00:00 app[web.1]:    at play.core.server.ProdServerStart.main(ProdServerStart.scala)
2015-07-05T12:22:49.782161+00:00 app[web.1]: Caused by: java.lang.NullPointerException
2015-07-05T12:22:49.782226+00:00 app[web.1]:    at com.zaxxer.hikari.pool.BaseHikariPool.addConnection(BaseHikariPool.java:446)
2015-07-05T12:22:49.782328+00:00 app[web.1]:    at com.zaxxer.hikari.pool.BaseHikariPool$1.run(BaseHikariPool.java:413)
2015-07-05T12:22:49.782417+00:00 app[web.1]:    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
2015-07-05T12:22:49.782515+00:00 app[web.1]:    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
2015-07-05T12:22:49.783691+00:00 app[web.1]:    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
2015-07-05T12:22:49.783803+00:00 app[web.1]:    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
2015-07-05T12:22:49.783883+00:00 app[web.1]:    at java.lang.Thread.run(Thread.java:745)

Answer 1

The Play HikariCP module (look for "connectionTestQuery" config) suggests that you can set the test query as follows:

slick.dbs.default.db.connectionTestQuery="/*ping*/ select 1" // or whatever

Then the error in your stacktrace should in theory go away.

Answer 2

I believe this problem was discussed on the Play mailing list here.

The cause turned out to be that Slick does not support the DATABASE_URL format that Heroku sets. You will have to parse the DATABASE_URL into a JDBC URL. You can do this fairly easily with the heroku-jdbc library: https://github.com/jkutner/heroku-jdbc

Just add a line like this to your deps:

libraryDependencies += "com.heroku.sdk" % "heroku-jdbc" % "0.1.0"

Then you can parse the URL like so:

var jdbcURL = DatabaseUrl.extract.jdbcUrl

Then pass that URL to slick

EDIT

Actually, looks like most people are parsing the DATABASE_URL manually, and setting up slick conf with hard-coded values. This is fine, but be aware that the connection may change, and you'd have to change your settings manually.

So you'll need to change your config to this:

slick.dbs.default.db.url="jdbc:postgresql://host:port/path"

But you might want to destroy and recreate your database because you've posted your connection details in the public.

I've submitted a PR to slick to fix this: https://github.com/slick/slick/pull/1193

Answer 3

Play configuration requires jdbc url (JDBC_DATABASE_URL) and not postgres (DATABASE URL) to convert database url into jdbc url here is the way. Notice in the first line postgres is written in the next line which starts with jdbc postgresql ql is appended to the word postgres.

postgres://<username>:<password>@<host>:<port>/<dbname>

The above format is given heroku postgres. but play expects below format.

jdbc:postgresql://<host>:<port>/<dbname>?user=<username>&password=<password>

Note: In the above URL do not forget to add ql at the end jdbc:postgres + ql becomes jdbc:postgresql

Also add below line to play conf

slick.dbs.default.db.connectionTestQuery="SELECT 1" 

Finally your configuration file application.conf becomes

slick.dbs.default.driver="slick.driver.PostgresDriver$"

slick.dbs.default.db.driver="org.postgresql.Driver"

slick.dbs.default.db.url="jdbc:postgresql://rycmvicwkkzofh:Xt2PWggm__FQEHOeq9EoMqTOtF@ec2-54-243-132-114.compute-1.amazonaws.com:5432/d5pr4h65obtb0s?user=rycmuicwkkzofh&password=copiedfromheroku"

slick.dbs.default.db.user=rycmuicwkkzofh

slick.dbs.default.db.password="copiedfromheroku"

slick.dbs.default.db.connectionTestQuery="SELECT 1"