SpringBoot+Flyway执行mvn clean install时sp_getapplock参数缺失报错

环境配置 #

• Docker部署的SQL Server：mcr.microsoft.com/mssql/server:latest
• 开发工具：IntelliJ IDEA
• 技术栈：SpringBoot + Maven

问题描述 #

执行mvn clean install时Flyway运行失败，控制台报错显示：无法获取SQL Server应用锁，存储过程/函数'sp_getapplock'期望参数'@LockMode'但未提供。具体报错栈信息如下：

Caused by: org.flywaydb.core.internal.exception.FlywaySqlException: Unable to acquire SQL Server application lock
---------------------------------------------
SQL State  : S0004
Error Code : 201
Message    : Procedure or function 'sp_getapplock' expects parameter '@LockMode', which was not supplied.
    at org.flywaydb.database.sqlserver.SQLServerApplicationLockTemplate.execute(SQLServerApplicationLockTemplate.java:63)
    at org.flywaydb.database.sqlserver.SQLServerConnection.lock(SQLServerConnection.java:93)
    at org.flywaydb.core.internal.schemahistory.JdbcTableSchemaHistory.create(JdbcTableSchemaHistory.java:104)
    at org.flywaydb.core.Flyway.lambda$migrate$0(Flyway.java:185)
    at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:213)
    at org.flywaydb.core.Flyway.migrate(Flyway.java:140)
    at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:66)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1822)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1771)
    ... 40 more
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:1730)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:653)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:572)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7706)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4201)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:289)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:263)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:549)
    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:182)
    at org.flywaydb.database.sqlserver.SQLServerApplicationLockTemplate.execute(SQLServerApplicationLockTemplate.java:59)
    ... 48 more

配置文件 #

application.yml #

spring:
  # Database Configuration
  datasource:
    url: jdbc:sqlserver://localhost:1433;encrypt=true;trustServerCertificate=true;databaseName=db
    username: sa
    password: password@1
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
  # Hibernate properties
  jpa:
    properties:
      hibernate:
        dialect: org.hibernate.dialect.SQLServerDialect
    hibernate:
      ddl-auto: none
  # Flyway migrations
  flyway:
    locations: classpath:db/migration
  # Spring Security
  security:
    user:
      name: name
      password: password@1

# Actuator endpoints
management:
  endpoints:
    web:
      exposure:
        include: "*"

pom.xml #

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.2.0</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>io.demo</groupId>
    <artifactId>crm</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo.io</name>
    <description>demo.io</description>
    <properties>
        <java.version>17</java.version>
        <spring-cloud-azure.version>5.7.0</spring-cloud-azure.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-mail</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-validation</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>com.azure.spring</groupId>
            <artifactId>spring-cloud-azure-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>com.azure.spring</groupId>
            <artifactId>spring-cloud-azure-starter-active-directory</artifactId>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.microsoft.sqlserver/mssql-jdbc -->
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>12.5.0.jre11-preview</version>
        </dependency>
        <dependency>
            <groupId>com.azure.spring</groupId>
            <artifactId>spring-cloud-azure-starter-keyvault</artifactId>
        </dependency>
        <dependency>
            <groupId>com.okta.spring</groupId>
            <artifactId>okta-spring-boot-starter</artifactId>
            <version>3.0.6</version>
        </dependency>
        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-core</artifactId>
        </dependency>
        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-sqlserver</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-actuator</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.security</groupId>
            <artifactId>spring-security-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>com.azure.spring</groupId>
                <artifactId>spring-cloud-azure-dependencies</artifactId>
                <version>${spring-cloud-azure.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

已尝试的解决措施（无效） #

• 更换Docker中SQL Server版本
• 更换Flyway版本（新旧版本均试过）
• 反复检查数据库连接字符串
• 通过IntelliJ可正常连接数据库

内容的提问来源于stack exchange，提问作者William