StackOverflowError for large Scrollable result sets

[Jason Ward-6]

Hi folks. Has anyone had difficulties using scrollable result sets when
the results sets are large? I'm hoping it's a simple configuration issue
or coding problem.
 
I'm fronting this with Hibernate so it's possible it's doing something
bad... I'm investigating the ScrollableResultsImpl.java calls, but from
the trace it looks like the problem is somewhere in Derby. Interestingly
enough this works for small result sets.

Here's my simplified code and the corresponding exception. Any help
would be greatly appreciated!

Criteria query = ...
query.setMaxResults(pageSize);
query.setFetchSize(pageSize);

try {
    ScrollableResults results = query.scroll(ScrollMode.SCROLL_INSENSITIVE);
    if(results != null) {
    //results.afterLast();
    while(results.next()){
        System.out.println("get(0) = " +results.get(0));
    }
} catch (Exception e) {
    e.printStackTrace();
}

org.hibernate.exception.GenericJDBCException: could not advance using next()
    at
org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
    at
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
    at
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)
    at
org.hibernate.impl.ScrollableResultsImpl.next(ScrollableResultsImpl.java:127)
    <clipped... removed my private classes from trace>
    at
java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
    at java.util.concurrent.FutureTask.run(FutureTask.java:138)
    at
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
    at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
    at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.SQLException: DERBY SQL error: SQLCODE: -1,
SQLSTATE: XJ001, SQLERRMC: java.lang.StackOverflowErrorXJ001.U
    at
org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown
Source)
    at org.apache.derby.client.am.SqlException.getSQLException(Unknown
Source)
    at org.apache.derby.client.am.ResultSet.next(Unknown Source)
    at
com.mchange.v2.c3p0.impl.NewProxyResultSet.next(NewProxyResultSet.java:2859)
    at
org.hibernate.impl.ScrollableResultsImpl.next(ScrollableResultsImpl.java:122)
    ... 11 more
Caused by: org.apache.derby.client.am.SqlException: DERBY SQL error:
SQLCODE: -1, SQLSTATE: XJ001, SQLERRMC:
java.lang.StackOverflowErrorXJ001.U
    at org.apache.derby.client.am.ResultSet.completeSqlca(Unknown Source)
    at
org.apache.derby.client.net.NetResultSetReply.parseFetchError(Unknown
Source)
    at
org.apache.derby.client.net.NetResultSetReply.parseCNTQRYreply(Unknown
Source)
    at
org.apache.derby.client.net.NetResultSetReply.readPositioningFetch(Unknown
Source)
    at
org.apache.derby.client.net.ResultSetReply.readPositioningFetch(Unknown
Source)
    at
org.apache.derby.client.net.NetResultSet.readPositioningFetch_(Unknown
Source)
    at org.apache.derby.client.am.ResultSet.getRowCount(Unknown Source)
    at
org.apache.derby.client.am.ResultSet.resultSetContainsNoRows(Unknown Source)
    at org.apache.derby.client.am.ResultSet.getNextRowset(Unknown Source)
    at org.apache.derby.client.am.ResultSet.nextX(Unknown Source)
    ... 14 more

Thanks,
JW

[Bryan Pendleton]

> Caused by: org.apache.derby.client.am.SqlException: DERBY SQL error:
> SQLCODE: -1, SQLSTATE: XJ001, SQLERRMC:
> java.lang.StackOverflowErrorXJ001.U
>    at org.apache.derby.client.am.ResultSet.completeSqlca(Unknown Source)

Is there a more detailed exception trace in your derby.log file?

I'd expect to see a stack trace with hundreds, perhaps thousands, of
stack frames in it, which is what I usually see in the cases where I've
had stack overflow errors. Usually such a stack trace makes it pretty
easy to see where there is code that is re-entering itself (possibly
indirectly), and causing the stack overflow.

If you can't find a more detailed exception trace in the derby.log
file, here's another thing you can try to do to get more information:
http://wiki.apache.org/db-derby/UnwindExceptionChain

thanks,

bryan

[Jason Ward-6]

Thanks Bryan, I should have included the info from the derby.log. Here's
a small snippet of the ~1K lines from the log:

2009-09-17 14:58:48.873 GMT Thread[DRDAConnThread_19,5,derby.daemons]
(XID = 5212534), (SESSIONID = 21), (DATABASE = C:/temp/testDB), (DRDAID
= NF000001.H0EE-4195946224626394775{15}), Cleanup action starting
2009-09-17 14:58:48.873 GMT Thread[DRDAConnThread_19,5,derby.daemons]
(XID = 5212534), (SESSIONID = 21), (DATABASE = C:/temp/testDB), (DRDAID
= NF000001.H0EE-4195946224626394775{15}), Failed Statement is: <clipped
for proprietary reasons... I can modify and disclose of required, but
the SQL select looks as expected>:end parameter
java.lang.StackOverflowError
    at java.io.BufferedInputStream.read(BufferedInputStream.java:317)
    at java.io.FilterInputStream.read(FilterInputStream.java:116)
    at org.apache.derby.iapi.services.io.LimitInputStream.read(Unknown
Source)
    at java.io.FilterInputStream.read(FilterInputStream.java:116)
    at java.io.PushbackInputStream.read(PushbackInputStream.java:169)
    at java.io.FilterInputStream.read(FilterInputStream.java:116)
    at java.io.PushbackInputStream.read(PushbackInputStream.java:169)
    at java.io.FilterInputStream.read(FilterInputStream.java:116)
    at java.io.PushbackInputStream.read(PushbackInputStream.java:169)
    at java.io.FilterInputStream.read(FilterInputStream.java:116)
    at java.io.PushbackInputStream.read(PushbackInputStream.java:169)
    at java.io.FilterInputStream.read(FilterInputStream.java:116)
    at java.io.PushbackInputStream.read(PushbackInputStream.java:169)
    at java.io.FilterInputStream.read(FilterInputStream.java:116)
    at java.io.PushbackInputStream.read(PushbackInputStream.java:169)
    at java.io.FilterInputStream.read(FilterInputStream.java:116)
...
    at java.io.PushbackInputStream.read(PushbackInputStream.java:169)
    at java.io.FilterInputStream.read(FilterInputStream.java:116)
Cleanup action completed

Additionally.. unchaining the exception is a little more complicated
with Hibernate in the way, but I've gone about doing so... seems it's
the end of the chain tho. (Interrogating the exception with debugger
confirms that there's no next exception, but I'm not sure if that's only
because someone's swallowing it along the line.)

Thanks,
JW

----- SQLException -----
  SQLState:   XJ001
  Error Code: -1
  Message:    DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ001,
SQLERRMC: java.lang.StackOverflowErrorXJ001.U

Any thoughts on where I should focus my attention? I can't be the only
one using Derby and scrolling on large result-sets can I?

Bryan Pendleton wrote:

>> Caused by: org.apache.derby.client.am.SqlException: DERBY SQL error:
>> SQLCODE: -1, SQLSTATE: XJ001, SQLERRMC:
>> java.lang.StackOverflowErrorXJ001.U
>>    at org.apache.derby.client.am.ResultSet.completeSqlca(Unknown Source)
>
> Is there a more detailed exception trace in your derby.log file?
>
> I'd expect to see a stack trace with hundreds, perhaps thousands, of
> stack frames in it, which is what I usually see in the cases where I've
> had stack overflow errors. Usually such a stack trace makes it pretty
> easy to see where there is code that is re-entering itself (possibly
> indirectly), and causing the stack overflow.
>
> If you can't find a more detailed exception trace in the derby.log
> file, here's another thing you can try to do to get more information:
> http://wiki.apache.org/db-derby/UnwindExceptionChain
>
> thanks,
>
> bryan
>
>

[Bryan Pendleton]

> java.lang.StackOverflowError
>    at java.io.BufferedInputStream.read(BufferedInputStream.java:317)
>    at java.io.FilterInputStream.read(FilterInputStream.java:116)
>    at org.apache.derby.iapi.services.io.LimitInputStream.read(Unknown
...

That's a very interesting stack trace, thanks for including it!

I think it would be great if you could open an issue in Jira, and
include as much of this information as possible.

http://db.apache.org/derby/DerbyBugGuidelines.html

If you can include as much of the stack trace from derby.log, even
the lines that seem repetitive, I think that would be quite useful.

Of course, please elide the parts that touch on confidential or
proprietary parts of your application.

Regarding your other question, about whether you are the only person
who's experiencing this problem, or using these features in this way,
I'm afraid I don't know. Scrollable result sets have been around
for a while, although there was substantial work done on them in 10.2
and 10.3, I believe.

But I certainly haven't seen this particular behavior before.

thanks,

bryan

[Kristian Waagan-4]

Jason Ward wrote:

> Thanks Bryan, I should have included the info from the derby.log.
> Here's a small snippet of the ~1K lines from the log:
>
> 2009-09-17 14:58:48.873 GMT Thread[DRDAConnThread_19,5,derby.daemons]
> (XID = 5212534), (SESSIONID = 21), (DATABASE = C:/temp/testDB),
> (DRDAID = NF000001.H0EE-4195946224626394775{15}), Cleanup action starting
> 2009-09-17 14:58:48.873 GMT Thread[DRDAConnThread_19,5,derby.daemons]
> (XID = 5212534), (SESSIONID = 21), (DATABASE = C:/temp/testDB),
> (DRDAID = NF000001.H0EE-4195946224626394775{15}), Failed Statement is:
> <clipped for proprietary reasons... I can modify and disclose of
> required, but the SQL select looks as expected>:end parameter
> java.lang.StackOverflowError
>    at java.io.BufferedInputStream.read(BufferedInputStream.java:317)
>    at java.io.FilterInputStream.read(FilterInputStream.java:116)
>    at org.apache.derby.iapi.services.io.LimitInputStream.read(Unknown
> Source)
>    at java.io.FilterInputStream.read(FilterInputStream.java:116)
>    at java.io.PushbackInputStream.read(PushbackInputStream.java:169)
>    at java.io.FilterInputStream.read(FilterInputStream.java:116)
>    at java.io.PushbackInputStream.read(PushbackInputStream.java:169)
>    at java.io.FilterInputStream.read(FilterInputStream.java:116)
>    at java.io.PushbackInputStream.read(PushbackInputStream.java:169)
>    at java.io.FilterInputStream.read(FilterInputStream.java:116)
>    at java.io.PushbackInputStream.read(PushbackInputStream.java:169)
>    at java.io.FilterInputStream.read(FilterInputStream.java:116)
>    at java.io.PushbackInputStream.read(PushbackInputStream.java:169)
>    at java.io.FilterInputStream.read(FilterInputStream.java:116)
>    at java.io.PushbackInputStream.read(PushbackInputStream.java:169)
>    at java.io.FilterInputStream.read(FilterInputStream.java:116)
> ...
>    at java.io.PushbackInputStream.read(PushbackInputStream.java:169)
>    at java.io.FilterInputStream.read(FilterInputStream.java:116)
> Cleanup action completed
>
> Additionally.. unchaining the exception is a little more complicated
> with Hibernate in the way, but I've gone about doing so... seems it's
> the end of the chain tho. (Interrogating the exception with debugger
> confirms that there's no next exception, but I'm not sure if that's
> only because someone's swallowing it along the line.)
>
> Thanks,
> JW
>
> ----- SQLException -----
>  SQLState:   XJ001
>  Error Code: -1
>  Message:    DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ001,
> SQLERRMC: java.lang.StackOverflowErrorXJ001.U
>
> Any thoughts on where I should focus my attention? I can't be the only
> one using Derby and scrolling on large result-sets can I?

Hi Jason,

Just a few control questions, as Derby doesn't use PushbackInputStream
much internally:
 1) Which Derby version are you using?
 2a) Does the failing result set contain any CLOB columns?
 2b) Are you accessing an upgraded database?


Regards,
--
Kristian

>
> Bryan Pendleton wrote:
>>> Caused by: org.apache.derby.client.am.SqlException: DERBY SQL error:
>>> SQLCODE: -1, SQLSTATE: XJ001, SQLERRMC:
>>> java.lang.StackOverflowErrorXJ001.U
>>>    at org.apache.derby.client.am.ResultSet.completeSqlca(Unknown
>>> Source)
>>
>> Is there a more detailed exception trace in your derby.log file?
>>
>> I'd expect to see a stack trace with hundreds, perhaps thousands, of
>> stack frames in it, which is what I usually see in the cases where I've
>> had stack overflow errors. Usually such a stack trace makes it pretty
>> easy to see where there is code that is re-entering itself (possibly
>> indirectly), and causing the stack overflow.
>>
>> If you can't find a more detailed exception trace in the derby.log
>> file, here's another thing you can try to do to get more information:
>> http://wiki.apache.org/db-derby/UnwindExceptionChain
>>
>> thanks,
>>
>> bryan
>>
>>

[Jason Ward-6]

> Hi Jason,
>
> Just a few control questions, as Derby doesn't use PushbackInputStream
> much internally:
> 1) Which Derby version are you using?
> 2a) Does the failing result set contain any CLOB columns?
> 2b) Are you accessing an upgraded database?
1 - I just upgraded to 10.5.3 from 10.5.2 and verify it's still an
issue. From derby.log "Apache Derby Network Server - 10.5.3.0 - (802917)"
2a - Yes!
2b -  No

Interesting piece of info I came across while putting together a small
Eclipse project for repro... it seems very specific to my actual schema
and the inclusion of an Order By clause on the query. I'm still
debugging but seeing it work successfully on 350K result set on my
repro-app was encouraging.

Thanks,
JW

Kristian Waagan wrote:

> Jason Ward wrote:
>> Thanks Bryan, I should have included the info from the derby.log.
>> Here's a small snippet of the ~1K lines from the log:
>>
>> 2009-09-17 14:58:48.873 GMT Thread[DRDAConnThread_19,5,derby.daemons]
>> (XID = 5212534), (SESSIONID = 21), (DATABASE = C:/temp/testDB),
>> (DRDAID = NF000001.H0EE-4195946224626394775{15}), Cleanup action
>> starting
>> 2009-09-17 14:58:48.873 GMT Thread[DRDAConnThread_19,5,derby.daemons]
>> (XID = 5212534), (SESSIONID = 21), (DATABASE = C:/temp/testDB),
>> (DRDAID = NF000001.H0EE-4195946224626394775{15}), Failed Statement
>> is: <clipped for proprietary reasons... I can modify and disclose of
>> required, but the SQL select looks as expected>:end parameter
>> java.lang.StackOverflowError
>>    at java.io.BufferedInputStream.read(BufferedInputStream.java:317)
>>    at java.io.FilterInputStream.read(FilterInputStream.java:116)
>>    at org.apache.derby.iapi.services.io.LimitInputStream.read(Unknown
>> Source)
>>    at java.io.FilterInputStream.read(FilterInputStream.java:116)
>>    at java.io.PushbackInputStream.read(PushbackInputStream.java:169)
>>    at java.io.FilterInputStream.read(FilterInputStream.java:116)
>>    at java.io.PushbackInputStream.read(PushbackInputStream.java:169)
>>    at java.io.FilterInputStream.read(FilterInputStream.java:116)
>>    at java.io.PushbackInputStream.read(PushbackInputStream.java:169)
>>    at java.io.FilterInputStream.read(FilterInputStream.java:116)
>>    at java.io.PushbackInputStream.read(PushbackInputStream.java:169)
>>    at java.io.FilterInputStream.read(FilterInputStream.java:116)
>>    at java.io.PushbackInputStream.read(PushbackInputStream.java:169)
>>    at java.io.FilterInputStream.read(FilterInputStream.java:116)
>>    at java.io.PushbackInputStream.read(PushbackInputStream.java:169)
>>    at java.io.FilterInputStream.read(FilterInputStream.java:116)
>> ...
>>    at java.io.PushbackInputStream.read(PushbackInputStream.java:169)
>>    at java.io.FilterInputStream.read(FilterInputStream.java:116)
>> Cleanup action completed
>>
>> Additionally.. unchaining the exception is a little more complicated
>> with Hibernate in the way, but I've gone about doing so... seems it's
>> the end of the chain tho. (Interrogating the exception with debugger
>> confirms that there's no next exception, but I'm not sure if that's
>> only because someone's swallowing it along the line.)
>>
>> Thanks,
>> JW
>>
>> ----- SQLException -----
>>  SQLState:   XJ001
>>  Error Code: -1
>>  Message:    DERBY SQL error: SQLCODE: -1, SQLSTATE: XJ001,
>> SQLERRMC: java.lang.StackOverflowErrorXJ001.U
>>
>> Any thoughts on where I should focus my attention? I can't be the
>> only one using Derby and scrolling on large result-sets can I?
>
> Hi Jason,
>
> Just a few control questions, as Derby doesn't use PushbackInputStream
> much internally:
> 1) Which Derby version are you using?
> 2a) Does the failing result set contain any CLOB columns?
> 2b) Are you accessing an upgraded database?
>
>
> Regards,