H2 version shipped w/Grails has bad bug, needs upgrade

The version of H2 in Grails is 1.2.147. This version has bug in the JDBC driver. Specifically, when using a remote H2 server (when the JDBC URL says something like "jdbc:h2:tcp:..."), whenever the application calls setAutoCommit(true), the driver actually sends "SET AUTOCOMMIT FALSE" to the database, and vice-versa. You can see the bug in the code for SessionRemote.java, in the trunk, prior to change r4059:

http://code.google.com/p/h2database/source/detail?spec=svn4059&r=4059

Needless to say it's pretty obnoxious to have the database set the auto commit state to the opposite of what you think it should be.

At first, Grails appeared to work for me, because the database connection comes out of the pool with auto commit = true, and the scaffolding controllers don't use transactions (which turn off auto commit), and so the auto commit state never changed. But as soon as I introduced a service (which is transactional by default) things started getting weird. As soon as I called any service, suddenly the scaffolding stopped working. That's because my service set auto commit to false (making it true in the database), and then, when the connection was returned to the pool, DBCP set it to true (really making it false), where it remained for as long as the connection was in the pool, disabling any further updates made by the scaffolding controllers.

It looks like this fix hasn't made it into an H2 release yet (it was just fixed about 4 weeks ago), but please update H2 as soon a a new release becomes available.

A workaround in the meantime is to use a local H2 database, since the bug is only in the remote side of a TCP database connection.