We found out that when using offset and limit in an active objects query, if the underlying db is MS SQL Server, it fails with an SQL exception. The following query crashes
@Override public List<ListsDataEntity> loadAllInRange(int pageSize, int startIndex) { final List<ListsDataEntity> listsDataEntityList = new ArrayList<ListsDataEntity>(); ao.stream(ListsDataEntity.class, Query.select(queryFields).limit(pageSize).offset(startIndex), new EntityStreamCallback<ListsDataEntity, Integer>() { @Override public void onRowRead(ListsDataEntity foundListDataEntity) { ...
with this trace
There was a SQL exception thrown by the Active Objects library: Database: - name:Microsoft SQL Server - version:10.50.2861 - minor version:50 - major version:10 Driver: - name:jTDS Type 4 JDBC Driver for MS SQL Server and Sybase - version:1.2.2 java.sql.SQLException: ResultSet may only be accessed in a forward direction. at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.stream(EntityManagedActiveObjects.java:221) at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects.stream(TenantAwareActiveObjects.java:359) at sun.reflect.GeneratedMethodAccessor835.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) ... at loadAllInRange(LinksDataAOServiceImpl.java:45)
This is a known and "Won't fix" issue https://ecosystem.atlassian.net/browse/AO-358, which seems to be fixed in the original AO project https://java.net/jira/browse/ACTIVEOBJECTS-17
Any idea on how to bypass this without much "harm" for those MS SQL server users?
/CC @Alexander Courtis // @Alex Courtis I've seen that you worked in the project (https://bitbucket.org/acourtis/ao), any insight on this?
Thanks!
Community moderators have prevented the ability to post new answers.
Fixed in 1.1.2 by https://ecosystem.atlassian.net/browse/AO-685
Note @Gorka Puente [Comalatech]'s awesome replicator project!
LIMIT and OFFSET have always been problematic under Oracle and MSSQL databases, due to their nonstandard syntax used to achieve these operations under older versions.
What is the actual query that is being executed? Is there perhaps a negative offset or limit?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
The query is Query.select(queryFields).limit(pageSize).offset(startIndex) that translated is Query.select(ID, KEY, CREATOR, TIMESTAMP, ...).limit(10).offset(0) Here is the log in case it helps pastebin.com/9s0uL3BJ Thanks!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Interesting - we have specific fields in the select - what happens if you just execute a select()? Which AO version are you running against i.e. which confluence version are you running? This could be a problem with the SQL statement rendering by the AO library itself. Please can you enable logging so we can see what is actually rendered: https://developer.atlassian.com/docs/atlassian-platform-common-components/active-objects/developing-your-plugin-with-active-objects/active-objects-faq/enabling-sql-logging
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Alex, If I use just the select it doesn't return the fields values (only the ID, the rest are 0 or nulls). I'm building with Confluence 5.7 with AO 0.28.12. Here's the log with SQL logging enabled http://pastebin.com/AX7DhqQD Thanks for the help!!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Apologies for the very belated response! {code} SELECT TOP 10 ID,"KEY",CLICKS,CREATOR,CREATED_TIMESTAMP,LATEST_ACCESS FROM AO_C2BE6F_LINKS_DATA_ENTITY {code} The query looks quite reasonable in terms of syntax, except for that {{"KEY"}} select. Is that the name of one of your columns? Could it be some sort of MS SQL Server hidden keyword that is confusing things?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Can you do a most horrible bit of refactoring and change that KEY column's name, to see if it's the source of the problems?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Alex! I changed the column's name to CLAVE, but that didn't work, here's the log http://pastebin.com/SnagMbEs That shouldn't be a problem, because it's also used in other columns' names like this query SELECT "DATE",ID,USER_KEY,PLUGIN_KEY,"KEY" FROM AO_6384AB_DISCOVERED WHERE USER_KEY = ? In the original project they solved it by modifying a prepared statement, check the comment here https://java.net/jira/browse/ACTIVEOBJECTS-17 Thanks!!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi, any update on this? Cheers
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Apologies for the delay! Everything looks like it should work, as far as I can see. The fix for the above issue (ACTIVEOBJECTS-17) appears to be still in place... however we might be traversing a code path that doesn't invoke it. Please can you create a standalone replicator for this and create a new AO ticket https://ecosystem.atlassian.net/browse/AO with that attached. The replicator could be: * a small plugin that does just that * a fork of https://bitbucket.org/activeobjects/ao with a new test, similar to, say, TestManyToManyWithPreload added
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi! I just created https://ecosystem.atlassian.net/browse/AO-685. There's a small add-on to replicate the behaviour attached. Thank you Alex!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks for putting that together! We can spin it up and have a look.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.