Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
3.2.2, 3.2.3
-
None
-
Arch Linux x86_64 running MariaDB 11.5.2 or 11.4.2
Description
When using prepared statements with SQLPrepare, the Prepared_stmt_count increases on MariaDB (when shown with SHOW GLOBAL STATUS LIKE 'Prepared_stmt_count'). It does not decrease when calling SQLFreeHandle(SQL_HANDLE_STMT, stmt) on the statement handle that was made as a prepared statement. Disconnecting and reconnecting, however, does lower the Prepared_stmt_count.
On long-lived connections or connections with many prepared statements, this can have the effect of throwing errors like:
STATE 42000: ODBC ECode 1461: EMsg [ma-3.2.2][11.4.2-MariaDB-log]Can't create more than max_prepared_stmt_count statements (current value: 16382)
Attached is a test program that demonstrates this.
Expected Output:
Initial - 0
After SQLExecDirect - 0
After SQLPrepare - 1
After SQLExecute - 1
After SQLCloseCursor - 1
After SQLFreeHandle - 0
After Reconnect - 0
Actual Output:
Initial - 0
After SQLExecDirect - 0
After SQLPrepare - 1
After SQLExecute - 1
After SQLCloseCursor - 1
After SQLFreeHandle - 1
After Reconnect - 0
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
When using prepared statements with {{SQLPrepare}}, the {{Prepared_stmt_count}} increases on MariaDB (when shown with {{SHOW GLOBAL STATUS LIKE 'Prepared_stmt_count'}}). It does not decrease when calling {{SQLFreeHandle(SQL_HANDLE_STMT, stmt)}} on the statement handle that was made as a prepared statement. Disconnecting and reconnecting, however, does lower the {{Prepared_stmt_count}}.
On long-lived connections or connections with many prepared statements, this can have the effect of throwing errors like: {{STATE 42000: ODBC ECode 1461: EMsg [ma-3.2.2][11.4.2-MariaDB-log]Can't create more than max_prepared_stmt_count statements (current value: 16382)}} Attached is a test program that demonstrates this. Expected Output: {{Initial - 0 After SQLExecDirect - 0 After SQLPrepare - 1 After SQLExecute - 1 After SQLCloseCursor - 1 After SQLFreeHandle - 0 After Reconnect - 0}} Actual Output: {{Initial - 0 After SQLExecDirect - 0 After SQLPrepare - 1 After SQLExecute - 1 After SQLCloseCursor - 1 After SQLFreeHandle - 1 After Reconnect - 0}} |
When using prepared statements with {{SQLPrepare}}, the {{Prepared_stmt_count}} increases on MariaDB (when shown with {{SHOW GLOBAL STATUS LIKE 'Prepared_stmt_count'}}). It does not decrease when calling {{SQLFreeHandle(SQL_HANDLE_STMT, stmt)}} on the statement handle that was made as a prepared statement. Disconnecting and reconnecting, however, does lower the {{Prepared_stmt_count}}.
On long-lived connections or connections with many prepared statements, this can have the effect of throwing errors like: {{STATE 42000: ODBC ECode 1461: EMsg [ma-3.2.2][11.4.2-MariaDB-log]Can't create more than max_prepared_stmt_count statements (current value: 16382)}} Attached is a test program that demonstrates this. Expected Output: Initial - 0 After SQLExecDirect - 0 After SQLPrepare - 1 After SQLExecute - 1 After SQLCloseCursor - 1 *After SQLFreeHandle - 0* After Reconnect - 0 Actual Output: Initial - 0 After SQLExecDirect - 0 After SQLPrepare - 1 After SQLExecute - 1 After SQLCloseCursor - 1 *After SQLFreeHandle - 1* After Reconnect - 0 |
Setting PSCACHESIZE to 0 works great as a work around. However, after modifying my test to use a PSCACHESIZE of 5 and preparing 10 different prepared statements (SELECT 1, SELECT 2, etc), the Prepared_stmt_count continued to rise to 10. Is this the expected behavior? I'd think that for a given connection, the PSCACHESIZE would limit the number of cached prepared statements to the number specified and old prepared statements that exceed the cache would be cleared.
I can confirm this is a bug that is occurring to us too. Happened immediately when upgrading from 3.1.20 (to either 3.2.2 or 3.2.3), across all our production mariadb servers. We had to revert back to 3.1.20.
This may be the culprit:
https://jira.mariadb.org/browse/ODBC-397
For backwards compatibility with existing installations, that new feature should have been disabled by default in my opinion. A default of 250 cached statements is also absurdly high. In our case, we had over 140 instances of the ODBC connector running, which exhausted MariaDB Server's default of ~16,000 cached statements within just a few minutes. (140 * 250 = 35,000)
cfsaaron I will check this. I have the test for this case, maybe something wrong with it, and there is a bug. If you SQL_DROP all statements, ps count is supposed to stay at 5, as newly prepared query should push longest time unused query out of the cache, and that should cause it to be closed at the server.
lukeescude The feature has been added in the new minor version series, so having it on default on is kinda legit. On other hand we almost* discontinued 3.1, and 3.2 substitutes it - this adds to your point. As for the default cache size - I think I took this number from other connector, that has is since long time. I will doublecheck that, but even if this is the case, what is good for one connector not necessary fits other connector. atm I feel like 250 is too much indeed.
*) iirc we will still release security fixes in case needed.
cfsaaron I can't see flaws in my test logic and it passes. Could you please provide your test final version?
Noticed one more thing - fields for cache config are missing in the setup dialog 
Gonna add them.
cfsaaron, lukeescude Also I verified that that other our connector I mentioned(that is Java connector) uses exactly 250 as default value. But I am still inclined to make it less in the C/ODBC. I am thinking about 50, if you have thoughts on that - I am ready to listen.
@Lawrin Here's my test. I set the PSCACHESIZE to 5 and see an increasing Prepared_stmt_count until it gets to After Reconnect. When I set PSCACHESIZE to 0 in this test it behaves as expected.
I don't know that lowering the default value is warranted if this is indeed a bug. 250 seems like a fairly sane number given most situations where connections are short lived or occasionally have many statements but limit the cache to 250. Given the default max of 16,384 prepared statements for a single server, you'd need at least 66 simultaneous connections with full caches. If the overhead of SQLPrepare is significant enough to warrant it (stated performance improvement of up to 2x in the release notes), I think 250 is fine. I could, however, see a use case for connection pool implementations to be able to clear the PS cache without having to disconnect and reconnect.
| Attachment | Prepared_stmt_count2.c [ 74028 ] |
Lawrin, yeah maybe 250 is OK then, our specific case may be more of an edge case - we have long-lived connections, and a lot of them.
The cache logic was fine, the problem was in the function that closes statements. In some (not covered by the old test) cases it could never obtain the lock, ant thus did not close the statement. so, yeah, it was leaking statements That deserves quick release imho. I will try to release right after next C/C release, which supposed to happen pretty soon
| Fix Version/s | 3.2.4 [ 29919 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
Thanks! I can confirm that after applying commit cf23af5 my test stayed at no more than 5 Prepared_stmt_count with PSCACHESIZE set to 5.
The driver has features prepared statements cache that is on by default. Thus it can be expected that prepares statements count doesn't decrement on statement handle freeing. You can turn it off by setting PSCACHESIZE to 0.
So, I think there is no bug here, especially since the testsuite has tests controlling closing of server side prepared statement.
Possibly the default size of the cache 250 is too high though.
I will live the ticket open until you verify that you can solve your problem using smaller cache size or completely turning it off.