Error opening database: Index "PRIMARY_KEY_11" already exists

[GoogleCodeExporter]

Before submitting a bug, please check the FAQ:
http://www.h2database.com/html/faq.html

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)

I created a database with 1.1.118. When I open the database with 1.2.128, I get 
the following error:

Exception in thread "main" org.h2.jdbc.JdbcSQLException: Index "PRIMARY_KEY_11"
  besteht bereits
  Index "PRIMARY_KEY_11" already exists; SQL statement:
  CREATE PRIMARY KEY PUBLIC.PRIMARY_KEY_11 ON PUBLIC.TIM_DAILYPROGRAM_CONST(O_ID)
  [42111-128]
          at org.h2.message.Message.getSQLException(Message.java:110)
          at org.h2.message.Message.getSQLException(Message.java:121)
          at org.h2.message.Message.getSQLException(Message.java:74)
          at org.h2.command.ddl.CreateIndex.update(CreateIndex.java:77)
          at org.h2.engine.MetaRecord.execute(MetaRecord.java:82)
          at org.h2.engine.Database.open(Database.java:709)
          at org.h2.engine.Database.openDatabase(Database.java:228)
          at org.h2.engine.Database.<init>(Database.java:223)
          at org.h2.engine.Engine.openSession(Engine.java:58)
          at org.h2.engine.Engine.openSession(Engine.java:142)
          at org.h2.engine.Engine.getSession(Engine.java:122)
          at org.h2.engine.SessionFactoryEmbedded.createSession(SessionFactoryEmbe
  dded.java:17)
          at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.jav
  a:245)
          at org.h2.engine.SessionRemote.createSession(SessionRemote.java:223)
          at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:110)
          at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:94)
          at org.h2.Driver.connect(Driver.java:58)
          at java.sql.DriverManager.getConnection(Unknown Source)
          at java.sql.DriverManager.getConnection(Unknown Source)2.

However, I could dump the database to a sql file using the Recover tool (using 
1.2.128).

But I cannot run the created sql script. When I run it (using 1.2.137), I get 
the same error:

  java -cp h2-1.2.137.jar -Xmx256m org.h2.tools.RunScript -script h2-database\matrix.data.sql -url jdbc:h2:file:new-h2-database/matrix;MVCC=TRUE -user sa
  Exception in thread "main" org.h2.jdbc.JdbcSQLException: Index "PRIMARY_KEY_11" besteht bereits
  Index "PRIMARY_KEY_11" already exists; SQL statement:

  CREATE PRIMARY KEY PUBLIC.PRIMARY_KEY_11 ON PUBLIC.TIM_DAILYPROGRAM_CONST(O_ID) [42111-137]
          at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
          at org.h2.message.DbException.get(DbException.java:167)
          at org.h2.message.DbException.get(DbException.java:144)
          at org.h2.command.ddl.CreateIndex.update(CreateIndex.java:75)
          at org.h2.command.CommandContainer.update(CommandContainer.java:70)
          at org.h2.command.Command.executeUpdate(Command.java:199)
          at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:176)
          at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:151)
          at org.h2.tools.RunScript.process(RunScript.java:249)
          at org.h2.tools.RunScript.process(RunScript.java:185)
          at org.h2.tools.RunScript.process(RunScript.java:310)
          at org.h2.tools.RunScript.runTool(RunScript.java:139)
          at org.h2.tools.RunScript.main(RunScript.java:67)

What is the expected output? What do you see instead?

The sql script should run completely.

What version of the product are you using? On what operating system, file
system, and virtual machine?

H2 version: Recover tool: 1.2.128, RunScript: 1.2.137
OS: Windows XP SP3
Java: Sun 1.6.0_20

Do you know a workaround?

No.

How important/urgent is the problem for you?

Very urgent.

In your view, is this a defect or a feature request?

Defect.

Please provide any additional information below.

I attach the sql file.

Original issue reported on code.google.com by dmoeb...@gmx.net on 24 Jun 2010 at 9:16

[GoogleCodeExporter]

Note that the error occurs when RunScript tries to execute line 21715, which is:

  CREATE PRIMARY KEY PUBLIC.PRIMARY_KEY_11 ON PUBLIC.TIM_DAILYPROGRAM_CONST(O_ID);

This is the first occurrence of the name "PRIMARY_KEY_11" (besides inserts on 
table O_0"). Since the database is completely new, I don't know why there is 
already a primary key called "PRIMARY_KEY_11".

Also note that MVCC=TRUE.

Original comment by dmoeb...@gmx.net on 24 Jun 2010 at 9:18

[GoogleCodeExporter]

Looking at the file matrix.data.sql, I see that some primary key indices are 
created with a name:

  CREATE PRIMARY KEY PUBLIC.PRIMARY_KEY_87 ON PUBLIC.ACC_MANDANT_RELEASES(O_ID);

and some are not:

  CREATE PRIMARY KEY ON PUBLIC.BAS_WEEKDAY(O_ID);

Could it be that if the primary key is created without a name, an internal name 
is used, and that happens to be "PRIMARY_KEY_11"?

Original comment by dmoeb...@gmx.net on 24 Jun 2010 at 4:43

[GoogleCodeExporter]

This is related to a problem fixed in version 1.2.125 (2009-12-06): "When 
running against an old database, the SCRIPT statement could generate a SQL 
script that contained duplicate indexes (PRIMARY_KEY_E)."

I think the workaround is to just ignore the exception.

Original comment by thomas.t...@gmail.com on 27 Jun 2010 at 6:57

[GoogleCodeExporter]

But I dumped the old database using 1.2.128, so that problem should have been 
fixed.

Also it does not explain why I cannot open the old database using 1.2.128.

Original comment by dmoeb...@gmx.net on 28 Jun 2010 at 6:56

[GoogleCodeExporter]

I attached the database that I cannot dump.
I used the following command to dump the contents of the database to a script:

java -cp h2-1.2.128.jar -Xmx256m org.h2.tools.Script -script out.sql -url 
jdbc:h2:matrix;MVCC=TRUE -user sa

Gives me the first stacktrace from above.

Original comment by dmoeb...@gmx.net on 29 Jun 2010 at 7:03

[GoogleCodeExporter]

I should add that I try to migrate this old database to the new page store 
format. I wanted to use your Migrate tool, which basically executes the same 
Script command.

I don't want to use the Recover tool to dump the contents of the database, 
because I don't know if it replicates the same data structures as the script 
command. (The Recover tools seems more like a "tool of last resort").

Original comment by dmoeb...@gmx.net on 29 Jun 2010 at 7:08

[GoogleCodeExporter]

What works is: open the database using version 1.1.118, then run
"script to '...'", close the database, use the newest version of H2,
and re-create the database using "runscript from '...'".

> I wanted to use your Migrate tool

The migrate tool should work, because it basically does the above, however
instead of using version 1.2.127 it looks like it's necessary to use
version 1.1.118 instead.

Original comment by thomas.t...@gmail.com on 1 Jul 2010 at 10:08

[GoogleCodeExporter]

Could you please re-download the migration .jar file 
http://h2database.com/h2mig_pagestore_addon.jar and test again? The file is now 
build from the version-1.1.x branch which contains a bug fix for the problem. 
Please report back

Original comment by christian.peter.io@googlemail.com on 11 Jul 2010 at 12:27

[GoogleCodeExporter]

We are seeing this happen in version 1.2.136 as well, although the database is 
once restored using RunScript.

Please refer to the attched test script, which I created manually following the 
same pattern in our database backup script. When this is restored using the 
following command[1], the exception is thrown[2].

I tried to debug the source and tried the following change[3] which worked for 
us! But I don't know, if it is the right approach or breaks any other part of 
codebase. It'd be great if you could verify, if this change can be used.

Thanks,
Prashant

[1] java -classpath .:./h2-1.2.140.jar org.h2.tools.RunScript -url 
jdbc:h2:test/issue-210 -user sa -password '' -script issue-210.zip -options 
compression zip;

[2]  Exception in thread "main" org.h2.jdbc.JdbcSQLException: Index 
"PRIMARY_KEY_7" already exists; SQL statement:           

-- 1 +/- SELECT COUNT(*) FROM ISSUE.BRANCH; 
CREATE PRIMARY KEY ISSUE.PRIMARY_KEY_7 ON ISSUE.BRANCH(ID) [42111-138]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
    at org.h2.message.DbException.get(DbException.java:167)
    at org.h2.message.DbException.get(DbException.java:144)
    at org.h2.command.ddl.CreateIndex.update(CreateIndex.java:75)
    at org.h2.command.dml.RunScriptCommand.execute(RunScriptCommand.java:61)
    at org.h2.command.dml.RunScriptCommand.update(RunScriptCommand.java:43)
    at org.h2.command.CommandContainer.update(CommandContainer.java:70)
    at org.h2.command.Command.executeUpdate(Command.java:199)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:176)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:151)
    at org.h2.tools.RunScript.processRunscript(RunScript.java:271)
    at org.h2.tools.RunScript.runTool(RunScript.java:137)
    at org.h2.tools.RunScript.main(RunScript.java:67)

[3]
Index: src/main/org/h2/schema/Schema.java
===================================================================
--- src/main/org/h2/schema/Schema.java  (revision 2860)
+++ src/main/org/h2/schema/Schema.java  (working copy)
@@ -331,12 +331,16 @@
         String hash = Integer.toHexString(obj.getName().hashCode()).toUpperCase();
         String name = null;
         synchronized (temporaryUniqueNames) {
-            for (int i = 1; i < hash.length(); i++) {
-                name = prefix + hash.substring(0, i);
-                if (!map.containsKey(name) && 
!temporaryUniqueNames.contains(name)) {
-                    break;
+            if (!map.containsKey(hash) && 
!temporaryUniqueNames.contains(hash)) {
+                name = hash;
+            } else {
+                for (int i = 1; i < hash.length(); i++) {
+                    name = prefix + hash.substring(0, i);
+                    if (!map.containsKey(name) && 
!temporaryUniqueNames.contains(name)) {
+                        break;
+                    }
+                    name = null;
                 }
-                name = null;
             }
             if (name == null) {

Original comment by prashant...@gmail.com on 21 Aug 2010 at 7:09

[GoogleCodeExporter]

Hi Thomas,

Did you've chance to look at this patch? It'd help us in updating to the latest 
version!

Thanks,
Prashant

Original comment by prashant...@gmail.com on 31 Aug 2010 at 6:48

[GoogleCodeExporter]

Hi,

I would like to _not_ apply the patch because it uses longer identifier names 
by default. With with version of H2 did you create the script in issue-210.zip? 
I can't reproduce the problem with a recent version of H2. Could you describe 
the steps to create this script file?

Original comment by thomas.t...@gmail.com on 3 Sep 2010 at 12:28

[GoogleCodeExporter]

Hi,

The database was created using 1.2.136 version. The tables and constraints are 
originally created using OpenJPA MappingTool. Please note that, if the 
PICKING_LIST table also had used the sql like 'create primary key', it works; 
but not if the alter statement is used.

This issue can be recreated using the following steps:
1. Using H2 Console create a new database
2. Execute the following sqls

CREATE SCHEMA IF NOT EXISTS ISSUE AUTHORIZATION SA;
CREATE CACHED TABLE ISSUE.PICKING_LIST(
    ID BIGINT NOT NULL
);
ALTER TABLE ISSUE.PICKING_LIST ADD CONSTRAINT ISSUE.CONSTRAINT_77 PRIMARY 
KEY(ID);
CREATE CACHED TABLE ISSUE.BRANCH(
    ID BIGINT NOT NULL SELECTIVITY 100
);
CREATE PRIMARY KEY ISSUE.PRIMARY_KEY_7 ON ISSUE.BRANCH(ID);

3. Then create the backup using Script Tool from command line 
java -classpath .:./h2-1.2.142.jar org.h2.tools.Script -url 
jdbc:h2:tcp://localhost/issue-210 -user sa -password '' -script 
issue-210-db.zip -options compression zip

4. Restore using RunScript Tool. This throws an exception!

May be it's an issue with the way OpenJpa Mapping tool generates constraints.

Thanks & Regards,
Prashant

Original comment by prashant...@gmail.com on 6 Sep 2010 at 11:01

[GoogleCodeExporter]

When I try to execute the script you sent it throws the exception: "Index 
"PRIMARY_KEY_7" already exists". Primary key index names shouldn't be set 
explicitly.

I am resolving this issue as "won't fix".

Original comment by thomas.t...@gmail.com on 17 Sep 2010 at 8:35

• Changed state: WontFix