7

I used the statement below to create a Derby database table with auto-increment primary column.

CREATE TABLE \"table\" (\n"
            + " \"id\" INTEGER  NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,\n"
            + " \"path\" VARCHAR(2000) DEFAULT NULL,\n"
            + " \"downloaded\" BOOLEAN DEFAULT false NOT NULL,\n"
            + " \"retried_times\" SMALLINT DEFAULT 0 NOT NULL,\n"
            + " \"name\" VARCHAR(40),\n"
            + " \"downloaded_date\" TIMESTAMP DEFAULT NULL,\n"
            + " PRIMARY KEY (\"id\")\n"

When I insert a row through Spring JDBC, it increments by 100. Is there any error in my statement?

enter image description here

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Bruce
  • 8,609
  • 8
  • 54
  • 83

2 Answers2

11

This is due to pre-allocation of values for auto-increment columns. Derby being an in-memory database, caches auto-increment values when the database is first loaded into the memory. Then, future values of the auto-increment columns are generated using the cache instead of querying the database again and again. If the database is not shut down properly, unused values from the cache are lost forever.

You have two options to address this:

  1. Add ;shutdown=true to the JDBC URL. This will shut the database down when the application ends.
  2. Set the derby.language.sequence.preallocator property to 1 (its default value is 100). This will ensure that the column value is never cached.

Note that most databases behave similarly for sequences. For example, H2 has the exact same behaviour but uses a cache size of 32 instead of 100 like Derby does.

manish
  • 19,695
  • 5
  • 67
  • 91
  • This is definitely a serious bug in Derby and H2. The auto-increment values should be checked and corrected after improper shut down, In real life proper shut down cannot be guaranteed because there are blackouts, sometimes hard reset is needed, etc... – JHead Sep 13 '17 at 16:38
  • please tell how to set preallocator values – Ranjit Vamadevan Sep 29 '21 at 08:35
  • 1
    @RanjitVamadevan just insert this line System.setProperty("derby.language.sequence.preallocator", String.valueOf(1)); – Abderaouf Boucenna Oct 07 '21 at 14:53
0

Thanks to @manish the second option worked for me.

In order to implement the 2nd solution, add the following code line where you set your database connection as shown in the below example.

Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
String URL = "jdbc:derby:testDB;create=true;";
conn = DriverManager.getConnection(URL);
System.setProperty("derby.language.sequence.preallocator", "1"); // Add this line

Thanks to answer provided by @xfocus99, I was able to know how to implement the 2nd solution.

Uditha Silva
  • 26
  • 1
  • 3