CODEWITHSUNDEEP
javahsqldb
Nicolai Parlog
Nicolai Parlog

Reputation: 51130

Error in Script File If I Restart Identifyer Column After Insert

Is it possible to restart the ID column of an HSQLDB after rows were inserted? Can I even set it to restart at a value lower than existing IDs in the table?

The Situation

I have a simple Java program which connects to a HSQLDB like so:

DriverManager.getConnection("jdbc:hsqldb:file:" + hsqldbPath, "", "");

This gives me an HsqlException when executing the following script (this is an excerpt, the complete script for HSQLDB 2.2.4 can be found here):

SET SCHEMA PUBLIC
CREATE MEMORY TABLE PUBLIC.MAP(
    ID BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
    FOO VARCHAR(16) NOT NULL)
ALTER TABLE PUBLIC.MAP ALTER COLUMN ID RESTART WITH 1
// [...]
SET SCHEMA PUBLIC
INSERT INTO MAP VALUES(1,'Foo')
INSERT INTO MAP VALUES(2,'Bar')
ALTER TABLE PUBLIC.MAP ALTER COLUMN ID RESTART WITH 42

The message is:

HsqlException: error in script file: ALTER TABLE PUBLIC.MAP ALTER COLUMN ID RESTART WITH 42

The exception goes away when I move the RESTART-command before the INSERTs. The documentation gives no hint as to why that would be necessary.

I will eventually have to make this work on version 2.2.4 but have the same problem with the current version 2.3.2.

Background

What I am trying to do here is to recreate a situation which apparently occurred in production: An unlucky interaction with the database (I don't know what exactly happened) seems to have caused newly inserted rows to collide with existing ones because they were issued the same IDs. I want to create a test replicating the scenario in order to write a proper fix.

Upvotes: 1

Views: 101

Answers (2)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 154070

I think SEQUENCES are much more flexiblee than IDENTITY. The IDENTITY generator disabled JDBC batching, by the way.

But if you use SEQUENCE identifiers, you must pay attention to the hilo optimizers as well, because identifier are generated by Hibernate using a sequence value as a base calculation starting point.

With a SEQUENCE the restart goes like this:

ALTER SEQUENCE my_seqeunce RESTART WITH 105;

Upvotes: 0

fredt
fredt

Reputation: 24372

The .script file of the database follows a predefined order for the statements. This shouldn't be altered if it is edited and only certain manual changes are allowed (see the guide for details).

You can execute the ALTER TABLE statement via JDBC at the start of your test instead of inserting it in the script.

If IDENTITY values for the PRIMARY KEY collide, you will get an exception when you insert the values.

The actual fix for a problem like this is to RESTART WITH the max value in the primary key column plus one.

Upvotes: 1

Related Questions