I was finally able to reproduce the problems and was able to narrow it
down to simple queries.
The problem occurs when there is a join query across a view and
another table with a WHERE clause referencing the table:
select some_view.id from some_view inner join some_table on
some_view.some_id=some_table.id where some_table.another_id in (1,2)
Changing the "inner join" to "left outer join" doesn't make a
difference.
I noticed that the stack trace contained some "updateAggregate"
references, so here's a simplified definition of the view:
create view if not exists some_view as
select a.id,
a.status,
count(svs.id) as vulnerabilities,
(select max(id) from acme where id < a.id and some_id =
some_table.some_id) previous_a_id
from acme a left outer join svs on a.svs_id = svs.id
group by s.id, s.status
Notice that some_table is referenced in the sub-select as well as in
the original query above.
Let me know if there is some more specific information I should
provide,
Chris
On Sep 29, 1:09 pm, Thomas Mueller <[email protected]>
wrote:
> Hi,
>
> Could you append ";TRACE_LEVEL_FILE=2" to the database URL and then
> check in the .trace.db file what SQL statement caused this problem? Or
> maybe there is another way to find out what SQL statement caused the
> problem. It looks like a bug in H2 where the internal query data
> structure is broken when re-running a query. However, I don't
> understand what the problem could be. With the actual statement it's
> much simpler to analyze the problem.
>
> Regards,
> Thomas
>
>
>
> On Mon, Sep 27, 2010 at 11:09 PM, Chris <[email protected]> wrote:
> > Hi,
>
> > We're using H2 (version 1.2.143) in a Grails project with connection
> > string
> > "jdbc:h2:file:mydb;MVCC=TRUE;DEFAULT_LOCK_TIMEOUT=3000;AUTO_SERVER=TRUE;CAC
> > HE_SIZE=262144"
> > When using a pooled datasource, the following stacktrace occurs
> > regularly when performing a criteria query over a view:
>
> > 13:55:08,168 ERROR [GrailsExceptionResolver] - General error:
> > "java.lang.NullPointerException" [50000-143]
> > org.h2.jdbc.JdbcSQLException: General error:
> > "java.lang.NullPointerException" [50000-143]
> > at org.h2.message.DbException.getJdbcSQLException(DbException.java:
> > 327)
> > at org.h2.message.DbException.get(DbException.java:156)
> > at org.h2.message.DbException.convert(DbException.java:279)
> > at org.h2.server.TcpServerThread.sendError(TcpServerThread.java:186)
> > at org.h2.server.TcpServerThread.run(TcpServerThread.java:139)
> > at java.lang.Thread.run(Thread.java:637)
> > Caused by: java.lang.NullPointerException
> > at org.h2.index.IndexCursor.get(IndexCursor.java:209)
> > at org.h2.table.TableFilter.getValue(TableFilter.java:824)
> > at
> > org.h2.expression.ExpressionColumn.updateAggregate(ExpressionColumn.java:
> > 133)
> > at org.h2.expression.Comparison.updateAggregate(Comparison.java:411)
> > at
> > org.h2.expression.ConditionAndOr.updateAggregate(ConditionAndOr.java:
> > 242)
> > at org.h2.command.dml.Select.updateAggregate(Select.java:1106)
> > at org.h2.expression.Subquery.updateAggregate(Subquery.java:92)
> > at org.h2.expression.Alias.updateAggregate(Alias.java:76)
> > at org.h2.command.dml.Select.queryGroup(Select.java:336)
> > at org.h2.command.dml.Select.queryWithoutCache(Select.java:575)
> > at org.h2.command.dml.Query.query(Query.java:241)
> > at org.h2.index.ViewIndex.find(ViewIndex.java:231)
> > at org.h2.index.IndexCursor.find(IndexCursor.java:138)
> > at org.h2.table.TableFilter.next(TableFilter.java:309)
> > at org.h2.table.TableFilter.next(TableFilter.java:379)
> > at org.h2.table.TableFilter.next(TableFilter.java:379)
> > at org.h2.table.TableFilter.next(TableFilter.java:379)
> > at org.h2.command.dml.Select.queryFlat(Select.java:493)
> > at org.h2.command.dml.Select.queryWithoutCache(Select.java:580)
> > at org.h2.command.dml.Query.query(Query.java:241)
> > at org.h2.index.ViewIndex.find(ViewIndex.java:231)
> > at org.h2.index.IndexCursor.find(IndexCursor.java:138)
> > at org.h2.table.TableFilter.next(TableFilter.java:309)
> > at org.h2.command.dml.Select.queryFlat(Select.java:493)
> > at org.h2.command.dml.Select.queryWithoutCache(Select.java:580)
> > at org.h2.command.dml.Query.query(Query.java:241)
> > at org.h2.command.CommandContainer.query(CommandContainer.java:80)
> > at org.h2.command.Command.executeQuery(Command.java:132)
> > at org.h2.server.TcpServerThread.process(TcpServerThread.java:278)
> > at org.h2.server.TcpServerThread.run(TcpServerThread.java:137)
>
> > This doesn't happen the first time the query runs after the
> > application starts, but every time afterwards. Also, when not using a
> > pooled connection, this problem doesn't seem to appear, so we are
> > currently assuming it might have something to do with Hibernate
> > session management in Grails. However, theNullPointerException
> > doesn't give us any hints on where to start and there is no related
> > Hibernate exception.
>
> > The AUTO_SERVER feature is used, because there are actually two
> > separate web applications running, sharing the same database and it's
> > not predictable which application starts first.
>
> > I'd appreciate any hints for a workaround or solution,
>
> > Chris
>
> > --
> > 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
> > athttp://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.
