From user:

I think the issue I am running into for #2 is that my user does not have permissions to run killOp, which is typically an administrator level function.  It would be interesting to see if there is a way to provide a mechanism for a user to terminate their own processes without having to grant the ability to terminate all processes...though I suspect that is functionality MongoDB would have to implement.  

The only way to request MongoDB Server to cancel a long running query is to issue the killOp remote command. This command requires a numeric argument ( " opId " ) that is determined using the currentOp command. Thus, a given user must have the following privilege actions set in order to be able to cancel a query within ADS:  " inprog " (in order to be able to issue the "currentOp" command) and killOp action ( in order to be able to issue the killOp command ).

If these two privilege actions are not present for given user, then an in-progress long running query cannot be cancelled as MongoDB does not provide other alternatives.

t would be interesting to see if there is a way to provide a mechanism for a user to terminate their own processes without having to grant the ability to terminate all processes.

MongoDB Server does not provide such feature so far.

In particular, db.CurrentOp() seems to have been enhanced but unclear if there is still enough information : http://docs.mongodb.org/manual/reference/method/db.currentOp/#db.currentOp

The enhancements reported on that documentation page refers to the way how pending operations are displayed and how filters can be applied. However, if privilege actions are missing, there is no alternative for current implementation ( db.currentOp() + db.killOp(opId) .

We should also run some tests w/ long running queries -- the same query from our Query Analyzer & one from MongoShell -- to see if their is a difference in time to kill query between the 2 scenarios.

You mean by comparing the responsiveness behaviour of the ADS cancel button present within Query Analyzer window with the behaviour of manually running db.currentOp() + db.killOp() from ADS MongoShell ?

There is a similar issue reported for RoboMongo, but it seems they didn't find an alternative for killOp() either.

You mean by comparing the responsiveness behaviour of the ADS cancel button present within Query Analyzer window with the behaviour of manually running db.currentOp() + db.killOp() from ADS MongoShell ?

Yes, and also comparing the response time from the MongoDB's own shell

From user:

So just as an update to this issue.   We recently tested this out with an admin user (that had permissions to run killOp and currentOp) and we couldn’t kill the query with the stop button with that user either.   So it appears to be more than a permission thing.   In fact, we were able to open up a mongoShell within Aqua and run the currentOp and killOp command manually and see that the queries were terminated immediately.   Clicking the stop button within the query analyzer just gives a message saying “Cancelling…Executing” which never goes away until the query eventually finishes it’s execution.

@emil: I've attached 3 documents to the issue based upon our QA findings. Pls diagnose.

@emil

I added 1 million record in MongoDB through Mongoshell .When we display the result in ADS through Query analyzer (Default Memory : 672MB).The ADS completely hang After memory reach to 550MB. Before memory reached to 550MB. we can terminate the Query immediately. 

I increased default memory of ADS to 1GB , after that the result properly displayed in ADS through Query Analyzer.

Displaying the results in ADS (For MongoDB) totally depend on memory because Firstly it fetch all the documents then print into the screen.

If I takes single column from display , it take less memory for displaying  the result and less time also.

If I increased no of column it take more memory to display the result and also take more time to display.

It take only 4 sec for retrieving a record. 

Thanks

I cannot reproduce the Cancellation issue reported by the QA team nor using the MongoDB Server 2.6.x or 3.0.x versions. When issuing the

eval(db.currentOpTest.ensureIndex({i:1}, {background:1}))

command then hitting the Stop button after a few seconds, the operation is cancelled immediately and the following error message is displayed inside Messages tab:

>[Error] Script lines: 1-1 --------------------------
 ScriptCancelledException~ScriptCancelledException

After that, if I run a

> db.currentOp(true).inprog 

on a separate MongoShell window, the "create background index" operation is no more active (on 2.6.x version is displayed as inactive, while on 3.0.x version is not displayed any more after cancellation).

The only problem I see inside the MongoStatement.cancel() method when issued against a MongoDB Server v3.x would be related to DB authentication.

Was MongoDB authentication enabled on QA tests? I need more information about the environment used (MongoDB config, ADS connection settings) so that I can reproduce the issue.

Applied patches from Emil:

- I have changed the way how anotherMongoInstance is initialized within MongoStatement.cancel(). Now it uses the same code used by MongoDriver.connect() when building a new MongoClient instance.
  The reason why we have to use a separate MongoClient instance for obtaining opid and issuing db.killOp() is that we have enforced one connection per host inside our MongoJDBC driver. Therefore separate environment should be used for killing the pending query.

- Log error messages that help us debug further when MongoStatement.cancel() fails

- Fix MongoJDBC resultset caching issue (ADS freezes when running on low memory)

@dutondeumesh : The test scenario you have performed is invalid, because "The local database is invisible to replication: collections in the local database are not replicated." (source http://docs.mongodb.org/master/reference/local-database/ ). Please wait for another ADS v18 build that includes latest fixes, then perform it again against different database (i.e. not the local one).

@emil / @jenny: Due to the nature of these changes, we should apply this to v17, not v16.

Notes to QA:
- The fix has been applied to ADS 17.0.0-rc-17 and 18.0.0-dev-5.
- When hitting the Stop button, the query should be cancelled almost instantaneously.
- Verify the fix in both standalone server and replica set environments.
- Verify the fix with MongoDB 2.x and 3.x.
- Verify different MongoSQL commands to make sure that there is no regression.
 

Notes to QA:
[...]
- Verify different MongoSQL commands to make sure that there is no regression.

The fix made for #13171 changes the way how socket connections are acquired by ADS when connecting to a replicaset environment. As a result, some regressions such as long unexpected delays (i.e. ADS waiting for socket resources to be released) can potentially occur in latest ADS builds.

Therefore, please run various MongoSQL commands especially on replicaset env. to ensure that such regressions do not occur.

I have done some testing using the attached SQL script sqlScriptReplica.sql in a replica set.  After running the following INSERT statement, subsequent queries fail.

insert into target_coll select _id as _id,b as a from source_coll
go

I have fixed the implementation for INSERT ... SELECT ... when replicaset is used plus few more cases. Please retest.

Notes to QA:

- Re-test all test cases in standalone server and replica set.
- Verify that cancel still works in standalone server and replica set.
- Verify that all MongoSQL commands work and don't cause subsequent command to fail.  When there is a command that returns the "timeout waiting for a pooled item" error, the previous command is the cause of the problem.  Mention the previous command in the problem description.
- Refer to the MongoDB WikiBook for a list of MongoSQL commands.  Make sure that all commands and different variations of syntax are covered, including DELETE ONE, DELETE ALL, CREATE INDEX, EVAL, EVAL_ARRAY, etc.

Verified in ADS 17.0.0-rc-28. Please see attached primaryPreferred-RC-28.png and secondaryPreferred-RC-28.png

Verified in ADS v17.0.0-rc-49