Derby Auto Increment by 100 when specified as 1

Question

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?

Answer 1

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.