Hi,
What I do is backup the database, run the upgrade, then on a failure the
backup can be restored. But this really means that the database needs to
be taken offline during any upgrades in-case users connect, update data,
the upgrade fails and they lose their data on the restore. Would that
work for you?. I guess the alternative is to implement running the DDL
statements in transactions, but that sounds hard to me :)
Cheers, Ryan
On 16/05/2011 4:05 PM, Michael wrote:
According to the documentation, quite every DDL statement "commits an
open transaction".
If this is the case, it's quite difficult to implement a correct DB
schema upgrade mechanism without transactions :-(
What I wanted is that upgrade scenario runs in a transaction to avoid
semi-successful schema upgrades.
On May 16, 10:57 am, Ryan How<[email protected]> wrote:
Hi,
My understanding is that DROP statements commit open transactions. So it
would appear that even though the drop statement fails, the open
transaction is still committed.
See
http://www.h2database.com/html/grammar.html?highlight=drop&search=DRO...
"This command commits an open transaction."
My guess is this is the way that it is meant to work. Maybe try putting
an UPDATE to a table that doesn't exist or something like that?
Hope this helps,
Ryan
On 16/05/2011 3:01 PM, Michael wrote:
Hi,
I've just paid an attention that rollback doesn't revert changes to
database made in transaction. This is the simplified code of our
upgrade scenario:
// This line is just for showing what connection parameters we're
using:
JdbcConnectionPool pool = JdbcConnectionPool.create("jdbc:h2:C:
\test;CIPHER=AES;LOCK_TIMEOUT=10000;AUTO_SERVER=TRUE", user, password
+ " " + password);
Connection connection = pool.getConnection();
connection.setAutoCommit(false);
try {
Statement statement = connection.createStatement();
try {
statement.executeUpdate("UPDATE versions SET version='1.1';
DROP non_existent;");
} finally {
statement.close();
}
connection.commit();
} catch (SQLException e) {
connection.rollback();
} finally {
connection.setAutoCommit(true);
}
I intentionally wrote "DROP non_existent" to make the query fail, and
it failed indeed. But the first statement (version increment)
succeeds, even though connection.rollback() is being called.
What am I doing wrong?
Thanks,
Michael
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
