We're seeing some odd behavior and bad results involving the number of prepared statements on a connection. This is a .net v3.5 application, using the ianywhere .net provider v. 22.214.171.12462. The results described below have been repro'd on win7 client and server, as well as win7 client, win2008 r2 server.
We set max_prepared_statements=0, launch our test app, we can monitor the prepared statement count from isql using connection_property('PrepStmt', number)
We see the count rise to the 900's (which is WAY too high) and stabilize. Eventually, after several
hours of the test run, the engine crashes.
This looks like a classic failure to dispose of prepared statements. We thought so too, but have been unable to find bad code, despite extensive review and testing. AND, THIS:
We set max_prepared_statements=50, and run our test again. NOW, monitoring shows that the number of prepared statements don't rise above 40-something. We get no exceptions in the app, and the engine does not crash. If the app was really failing to dispose of statements, it should have seen an error when the limit was exceeded.
Obviously, this by itself is not really a problem for us. We just set the max to 50 and things run well. However, I'm posting this because a) it may indicate a defect in sqlanwhere that should be investigated. and b) we have some other issues that seem to suggest problems in the ianywhere provider, and those problems seem to involve the prepared statement management. I'll post that problem on a different thread, when I can.
asked 31 Aug '11, 15:33
This issue was resolved in CR #687447 in builds 126.96.36.19995, 188.8.131.5277 and later.
In rare, timing dependent cases the server could have incorrect behaviour if a connection had at least 20 prepared statements and cached prepared statements or at least 20 cursors open. The incorrect behaviour could vary, and the only known symptom is incorrectly returning the error "Resource governor for 'prepared statements' exceeded", but it may also be possible for server crashes or assertions to occur.
answered 17 Nov '11, 12:40
There has been an already-identified issue with the .NET provider 'leaking' prepared statements if an exception occurred while using SACommand.ExecuteReader(). See CR #625219 for more details regarding this issue (fixed in 184.108.40.2062).
Is it possible that you're seeing some evidence of this bug, perhaps? Can you try a later version of the .NET provider and see if you can get the same behaviour?
answered 31 Aug '11, 16:42