Spring Batch对接MariaDB报错：无法递增BATCH_JOB_SEQ序列ID

问题描述 #

开发小型Spring Batch应用，使用H2数据库时运行正常，切换到MariaDB后出现错误。手动执行Spring Batch官方提供的MariaDB建表脚本后，出现以下两个错误：

• Could not increment ID for BATCH_JOB_SEQ sequence table
• Unknown column 'ID' in 'field list'
  查询BATCH_JOB_SEQ表结构，发现确实没有ID字段。

代码配置 #

Application.java #

@SpringBootApplication()
public class Application implements CommandLineRunner {

    public static void main(String[] args) {
        SpringApplication.run(FaxProcesser35Application.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
        System.out.println("Starting the job");
    }
}

JobConfig.java #

@Configuration
public class JobConfig {

    @Autowired
    private JobRepository jobRepository;
    @Autowired
    private PlatformTransactionManager transactionManager;

    @Bean
    public Tasklet myTasklet() {
        System.out.println("tasklet method was called");
        return new MyTasklet() ;
    }

    @Bean
    public Step myStep(JobRepository jobRepository, Tasklet myTasklet, PlatformTransactionManager transactionManager) {
        return new StepBuilder("myStep", jobRepository)
                .tasklet(myTasklet, transactionManager) // or .chunk(chunkSize, transactionManager)
                .build();
    }

    @Bean
    public Job myJob() {
        System.out.println("Job Method was called");
        return new JobBuilder("myJob", jobRepository)
                .start(myStep(jobRepository, myTasklet(), transactionManager))
                .build();
    }

    private class MyTasklet implements Tasklet {
        @Override
        public RepeatStatus execute(StepContribution  stepContribution, ChunkContext chunkContext) throws Exception {
            System.out.println("Running MyTasklet");
            return RepeatStatus.FINISHED;
        }
    }
}

application.properties #

#--------------------- DB Connection ------------------
#spring.datasource.driver-class-name=com.mariadb.jdbc.Driver
#spring.datasource.url=jdbc:mariadb://localhost:3306/database

spring.datasource.url=jdbc:mysql://localhost:3306/database
spring.datasource.username=username
spring.datasource.password=password

BATCH_JOB_SEQ表结构 #

+-----------------------+---------------------+------+-----+---------+-------+
| Field                 | Type                | Null | Key | Default | Extra |
+-----------------------+---------------------+------+-----+---------+-------+
| next_not_cached_value | bigint(21)          | NO   |     | NULL    |       |
| minimum_value         | bigint(21)          | NO   |     | NULL    |       |
| maximum_value         | bigint(21)          | NO   |     | NULL    |       |
| start_value           | bigint(21)          | NO   |     | NULL    |       |
| increment             | bigint(21)          | NO   |     | NULL    |       |
| cache_size            | bigint(21) unsigned | NO   |     | NULL    |       |
| cycle_option          | tinyint(1) unsigned | NO   |     | NULL    |       |
| cycle_count           | bigint(21)          | NO   |     | NULL    |       |
+-----------------------+---------------------+------+-----+---------+-------+
8 rows in set (0.017 sec)

完整错误栈 #

java.lang.IllegalStateException: Failed to execute ApplicationRunner
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:763) ~[spring-boot-3.0.5.jar:3.0.5]
    at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:750) ~[spring-boot-3.0.5.jar:3.0.5]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:317) ~[spring-boot-3.0.5.jar:3.0.5]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1304) ~[spring-boot-3.0.5.jar:3.0.5]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1293) ~[spring-boot-3.0.5.jar:3.0.5]
    at com.ahmadiyya.LetterTohazoor.faxprocesser35.FaxProcesser35Application.main(FaxProcesser35Application.java:15) ~[classes/:na]
Caused by: org.springframework.dao.DataAccessResourceFailureException: Could not increment ID for BATCH_JOB_SEQ sequence table
    at org.springframework.jdbc.support.incrementer.MySQLMaxValueIncrementer.getNextKey(MySQLMaxValueIncrementer.java:153) ~[spring-jdbc-6.0.7.jar:6.0.7]
    at org.springframework.jdbc.support.incrementer.AbstractDataFieldMaxValueIncrementer.nextLongValue(AbstractDataFieldMaxValueIncrementer.java:128) ~[spring-jdbc-6.0.7.jar:6.0.7]
    at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.createJobInstance(JdbcJobInstanceDao.java:110) ~[spring-batch-core-5.0.1.jar:5.0.1]
    at org.springframework.batch.core.repository.support.SimpleJobRepository.createJobExecution(SimpleJobRepository.java:168) ~[spring-batch-core-5.0.1.jar:5.0.1]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343) ~[spring-aop-6.0.7.jar:6.0.7]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196) ~[spring-aop-6.0.7.jar:6.0.7]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-6.0.7.jar:6.0.7]
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-6.0.7.jar:6.0.7]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:391) ~[spring-tx-6.0.7.jar:6.0.7]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-6.0.7.jar:6.0.7]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.7.jar:6.0.7]
    at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean$1.invoke(AbstractJobRepositoryFactoryBean.java:207) ~[spring-batch-core-5.0.1.jar:5.0.1]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.7.jar:6.0.7]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:218) ~[spring-aop-6.0.7.jar:6.0.7]
    at jdk.proxy2/jdk.proxy2.$Proxy68.createJobExecution(Unknown Source) ~[na:na]
    at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:145) ~[spring-batch-core-5.0.1.jar:5.0.1]
    at org.springframework.batch.core.launch.support.TaskExecutorJobLauncher.run(TaskExecutorJobLauncher.java:70) ~[spring-batch-core-5.0.1.jar:5.0.1]
    at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.execute(JobLauncherApplicationRunner.java:199) ~[spring-boot-autoconfigure-3.0.5.jar:3.0.5]
    at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.executeLocalJobs(JobLauncherApplicationRunner.java:178) ~[spring-boot-autoconfigure-3.0.5.jar:3.0.5]
    at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.launchJobFromProperties(JobLauncherApplicationRunner.java:166) ~[spring-boot-autoconfigure-3.0.5.jar:3.0.5]
    at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.run(JobLauncherApplicationRunner.java:161) ~[spring-boot-autoconfigure-3.0.5.jar:3.0.5]
    at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.run(JobLauncherApplicationRunner.java:156) ~[spring-boot-autoconfigure-3.0.5.jar:3.0.5]
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:760) ~[spring-boot-3.0.5.jar:3.0.5]
    ... 5 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'ID' in 'field list'
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-j-8.0.32.jar:8.0.32]
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-j-8.0.32.jar:8.0.32]
    at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1334) ~[mysql-connector-j-8.0.32.jar:8.0.32]
    at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2084) ~[mysql-connector-j-8.0.32.jar:8.0.32]
    at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1245) ~[mysql-connector-j-8.0.32.jar:8.0.32]
    at com.zaxxer.hikari.pool.ProxyStatement.executeUpdate(ProxyStatement.java:119) ~[HikariCP-5.0.1.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyStatement.executeUpdate(HikariProxyStatement.java) ~[HikariCP-5.0.1.jar:na]
    at org.springframework.jdbc.support.incrementer.MySQLMaxValueIncrementer.getNextKey(MySQLMaxValueIncrementer.java:148) ~[spring-jdbc-6.0.7.jar:6.0.7]
    ... 31 common frames omitted

问题原因 #

Spring Batch会根据数据库连接URL自动选择ID增量器：

• 当使用jdbc:mysql://时，Spring Batch使用MySQLMaxValueIncrementer，该增量器期望序列表（如BATCH_JOB_SEQ）包含ID字段，通过更新该字段生成自增ID。
• 但你执行的是MariaDB官方建表脚本，该脚本创建的是MariaDB原生SEQUENCE表（10.3+支持），表结构没有ID字段，而是使用SEQUENCE的原生机制生成ID。

解决方案 #

1. 切换为MariaDB JDBC驱动和URL #

修改application.properties配置，启用MariaDB专属配置：

spring.datasource.driver-class-name=com.mariadb.jdbc.Driver
spring.datasource.url=jdbc:mariadb://localhost:3306/database
spring.datasource.username=username
spring.datasource.password=password

这样Spring Batch会识别出MariaDB数据库，自动使用MariaDBMaxValueIncrementer，适配原生SEQUENCE表结构。

2. 添加MariaDB驱动依赖 #

确保项目中包含MariaDB JDBC驱动依赖，比如Maven：

<dependency>
    <groupId>org.mariadb.jdbc</groupId>
    <artifactId>mariadb-java-client</artifactId>
    <version>3.3.3</version> <!-- 使用最新稳定版本 -->
</dependency>

3. 验证配置 #

重启应用后，Spring Batch会正确使用MariaDB的SEQUENCE生成ID，不再报错。

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