Play Cannot connect to (PostgreSQL) database [default]

Question

I'm getting

CreationException: Unable to create injector, see the following errors: 
1) Error in custom provider, Configuration error: Configuration error[Cannot connect to database [default]]
[...]
2) Error in custom provider, Configuration error: Configuration error[Cannot connect to database [default]]

Originally I had MySQL database used with Play Framework app which worked, but I wanted to change it to PostgreSQL and that's when problems started appearing. I've installed them both on my Ubuntu computer and changed play config to use Postgres (added "postgresql" % "postgresql" % "9.1-901-1.jdbc4", to build.sbt and changed db.default properties to reference Postgres). Exact application.conf is:

db.default.driver="org.postgresql.Driver"
db.default.url="jdbc:postgres://localhost:5432/playdb"
db.default.username="luka"
db.default.password="test"

I have manually created user luka with password test and database playdb. I have tried with postgres user as well with no avail.

What bugs me more, MySQL won't work either now with the same error. I have created new project with only modifying db.default params in conf and it fails in the same manner. Commenting out application.conf makes it go away, so that's definitely the problem. I have checked PostgreSQL logs (/var/log/postgresql/postgresql-9.4-main.log) and only line that doesn't seem right is [unknown]@[unknown] LOG: invalid length of startup packet. It appears multiple times, but not every time I refresh project (I'm not even sure it's related). I have removed mysql-server from my PC hoping everything will magically fix itself. It didn't.

Ideas?

I'm using Play 2. 4. 6 and IntelliJ IDEA 15. Project is created using Activator and importing sources to IDEA (using SBT model).

EDIT I'm also getting errors when I add db.default.hikaricp.connectionTestQuery = "SELECT 1" to my application.conf.

Solution 1

So, the definite answer is:

First, there's a mistake in the database url, it should be db.default.url="jdbc:postgresql://localhost:5432/playdb" as chabeee pointed out. It's the only correct format for db.default.url (so no jdbc:postgresql://username:pasword:localhost/dbname or similar, as I've seen suggesting on other places).

Second, more tricky, is that there's a bug in the driver as Salem pointed out and workaround is adding db.default.hikaricp.connectionTestQuery = "SELECT 1" to application.conf.
However, that bug is fixed (well, that workaround is implemented) in versions more recent than 9.1-903. The catch is, after version 9.1-901 postgresql changed its groupID in the repos and now it's referenced by org.postgresql. A better solution than the workaround would be updating dependencies to "org.postgresql" % "postgresql" % "9.4-1206-jdbc4" (current version, MVNrepository). Append the appropriate jdbc version to the most recent PostgreSQL driver (4 for Java 6, 41 for Java 7, 42 for Java 8).

My final application.conf:

db.default.driver="org.postgresql.Driver"
db.default.url="jdbc:postgresql://localhost/playdb" #the port is optional
db.default.username="luka"
db.default.password="test"

And libraryDependencies in build.sbt:

libraryDependencies ++= Seq(
  jdbc,
  "org.postgresql" % "postgresql" % "9.4-1206-jdbc42",
  cache,
  javaWs
)

UPDATE 2017: I have only now noticed that not long after writing this answer they changed versioning scheme and removed -jdbc[code] fragment, replacing it with .jre6, .jre7 or nothing, apparently meaning it's meant for the latest Java version (I haven't found anything supporting this claim, but it works). Yet again in February 2017 they changed the version scheme again and jumped from major version 9 to 42, making the current version (as of 17 July 2017) denoted by "org.postgresql" % "postgresql" % "42.1.3" (or, accordingly, "org.postgresql" % "postgresql" % "42.1.3.jre7" / "org.postgresql" % "postgresql" % "42.1.3.jre6")

Solution 2

From your stacktrace

JDBC4 Connection.isValid() method not supported, connection test query must be configured

Basically this is a problem with your driver (it does not support that method), but you should be able to work around it specifying a test query in your application.conf:

play.db.default.hikaricp.connectionTestQuery = "SELECT 1"

Solution 3

Try to replace this line:

db.default.url="jdbc:postgres://localhost:5432/playdb"

to this:

db.default.url="jdbc:postgresql://localhost:5432/playdb"

Hope, it helps.

Comments

[Luke]

That does indeed solve the original error, however it creates a new one: play.db.default has type OBJECT rather than STRING (it's on that line, I checked)

[Salem]

@Luke Try to use db.default.hikaricp.connectionTestQuery = "SELECT 1" instead

[Luke]

I've tried that also. At first I thought it gives the same error, however the stack trace is different now: hastebin.com/hazoraradi.txt

[Luke]

My db.default.url is already jdbc:postgres://localhost:5432/playdb

[chabeee]

change it to postgresQL, so you are missing the 'q' and the 'l' characters

[Luke]

This actually solved the problem, but also Salem's workaround needs to be applied. I swear I have just copy-pasted that part, didn't cross my mind it'd be inaccurate.

[slfan]

This is more like a comment than an answer to the question

[Luke]

Yes, comments are used for asking for clarification, answers should answer the question, not ask more of them. But to answer you, no, I'm on Ubuntu (and have solved the issue).

[Silas]

The workaround (over the bug you told about) made it work. Thanks!

[Serendipity]

@Luke That didn't work for me sadly. I assumed there was some other work around.