mssql-jdbc:12.6.0.jre11 - 'sp_getapplock' expects parameter '@LockMode'

[driverpt]

Driver version

Provide the JDBC driver version (e.g. 12.6.0.jre11).

SQL Server version

2017

Client Operating System

Docker

JAVA/JVM version

17

Table schema

N/A

Problem description

We're currently using Spring Boot w/ Flyway Migration (You can use TestContainers to debug the issue). We recently upgraded to 12.6.0-jre11 Driver and this caused Flyway to break.

The Query that Flyway uses seems to be fine, but probably something changed in the Driver Library that broke it.

Please check the link to the source code of the query.

Expected behavior

Flyway to execute

Actual behavior

Check Stack Trace

Error message/stack trace

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Procedure or function 'sp_getapplock' expects parameter '@LockMode', which was not supplied.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:261)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1752)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:657)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:576)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7739)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4384)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:293)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:263)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:553)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at org.flywaydb.core.internal.jdbc.JdbcTemplate.execute(JdbcTemplate.java:190)
at org.flywaydb.database.sqlserver.SQLServerApplicationLockTemplate.execute(SQLServerApplicationLockTemplate.java:59)
... 10 more

Procedure or function 'sp_getapplock' expects parameter '@LockMode', which was not supplied.
com.microsoft.sqlserver.jdbc.SQLServerException: Procedure or function 'sp_getapplock' expects parameter '@LockMode', which was not supplied.
at app//com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:261)
at app//com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1752)
at app//com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:657)
at app//com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:576)
at app//com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7739)
at app//com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4384)
at app//com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:293)
at app//com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:263)
at app//com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:553)
at app//com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at app//com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at app//org.flywaydb.core.internal.jdbc.JdbcTemplate.execute(JdbcTemplate.java:190)
at app//org.flywaydb.database.sqlserver.SQLServerApplicationLockTemplate.execute(SQLServerApplicationLockTemplate.java:59)
at app//org.flywaydb.database.sqlserver.SQLServerConnection.lock(SQLServerConnection.java:93)
at app//org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.create(JdbcTableSchemaHistory.java:104)
at app//org.flywaydb.core.Flyway.lambda$migrate$0(Flyway.java:199)
at app//org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:200)
at app//org.flywaydb.core.Flyway.migrate(Flyway.java:147)
at ####ifx.utils.Database.migrate(Database.kt:122)
at ####DatabaseTestBaseSimulatorTestDb.initTest(DatabaseTestBaseSimulatorTestDb.kt:12)
at java.base@17.0.10/java.lang.reflect.Method.invoke(Method.java:568)
at java.base@17.0.10/java.util.ArrayList.forEach(ArrayList.java:1511)
at java.base@17.0.10/java.util.ArrayList.forEach(ArrayList.java:1511)

Any other details that can be helpful

Source Code

Flyway Issue

JDBC trace logs

N/A

[David-Engel]

Can you collect trace logs from the driver?
https://learn.microsoft.com/en-us/sql/connect/jdbc/tracing-driver-operation?view=sql-server-ver16#enabling-tracing-programmatically

The error itself indicates that the application is simply missing a parameter when executing sp_getapplock against the server. However, the Flyway code you linked to clearly includes it.

Logs will verify what is actually being passed to the driver. This could be a Flyway issue or a driver regression (a pretty bad one, if so).

[tkyc]

@driverpt I'm able to repro the issue. I'm still looking into it... What was the prior driver version that worked for you?

[driverpt]

I'm away from the computer now. But 12.3 or 12.4, can't recall exactly

[tkyc]

This is a regression because of changed logic on how the driver executes CallableStatements. But as of now, in 12.6.0, when calling stored procedures, the driver is expecting stored procedures to always be parameterized.

So a workaround would be:

            CallableStatement stmt = conn.prepareCall("EXEC sp_getapplock ?,?,?,?");
            stmt.setString(1, "resource");
            stmt.setString(2, "Exclusive");
            stmt.setString(3, "Session");
            stmt.setString(4, "3600000");

Otherwise I recommend remaining on 12.4 for now. I'm currently working on a fix for this.

[driverpt]

@tkyc, should we create a PR for Flyway to change the logic? Is this the new "standard" going forward?

[David-Engel]

should we create a PR for Flyway to change the logic? Is this the new "standard" going forward?

That shouldn't be necessary. This is a driver regression that we will make a fix for. We were actually surprised that there are no existing tests covering this type of query. We'll be adding tests for that, too, to ensure it's not regressed again in the future.

[lilgreenbird]

@driverpt we will be releasing a hotfix release for this soon, thank you for alerting us.