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

Asked 5/7, 2015 at 7:24 Answered 9/9, 2016 at 9:5

Solved scala heroku heroku-postgres playframework-2.4 slick-3.0

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)

Acceptation answered 5/7, 2015 at 7:24 Comment(4)

See if you can specify a cheap test query on the connection, e.g. SELECT 1. The property will be called something like connectionTestQuery. Related and here – Nimble 5/7, 2015 at 7:30

@StuartLC: Could you please elaborate more, where do I need to specify this cheap test query? – Acceptation 5/7, 2015 at 7:35

Can you verify which version of postgresdriver you are using ? The latest ones should have fixed this. – Catgut 5/7, 2015 at 8:59

In my local it is pg_ctl (PostgreSQL) 9.3.5, I checked in heroku it is using PostgreSQL 9.4.4 and in my build.sbt I have added "org.postgresql" % "postgresql" % "9.4-1201-jdbc41" – Acceptation 5/7, 2015 at 9:9

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

Unconditional answered 6/7, 2015 at 14:43 Comment(1)

Note that heroku now provides a JDBC_DATABSE_URL – Silvertongued 5/3, 2016 at 2:13

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.

Limelight answered 5/7, 2015 at 11:0 Comment(2)

I added that line but now getting nullPointerException. Please look into question description, I have updated question details with my new error. – Acceptation 5/7, 2015 at 12:32

Have you got the PostgreSQL database setup on Heroku? Is the app using the environment variable with the PostgreSQL URL in the Procfile? playframework.com/documentation/2.4.x/ProductionHeroku – Limelight 5/7, 2015 at 15:50

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

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

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

Unconditional answered 6/7, 2015 at 14:43 Comment(1)

Note that heroku now provides a JDBC_DATABSE_URL – Silvertongued 5/3, 2016 at 2:13

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"

Breakout answered 6/4, 2016 at 10:28 Comment(0)

I had the same problem and after upgrading to HikariCP-2.4.0 and Postgresql-9.4 the issue was resolved :)

Hectocotylus answered 10/5, 2016 at 9:13 Comment(0)

Use

slick.dbs.default.driver="slick.driver.PostgresDriver$"
slick.dbs.default.db.dataSourceClass = "slick.jdbc.DatabaseUrlDataSource"
slick.dbs.default.db.driver="org.postgresql.Driver"
slick.dbs.default.db.url=${JDBC_DATABASE_URL}

Flita answered 9/9, 2016 at 9:5 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags