新增第二数据源后出现Hibernate sequence不存在错误

问题详情 #

新增第二数据源后，插入使用GenerationType.AUTO主键策略的实体时触发如下错误：

Table 'portal-titan.hibernate_sequence' doesn't exist; error performing isolated work; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: error performing isolated work

切换为GenerationType.IDENTITY可正常运行，但该方式存在性能损耗，并非最优解。已在yml配置中设置use-new-id-generator-mappings: false，但问题仍未解决。

相关配置与代码 #

1. application.yml 全局配置 #

management:
  security:
    roles: ADMIN
  context-path: /management

spring:
  messages:
    basename: i18n/messages
  mvc:
    favicon:
      enabled: false
  thymeleaf:
    mode: XHTML
  jpa:
    hibernate:
      ddl-auto: validate
      use-new-id-generator-mappings: false
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQLDialect
        format-sql: true
        physical_naming_strategy: org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
        implicit_naming_strategy: org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy
 
caching:
  specs:
    products:
      timeout: 60
    orders:
      timeout: 60
      max: 500
    deliveries:
      timeout: 120
    tracking:
      timeout: 1
    admins:
      timeout: 120
    balance:
      timeout: 120

application:
  async:
    core-pool-size: 2
    max-pool-size: 50
    queue-capacity: 1000
  jwt:
    token-secret: secret-key
    token-validity: PT6H
    token-remember-me-validity: P7D
  default-language-tag: bg
  upload:
    allowed-content-types:
      - image/jpg
      - image/jpeg
      - image/png
  static-resource:
    path: /static/
  jobs:
    batch-size: 20

activity:
  purge:
    ttl-value: 90
    ttl-unit: days
    job-run-interval-value: 1
    job-run-interval-unit: days

2. 报错实体类（Comment，归属主数据源） #

@Getter
@Setter
@Entity
@Table(name = "comments")
public class Comment implements Serializable {
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Integer id;

  @Column(nullable = false, unique = true)
  private String uuid;

  @Column(nullable = false)
  private String content;

  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "delivery_id")
  private Delivery delivery;

  @CreatedDate
  @Column(name = "created_at", nullable = false)
  private Instant createdAt = Instant.now();

  @LastModifiedDate
  @Column(name = "updated_at", nullable = false)
  private Instant updatedAt = Instant.now();
}

3. 控制器插入接口 #

@PostMapping("/{deliveryUuid}")
@ApiOperation(value = "Submit a comment")
@ApiResponses(
    value = {
      @ApiResponse(code = 201, message = "Comment created"),
      @ApiResponse(code = 400, message = "Validation failed")
    })
@PreAuthorize("hasRole('ROLE_CUSTOMER')")
@ResponseStatus(value = HttpStatus.CREATED)
public void submitComment(
    @PathVariable("deliveryUuid") String deliveryUuid,
    @Valid @RequestBody CommentDto commentDto,
    @CurrentUser AuthUser principal) {
  commentService.submitComment(commentDto, deliveryUuid, principal);
}

4. 主数据源配置类 #

@Configuration
@EnableTransactionManagement
@EnableJpaAuditing
@EntityScan(basePackageClasses = {TitanClientApp.class})
@EnableJpaRepositories(
        entityManagerFactoryRef = "clientEntityManagerFactory",
        transactionManagerRef = "clientTransactionManager",
        basePackages = { "titan.client" }
)
public class DbConfiguration {

    @Primary
    @Bean(name="clientDataSource")
    @ConfigurationProperties(prefix="spring.datasource.primary")
    public DataSource clientDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "clientEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean clientEntityManagerFactory(EntityManagerFactoryBuilder builder,
                                                                              @Qualifier("clientDataSource") DataSource clientDataSource) {
        return builder
                .dataSource(clientDataSource)
                .packages("titan.client")
                .build();
    }

    @Primary
    @Bean(name = "clientTransactionManager")
    public PlatformTransactionManager clientTransactionManager(
            @Qualifier("clientEntityManagerFactory") EntityManagerFactory clientEntityManagerFactory) {
        return new JpaTransactionManager(clientEntityManagerFactory);
    }
}

5. 第二数据源配置类 #

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "gpsEntityManagerFactory",
        transactionManagerRef = "gpsTransactionManager",
        basePackages = {"titan.gps"}
)
public class SecondaryDbConfiguration {

    @Bean(name = "gpsDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource gpsDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "gpsEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean gpsEntityManagerFactory(EntityManagerFactoryBuilder builder,
                                                                          @Qualifier("gpsDataSource") DataSource gpsDataSource) {
        return builder
                .dataSource(gpsDataSource)
                .packages("titan.gps")
                .build();
    }

    @Bean(name = "gpsTransactionManager")
    public PlatformTransactionManager gpsTransactionManager(
            @Qualifier("gpsEntityManagerFactory") EntityManagerFactory gpsEntityManagerFactory) {
        return new JpaTransactionManager(gpsEntityManagerFactory);
    }
}

解决方案 #

问题根源 #

多数据源配置下，主数据源的EntityManagerFactory未正确继承全局JPA的Hibernate属性，导致use-new-id-generator-mappings: false未生效。当该属性为false时，Hibernate对MySQL应自动使用自增主键（IDENTITY），但未生效时会 fallback 到序列表hibernate_sequence，而数据库中不存在该表，因此报错。

修复步骤 #

1. 为主数据源绑定全局JPA属性
   修改主数据源配置中的clientEntityManagerFactory方法，注入全局JpaProperties并绑定到EntityManagerFactory：

@Autowired
private JpaProperties jpaProperties;

@Primary
@Bean(name = "clientEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean clientEntityManagerFactory(EntityManagerFactoryBuilder builder,
                                                                          @Qualifier("clientDataSource") DataSource clientDataSource) {
    return builder
            .dataSource(clientDataSource)
            .packages("titan.client")
            .properties(jpaProperties.getHibernateProperties(clientDataSource))
            .build();
}

2. 确保数据库表主键为自增列
   检查comments表的id字段是否设置为AUTO_INCREMENT，若未设置执行以下SQL修改：

ALTER TABLE comments MODIFY COLUMN id INT AUTO_INCREMENT;

3. 可选：显式指定主键生成策略（兼容旧版Hibernate）
   若上述配置仍不生效，可直接在实体类中显式指定生成策略：

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;

优先推荐通过配置修复，保留GenerationType.AUTO的灵活性。

验证 #

重启应用后，插入Comment实体时，Hibernate会正确使用MySQL的自增主键机制，不再尝试访问hibernate_sequence表，错误即可解决。

内容的提问来源于stack exchange，提问作者Даяна Димитрова