It was me actually, I accidentally used a top level context instead of the one from the transaction so it basically executed the `FOUND_ROWS()` outside of the transaction which caused it to get a value of 0 or 1.
On Thu, Apr 7, 2016 at 12:03 PM, Lukas Eder <[email protected]> wrote: > Hi Jayson, > > Hmm, interesting finding. A quick search on google has shown that some > JDBC API calls can cause such effects, e.g. when calling > metaData.getColumnDisplaySize(): > https://bugs.mysql.com/bug.php?id=15117 > > jOOQ by default fetches all SQLWarnings. You can turn this off via > Settings.fetchWarnings. Perhaps that's the reason why you get 1, because > the Statement.getWarnings() call runs another statement...? > > What happens if you run the SELECT FOUND_ROWS() statement inside of an > ExecuteListener (directly with JDBC), e.g. in the executeEnd() method? > > Best Regards, > Lukas > > 2016-04-07 16:52 GMT+02:00 <[email protected]>: > >> In current JOOQ, this doesn't work for me, FOUND_ROWS() returns 1 every >> time. It is like some other query is executing in between. >> >> 11:40:48.592 [main] DEBUG org.jooq.tools.LoggerListener - Executing query >> : select SQL_CALC_FOUND_ROWS `OrgUserRole`.`guid`, >> `OrgUserRole`.`orgGuid`, `OrgUserRole`.`userGuid`, >> `OrgUserRole`.`orgUserRoleType`, `OrgUserRole`.`dtCreated`, >> `OrgUserRole`.`dtUpdated` from `OrgUserRole` where `OrgUserRole`.`userGuid` >> = ? order by cast(`OrgUserRole`.`orgGuid` as binary) asc limit ? >> 11:40:48.592 [main] DEBUG org.jooq.tools.LoggerListener - -> with bind >> values : select SQL_CALC_FOUND_ROWS `OrgUserRole`.`guid`, >> `OrgUserRole`.`orgGuid`, `OrgUserRole`.`userGuid`, >> `OrgUserRole`.`orgUserRoleType`, `OrgUserRole`.`dtCreated`, >> `OrgUserRole`.`dtUpdated` from `OrgUserRole` where `OrgUserRole`.`userGuid` >> = 'Ie8qOLrWXo2XRr2Cosozzw' order by cast(`OrgUserRole`.`orgGuid` as binary) >> asc limit 10 >> 11:40:48.598 [main] DEBUG org.jooq.tools.StopWatch - Query executed >> : Total: 6.918ms >> 11:40:48.598 [main] DEBUG org.jooq.tools.LoggerListener - Fetched result >> : >> +----------------------+----------------------+----------------------+---------------+-----------------------+-----------------------+ >> 11:40:48.598 [main] DEBUG org.jooq.tools.LoggerListener - >> : |guid |orgGuid |userGuid >> |orgUserRoleType|dtCreated |dtUpdated | >> 11:40:48.599 [main] DEBUG org.jooq.tools.LoggerListener - >> : >> +----------------------+----------------------+----------------------+---------------+-----------------------+-----------------------+ >> 11:40:48.599 [main] DEBUG org.jooq.tools.LoggerListener - >> : >> |AufS0_TaiKiFQZurMts9KA|00AZZ_WIMqkuSH3WI9LhJw|Ie8qOLrWXo2XRr2Cosozzw|Owner >> |2016-04-07 11:40:46.625|2016-04-07 11:40:46.625| >> 11:40:48.599 [main] DEBUG org.jooq.tools.LoggerListener - >> : >> |UAYdanK9cYFoQTqBeVz6AQ|0wBdUOhLOZe6QfdZZrOQkw|Ie8qOLrWXo2XRr2Cosozzw|Owner >> |2016-04-07 11:40:47.318|2016-04-07 11:40:47.318| >> 11:40:48.599 [main] DEBUG org.jooq.tools.LoggerListener - >> : >> |XdLw8BSEVIa0SIyrdHBz0Q|1-cdqaqInq19QDwcyHRemg|Ie8qOLrWXo2XRr2Cosozzw|Owner >> |2016-04-07 11:40:46.381|2016-04-07 11:40:46.381| >> 11:40:48.599 [main] DEBUG org.jooq.tools.LoggerListener - >> : >> |SMIGsWFAXKVQQSK1x4cKIg|17TdJt9fwZj5TqcCz04ZjA|Ie8qOLrWXo2XRr2Cosozzw|Owner >> |2016-04-07 11:40:46.913|2016-04-07 11:40:46.913| >> 11:40:48.599 [main] DEBUG org.jooq.tools.LoggerListener - >> : >> |xgppTJDF66mfRK2N-Burjg|1UQmbt3IqJLuR3NZzrzeWQ|Ie8qOLrWXo2XRr2Cosozzw|Owner >> |2016-04-07 11:40:46.479|2016-04-07 11:40:46.479| >> 11:40:48.599 [main] DEBUG org.jooq.tools.LoggerListener - >> : >> +----------------------+----------------------+----------------------+---------------+-----------------------+-----------------------+ >> 11:40:48.599 [main] DEBUG org.jooq.tools.LoggerListener - >> : |...5 record(s) truncated... >> 11:40:48.599 [main] DEBUG org.jooq.tools.StopWatch - Finishing >> : Total: 8.366ms, +1.448ms >> 11:40:48.600 [main] DEBUG org.jooq.tools.LoggerListener - Executing query >> : select FOUND_ROWS() >> 11:40:48.601 [main] DEBUG org.jooq.tools.StopWatch - Query executed >> : Total: 0.791ms >> 11:40:48.609 [main] DEBUG org.jooq.tools.LoggerListener - Fetched result >> : +------------+ >> 11:40:48.609 [main] DEBUG org.jooq.tools.LoggerListener - >> : |FOUND_ROWS()| >> 11:40:48.609 [main] DEBUG org.jooq.tools.LoggerListener - >> : +------------+ >> 11:40:48.609 [main] DEBUG org.jooq.tools.LoggerListener - >> : | 1| >> 11:40:48.609 [main] DEBUG org.jooq.tools.LoggerListener - >> : +------------+ >> >> running the queries in SQL workbench... >> >> >> use integration_test; >> select SQL_CALC_FOUND_ROWS `OrgUserRole`.`guid`, `OrgUserRole`.`orgGuid`, >> `OrgUserRole`.`userGuid`, `OrgUserRole`.`orgUserRoleType`, `OrgUserRole`. >> `dtCreated`, `OrgUserRole`.`dtUpdated` from `OrgUserRole` where >> `OrgUserRole`.`userGuid` = 'Ie8qOLrWXo2XRr2Cosozzw' order by cast( >> `OrgUserRole`.`orgGuid` as binary) asc limit 10; >> Select FOUND_ROWS(); >> >> >> returns the expected results (100 in this case) >> >> My queries above are running in the same transaction (using JOOQ >> transaction closure) >> >> On Monday, November 12, 2012 at 2:36:34 PM UTC-2, Lukas Eder wrote: >>> >>> > "Using getQuery().addHint(...)" this saved my day! >>> >>> Great, good to know it worked. I'll add some Javadoc and manual >>> documentation : >>> https://github.com/jOOQ/jOOQ/issues/1967 >>> >>> > Like Jooq day by day more and more :). >>> >>> Feel free to write a success story in your blog, etc... :-) >>> >>> Cheers >>> Lukas >>> >> -- >> You received this message because you are subscribed to the Google Groups >> "jOOQ User Group" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> For more options, visit https://groups.google.com/d/optout. >> > > -- > You received this message because you are subscribed to a topic in the > Google Groups "jOOQ User Group" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/jooq-user/F60V--1QbRw/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/d/optout.
