mssql-jdbc:12.6.0.jre11 - 'sp_getapplock' expects parameter '@LockMode'
driverpt opened this issue · 7 comments
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
JDBC trace logs
N/A
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).
@driverpt I'm able to repro the issue. I'm still looking into it... What was the prior driver version that worked for you?
I'm away from the computer now. But 12.3 or 12.4, can't recall exactly
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.
@tkyc, should we create a PR for Flyway to change the logic? Is this the new "standard" going forward?
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.
@driverpt we will be releasing a hotfix release for this soon, thank you for alerting us.