Upgrade to new version

[Volk]

Hello, Thomas.
We are using H2 database and have automatic database update. The
problem occurred when we tried to upgrage h2 version from
1.0(2007-03-04) to the current version 1.0(2007-08-02) just through
replacing h2.jar library.
Is it only one way to upgrade to new version : backup current database
to .sql with old db_engine and run this .sql script with new one? It's
not very convenient when you do it by hand, and really inconvenient
when you try to automatize the process of db updating.

In any case,
thanx for your efforts.

Sincerely yours,
Kirill Volgin.

[Thomas Mueller]

Hi,

So far I have tried to not change the file format whenever possible.
Actually I don't remember changing the file format between 2007-03-04
and now... What was the problem when upgrading?

> Is it only one way to upgrade to new version : backup current database
> to .sql with old db_engine and run this .sql script with new one?

So far yes. When file format changes are required, I will try to write
an automatic upgrade feature (this will automatically create a SQL
script, and execute the script).

> not very convenient when you do it by hand, and really inconvenient
> when you try to automatize the process of db updating.

I know... Currently you need two versions of h2 in the classpath.

Thomas

[Volk]

Hi,
the problem occurs after replacing old h2.jar to new one (we use H2 in
embedded mode in JBoss) :

javax.ejb.EJBException: javax.persistence.PersistenceException:
org.hibernate.exception.GenericJDBCException: could not execute query
at
org.jboss.ejb3.tx.Ejb3TxPolicy.handleExceptionInOurTx(Ejb3TxPolicy.java:
69)
at org.jboss.aspects.tx.TxPolicy.invokeInOurTx(TxPolicy.java:83)
at org.jboss.aspects.tx.TxInterceptor
$Required.invoke(TxInterceptor.java:191)
at
org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:
101)
...
Caused by: org.h2.jdbc.JdbcSQLException: General error:
java.lang.ClassCastException: org.h2.value.ValueStringFixed [HY000-56]
at org.h2.message.Message.getSQLException(Message.java:84)
at org.h2.message.Message.convert(Message.java:386)
at org.h2.command.Command.executeQueryLocal(Command.java:71)
at org.h2.command.Command.executeQuery(Command.java:58)
at
org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:
89)
at
org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:
236)
at
org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:
186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1668)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:
224)
at org.hibernate.loader.Loader.doList(Loader.java:2144)
... 80 more
Caused by: java.lang.ClassCastException: org.h2.value.ValueStringFixed
at org.h2.value.ValueString.compareSecure(ValueString.java:26)
at org.h2.value.Value.compareTypeSave(Value.java:555)
at org.h2.engine.Database.compareTypeSave(Database.java:272)
at org.h2.index.Index.compareValues(Index.java:177)
at org.h2.index.Index.compareRows(Index.java:139)
at org.h2.index.BtreeLeaf.findFirst(BtreeLeaf.java:151)
at org.h2.index.BtreeIndex.find(BtreeIndex.java:185)
at org.h2.table.TableFilter.next(TableFilter.java:218)
at org.h2.command.dml.Select.queryFlat(Select.java:273)
at org.h2.command.dml.Select.queryWithoutCache(Select.java:331)
at org.h2.command.dml.Query.query(Query.java:96)
at org.h2.command.CommandContainer.query(CommandContainer.java:73)
at org.h2.command.Command.executeQueryLocal(Command.java:69)
... 88 more

[Volk]

> So far I have tried to not change the file format whenever possible.
> Actually I don't remember changing the file format between 2007-03-04
> and now... What was the problem when upgrading?

I've found the problem :
in class org.h2.value.ValueString you compare strings the following
way:
protected int compareSecure(Value o, CompareMode mode) {
ValueString v = (ValueString) o;
return mode.compareString(value, v.value, false);
}
And if "o" type is ValustStringFixed then we get ClassCastException.

The possible fix :

protected int compareSecure(Value o, CompareMode mode) {
ValueStringBase v = (ValueStringBase) o;
return mode.compareString(value, v.value, false);
}

it solves my problem. ( I also fixed the same method in
ValueStringFixed and ValueStringIgnoreCase classes)

[Thomas Mueller]

Hi,

I think I understand the problem now. Yes, there was a change
recently: adding the CHARACTER data type. I didn't know this broke
compatibility. I will change compareSecure as you have described, but
only in ValueString (VARCHAR) and ValueStringFixed (CHAR). I don't
think a change is required in ValueStringIgnoreCase
(VARCHAR_IGNORECASE).

Thomas

[Volk]

Hello,

> I will change compareSecure as you have described

Thanks, it'll help me to upgrade to new version.

> but only in ValueString (VARCHAR) and ValueStringFixed (CHAR). I don't
> think a change is required in ValueStringIgnoreCase (VARCHAR_IGNORECASE).

I don't know, I hope not, but it is strange how ValueStringFixed
object appears in compareSecure method of ValueString class, I have
the following schema and select(using hibernate) :

CREATE TABLE T1 (
...
OID CHAR(80) NULL,
...
);

@NamedQuery(name = "findByOID", query = "select object(a) from T1 a
where a.oid=:oid")

And of course, parameter passed to select is String.

Best regards,
Kirill Volgin.

[Thom Pischke]

Would also love to see an auto-migration feature added to H2 soon. I
get nervous whenever switching out h2.jar, and using the backup
technique is somewhat difficult. Especially since that just changed
recently. Ideally, the h2 engine would recognize older formats and
perform some sort of auto-migration.

[Thomas Mueller]

Hi,

There is a feature request for this (Automatic upgrade if there is a
file format change). I will move it up. But this will also take some
time, unless somebody else wants to implement it.

Thomas