flyway: Migrations don't run on 9.1.0 (possible too many connections required issue?)

Which version and edition of Flyway are you using?

My dependency updating program automatically opened an upgrade from 9.0.4 to 9.1.0 for me

If this is not the latest version, can you reproduce the issue with the latest one as well? (Many bugs are fixed in newer releases and upgrading will often resolve the issue)

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

Java library

Which database are you using? (Type & version)

Postgres 14.4

Which operating system are you using?

Java on Ubuntu Focal (Azure Zulu JDK docker images)

What did you do? (Please include the content causing the issue, any relevant configuration settings, the SQL statement(s) that failed (if any), and the command you ran)

Usually during application startup migrations are applied, this is a new application on a blank database

What did you expect to see?

Successful migrations

What did you see instead?

[2022-08-03T09:23:45,240] [INFO] o.f.c.i.c.DbValidate - Successfully validated 333 migrations (execution time 00:00.110s)

[2022-08-03T09:23:55,125] [WARN] c.z.h.p.ProxyLeakTask - Connection leak detection triggered for org.postgresql.jdbc.PgConnection@55a2f995 on thread hc-io-28, stack trace follows

[2022-08-03T09:24:08,568] [DEBUG] c.z.h.p.HikariPool - hc-cp - Pool stats (total=1, active=1, idle=0, waiting=1)
[2022-08-03T09:24:08,568] [DEBUG] c.z.h.p.HikariPool - hc-cp - Fill pool skipped, pool is at sufficient level.
[2022-08-03T09:24:15,242] [DEBUG] c.z.h.p.HikariPool - hc-cp - Timeout failure stats (total=1, active=1, idle=0, waiting=0)

It’s worth noting that all of our test environments like this are limited to one database connection to ensure that we’re never accidentally nesting transactions.

Could Flyway have made a change in this release that now requires 2 connections active at the same time to complete migrations? (Just a guess)

On my local machine, capped at 3 connections, migrations start running before a connection leak occurs, and I have two active connections stuck in active indefinitely

[2022-08-03T10:31:32,783] [debug] c.z.h.p.HikariPool - hc-cp - Fill pool skipped, pool is at sufficient level.
[2022-08-03T10:32:02,783] [debug] c.z.h.p.HikariPool - hc-cp - Before cleanup stats (total=3, active=2, idle=1, waiting=0)
[2022-08-03T10:32:02,784] [debug] c.z.h.p.HikariPool - hc-cp - After cleanup  stats (total=3, active=2, idle=1, waiting=0)
[2022-08-03T10:32:02,784] [debug] c.z.h.p.HikariPool - hc-cp - Fill pool skipped, pool is at sufficient level.

About this issue

Original URL
State: closed
Created 2 years ago
Comments: 22 (10 by maintainers)

Commits related to this issue

#3491 use one connection for session locks and two for transactional locks in postgres — committed to flyway/flyway by DoodleBobBuffPants 2 years ago
Gi hikari migration config max to connections i stedet for 1 * I følge denne https://github.com/flyway/flyway/issues/3491 ser det ut til at Flyway etter 9.1 krever to connections for å kunne kjøre mi... — committed to navikt/helse-spesialist by chsko 2 years ago

Most upvoted comments

I’m telling lies, it works great. Thanks again @DoodleBobBuffPants

henricook on Aug 3, 2022

Version 9.1.2 should be treated as a breaking change version. I spent 4 hours figuring out why my migrations do not run anymore, after updating from 9.0.4 only to come here, go through all the comments and realizing I have to increase the pool size from 1 to 2.

avihoo on Mar 29, 2023

Thanks for asking. It’s technically very easy for us to change our max allowed connections. We keep our local and dev (CI) environments at 1 max connection so that we can easily see if any of us accidentally nest transactions (i.e. one API call ends up requiring two connections simultaneously if our transactions are accidentally nested). So it’s very specific I guess to our use of JDBC and the Doobie library for Scala. So the negative for us is that at two connections we’re less likely to see those if they happen. That said, the last one we noticed was probably over a year ago so they’re not exactly common.

henricook on Aug 3, 2022

That’s correct - for PostgreSQL we now have two active connections in order to support this request, the underlying reason being that we use transactional locks instead of session-level locks

You can revert to the old behaviour by setting flyway.postgresql.transactional.lock=false

DoodleBobBuffPants on Aug 3, 2022