Issue
I am getting JdbcSQLIntegrityConstraintViolationException
exception while starting my server.
Seems like some problem in using <map>
, earlier it was working file with older version of H2 but now not working.
implementation group: 'com.h2database', name: 'h2', version: '1.4.200'
Older was:
compile group: 'com.h2database', name: 'h2', version: '1.4.193'
My class is using simple map variable like: private Map<String, String> extraData;
Can someone tell me, how to fix this??? Thanks
Here is my XML snippet:
<class name="pojo.MachineInstruction" >
<id name="machineId" type="java.lang.Integer"/>
<!--Time stamp is auto generated. No need to set the value..-->
<timestamp name="timestamp"/>
<property name="instructionType" not-null="true">
<type name="org.hibernate.type.EnumType">
<param name="enumClass">pojo.MachineInstructionType</param>
</type>
</property>
<property name="instructionStatus" not-null="true">
<type name="org.hibernate.type.EnumType">
<param name="enumClass">pojo.MachineInstructionStatus</param>
</type>
</property>
<property name="version" type="java.lang.String"/>
<map name="extraData" cascade="all">
<key column="extraData" />
<map-key type="text" column="key"/>
<element type="text" column="value"/>
</map>
</class>
Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Unique index or primary key violation: "PRIMARY KEY ON """".PAGE_INDEX"; SQL statement:
ALTER TABLE PUBLIC.MACHINEINSTRUCTION_EXTRADATA ADD CONSTRAINT PUBLIC.FKROHO504EJPG9G4R81YYKTV44K FOREIGN KEY(EXTRADATA) REFERENCES PUBLIC.MACHINEINSTRUCTION(MACHINEID) NOCHECK [23505-200]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:459)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
at org.h2.message.DbException.get(DbException.java:205)
at org.h2.message.DbException.get(DbException.java:181)
at org.h2.pagestore.db.PageDataIndex.add(PageDataIndex.java:125)
at org.h2.pagestore.PageStore.addMeta(PageStore.java:1804)
at org.h2.pagestore.db.PageBtreeIndex.<init>(PageBtreeIndex.java:65)
at org.h2.pagestore.db.PageStoreTable.addIndex(PageStoreTable.java:183)
at org.h2.command.ddl.AlterTableAddConstraint.createIndex(AlterTableAddConstraint.java:298)
at org.h2.command.ddl.AlterTableAddConstraint.tryUpdate(AlterTableAddConstraint.java:223)
at org.h2.command.ddl.AlterTableAddConstraint.update(AlterTableAddConstraint.java:78)
at org.h2.engine.MetaRecord.execute(MetaRecord.java:60)
at org.h2.engine.Database.open(Database.java:759)
at org.h2.engine.Database.openDatabase(Database.java:307)
at org.h2.engine.Database.<init>(Database.java:301)
at org.h2.engine.Engine.openSession(Engine.java:74)
at org.h2.engine.Engine.openSession(Engine.java:192)
at org.h2.engine.Engine.createSessionAndValidate(Engine.java:171)
at org.h2.engine.Engine.createSession(Engine.java:166)
at org.h2.engine.Engine.createSession(Engine.java:29)
at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:340)
at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:173)
at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:152)
at org.h2.Driver.connect(Driver.java:69)
at org.hibernate.engine.jdbc.connections.internal.DriverConnectionCreator.makeConnection(DriverConnectionCreator.java:38)
... 35 more
Solution
Unfortunately, H2 1.4.* and older versions don't have automatic upgrade procedure, it is under responsibility of their users. https://h2database.com/html/tutorial.html#upgrade_backup_restore
(The upcoming H2 2.0 will have a built-in upgrade utility and will refuse to open old files without upgrade to avoid such issues.)
1.4.196 and older versions had a bug with some combinations of indexes and referential constraints, it was fixed in 1.4.197, but upgrade path for the legacy PageStore engine wasn't provided. MVStore also had similar upgrade issues, but they were fixed.
Right now your database seems to be corrupted, because upgrade wasn't performed. You need to write some dummy implementation of org.h2.api.DatabaseEventListener
, put it into the classpath of your application (classpath of your server if you use separate H2 Server process), and open your database with ;DATABASE_EVENT_LISTENER='path.to.YourListener'
appended to JDBC connection URL. Wrong constraints will be logged to your listener. After that you will be able to export your database to the SQL Script with SCRIPT TO 'filename.sql'
command and create a new database file and populate it with that script with RUNSCRIPT FROM 'filename.sql'
.
Answered By - Evgenij Ryazanov
Answer Checked By - Terry (JavaFixing Volunteer)