H2 in PosgreSQL compatibility mode: strange error regarding PRIMARY_KEY constraint violation

[Mauro Molinari]

Hello all,
I have a problem with H2.
I'm using H2 version 1.2.139. I have a database with an only table
(named JBOSSTSTXTABLE) with the following structure:

- column STATETYPE INTEGER(10) NOT NULL
- column TYPENAME VARCHAR(1024) NOT NULL
- column UIDSTRING VARCHAR(255) NOT NULL,
- column OBJECTSTATE VARBINARY(2147483647)
- an only index, of type PRIMARY KEY, on UIDSTRING, STATETYPE,
TYPENAME

I'm accessing this database in PostgreSQL compatibility mode. What I'm
seeing is the following message:

org.h2.jdbc.JdbcSQLException: Unique index or primary key violation:
"PRIMARY_KEY_E ON PUBLIC.JBOSSTSTXTABLE(UIDSTRING, STATETYPE)"; SQL
statement:
INSERT INTO JBossTSTxTable (StateType,TypeName,UidString,ObjectState)
VALUES (?,?,?,?) [23001-139]

As you can see, the exception message says that a primary key
constraint violation has occurred on columns UIDSTRING, STATETYPE.
However I'm sure the primary key is set on (UIDSTRING, STATETYPE,
TYPENAME): I can see this through the H2 console. Moreover, I know
which is the SQL statement used to create the table:

CREATE TABLE JBOSSTSTXTABLE (StateType INTEGER, TypeName
VARCHAR(1024), UidString VARCHAR(255), ObjectState bytea, PRIMARY
KEY(UidString, StateType, TypeName))

However, I don't know the exact insert statement that causes the
error, because it's not under my control (it's generated by a Java
library and I can't debug it).

What could be the cause of this problem? Could this be a problem bound
to the use of the PostgreSQL compatibility mode? May it be a bug fixed
in H2 > 1.2.139? I looked at the changelog but I couldn't find any
suspicious entry.

The parameters I'm using to connect to my database are:
DB_CLOSE_ON_EXIT=TRUE;AUTO_SERVER=TRUE;AUTO_RECONNECT=FALSE;MODE=PostgreSQL

Any help would be greatly appreciated.
Thanks in advance,
Mauro.

[Thomas Mueller]

Hi,

Could you verify you are accessing the same database as where the error occurs?

Also, to ensure the primary key is set correctly, could you run the statement SCRIPT NODATA and check the primary key definition?

Regards,

Thomas

[Mauro Molinari]

Hi Thomas!
I can state for sure that I'm accessing the same database as where the
error occurs. I'm inspecting it using the H2 console run by it.
The structure of the table was extracted using the H2 console.
Moreover, if I try the statement:

SELECT * FROM
information_schema.COLUMNS
WHERE
table_name = 'JBOSSTSTXTABLE'

I get the result you can better read at:
https://community.jboss.org/message/647184#647184
(where I'm talking with the JBoss developers to see whether it's a
JBossTS bug or not)

If I do:
SELECT * FROM
information_schema.CONSTRAINTS
WHERE
table_name = 'JBOSSTSTXTABLE'

I get an only row with:
CONSTRAINT_CATALOG = OBJECTSTORE
CONSTRAINT_SCHEMA = PUBLIC
CONSTRAINT_NAME = CONSTRAINT_E
CONSTRAINT_TYPE = PRIMARY KEY
TABLE_CATALOG = OBJECTSTORE
TABLE_SCHEMA = PUBLIC
TABLE_NAME = JBOSSTSTXTABLE
UNIQUE_INDEX_NAME = PRIMARY_KEY_E
CHECK_EXPRESSION = null
COLUMN_LIST = UIDSTRING,STATETYPE,TYPENAME
REMARKS = (empty)
SQL = ALTER TABLE PUBLIC.JBOSSTSTXTABLE ADD CONSTRAINT
PUBLIC.CONSTRAINT_E PRIMARY KEY(UIDSTRING, STATETYPE, TYPENAME) INDEX
PUBLIC.PRIMARY_KEY_E
ID = 10

If I try SCRIPT NODATA, as you suggest, I get:

SET CLUSTER '';
SET DEFAULT_TABLE_TYPE 0;
SET WRITE_DELAY 500;
SET DEFAULT_LOCK_TIMEOUT 2000;
SET CACHE_SIZE 16384;
CREATE USER IF NOT EXISTS SA SALT '288f3796da62f235' HASH
'b391b8e34b7b09e4194248bcc48a301cb678da43a2476f1f4101786fde363e8e'
ADMIN;
CREATE CACHED TABLE PUBLIC.JBOSSTSTXTABLE(
STATETYPE INTEGER NOT NULL,
TYPENAME VARCHAR(1024) NOT NULL,
UIDSTRING VARCHAR(255) NOT NULL,
OBJECTSTATE BYTEA
);
ALTER TABLE PUBLIC.JBOSSTSTXTABLE ADD CONSTRAINT PUBLIC.CONSTRAINT_E
PRIMARY KEY(UIDSTRING, STATETYPE, TYPENAME);
-- 0 +/- SELECT COUNT(*) FROM PUBLIC.JBOSSTSTXTABLE;

Mauro.

[Thomas Mueller]

Hi,

Could you send me the database? I can't reproduce the problem. I always get

Unique index or primary key violation: "PRIMARY_KEY_E ON PUBLIC.JBOSSTSTXTABLE(UIDSTRING, STATETYPE, TYPENAME)"

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Mauro Molinari]

Hi Thomas,
in the H2 group you asked me to send you the database.
Here it is a real database instance we use in our web application.
Unfortunately, it is not the exact instance where the reported errors
were observed.

On this instance I was not able to reproduce the problem by manually
running SQL instructions like:
insert into jbosststxtable values('1', 'ciao', 'bau', null)
insert into jbosststxtable values('1', 'ciao2', 'bau', null)
update jbosststxtable set typename='ciao3' where typename='ciao'

That is, I tried to add rows where just the TYPENAME column value was
different or to update existing rows by changing only that column value,
since the original error message seems to suggest that H2 is considering
only (UIDSTRING, STATETYPE) as the primary key.

However, these simple queries do not produce any errors.

I've reported back at https://community.jboss.org/message/717102 and
asked the JBossTS developers if they could provide some SQL instruction
that will likely be produced by the JBossTS library that is using this
database in our production environment. I will let you know if some
useful information will be collected.

By the way, is there a way to trace not only the DDL instructions issued
on the database, but also the DML ones?

Thanks for your help.

--

Mauro Molinari
E-mail: maur...@tiscali.it
ICQ UIN: 10379016
Digital Things Home Space: http://digitalthings.cjb.net
Il Sito piￜ Banale: http://www.poesiamasini.it