Hi, Thanks, this can work. My only concern is the upgrade time.
BTW, just found that DDL statements upgrade is in the H2 roadmap for v4 :-) On Mon, May 16, 2011 at 11:14 AM, Ryan How <[email protected]> wrote: > 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. > > -- 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.
