Hi Andrus,
thanks for your work. I found out I already have all my PKs mapped as
INTEGERs (and the Article entity has a pk db column with type
INT(11)). So I just tried switching it to BIGINT and it did the trick
:)
What troubles me now is that paginated query still retrieves EVERYTHING :(
The manual says Cayenne will retrieve only PKs, but the code I posted
before results in:
SELECT t0.handle, t0.last_edited, t0.perex, t0.published,
t0.read_count, t0.text, t0.title, t0.user_id, t0.id FROM Article t0
ORDER BY t0.id
And it is done on every page load. After this, during the subList()
call there is another SQL call, that retrieves only records relevant
to that page. Am I doing something wrong?
Thanks,
Dan
On Jan 15, 2008 11:25 PM, Andrus Adamchik <andrus@objectstyle.org> wrote:
> Actually I finally got frustrated with this to a point of actually
> fixing it in 3.0 :-)
>
> https://issues.apache.org/cayenne/browse/CAY-961
>
> (we can't fix the issue in 2.0.x cause it requires a serious API
> rework, which we can't do on a stable release)
>
> Andrus
>
>
>
> On Jan 15, 2008, at 10:23 PM, Andrus Adamchik wrote:
>
> > Hi Dan,
> >
> > Your diagnostics is correct... I just tested your case on MySQL with
> > PK defined as BIGINT, and got the exact same error. As a temporary
> > fix I suggest mapping your PK column as INTEGER in the Modeler
> > without changing the DB (I *think* that should work... unless you
> > expect values there higher than Integer.MAX_VALUE of course). And
> > I'll think of a more permanent fix.
> >
> > Thanks,
> > Andrus
> >
> >
> > On Jan 15, 2008, at 1:01 PM, Daniel Kvasnička jr. wrote:
> >
> >> Hi Andrus,
> >> I probably didn't write it clear enough.
> >>
> >> The scenario is: show a page, which shows first five articles
> >> obtained
> >> by paginated SelectQuery (via AJAX, using Adobe Spry -- but it occus
> >> even if I load the page directly) -> click a button for a new page,
> >> fill in the form and click save -> save the article to the database
> >> and return to the previous page with the list (e.g. a new select
> >> query) -> paginate through the pages to the last page, where the new
> >> article is -> Exception (the last page is not loaded at all). And as
> >> I've said before, if I change ordering so that the new record is on
> >> the first page, I can browse through the pages with no exception.
> >>
> >> The code:
> >> SelectQuery select = new SelectQuery(Article.class);
> >> select.addOrdering(realOrdering, isAscending);
> >> select.setPageSize(pageSize);
> >> List<Article> articles = (List<Article>)
> >> this.dataCtx.performQuery(select);
> >>
> >>
> >> int start = (page - 1) * pageSize;
> >> int end = page * pageSize;
> >>
> >> System.out.println("END --------------------- service method call
> >> to
> >> retrieve the list");
> >> return articles.subList(start, (end > articles.size() ?
> >> articles.size() : end)); // from here the exception comes
> >>
> >>
> >> SQL log & stacktrace (what troubles me is that cayenne retrieves
> >> EVERYTHING even though I've set setPageSize(5)):
> >>
> >> INFO QueryLogger: --- will run 1 query.
> >> INFO QueryLogger: --- transaction started.
> >> INFO QueryLogger: SELECT COUNT(*) AS count FROM Article // called
> >> with every page load to ensure the user sees an up to date articles
> >> count
> >> INFO QueryLogger: === returned 1 row. - took 0 ms.
> >> INFO QueryLogger: +++ transaction committed.
> >> BEGIN --------------------- service method call to retrieve the list
> >> // show first page of articles -- why does cayenne retireve ALL rows
> >> when I have pageSize = 5?
> >> INFO QueryLogger: --- will run 1 query.
> >> INFO QueryLogger: --- transaction started.
> >> INFO QueryLogger: SELECT t0.handle, t0.last_edited, t0.perex,
> >> t0.published, t0.read_count, t0.text, t0.title, t0.user_id, t0.id
> >> FROM
> >> Article t0 ORDER BY t0.id - prepared in 16 ms.
> >> INFO QueryLogger: === returned 13 rows. - took 25 ms.
> >> INFO QueryLogger: +++ transaction committed.
> >> END --------------------- service method call to retrieve the list
> >> INFO QueryLogger: --- will run 1 query.
> >> INFO QueryLogger: --- transaction started.
> >> INFO QueryLogger: SELECT t0.handle, t0.name, t0.id FROM Tag t0 //
> >> show the "add" page and retrieve list of tags to display them as
> >> checkboxes
> >> INFO QueryLogger: === returned 8 rows. - took 1 ms.
> >> INFO QueryLogger: +++ transaction committed.
> >> BEGIN --------------------- create new article
> >> INFO QueryLogger: --- will run 1 query.
> >> INFO QueryLogger: --- transaction started.
> >> INFO QueryLogger: SELECT t0.handle, t0.name, t0.id FROM Tag t0 WHERE
> >> t0.id = ? [bind: '5']
> >> INFO QueryLogger: === returned 1 row. - took 1 ms.
> >> INFO QueryLogger: +++ transaction committed.
> >> INFO QueryLogger: --- will run 1 query.
> >> INFO QueryLogger: --- transaction started.
> >> INFO QueryLogger: SELECT t0.handle, t0.name, t0.id FROM Tag t0 WHERE
> >> t0.id = ? [bind: '6']
> >> INFO QueryLogger: === returned 1 row. - took 0 ms.
> >> INFO QueryLogger: +++ transaction committed.
> >> INFO QueryLogger: --- will run 1 query.
> >> INFO QueryLogger: --- transaction started.
> >> INFO QueryLogger: SELECT t0.handle, t0.last_edited, t0.perex,
> >> t0.published, t0.read_count, t0.text, t0.title, t0.user_id, t0.id
> >> FROM
> >> Article t0 WHERE t0.handle = ? [bind: 'assdfasdf']
> >> INFO QueryLogger: === returned 0 rows. - took 2 ms.
> >> INFO QueryLogger: +++ transaction committed.
> >> INFO QueryLogger: --- transaction started.
> >> INFO QueryLogger: LOCK TABLES AUTO_PK_SUPPORT WRITE
> >> INFO QueryLogger: SELECT NEXT_ID FROM AUTO_PK_SUPPORT WHERE
> >> TABLE_NAME = 'Article_Tag'
> >> INFO QueryLogger: UPDATE AUTO_PK_SUPPORT SET NEXT_ID = NEXT_ID + 20
> >> WHERE TABLE_NAME = 'Article_Tag' AND NEXT_ID = 660
> >> INFO QueryLogger: UNLOCK TABLES
> >> INFO QueryLogger: --- will run 2 queries.
> >> INFO QueryLogger: INSERT INTO Article (handle, last_edited, perex,
> >> published, read_count, text, title, user_id) VALUES
> >> (?, ?, ?, ?, ?, ?,
> >> ?, ?)
> >> INFO QueryLogger: [bind: 'assdfasdf', '2008-01-15 11:49:36.992', '
> >> asdfasdfasdfasdf', '2008-01-15 11:49:36.992', NULL,
> >> '<p>asdfsdfasdfasd
> >> asd asdf asd</p> ', 'assdfasdf', 3]
> >> INFO QueryLogger: === updated 1 row.
> >> INFO QueryLogger: INSERT INTO Article_Tag (article_id, tag_id)
> >> VALUES (?, ?)
> >> INFO QueryLogger: [bind: 38, 5]
> >> INFO QueryLogger: === updated 1 row.
> >> INFO QueryLogger: [bind: 38, 6]
> >> INFO QueryLogger: === updated 1 row.
> >> INFO QueryLogger: +++ transaction committed.
> >> END --------------------- create new article
> >> INFO QueryLogger: --- will run 1 query.
> >> INFO QueryLogger: --- transaction started.
> >> INFO QueryLogger: SELECT COUNT(*) AS count FROM Article
> >> INFO QueryLogger: === returned 1 row. - took 1 ms.
> >> INFO QueryLogger: +++ transaction committed.
> >> BEGIN --------------------- service method call to retrieve the list
> >> // we have returned to the "list" page and see the first page again
> >> INFO QueryLogger: --- will run 1 query.
> >> INFO QueryLogger: --- transaction started.
> >> INFO QueryLogger: SELECT t0.handle, t0.last_edited, t0.perex,
> >> t0.published, t0.read_count, t0.text, t0.title, t0.user_id, t0.id
> >> FROM
> >> Article t0 ORDER BY t0.id
> >> INFO QueryLogger: === returned 14 rows. - took 3 ms.
> >> INFO QueryLogger: +++ transaction committed.
> >> END --------------------- service method call to retrieve the list
> >> INFO QueryLogger: --- will run 1 query.
> >> INFO QueryLogger: --- transaction started.
> >> INFO QueryLogger: SELECT COUNT(*) AS count FROM Article
> >> INFO QueryLogger: === returned 1 row. - took 1 ms.
> >> INFO QueryLogger: +++ transaction committed.
> >> BEGIN --------------------- service method call to retrieve the list
> >> // we go to the second page
> >> INFO QueryLogger: --- will run 1 query.
> >> INFO QueryLogger: --- transaction started.
> >> INFO QueryLogger: SELECT t0.handle, t0.last_edited, t0.perex,
> >> t0.published, t0.read_count, t0.text, t0.title, t0.user_id, t0.id
> >> FROM
> >> Article t0 ORDER BY t0.id
> >> INFO QueryLogger: === returned 14 rows. - took 3 ms.
> >> INFO QueryLogger: +++ transaction committed.
> >> END --------------------- service method call to retrieve the list //
> >> this is probably called when I call .subList(start, end) on the
> >> list ?
> >> INFO QueryLogger: --- will run 1 query.
> >> INFO QueryLogger: --- transaction started.
> >> INFO QueryLogger: SELECT t0.handle, t0.last_edited, t0.perex,
> >> t0.published, t0.read_count, t0.text, t0.title, t0.user_id, t0.id
> >> FROM
> >> Article t0 WHERE (t0.id = ?) OR (t0.id = ?) OR (t0.id = ?) OR
> >> (t0.id =
> >> ?) OR (t0.id = ?) [bind: 15, 19, 21, 22, 34]
> >> INFO QueryLogger: === returned 5 rows. - took 2 ms.
> >> INFO QueryLogger: +++ transaction committed.
> >> INFO QueryLogger: --- will run 1 query.
> >> INFO QueryLogger: --- transaction started.
> >> INFO QueryLogger: SELECT COUNT(*) AS count FROM Article
> >> INFO QueryLogger: === returned 1 row. - took 0 ms.
> >> INFO QueryLogger: +++ transaction committed.
> >> BEGIN --------------------- service method call to retrieve the list
> >> // we want to go to the third page where the new article is
> >> INFO QueryLogger: --- will run 1 query.
> >> INFO QueryLogger: --- transaction started.
> >> INFO QueryLogger: SELECT t0.handle, t0.last_edited, t0.perex,
> >> t0.published, t0.read_count, t0.text, t0.title, t0.user_id, t0.id
> >> FROM
> >> Article t0 ORDER BY t0.id
> >> INFO QueryLogger: === returned 14 rows. - took 4 ms.
> >> INFO QueryLogger: +++ transaction committed.
> >> END --------------------- service method call to retrieve the list
> >> INFO QueryLogger: --- will run 1 query.
> >> INFO QueryLogger: --- transaction started.
> >> INFO QueryLogger: SELECT t0.handle, t0.last_edited, t0.perex,
> >> t0.published, t0.read_count, t0.text, t0.title, t0.user_id, t0.id
> >> FROM
> >> Article t0 WHERE (t0.id = ?) OR (t0.id = ?) OR (t0.id = ?) OR
> >> (t0.id =
> >> ?) [bind: 35, 36, 37, 38]
> >> INFO QueryLogger: === returned 4 rows. - took 2 ms.
> >> INFO QueryLogger: +++ transaction committed.
> >> WARN DefaultExceptionHandler: Unhandled exception caught by the
> >> Stripes default exception handler.
> >> org.apache.cayenne.CayenneRuntimeException: [v.2.0.4 October 8 2007]
> >> Can't find id for {<ObjectId:Article, id=38>; committed; [tags=>(..);
> >> author=>?; title=>assdfasdf; text=><p>asdfsdfasdfasd asd asdf asd</p>
> >> ; handle=>assdfasdf; perex=> asdfasdfasdfasdf; readCount=>null;
> >> lastEdited=>Tue Jan 15 11:49:36 GMT+01:00 2008; published=>Tue Jan 15
> >> 11:49:36 GMT+01:00 2008]}
> >> at org.apache.cayenne.access.IncrementalFaultList
> >> $
> >> IncrementalListHelper
> >> .updateWithResolvedObjectInRange(IncrementalFaultList.java:760)
> >> at
> >> org
> >> .apache
> >> .cayenne
> >> .access
> >> .IncrementalFaultList.resolveInterval(IncrementalFaultList.java:408)
> >> at
> >> org
> >> .apache
> >> .cayenne
> >> .access.IncrementalFaultList.subList(IncrementalFaultList.java:685)
> >> at
> >> net
> >> .danielkvasnicka
> >> .mutara.service.ArticleService.getArticles(ArticleService.java:183)
> >> at
> >> net
> >> .danielkvasnicka
> >> .mutara.service.ArticleService.getArticlesAsXml(ArticleService.java:
> >> 198)
> >> at
> >> net
> >> .danielkvasnicka
> >> .mutara
> >> .actionbean
> >> .articles
> >> .ajax.ArticleAjaxActionBean.list(ArticleAjaxActionBean.java:40)
> >> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> >> at
> >> sun
> >> .reflect
> >> .NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
> >> at
> >> sun
> >> .reflect
> >> .DelegatingMethodAccessorImpl
> >> .invoke(DelegatingMethodAccessorImpl.java:25)
> >> at java.lang.reflect.Method.invoke(Method.java:597)
> >> at net.sourceforge.stripes.controller.DispatcherHelper
> >> $6.intercept(DispatcherHelper.java:445)
> >> at
> >> net
> >> .sourceforge
> >> .stripes.controller.ExecutionContext.proceed(ExecutionContext.java:
> >> 157)
> >> at
> >> net
> >> .sourceforge
> >> .stripes
> >> .controller
> >> .BeforeAfterMethodInterceptor
> >> .intercept(BeforeAfterMethodInterceptor.java:107)
> >> at
> >> net
> >> .sourceforge
> >> .stripes.controller.ExecutionContext.proceed(ExecutionContext.java:
> >> 154)
> >> at
> >> net
> >> .sourceforge
> >> .stripes.controller.ExecutionContext.wrap(ExecutionContext.java:73)
> >> at
> >> net
> >> .sourceforge
> >> .stripes
> >> .controller
> >> .DispatcherHelper.invokeEventHandler(DispatcherHelper.java:443)
> >> at
> >> net
> >> .sourceforge
> >> .stripes
> >> .controller
> >> .DispatcherServlet.invokeEventHandler(DispatcherServlet.java:241)
> >> at
> >> net
> >> .sourceforge
> >> .stripes.controller.DispatcherServlet.doPost(DispatcherServlet.java:
> >> 154)
> >> at
> >> net
> >> .sourceforge
> >> .stripes.controller.DispatcherServlet.doGet(DispatcherServlet.java:
> >> 61)
> >> at javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
> >> at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
> >> at
> >> org
> >> .apache
> >> .catalina
> >> .core
> >> .ApplicationFilterChain
> >> .internalDoFilter(ApplicationFilterChain.java:290)
> >> at
> >> org
> >> .apache
> >> .catalina
> >> .core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:
> >> 206)
> >> at
> >> net
> >> .sourceforge
> >> .stripes.controller.StripesFilter.doFilter(StripesFilter.java:180)
> >> at
> >> org
> >> .apache
> >> .catalina
> >> .core
> >> .ApplicationFilterChain
> >> .internalDoFilter(ApplicationFilterChain.java:235)
> >> at
> >> org
> >> .apache
> >> .catalina
> >> .core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:
> >> 206)
> >> at
> >> org
> >> .apache
> >> .cayenne
> >> .conf
> >> .WebApplicationContextFilter
> >> .doFilter(WebApplicationContextFilter.java:91)
> >> at
> >> org
> >> .apache
> >> .catalina
> >> .core
> >> .ApplicationFilterChain
> >> .internalDoFilter(ApplicationFilterChain.java:235)
> >> at
> >> org
> >> .apache
> >> .catalina
> >> .core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:
> >> 206)
> >> at
> >> org
> >> .tuckey
> >> .web.filters.urlrewrite.RuleChain.handleRewrite(RuleChain.java:164)
> >> at
> >> org.tuckey.web.filters.urlrewrite.RuleChain.doRules(RuleChain.java:
> >> 141)
> >> at
> >> org
> >> .tuckey
> >> .web.filters.urlrewrite.UrlRewriter.processRequest(UrlRewriter.java:
> >> 90)
> >> at
> >> org
> >> .tuckey
> >> .web
> >> .filters.urlrewrite.UrlRewriteFilter.doFilter(UrlRewriteFilter.java:
> >> 406)
> >> at
> >> org
> >> .apache
> >> .catalina
> >> .core
> >> .ApplicationFilterChain
> >> .internalDoFilter(ApplicationFilterChain.java:235)
> >> at
> >> org
> >> .apache
> >> .catalina
> >> .core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:
> >> 206)
> >> at
> >> org
> >> .apache
> >> .catalina
> >> .core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
> >> at
> >> org
> >> .apache
> >> .catalina
> >> .core.StandardContextValve.invoke(StandardContextValve.java:175)
> >> at
> >> org
> >> .apache
> >> .catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
> >> at
> >> org
> >> .apache
> >> .catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
> >> at
> >> org
> >> .apache
> >> .catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:
> >> 109)
> >> at
> >> org
> >> .apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:
> >> 263)
> >> at
> >> org
> >> .apache.coyote.http11.Http11Processor.process(Http11Processor.java:
> >> 844)
> >> at org.apache.coyote.http11.Http11Protocol
> >> $Http11ConnectionHandler.process(Http11Protocol.java:584)
> >> at org.apache.tomcat.util.net.JIoEndpoint
> >> $Worker.run(JIoEndpoint.java:447)
> >> at java.lang.Thread.run(Thread.java:619)
> >>
> >>
> >> On Jan 15, 2008 11:38 AM, Andrus Adamchik <andrus@objectstyle.org>
> >> wrote:
> >>> Hi Dan,
> >>>
> >>> Not even sure at this point that this is an issue of int vs.
> >>> long... I
> >>> wonder how the *new* object even appears in the paginated list that
> >>> was created *before* the new object? Or did I misunderstand
> >>> something
> >>> about your scenario?
> >>>
> >>> Maybe it would help if you could post the exception and the SQL log
> >>> preceding it.
> >>>
> >>> Thanks
> >>> Andrus
> >>>
> >>>
> >>>
> >>>
> >>> On Jan 14, 2008, at 10:07 PM, Daniel Kvasnička jr. wrote:
> >>>
> >>>> Hi people,
> >>>> I've bumped into an error with cayenne 2.0.4 and the only similar
> >>>> things I found were this message
> >>>> (http://cayenne.markmail.org/message/elln5rjlgrsdinxm?q=paging+cache+type:users
> >>>> )
> >>>> and the bug CAY-643 (and those linked to it).
> >>>>
> >>>> What I do is that I load a page with paginated query (list of
> >>>> articles, first 10), then I hit "new article" on the page, create
> >>>> and
> >>>> save the article and return back to the list.
> >>>> Then I paginate to find the newly added article and when I enter
> >>>> the
> >>>> page it is on and the page is the last page at the same time, I get
> >>>> the CayenneRuntimeException with "Can't find id for [the newly
> >>>> added
> >>>> article]" (i.e. when I set sorting to, let's say, by ID DESC --
> >>>> which
> >>>> puts the new article to the first page -- everythong is OK on all
> >>>> pages)
> >>>>
> >>>> I understand there is the problem with long vs. int and that it
> >>>> will
> >>>> be fixed in 3.0, so how can I as a user prevent this error in
> >>>> 2.0.4?
> >>>> This seems to me like a very common scenario in listing records and
> >>>> doing CRUD operations, but it seems almost nobody encounters
> >>>> this. Do
> >>>> you guys adandon pagination and use raw sql?
> >>>>
> >>>> I wanted to try 3.0M2, but when using it Stripes couldn't find any
> >>>> ActionBeans url mappings -- strange.
> >>>>
> >>>> Thanks for all hints in advance,
> >>>> Dan
> >>>>
> >>>> --
> >>>> http://www.danielkvasnicka.net -- webdesign & corporate design,
> >>>> programování internetových a intranetových aplikací
> >>>>
> >>>
> >>>
> >>
> >>
> >>
> >> --
> >> http://www.danielkvasnicka.net -- webdesign & corporate design,
> >> programování internetových a intranetových aplikací
> >
> >
>
>
-- http://www.danielkvasnicka.net -- webdesign & corporate design, programování internetových a intranetových aplikací
This archive was generated by hypermail 2.0.0 : Wed Jan 16 2008 - 04:52:28 EST