SQL Error: 50000, SQLState: HY000 - Upgrade from 1.2.121 to 1.2.141

[UncleTupelo]

Good Morning,

I am getting various "rowCount expected " errors since I upgraded the
h2 jar file from 1.2.121 to 1.2.141. I can connect to the db fine
and most things still seem to work, but various inserts don't.

If I run :

select * from information_schema.settings where name = 'CREATE_BUILD';

I get:

NAME VALUE
------------ --------
CREATE_BUILD 121


Do I need to do some kinda upgrade here? It doesn't appear the file
structure of h2 has altered between these versions so I didn't think I
would have to.

The java application stack trace look like:

09:10:17.491 [btpool0-7 ] WARN
org.hibernate.util.JDBCExceptionReporter - SQL Error: 50000, SQLState:
HY000
09:10:17.491 [btpool0-7 ] ERROR
org.hibernate.util.JDBCExceptionReporter - General error:
"java.lang.RuntimeException: rowCount expected 214 got 183
LEDGER.LEDGER_IX1"; SQL statement:
update "LEDGER" set VERSION=?, CLIENT_ID=?, REFERENCE=?, NAME=?,
CURRENCY_CODE=?, SCHEDULE_TYPE=?, SCHEDULE_VALUE=?,
EXPORT_DELIVERY_TYPE=?, LATEST_LEDGERUPDATE_ID=? where ID=? and
VERSION=? [50000-141]
GeminiCentral : 09:10:17.491 [btpool0-7 ]
Could not synchronize database state with session
org.hibernate.exception.GenericJDBCException: could not update:
[com.hpdsoftware.gemini.database.domain.Ledger#10]

Any help appreciated.

Cheers

Mat

[UncleTupelo]

Additionally! I am just trying the option to restore and backup from
a script but we don't always keep the lobs.db files folder (probably
should, but only one table uses a blob and once it has been processed
we don't care about it) so when I try and run the equivalent of:

java org.h2.tools.Script -url jdbc:h2:~/test -user sa -script test.zip
-options compression zip

I get a missing lobs exception :

Exception in thread "main" org.h2.jdbc.JdbcSQLException: File not
found: "C:\gemini\h2\demo-database.lobs.db\1.t33.lob.db"; SQL
statement:
SCRIPT TO 'backup.zip' compression zip [90124-141]
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.engine.Database.openFile(Database.java:443)
at org.h2.value.ValueLob.getInputStream(ValueLob.java:610)
at
org.h2.command.dml.ScriptCommand.writeLobStream(ScriptCommand.java:
350)
at org.h2.command.dml.ScriptCommand.query(ScriptCommand.java:
264)
at org.h2.command.CommandContainer.query(CommandContainer.java:
80)
at org.h2.command.Command.executeQuery(Command.java:132)
at
org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:172)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:151)
at org.h2.tools.Script.processScript(Script.java:112)
at org.h2.tools.Script.runTool(Script.java:98)
at org.h2.tools.Script.main(Script.java:50)


I can't delete the problem table rows either for the same reason.

Can I run backup scripts on each table seperately somehow and exclude
my "problem" table?

Cheers

Mat

[UncleTupelo]

Another update - I have been trying the Recover tool, but again the
same 'BLOB' issue occurs, albeit in a different guise.

I can create the sql schema and inserts script ok, but the problem is
that the table with the blob data type is still referred to and an
attempt made to be retrieved in the generated script, and as
mentioned above, I don't have the physical files anymore.

Basically I have a lot of these in the generated script.

INSERT INTO O_61 VALUES(4915, 3, 185, TIMESTAMP '2010-07-28
08:23:39.994', 4915, 20, READ_BLOB('C:\gemini\h2\data\gemini.lobs.db
\151.t61.lob.db.txt'), 50);

The recover tool says: "It also extracts the content of the
transaction log and large objects (CLOB or BLOB) ". I don't want it
to though. I just want it to read:

INSERT INTO O_61 VALUES(4915, 3, 185, TIMESTAMP '2010-07-28
08:23:39.994', 4915, 20, NULL, 50);

Any idea on this? Is it possible to ignore BLOBS\CLOBS in the recover
tool? Is it possible to just generate insert scripts for individual
tables?

Cheers

Mat

[Thomas Mueller]

Hi,

> Do I need to do some kinda upgrade here?

In theory you shouldn't need to, but in your case it looks like there
is a corruption in the database file. In this case please create a new
database using the SQL script of the old database. To create the SQL
script, use the SCRIPT statement or Script tool, and then to create a
new database use the RUNSCRIPT statement or RunScript tool.

> we don't always keep the lobs.db files folder

> File not found

One solution is to copy another file to the expected file name.

> Is it possible to ignore BLOBS\CLOBS in the recover tool?

You would need to manually edit the SQL script and replace the
READ_BLOB calls with NULL. You can use find/replace.

Regads,
Thomas

[UncleTupelo]

Thanks for the response.

> You would need to manually edit the SQL script and replace the
> READ_BLOB calls with NULL. You can use find/replace.

Yes, this is what we did in the end. I ran the recover tool and then
ran a simple regular expression (.*READ_BLOB\(.*?\).*) over the sql
script to replace the READ_BLOB statements with NULL, and all seems
ok.

Well apart from the restored database being twice the size it was
originally, but then I found this post (http://groups.google.com/
group/h2-database/browse_thread/thread/
27e1d11eb3ae8ae0/299a21890e17323a?lnk=gst&q=recover#299a21890e17323a)
and resolved it by doing a normal backup and restore and all was
good.

Cheers

Mat