new domain object created with grails console not visible in dbconsole

Question

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)

Found [this](http://stackoverflow.com/questions/2040055/grails-shell-not-seeing-domain-objects) which suggests wrapping the save in a transaction? — tim_yates, Oct 23 '13 at 13:11
@tim_yates in the question to which you refer to, the issue seems that objects cannot be created in the shell/console without using the transaction wrapping. My problem is different because the object is created fine in the shell and list() returns the newly created object of the type, but the object is not visible in the H2 dbconsole. Anyway, I tried it with the transaction wrapping but the result is the same. — nkr1pt, Oct 23 '13 at 13:29

Nick De Greek · Accepted Answer · 2013-10-25T16:09:26.510

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)

hi Nick, I came to exactly the same conclusion while reading the, surprisingly clear, h2 docs; but didn't come to writing it down here yet. So thank you for your clear explanation which will surely help more users who will stumble upon this question. Have an upvote and accepted answer; well deserved — nkr1pt, Oct 25 '13 at 16:29
Good answer. Just to be clear, per the docs: the AUTO_SERVER=TRUE feature does NOT work with in-memory databases. — Brice Roncace, May 15 '14 at 14:23

score 0 · Answer 2 · answered Oct 23 '13 at 15:15

0

I suggest to change the

dbCreate = "create-drop"

to

dbCreate = "update"

on your DataSource.groovy and try again

answered Oct 23 '13 at 15:15

Nick De Greek

1,834
1
18
19

Yingliang · Answer 3 · 2014-04-08T12:58:21.710

0

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 -:)

edited Apr 08 '14 at 12:58

answered Apr 08 '14 at 12:47

Yingliang

21
3

This is not an answer to the question. – Nejat Apr 08 '14 at 13:06

new domain object created with grails console not visible in dbconsole

3 Answers3