JdbcSQLSyntaxErrorException: Sequence "SYSTEM_SEQUENCE_*" not found

Question

I am trying to recover an H2 database that seems corrupted by using H2 recovery script (H2 v.1.4.199, and also .197 and .200), like this:

java -cp h2-1.4.200.jar org.h2.tools.RunScript -url "jdbc:h2:file:./testdb_recovered" -user test -password test -script testdb.h2.sql -showResults

Following error occurrs:

CREATE CACHED TABLE PUBLIC.VECTORS_DATA(
    ID BIGINT DEFAULT (NEXT VALUE FOR PUBLIC.SYSTEM_SEQUENCE_08DF5748_291A_46FD_9B83_818A3975DAF2) NOT NULL NULL_TO_DEFAULT SEQUENCE PUBLIC.SYSTEM_SEQUENCE_08DF5748_291A_46FD_9B83_818A3975DAF2 SELECTIVITY 100,
    NAME VARCHAR(256) NOT NULL SELECTIVITY 1,
    WIDTH INT SELECTIVITY 1,
    HEIGHT INT SELECTIVITY 1,
    DATA_SIZE INT SELECTIVITY 4
);Exception in thread "main" org.h2.jdbc.JdbcSQLSyntaxErrorException: Sequence "SYSTEM_SEQUENCE_08DF5748_291A_46FD_9B83_818A3975DAF2" not found; SQL statement:

CREATE CACHED TABLE PUBLIC.VECTORS_DATA(
    ID BIGINT DEFAULT (NEXT VALUE FOR PUBLIC.SYSTEM_SEQUENCE_08DF5748_291A_46FD_9B83_818A3975DAF2) NOT NULL NULL_TO_DEFAULT SEQUENCE PUBLIC.SYSTEM_SEQUENCE_08DF5748_291A_46FD_9B83_818A3975DAF2 SELECTIVITY 100,
    NAME VARCHAR(256) NOT NULL SELECTIVITY 1,
    WIDTH INT SELECTIVITY 1,
    HEIGHT INT SELECTIVITY 1,
    DATA_SIZE INT SELECTIVITY 4
) [90036-199]

The "VECTORS_DATA" table corresponds to a Java entity used in a Spring project:

@Entity
@Table(name = "vectors_data")
public class VectorData {

  /**
   * Database id.
   */
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  /**
   * Vector's data byte array.
   */
  @javax.persistence.Transient
  private byte[] data;

  /**
   * Vector's data byte array size. Used for persistency.
   */
  @Column(name = "dataSize")
  private int dataSize;

  /**
   * Vector's width in internal units.
   */
  @Column(name = "width")
  private int widthInternalUnit;

  /**
   * Vector's height in internal units.
   */
  @Column(name = "height")
  private int heightInternalUnit;

  /**
   * Vector's name.
   */
  @Column(name = "name")
  private String name;

H2 code statuses suggest that a sequence is trying to be accessed which does not exist. I am not sure how to proceed, although I've tried recovering it by using multiple versions, no help. Is there a way to debug recovery process more efficiently? Because I see no problem with the entity itself. Thanks!

score 0 · Answer 1 · answered Nov 08 '21 at 08:26

After a bit of struggle, I found a solution. Since H2 complained there was no sequence to be found, I opened the generated SQL file and found the code block that created the table in question (VectorData). Added a line above it that creates a sequence which was causing the error, like this:

CREATE SEQUENCE <sequence_name>;

Ran the command to create a db with:

java -cp h2-1.4.200.jar org.h2.tools.RunScript -url "jdbc:h2:file:./testdb_recovered" -user test -password test -script testdb.h2.sql -showResults

Started the config via IntelliJ and voila, data was there.

JdbcSQLSyntaxErrorException: Sequence "SYSTEM_SEQUENCE_*" not found

1 Answers1