Error when trying to connect to H2 database server

[Tobi Schäfer]

Hi.

I am running a H2 database server with 

$ java -cp h2-1.3.157.jar org.h2.tools.Server

The web interface opens but when I try to connect to my database I get

Attempt to define a second primary key; SQL statement: CREATE PRIMARY KEY IF NOT EXISTS INFORMATION_SCHEMA.PRIMARY_KEY_3 ON INFORMATION_SCHEMA.LOB_MAP(LOB, SEQ) [90017-136] 90017/90017

I do not understand the message as at the moment of connection i actually do not create any primary key... or do I?

Furthermore, the message confuses me as it says “IF NOT EXISTS” and obviously it is tried to create the key anyway.

Any ideas?

Cheers,

tobi

[Thomas Mueller]

Hi,

If the error code is really "[90017-136]", that would mean you are
actually using H2 version 1.2.136 from 2010-05-24. Could you verify
this?

There is a bug introduced in version 1.3.156 and 1.3.157 (actually,
it's a bit more complicated - it's a bug that was there before, but
didn't / couldn't show up until then). It is related to the BLOB /
CLOB optimization in version 1.3.156.

I found and fixed the problem now (in the trunk). Unfortunately, you
will need to use the Recover tool to restore the data. I'm sorry about
that. I will release a new version at the end of the week. Until then,
I suggest to use version 1.3.155 if possible.

Regards,
Thomas

[Tobi Schäfer]

If the error code is really "[90017-136]", that would mean you are
actually using H2 version 1.2.136 from 2010-05-24. Could you verify
this?

Yes, that is correct, my bad. I got the error first using 1.3.157:

Attempt to define a second primary key; SQL statement:

CREATE PRIMARY KEY IF NOT EXISTS INFORMATION_SCHEMA.PRIMARY_KEY_3 ON INFORMATION_SCHEMA.LOB_MAP(LOB, SEQ) [90017-157] 90017/90017

 Then tried it with 1.2.136 which was also available on the machine.

I found and fixed the problem now (in the trunk). Unfortunately, you
will need to use the Recover tool to restore the data. I'm sorry about
that. I will release a new version at the end of the week. Until then,
I suggest to use version 1.3.155 if possible.

I dumped the SQL data using the recovery tool like this:

$ java -cp h2-1.3.157.jar org.h2.tools.Recover -dir /path/to/database-dir -db database-name

Then I removed the database files *.db and successfully connected using the older H2 version, created a new database and copy&pasted the SQL output from the recovery tool into the web console.

Now the database is up and running correctly again.

Thanks for your help!

Cheers,

tobi