Recover and RunScript Tool Throws Exception on Default User

274 views

Skip to first unread message

Chuck

unread,

Feb 2, 2010, 1:14:42 PM2/2/10

to H2 Database

I am using H2 version 1.2.127 with the default username "sa". I run
the Recover tool
'java -cp ~/h2/h2/bin/h2-1.2.127.jar org.h2.tools.Recover' and this
creates the h2Database.db.data.sql file.

I then call RunScript 'java -cp ~/h2/h2/bin/h2-1.2.127.jar
org.h2.tools.RunScript -script h2Database.db.data.sql -checkResults -
url "jdbc:h2:file:../new_db/h2Database.db" -user sa'. This produces
the following error:

Exception in thread "main" org.h2.jdbc.JdbcSQLException: User "SA"
already exists; SQL statement:

---- Schema ----------
CREATE USER SA SALT 'b3dfc1b247defd6d' HASH
'0ac667eb56d8903e8feb1a5bbd8408e3796d128fd004e0d83f70c4fd8192c9a7'
ADMIN [90033-127]
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.CreateUser.update(CreateUser.java:69)
at org.h2.command.CommandContainer.update(CommandContainer.java:71)
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:215)
at org.h2.tools.RunScript.process(RunScript.java:186)
at org.h2.tools.RunScript.process(RunScript.java:311)
at org.h2.tools.RunScript.run(RunScript.java:140)
at org.h2.tools.RunScript.main(RunScript.java:68)

In the h2Database.db.data.sql file I found these two lines:
INSERT INTO O_0 VALUES(13, 0, 2, 'CREATE USER SA SALT
''b3dfc1b247defd6d'' HASH
''0ac667eb56d8903e8feb1a5bbd8408e3796d128fd004e0d83f70c4fd8192c9a7''
ADMIN');

CREATE USER SA SALT 'b3dfc1b247defd6d' HASH
'0ac667eb56d8903e8feb1a5bbd8408e3796d128fd004e0d83f70c4fd8192c9a7'
ADMIN;

Removing the CREATE USER statement eliminated the exception and
successfully restored the database.

Please let me know if I am using the tool appropriately or not.

Thanks,

Chuck Fouts

Thomas Mueller

unread,

Feb 2, 2010, 3:40:54 PM2/2/10

to h2-da...@googlegroups.com

Hi,

I know, it doesn't work as expected. This is a known problem. See
http://www.h2database.com/html/advanced.html#using_recover_tool "The
script includes at least one CREATE USER statement. If you run the
script against a database that was created with the same user, or if
there are conflicting users, running the script will fail. Consider
running the script against a database that was created with a user
name that is not in the script."

I will try to fix it in the future; however this will be a bit more
complex. I think instead of CREATE USER it will be CREATE FORCE USER
which will overwrite the user password if it exists.