Connection timeout after upgrading to Central v1.2

Support
1

Hello, I experience the same symptom but the "service" log shows a connection timeout.

Is there any way how to find out where it tries to connect?

Thanks

::ffff:172.18.0.9 - - [25/May/2021:15:02:59 +0000] "GET /v1/sessions/restore HTTP/1.0" 404 76
at processTicksAndRejections (internal/process/task_queues.js:85:5)ConnectionError: Connection terminated due to connection timeout
at Object.createConnection (/usr/odk/node_modules/slonik/dist/src/factories/createConnection.js:54:23)

::ffff:172.18.0.9 - - [25/May/2021:15:03:23 +0000] "POST /v1/sessions HTTP/1.0" 500 357

3

Hi @Jan_Vrana! As part of v1.2, we've changed how we connect to the database. We construct a Postgres connection string by inserting the values for host, user, password, and database name specified in the configuration file:

postgres://user:password@host/dbname
  • Do you specify all four of these values in your configuration file?
  • Does your password contain a special character like @?
1 Like
4

Hi Matthew,

Yes, I do specify all necessary values (I believe). Password does contain special characters (e.g. "*", ")", "(") but actually not "@"

My files/service/config.json.template:

{
  "default": {
    "database": {
      "host": [hostname],
	  "port": 25060,
      "user": [username],
      "password": [password],
      "database": [dbname],
	  "ssl": true
    },
	"email": {
...

Thanks Jan

1 Like
5

I think the main issue is that the properties port and ssl were allowed in v1.1, but are now ignored. I actually don't see anywhere that we document port and ssl. Did you find those in the Knex.js documentation, or did you just notice that they worked?

To specify the port, I think you can include that in the host property.

We don't have a good way right now to specify named parameters, including ssl=true. If you must specify ssl=true, you could try shoving it into the database name, since that comes last in the connection string: "database": "mydb?ssl=true". However, I'm not sure I recommend that, because the database property is intended for the database name, and this approach might not be supported in a future version.

I can't tell whether there's an issue with your password. Central v1.2 does not currently encode the password for the connection string, so there can be an issue if your password contains a symbol with special meaning. In that case, you should either change your password to something without the symbol, or percent-encode the symbol.

For more background on all this, check out the documentation on Postgres connection URIs: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING

I'll also file a GitHub issue so that we can think more about encoding the password and specifying named parameters.

2 Likes
6

Hi Matthew,

Thanks for the hint, this finally worked:

{
  "default": {
    "database": {
      "host": "hostname:25060",
      "user": "username",
      "password": "password",
      "database": "database?ssl=true"
    },
	"email": {

so it looks like that namely additional named parameters will be necessary to support.

Thanks Jan

3 Likes
7

Seconded, thanks for figuring that out, Jan!
I faintly remember having an SSL parameter in my db config, and an @ in the db username.

8

As of v1.3, it should now be easier to specify the options needed to connect to a custom database server. @Florian_May and I had an in-depth conversation about this as it relates to SSL (here). One highlight of that conversation is that given how Central connects to the database, when you specify true for ssl, that will automatically specify false for rejectUnauthorized.

Thank you for reporting this issue, @Jan_Vrana!

1 Like
9

Thanks for the change, I confirm the connection to the external database can now be configured in a more straightforward (although not documented - at least at https://docs.getodk.org/central-install-digital-ocean/#using-a-custom-database-server) way.

for 1.2 a more advanced configuration was:

    "database": {
      "host": "[hostname]:[port]",
      "user": "[dbuser]",
      "password": "[pwd]",
      "database": "[dbname]?ssl=true"
    },

whereas for 1.3 it is:

    "database": {
      "host": "[hostname]",
      "port": "[port]",
      "user": "[dbuser]",
      "password": "[pwd]",
      "database": "[dbname]",
      "ssl": true
    },

unfortunatelly 1.3 can't correctly read the 1.2 config.

Are there any other config attributes that may be used to configure a connection to an external DB?

Thanks

1 Like
10

That's a good point that we should document the available options. I've filed an issue for that here: https://github.com/getodk/docs/issues/1403

I can confirm that. The approach described for v1.2 was more of a workaround, and now that other options are available with v1.3, those options should be used. Actually, @Florian_May and I had trouble in the other thread even using the workaround.

That's the full list of options right now. What's changed is that port is supported and true can be specified for ssl. Using other options should result in an error message, which will hopefully make it clearer what's supported and what's not.