[Mondrian] Having some problems with query cancel

[Paul Stoellberger]

For us cancelling seems to work fine - although it seems like as soon as mondrian is done with SQL its not really possible to cancel the statement.
Do you check for cancel during segment processing? E.g. during each evaluation step of the tree?

I haven't done a full test to find out where it cancels and where it doesn't, it's just something I noticed in the past
Just my 2 cents on that topic.

-Paul


On Nov 19, 2013, at 5:37 PM, Luc Boudreau <lucboudreau at gmail.com> wrote:

> Hi Pedro,
> 
> Thanks for looking into this issue.
> 
> "Is it possible that we are trying to cancel the query when it's not ready to be stopped?"
> 
> Possible. When we cancel the queries, we don't interrupt the thread, but we set a flag in Execution. We then check periodically, at certain checkpoints, the state of the cancelation flag and we break execution if needed. As far as I know, SleepUdf doesn't check that flag, so it won't be able to notice that the query was canceled.
> 
> Another situation where a cancelation won't happen is when two MDX queries try to access the same segment. If query 1 gets canceled, we check if other MDX statements need that particular segment and will only cancel it if nobody cares about it anymore. 
> 
> It is also worth noting that we cannot enforce any kind of cancellation on the SQL drivers, other than calling cancel() and wishing for the best. Some drivers will return early, others will simply ignore the interruption flag on their thread (or whatever mechanism they use internally). This in turn blocks mondrian from checking the state of the MDX statement until the code returns from the driver. 
> 
> Back to your scenario. It is possible that the field openCellSet is not assigned a value if you call cancel() from another thread right after you have created an MDX statement. We don't have a mechanism in there to 'cancel later'. Try adding an artificial delay between the time you create your statement and the time you call cancel and add some System.out calls into it to figure out in what order stuff happens. If this is the cause, we can probably fix that.
> 
> Lastly, if you see a spot in mondrian's code where the state of the execution should be checked but it isn't, please share your finding and we'll look into it. 
> 
> Luc
> 
> 
> 
> On Mon, Nov 18, 2013 at 3:25 PM, Pedro Salgueiro <pedro.salgueiro at cortex-intelligence.com> wrote:
> Hi,
> 
> I have been playing with the method Statement.cancel() in order to cancel some long running queries on user demand, which is not revealing to be an easy task.
> 
> Although in some queries, the cancel() method seems to be working perfectly and an exception is thrown when the query is canceled, as it's supposed to be, on other queries, nothing happens.
> After some debugging, I reached to the conclusion that when a query includes many specific members, we are not able to cancel it.
> 
> If we execute the following query, we are able to cancel it with no problems.
> 
> WITH 
> MEMBER [Measures].[Sleepy]
> AS SleepUdf([Measures].[Unit Sales])
> SELECT
> [Product].[Product Family].Members ON 0,
> [Measures].[Unit Sales],[Measures].[Sleepy] ON 1
> FROM [Sales]
> 
> 
> On the other hand, if we execute the following query with about 30 or 40 members, we are no longer able to cancel it:
> 
> WITH 
> MEMBER [Measures].[Sleepy]
> AS SleepUdf([Measures].[Unit Sales])
> SELECT
> {[Product].[Product Family].[Member 1], [Product].[Product Family].[Member 1], ..., [Product].[Product Family].[Member n]} ON 0,
> [Measures].[Unit Sales],[Measures].[Sleepy] ON 1
> FROM [Sales]
> 
> It's worth point out that SleepUdf is a user defined function which uses a Thread.sleep(), forcing the queries to take longer to execute, inspired by Mondrian's BasicQueryTest.java. This way we are able to properly test if the cancel is working.
> 
> I was looking at the cancel implementation in the MondrianOlap4jStatement class, and if the openCellSet is null, nothing is done. What are the conditions that would lead to a null openCellSet? 
> 
> The queries that we are not being able to cancel produces a large number of SQL queries, and the cancel() method is executed while these SQL queries are being executed. Is it possible that we are trying to cancel the query when it's not ready to be stopped? Or maybethe cancel method encounters the query in a state that is not possible to be stopped yet, maybe between the execution of two SQL queries?
> 
> In situations like this, is there any other approach to cancel the query?
> Do you know any easy solution to solve this problem?
> 
> Any help will be greatly appreciated,
> Pedro Salgueiro
> 
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian
> 
> 
> _______________________________________________
> Mondrian mailing list
> Mondrian at pentaho.org
> http://lists.pentaho.org/mailman/listinfo/mondrian

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20131119/7c1d424e/attachment.html