Ibatis query returns no rows even though it should.

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Ibatis query returns no rows even though it should.

old_as_a_fossil
My application is issuing a SELECT FOR UPDATE query on a Oracle DB. There is only one thread querying the database, so there is no question of some other thread locking rows. The query is supposed to return a row, but it doesn't, for many iterations (done fromthe application), and only then it returns a row. I looked at the Ibatis log to see if something helps, but nothing such. Below is the log. The same query executed without FOR UPDATE on the sql client returns a row. Anyone knows what is going wrong?
 
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.Connection : {conn-110131} Connection
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.Connection : {conn-110131} Connection
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.Connection : {conn-110131} Preparing Statement:    select Q.JOB_ID, J.ORGNAME, J.CREATED, J.CONTENT_BASENAME, J.CONTENT_TYPE,     Q.RESTART_COUNT, JR.USERNAME, JR.RESEND_COUNT,             JE.EMAILADDR, JE.IS_PRIMARY_EMAIL          from ARSND_JOB_QUEUE Q, ARSND_JOBS J, ARSND_JOB_RECIPIENTS JR,               ARSND_JOB_EMAILS JE,         ( select JOB_ID          from ARSND_JOB_QUEUE Q2          where PROCESSING_STATE = ?            and WORKING_INSTANCE IS null     and WORKING_THREAD = 0            and ORGNAME not in     (      ?    )                order by LAST_UPDATED ) OldestReadyFirst          where J.JOB_ID = Q.JOB_ID            and JR.JOB_ID=J.JOB_ID            and JR.RESEND_COUNT=J.CONTENT_RESEND_COUNT            and JE.JOB_ID=JR.JOB_ID            and JE.RESEND_COUNT=JR.RESEND_COUNT            and JE.USERNAME=JR.USERNAME            and JE.ORGNAME=JR.ORGNAME            and Q.JOB_ID = OldestReadyFirst.JOB_ID            and rownum = 1          for update of Q.WORKING_INSTANCE NOWAIT 
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.Connection : {conn-110131} Preparing Statement:    select Q.JOB_ID, J.ORGNAME, J.CREATED, J.CONTENT_BASENAME, J.CONTENT_TYPE,     Q.RESTART_COUNT, JR.USERNAME, JR.RESEND_COUNT,             JE.EMAILADDR, JE.IS_PRIMARY_EMAIL          from ARSND_JOB_QUEUE Q, ARSND_JOBS J, ARSND_JOB_RECIPIENTS JR,               ARSND_JOB_EMAILS JE,         ( select JOB_ID          from ARSND_JOB_QUEUE Q2          where PROCESSING_STATE = ?            and WORKING_INSTANCE IS null     and WORKING_THREAD = 0            and ORGNAME not in     (      ?    )                order by LAST_UPDATED ) OldestReadyFirst          where J.JOB_ID = Q.JOB_ID            and JR.JOB_ID=J.JOB_ID            and JR.RESEND_COUNT=J.CONTENT_RESEND_COUNT            and JE.JOB_ID=JR.JOB_ID            and JE.RESEND_COUNT=JR.RESEND_COUNT            and JE.USERNAME=JR.USERNAME            and JE.ORGNAME=JR.ORGNAME            and Q.JOB_ID = OldestReadyFirst.JOB_ID            and rownum = 1          for update of Q.WORKING_INSTANCE NOWAIT 
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.PreparedStatement : {pstm-110132} Executing Statement:    select Q.JOB_ID, J.ORGNAME, J.CREATED, J.CONTENT_BASENAME, J.CONTENT_TYPE,     Q.RESTART_COUNT, JR.USERNAME, JR.RESEND_COUNT,             JE.EMAILADDR, JE.IS_PRIMARY_EMAIL          from ARSND_JOB_QUEUE Q, ARSND_JOBS J, ARSND_JOB_RECIPIENTS JR,               ARSND_JOB_EMAILS JE,         ( select JOB_ID          from ARSND_JOB_QUEUE Q2          where PROCESSING_STATE = ?            and WORKING_INSTANCE IS null     and WORKING_THREAD = 0            and ORGNAME not in     (      ?    )                order by LAST_UPDATED ) OldestReadyFirst          where J.JOB_ID = Q.JOB_ID            and JR.JOB_ID=J.JOB_ID            and JR.RESEND_COUNT=J.CONTENT_RESEND_COUNT            and JE.JOB_ID=JR.JOB_ID            and JE.RESEND_COUNT=JR.RESEND_COUNT            and JE.USERNAME=JR.USERNAME            and JE.ORGNAME=JR.ORGNAME            and Q.JOB_ID = OldestReadyFirst.JOB_ID            and rownum = 1          for update of Q.WORKING_INSTANCE NOWAIT 
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.PreparedStatement : {pstm-110132} Executing Statement:    select Q.JOB_ID, J.ORGNAME, J.CREATED, J.CONTENT_BASENAME, J.CONTENT_TYPE,     Q.RESTART_COUNT, JR.USERNAME, JR.RESEND_COUNT,             JE.EMAILADDR, JE.IS_PRIMARY_EMAIL          from ARSND_JOB_QUEUE Q, ARSND_JOBS J, ARSND_JOB_RECIPIENTS JR,               ARSND_JOB_EMAILS JE,         ( select JOB_ID          from ARSND_JOB_QUEUE Q2          where PROCESSING_STATE = ?            and WORKING_INSTANCE IS null     and WORKING_THREAD = 0            and ORGNAME not in     (      ?    )                order by LAST_UPDATED ) OldestReadyFirst          where J.JOB_ID = Q.JOB_ID            and JR.JOB_ID=J.JOB_ID            and JR.RESEND_COUNT=J.CONTENT_RESEND_COUNT            and JE.JOB_ID=JR.JOB_ID            and JE.RESEND_COUNT=JR.RESEND_COUNT            and JE.USERNAME=JR.USERNAME            and JE.ORGNAME=JR.ORGNAME            and Q.JOB_ID = OldestReadyFirst.JOB_ID            and rownum = 1          for update of Q.WORKING_INSTANCE NOWAIT 
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.PreparedStatement : {pstm-110132} Parameters: [10, DEFAULTORG]
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.PreparedStatement : {pstm-110132} Parameters: [10, DEFAULTORG]
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.PreparedStatement : {pstm-110132} Types: [java.lang.Integer, java.lang.String]
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.PreparedStatement : {pstm-110132} Types: [java.lang.Integer, java.lang.String]
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.ResultSet : {rset-110133} ResultSet
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.ResultSet : {rset-110133} ResultSet
 
 
Thanks
Kannan

Reply | Threaded
Open this post in threaded view
|

Re: Ibatis query returns no rows even though it should.

old_as_a_fossil
Any ideas, anyone? I find it difficult to believe Ibatis has such a HUGE bug, but I don't see anything wrong in my code.


--- On Wed, 8/9/10, Kannan J <[hidden email]> wrote:

From: Kannan J <[hidden email]>
Subject: Ibatis query returns no rows even though it should.
To: [hidden email]
Date: Wednesday, 8 September, 2010, 1:44 PM

My application is issuing a SELECT FOR UPDATE query on a Oracle DB. There is only one thread querying the database, so there is no question of some other thread locking rows. The query is supposed to return a row, but it doesn't, for many iterations (done fromthe application), and only then it returns a row. I looked at the Ibatis log to see if something helps, but nothing such. Below is the log. The same query executed without FOR UPDATE on the sql client returns a row. Anyone knows what is going wrong?
 
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.Connection : {conn-110131} Connection
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.Connection : {conn-110131} Connection
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.Connection : {conn-110131} Preparing Statement:    select Q.JOB_ID, J.ORGNAME, J.CREATED, J.CONTENT_BASENAME, J.CONTENT_TYPE,     Q.RESTART_COUNT, JR.USERNAME, JR.RESEND_COUNT,             JE.EMAILADDR, JE.IS_PRIMARY_EMAIL          from ARSND_JOB_QUEUE Q, ARSND_JOBS J, ARSND_JOB_RECIPIENTS JR,               ARSND_JOB_EMAILS JE,         ( select JOB_ID          from ARSND_JOB_QUEUE Q2          where PROCESSING_STATE = ?            and WORKING_INSTANCE IS null     and WORKING_THREAD = 0            and ORGNAME not in     (      ?    )                order by LAST_UPDATED ) OldestReadyFirst          where J.JOB_ID = Q.JOB_ID            and JR.JOB_ID=J.JOB_ID            and JR.RESEND_COUNT=J.CONTENT_RESEND_COUNT            and JE.JOB_ID=JR.JOB_ID            and JE.RESEND_COUNT=JR.RESEND_COUNT            and JE.USERNAME=JR.USERNAME            and JE.ORGNAME=JR.ORGNAME            and Q.JOB_ID = OldestReadyFirst.JOB_ID            and rownum = 1          for update of Q.WORKING_INSTANCE NOWAIT 
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.Connection : {conn-110131} Preparing Statement:    select Q.JOB_ID, J.ORGNAME, J.CREATED, J.CONTENT_BASENAME, J.CONTENT_TYPE,     Q.RESTART_COUNT, JR.USERNAME, JR.RESEND_COUNT,             JE.EMAILADDR, JE.IS_PRIMARY_EMAIL          from ARSND_JOB_QUEUE Q, ARSND_JOBS J, ARSND_JOB_RECIPIENTS JR,               ARSND_JOB_EMAILS JE,         ( select JOB_ID          from ARSND_JOB_QUEUE Q2          where PROCESSING_STATE = ?            and WORKING_INSTANCE IS null     and WORKING_THREAD = 0            and ORGNAME not in     (      ?    )                order by LAST_UPDATED ) OldestReadyFirst          where J.JOB_ID = Q.JOB_ID            and JR.JOB_ID=J.JOB_ID            and JR.RESEND_COUNT=J.CONTENT_RESEND_COUNT            and JE.JOB_ID=JR.JOB_ID            and JE.RESEND_COUNT=JR.RESEND_COUNT            and JE.USERNAME=JR.USERNAME            and JE.ORGNAME=JR.ORGNAME            and Q.JOB_ID = OldestReadyFirst.JOB_ID            and rownum = 1          for update of Q.WORKING_INSTANCE NOWAIT 
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.PreparedStatement : {pstm-110132} Executing Statement:    select Q.JOB_ID, J.ORGNAME, J.CREATED, J.CONTENT_BASENAME, J.CONTENT_TYPE,     Q.RESTART_COUNT, JR.USERNAME, JR.RESEND_COUNT,             JE.EMAILADDR, JE.IS_PRIMARY_EMAIL          from ARSND_JOB_QUEUE Q, ARSND_JOBS J, ARSND_JOB_RECIPIENTS JR,               ARSND_JOB_EMAILS JE,         ( select JOB_ID          from ARSND_JOB_QUEUE Q2          where PROCESSING_STATE = ?            and WORKING_INSTANCE IS null     and WORKING_THREAD = 0            and ORGNAME not in     (      ?    )                order by LAST_UPDATED ) OldestReadyFirst          where J.JOB_ID = Q.JOB_ID            and JR.JOB_ID=J.JOB_ID            and JR.RESEND_COUNT=J.CONTENT_RESEND_COUNT            and JE.JOB_ID=JR.JOB_ID            and JE.RESEND_COUNT=JR.RESEND_COUNT            and JE.USERNAME=JR.USERNAME            and JE.ORGNAME=JR.ORGNAME            and Q.JOB_ID = OldestReadyFirst.JOB_ID            and rownum = 1          for update of Q.WORKING_INSTANCE NOWAIT 
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.PreparedStatement : {pstm-110132} Executing Statement:    select Q.JOB_ID, J.ORGNAME, J.CREATED, J.CONTENT_BASENAME, J.CONTENT_TYPE,     Q.RESTART_COUNT, JR.USERNAME, JR.RESEND_COUNT,             JE.EMAILADDR, JE.IS_PRIMARY_EMAIL          from ARSND_JOB_QUEUE Q, ARSND_JOBS J, ARSND_JOB_RECIPIENTS JR,               ARSND_JOB_EMAILS JE,         ( select JOB_ID          from ARSND_JOB_QUEUE Q2          where PROCESSING_STATE = ?            and WORKING_INSTANCE IS null     and WORKING_THREAD = 0            and ORGNAME not in     (      ?    )                order by LAST_UPDATED ) OldestReadyFirst          where J.JOB_ID = Q.JOB_ID            and JR.JOB_ID=J.JOB_ID            and JR.RESEND_COUNT=J.CONTENT_RESEND_COUNT            and JE.JOB_ID=JR.JOB_ID            and JE.RESEND_COUNT=JR.RESEND_COUNT            and JE.USERNAME=JR.USERNAME            and JE.ORGNAME=JR.ORGNAME            and Q.JOB_ID = OldestReadyFirst.JOB_ID            and rownum = 1          for update of Q.WORKING_INSTANCE NOWAIT 
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.PreparedStatement : {pstm-110132} Parameters: [10, DEFAULTORG]
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.PreparedStatement : {pstm-110132} Parameters: [10, DEFAULTORG]
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.PreparedStatement : {pstm-110132} Types: [java.lang.Integer, java.lang.String]
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.PreparedStatement : {pstm-110132} Types: [java.lang.Integer, java.lang.String]
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.ResultSet : {rset-110133} ResultSet
2010-09-08 13:39:02,574 : [SUPR] : DEBUG : java.sql.ResultSet : {rset-110133} ResultSet
 
 
Thanks
Kannan


Reply | Threaded
Open this post in threaded view
|

Re: Ibatis query returns no rows even though it should.

Larry Meadors
On Wed, Sep 8, 2010 at 6:23 AM, Kannan J
<[hidden email]> wrote:
> Any ideas, anyone? I find it difficult to believe Ibatis has such a HUGE bug, but I don't see anything wrong in my code.

Me too.

Did you try it with JDBC?

Did you try removing the "for update" part?

Simplify the issue until it works, then build it back up to see where it breaks.

Larry
Reply | Threaded
Open this post in threaded view
|

Re: Ibatis query returns no rows even though it should.

old_as_a_fossil
(Recap: Ibatis query returned empty rows when a row should be returned. I was using select for update query.)
 
I tried after removing the "for update" clause, still Ibatis query returned no rows for several iterations, before finally returning a row. Bug in Ibatis?
 
thanks
Kannan
 
--- On Wed, 8/9/10, Larry Meadors <[hidden email]> wrote:

From: Larry Meadors <[hidden email]>
Subject: Re: Ibatis query returns no rows even though it should.
To: [hidden email]
Date: Wednesday, 8 September, 2010, 6:05 PM

On Wed, Sep 8, 2010 at 6:23 AM, Kannan J
<kannan_jayaprakasam@...> wrote:
> Any ideas, anyone? I find it difficult to believe Ibatis has such a HUGE bug, but I don't see anything wrong in my code.

Me too.

Did you try it with JDBC?

Did you try removing the "for update" part?

Simplify the issue until it works, then build it back up to see where it breaks.

Larry

Reply | Threaded
Open this post in threaded view
|

RE: Ibatis query returns no rows even though it should.

Poitras Christian

I don’t think it’s a bug since you seem to be the only one having it – unless you have an uncommon configuration.

Did you try the latest version?

 

Otherwise, I can only recommend you to do as Larry said by simplifying the issue.

You can also post you configuration. That may help us.

 

Christian

 

De : [hidden email] [mailto:[hidden email]] De la part de Kannan J
Envoyé : September-15-10 5:45 AM
À : [hidden email]
Objet : Re: Ibatis query returns no rows even though it should.

 

(Recap: Ibatis query returned empty rows when a row should be returned. I was using select for update query.)

 

I tried after removing the "for update" clause, still Ibatis query returned no rows for several iterations, before finally returning a row. Bug in Ibatis?

 

thanks

Kannan

 

--- On Wed, 8/9/10, Larry Meadors <[hidden email]> wrote:


From: Larry Meadors <[hidden email]>
Subject: Re: Ibatis query returns no rows even though it should.
To: [hidden email]
Date: Wednesday, 8 September, 2010, 6:05 PM

On Wed, Sep 8, 2010 at 6:23 AM, Kannan J
<kannan_jayaprakasam@...> wrote:
> Any ideas, anyone? I find it difficult to believe Ibatis has such a HUGE bug, but I don't see anything wrong in my code.

Me too.

Did you try it with JDBC?

Did you try removing the "for update" part?

Simplify the issue until it works, then build it back up to see where it breaks.

Larry

 

Reply | Threaded
Open this post in threaded view
|

Re: Ibatis query returns no rows even though it should.

Larry Meadors
In reply to this post by old_as_a_fossil
Uh. No rows or empty rows. Which is it? Can you reproduce it in a unit
test that you can share? Have you tried to simplify it until it works?
Have you tried jdbc without ibatis?

You have to give us something to work with here.

Obviously if queries just failed arbitrarily someone would have
noticed by now, don't you think?

Larry



On 9/15/10, Kannan J <[hidden email]> wrote:

> (Recap: Ibatis query returned empty rows when a row should be returned. I
> was using select for update query.)
>
> I tried after removing the "for update" clause, still Ibatis query returned
> no rows for several iterations, before finally returning a row. Bug in
> Ibatis?
>
> thanks
> Kannan
>
> --- On Wed, 8/9/10, Larry Meadors <[hidden email]> wrote:
>
>
> From: Larry Meadors <[hidden email]>
> Subject: Re: Ibatis query returns no rows even though it should.
> To: [hidden email]
> Date: Wednesday, 8 September, 2010, 6:05 PM
>
>
> On Wed, Sep 8, 2010 at 6:23 AM, Kannan J
> <[hidden email]> wrote:
>> Any ideas, anyone? I find it difficult to believe Ibatis has such a HUGE
>> bug, but I don't see anything wrong in my code.
>
> Me too.
>
> Did you try it with JDBC?
>
> Did you try removing the "for update" part?
>
> Simplify the issue until it works, then build it back up to see where it
> breaks.
>
> Larry
>
>
>

--
Sent from my mobile device
Reply | Threaded
Open this post in threaded view
|

Re: Ibatis query returns no rows even though it should.

Simone Tripodi
I had a similar issue, but I just forgot to set the auto commit to true :P
Simo

http://people.apache.org/~simonetripodi/
http://www.99soft.org/



On Wed, Sep 15, 2010 at 4:36 PM, Larry Meadors <[hidden email]> wrote:

> Uh. No rows or empty rows. Which is it? Can you reproduce it in a unit
> test that you can share? Have you tried to simplify it until it works?
> Have you tried jdbc without ibatis?
>
> You have to give us something to work with here.
>
> Obviously if queries just failed arbitrarily someone would have
> noticed by now, don't you think?
>
> Larry
>
>
>
> On 9/15/10, Kannan J <[hidden email]> wrote:
>> (Recap: Ibatis query returned empty rows when a row should be returned. I
>> was using select for update query.)
>>
>> I tried after removing the "for update" clause, still Ibatis query returned
>> no rows for several iterations, before finally returning a row. Bug in
>> Ibatis?
>>
>> thanks
>> Kannan
>>
>> --- On Wed, 8/9/10, Larry Meadors <[hidden email]> wrote:
>>
>>
>> From: Larry Meadors <[hidden email]>
>> Subject: Re: Ibatis query returns no rows even though it should.
>> To: [hidden email]
>> Date: Wednesday, 8 September, 2010, 6:05 PM
>>
>>
>> On Wed, Sep 8, 2010 at 6:23 AM, Kannan J
>> <[hidden email]> wrote:
>>> Any ideas, anyone? I find it difficult to believe Ibatis has such a HUGE
>>> bug, but I don't see anything wrong in my code.
>>
>> Me too.
>>
>> Did you try it with JDBC?
>>
>> Did you try removing the "for update" part?
>>
>> Simplify the issue until it works, then build it back up to see where it
>> breaks.
>>
>> Larry
>>
>>
>>
>
> --
> Sent from my mobile device
>
Reply | Threaded
Open this post in threaded view
|

Re: Ibatis query returns no rows even though it should.

Clinton Begin
Administrator
In reply to this post by old_as_a_fossil
You'll need to submit a unit test that demonstrates the problem.

We have 1200 unit tests that return data from two databases and thousands of users doing the same from many more different databases.... 

Submit a test that demonstrates the problem.

Cheers,
Clinton

On Wed, Sep 15, 2010 at 3:45 AM, Kannan J <[hidden email]> wrote:
(Recap: Ibatis query returned empty rows when a row should be returned. I was using select for update query.)
 
I tried after removing the "for update" clause, still Ibatis query returned no rows for several iterations, before finally returning a row. Bug in Ibatis?
 
thanks
Kannan
 
--- On Wed, 8/9/10, Larry Meadors <[hidden email]> wrote:

From: Larry Meadors <[hidden email]>
Subject: Re: Ibatis query returns no rows even though it should.Date: Wednesday, 8 September, 2010, 6:05 PM


On Wed, Sep 8, 2010 at 6:23 AM, Kannan J
<kannan_jayaprakasam@...> wrote:
> Any ideas, anyone? I find it difficult to believe Ibatis has such a HUGE bug, but I don't see anything wrong in my code.

Me too.

Did you try it with JDBC?

Did you try removing the "for update" part?

Simplify the issue until it works, then build it back up to see where it breaks.

Larry


Reply | Threaded
Open this post in threaded view
|

Re: Ibatis query returns no rows even though it should.

old_as_a_fossil
I finally discovered that the query used was not what I thought, I application changes the query used from what I thought it was to a different one based on some external stimuli.
Sorry for the trouble.
 
-Kannan

--- On Wed, 15/9/10, Clinton Begin <[hidden email]> wrote:

From: Clinton Begin <[hidden email]>
Subject: Re: Ibatis query returns no rows even though it should.
To: [hidden email]
Date: Wednesday, 15 September, 2010, 8:54 PM

You'll need to submit a unit test that demonstrates the problem.

We have 1200 unit tests that return data from two databases and thousands of users doing the same from many more different databases.... 

Submit a test that demonstrates the problem.

Cheers,
Clinton

On Wed, Sep 15, 2010 at 3:45 AM, Kannan J <kannan_jayaprakasam@...> wrote:
(Recap: Ibatis query returned empty rows when a row should be returned. I was using select for update query.)
 
I tried after removing the "for update" clause, still Ibatis query returned no rows for several iterations, before finally returning a row. Bug in Ibatis?
 
thanks
Kannan
 
--- On Wed, 8/9/10, Larry Meadors <larry.meadors@...> wrote:

From: Larry Meadors <larry.meadors@...>
Subject: Re: Ibatis query returns no rows even though it should. Date: Wednesday, 8 September, 2010, 6:05 PM


On Wed, Sep 8, 2010 at 6:23 AM, Kannan J
<kannan_jayaprakasam@...> wrote:
> Any ideas, anyone? I find it difficult to believe Ibatis has such a HUGE bug, but I don't see anything wrong in my code.

Me too.

Did you try it with JDBC?

Did you try removing the "for update" part?

Simplify the issue until it works, then build it back up to see where it breaks.

Larry