Sharing dataSource between Grails and Vaadin SQLContainer causes exception

I’m trying to use Grails/Vaadin with the VaadinOnGrails.com plugin. Things work well for the most part, except when it comes to containers.

I’m trying to share the dataSource from Grails to the Vaadin side for use in an SQLContainer, but it results in a "
This connection has been closed." exception. This happens when I use either H2 and PostgreSQL. I’ve created a sample project which you can run and see the error for yourself here: https://github.com/jbwiv/vaadin_connection_problem_demo

Also, my stackoverflow question is here: http://stackoverflow.com/questions/24874630/vaadin-sqlcontainer-grails-gives-error-org-postgresql-util-psqlexception-thi

Any ideas how to do this? Accessing domain objects via services in Grails works fine…it’s just when I try to use SQLContainer that I run into problems. BeanContainer works, but I’m concerned with performance on very large tables. I’ve also tried HbnContainer, which works initially but fails to close connections and drains the connection pool quickly. There’s some config options you can try in this regard, but having Grails as a piece of this stack complicates those substantially.

I welcome any help you can provide. I think not having access to SQLContainer is a big knock agains Grails + Vaadin.

Thanks!

Refer to the stackoverflow question I linked in my original question. It was solved there.

I’m running into exactly the same error, but in my case I’m using MySQL and a Tomcat connection pool (no Grails).

The common factor seems to be using a datasource with J2EEConnectionPool and a SQLContainer. I suspect there might be a bug in J2EEConnectionPool, or possibly SQLContainer.


Here’s another StackOverflow question
which shows something similar. The comment on the answer indicates that when the author tried to use J2EEConnectionPool, “I get this error: org.postgresql.util.PSQLException: This connection has been closed.”

I realize this is circumstantial but I’m having a lot of trouble tracking down exactly where the problem is myself, and it looks like people with more experience than me also are having trouble. The solutions so far have all been workarounds (don’t use J2EEConnectionPool, create a second datasource, or create a second connection pool.)

More findings.

  1. I should have mentioned before: this problem only started when I switched from SimpleJDBCConnectionPool to J2EEConnectionPool (wrapping Tomcat’s pool).

  2. I have no problems using J2EEConnectionPool with SQLContainer and FreeformQuery; only when I use TableQuery.

  3. Tracing it further, I see a potential issue in in TableQuery.getCount(). After the count has been (successfully) fetched, the cleanup code is as follows:

    finally {
         try {
             if (r != null) {
                 releaseConnection(r.getStatement().getConnection(),
                         r.getStatement(), r);
             }
         } finally {
             if (shouldCloseTransaction) {
                 commit();
             }
         }
    

The code attempts to release the connection if the query was successful (i.e. r is not null). Then it commits and closes the transaction again if shouldCloseTransaction is true.

releaseConnection is only supposed to close the connection if it isn’t part of an active transaction. So maybe the intention was that the commit picks up a case that releaseConnection skips over? But however it is supposed to work, in my case the connectionPool.releaseConnection gets called twice, which leads directly to the “connection is closed” exception.

I’m guessing that SimpleJDBCConnectionPool allows connections to be released twice, but J2EEConnectionPool (or the underlying data source) is throwing the exception.

If I’m right, or possibly if I’m not :slight_smile: I may be able to avoid this problem completely by not using TableQuery with J2EEConnectionPool.

Comments and corrections welcome.

Turns out there was an open ticket for this already, filed a year ago: http://dev.vaadin.com/ticket/12370

Maybe “the powers that be” will take note of the fact that additional developers have run into it since it was first opened… In my case, I was able to eliminate TableQuery from my code.