[ new domain object created with grails console not visible in dbconsole ]

I am trying to create a new domain object in the grails console with the help of this guide. According to the console output the new object is created:

grails> shell
groovy:000> new foo.Book(title: 'bar').save(failOnError: true, flush: true)
groovy:000> foo.Book : 1
groovy:000> foo.Book.list()
groovy:000> [foo.Book : 1]

But this new book entity is not visible in the dbconsole The table BOOK is present when I connect with the JDBC url for the dev environment as found in DataSource.groovy:

jdbc:h2:mem:devDb;MVCC=TRUE
username: sa
password: <blank>

but a select returns 0 rows

The relevant piece of DataSource.groovy config (the default)

dataSource {
    pooled = true
    driverClassName = "org.h2.Driver"
    username = "sa"
    password = ""
}
hibernate {
    cache.use_second_level_cache = true
    cache.use_query_cache = false
    cache.region.factory_class = 'net.sf.ehcache.hibernate.EhCacheRegionFactory' // Hibernate 3
//    cache.region.factory_class = 'org.hibernate.cache.ehcache.EhCacheRegionFactory' // Hibernate 4
}

// environment specific settings
environments {
    development {
        dataSource {
            dbCreate = "create-drop" // one of 'create', 'create-drop', 'update', 'validate', ''
            url = "jdbc:h2:mem:devDb;MVCC=TRUE;LOCK_TIMEOUT=10000"
        }
    }

When the entity is created using the console, rather than the groovy shell, the issue remains.

I am using the newest grails build at this moment, which is 2.3.1
The embedded H2 database vrsion = H2 1.3.173 (2013-07-28)

Answer 1

---

I think the problem is that the database is getting locked. Let's try this one then (works on my experiment):

edit your grails-app/conf/spring/resources.groovy and make it looking like this:

// Place your Spring DSL code here
beans = {
    h2Server(org.h2.tools.Server, "-tcp,-tcpPort,8043") { bean ->
        bean.factoryMethod = "createTcpServer"
        bean.initMethod = "start"
        bean.destroyMethod = "stop"
    }
}

Then, modify your grails-app/conf/DataSource.groovy to look like this:

test {
        dataSource {
            dbCreate = "update"
            url = "jdbc:h2:mem:devDb;MVCC=TRUE;LOCK_TIMEOUT=10000"
        }
}

Now, you are ready to add some new objects as per the tutorial:

$ grails
grails> run-app
grails> shell
groovy:000> new test.Book(title: 'Book 1').save(failOnError: true)
===> test.Book : 1
groovy:000> new test.Book(title: 'Book 2').save(failOnError: true)
===> test.Book : 2
groovy:000> test.Book.list()
===> [test.Book : 1, test.Book : 2]

To view the H2 console, go to

http://localhost:8080/{project}/dbconsole 

but select [Generic H2 Server] from the list and on the JDBC URL enter:

jdbc:h2:tcp://localhost:8043/mem:devDb 

and connect. I hope that helps

======================

After a bit further experimentation, it appears that locking was your problem and you need to use a mixed mode approach when connecting to your H2. You can read more information here:

http://www.h2database.com/html/features.html#auto_mixed_mode

So, the simplest thing to do is use this jdbc connection URL:

url = "jdbc:h2:/tmp/myDB;MVCC=TRUE;LOCK_TIMEOUT=10000;AUTO_SERVER=TRUE"

for both your application and the H2 dbconsole (notice the AUTO_SERVER=TRUE) (no need to modify the spring bean)

Answer 2

---

I suggest to change the

dbCreate = "create-drop"

to

dbCreate = "update"

on your DataSource.groovy and try again

Answer 3

---

When I modified the spring bean as Nick suggested, I could not start run-app and at the same time start the grails console or shell. Here is the error I got:

Message: Error creating bean with name 'h2Server': Invocation of init method failed; nested exception is org.h2.jdbc.JdbcSQLException: Exception opening port "8043" (port may be in use), cause: "java.net.BindException: Address already in use" [90061-173]

The simple change to url worked, thanks Nick -:)