Please add a warning in the reference docs for the JDBC Sampler that not using bind variables in the query keeps the cursors in the database open (until end of test) Executing a query (without bind, ie question marks) too often in a test can cause an error on the database, because of the way JDBC Prepared/Callable-Statement objects are being cached in Jmeter. There is no problem in JMeter (or in the jdbc lib) and it turned out te be in our script. But it took us a day to debug...
I'm not sure I follow why using bind should make a difference. If it does matter, it seems to me that this may be a bug in JMeter. Would you be able to provide sample a simple test plan that shows the difference?
@sebb I don't think it's a bug of JMeter, it's more a feature and due to the PreparedStatement cache per connection. See perConnCache in AbstractJDBCTestElement. If users don't use ? , then you will have one query per different value. As statements are closed only at end of test, this lead @sander test to saturate cursors. So we could add this warning, but should we change this behaviour ?
Thanks for the explanation. Sorry I did not pick that up earlier. It's certainly worth warning users that Prepared and Callable statements are cached, and so they should be used sparingly. It does not make sense to drop the cache entirely, so the best we can do is either limit the number of entries somehow (drop the oldest one?) or add some way for the user to drop specific statements or perhaps limit how many times they are used. All of this is a bit complicated, and this is the first report we have had, so I think clearer documentation may be enough unless we get more reports - and a use case as to how the strategy could be improved.
Date: Sun Sep 8 21:00:28 2013 New Revision: 1520926 URL: http://svn.apache.org/r1520926 Log: Bug 55518 - Add ability to limit number of cached PreparedStatements per connection when "Prepared Select Statement", "Prepared Update Statement" or "Callable Statement" query type is selected Bugzilla Id: 55518 Modified: jmeter/trunk/bin/jmeter.properties jmeter/trunk/src/protocol/jdbc/org/apache/jmeter/protocol/jdbc/AbstractJDBCTestElement.java jmeter/trunk/xdocs/changes.xml Date: Sun Sep 8 21:01:26 2013 New Revision: 1520927 URL: http://svn.apache.org/r1520927 Log: Bug 55518 - Add ability to limit number of cached PreparedStatements per connection when "Prepared Select Statement", "Prepared Update Statement" or "Callable Statement" query type is selected Documentation Bugzilla Id: 55518 Modified: jmeter/trunk/xdocs/usermanual/component_reference.xml
Hello Sander, We fixed the issue by adding a LRU Map with a default size of 100 and a property to configure it jdbcsampler.maxopenpreparedstatements. Could you give jenkins nightly build a try and give us feedback ? Thanks
This issue has been migrated to GitHub: https://github.com/apache/jmeter/issues/3225