Spring Boot with Apache Derby increments the primary key with 100

Question

I have spring boot App with Derby Database. In application property file below is configuration.

spring.datasource.url=jdbc:derby://localhost:1600/derbydataLogger
spring.datasource.username=
spring.datasource.password=
spring.datasource.driver-class-name=org.apache.derby.jdbc.ClientDriver
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=none
spring.datasource.initialize=false


CREATE TABLE "DCS"."DEVICES" (
  "ID" bigint generated by default as identity,
  "DEVICE_NAME" varchar(45) NOT NULL,
  "IP_ADDRESS" varchar(60) NOT NULL,
  "PORT" int NOT NULL,
  "IS_CONNECTED" boolean DEFAULT false,
  "UPDATED_DATE" TIMESTAMP DEFAULT NULL,
   CONSTRAINT PK_devices_ID PRIMARY KEY ("ID")
);

I have noticed that when there is a table with an auto generated primary key, the auto incrementation by INSERT fails. Instead of increment the value by 1, sometimes Derby increments the primary key with 100 or 1000 or other random value.

I can't reproduce it, because it is random.

For example, I have a table named 'DEVICE', and i have inserted 4 rows, and I get the following the auto generated keys:

1. INSERT: auto generated primary key: 806 
2. INSERT: auto generated primary key: 807 
3. INSERT: auto generated primary key: *904* 
4. INSERT: auto generated primary key: *1004* 
5. INSERT: auto generated primary key: 1005

It should be incremented by 1. The expected sequence should be:

1. INSERT: auto generated primary key: 806 
2. INSERT: auto generated primary key: 807 
3. INSERT: auto generated primary key: 808 
4. INSERT: auto generated primary key: 809 
5. INSERT: auto generated primary key: 810

Has anybody met with this strange error? Do you have any suggestions, how to start to debug it? I can't reproduce it.

Why does having gaps in the auto-generated primary key values break your application? What is the actual failure that you get? — Bryan Pendleton, Jul 16 '18 at 17:32

score 2 · Answer 1 · answered Jul 16 '18 at 15:00

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:

Add ;shutdown=true to the JDBC URL. This will shut the database down when the application ends.
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.

Referred from here Derby Auto Increment by 100 when specified as 1

shutdown=true give error in connection in spring boot I have code ad in application.properyfile as .spring.datasource.url=jdbc:derby://localhost:1600/derbydataLogger;shutdown=true spring.datasource.username= spring.datasource.password= spring.datasource.driver-class-name=org.apache.derby.jdbc.ClientDriver spring.jpa.show-sql=true spring.jpa.hibernate.ddl-auto=none spring.datasource.initialize=false — kalpesh Ajudiya, Jul 17 '18 at 15:44

Sagar · Answer 2 · 2018-07-21T01:22:13.480

Below code is working for me.

@Value("${spring.datasource.url}")
private String connectionURL;

@PreDestroy
public void shutdown() {
    System.out.println("Shutting down.. DB and Network Server Control..");
    try {
        DriverManager.getConnection(connectionURL+";shutdown=true");
        if(nc !=null)
            nc.shutdown();
    } catch (Exception e) {
        e.printStackTrace();
    }

}

Here "nc" is the reference to NetworkServerControl instance in the application.

Using @PreDestory this method will do orderly shutdown of derby database and primary key generation will be proper after restarts as well.

You will get below like exception on proper shutdown of the application.

Caused by: ERROR 08006: DERBY SQL error: ERRORCODE: 45000, SQLSTATE: 08006, SQLERRMC: Database 'derbycheckdb' shutdown.
at org.apache.derby.client.am.ClientConnection.completeSqlca(Unknown Source)

This exception indicates that database shutdown was graceful and no other exception were there. Please see this documentation for more details. Shutting down Derby or an individual database

Note:

This code assumes a graceful shutdown of spring boot app (like running the app from terminal and perfoming ctrl+c to stop the app or other ways if any)

Spring Boot with Apache Derby increments the primary key with 100

2 Answers2