[FIXED] Hibernate sequence does not exist error after adding second Database source

Issue

I face the following error after adding a second datasource in my project:

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

It appears when I try to INSERT an object with type, including GenerationType.AUTO. I am a bit confused because there are a lot of questions and discussions for this topic and I tried a lot, but I do not get the desired result. It starts working when I change the GenerationType to IDENTITY, but I read that this can lead to performance issues, which is not the desired result. Something more, I have use-new-id-generator-mappings: false in my hibernate properties in yml file, but this does not help solving the problem, too.

Here it is my yml file:

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

Here it is how the entity which does now want to insert looks:

@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();
}

And this is the method for inserting in the controller part:

  @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);
  }

Because the error starter to appear after I configured second database, I am adding their code too. Comment entity is in the primary database.

Primary:

@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);
    }
}

Secondary:

@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);
    }
}

Solution

Your second database is simply lacking a table that Hibernate needs to work correctly. You have to create that table if you want to use table based sequences, which is kind of the default.

Using IDENTITY is totally fine though as long as you don't insert thousands of records per second.

Answered By - Christian Beikov
Answer Checked By - Pedro (JavaFixing Volunteer)