| Bug #112329 | RELEASE_LOCK does not properly release a lock acquired with GET_LOCK | ||
|---|---|---|---|
| Submitted: | 12 Sep 2023 23:43 | Modified: | 1 Dec 2023 1:41 |
| Reporter: | David LeBlanc | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S1 (Critical) |
| Version: | 8.0.33 | OS: | Windows |
| Assigned to: | CPU Architecture: | Any | |
[12 Sep 2023 23:58]
David LeBlanc
Example log of running Get_Lock and Release_Lock that failes with 8.1
Attachment: SQL-8.1.LOG (application/octet-stream, text), 56.41 KiB.
[12 Sep 2023 23:59]
David LeBlanc
ODBC log from 8.0.32 which still works properly with get_lock and release_lock
Attachment: SQL-8.0.32.LOG (application/octet-stream, text), 53.55 KiB.
[13 Sep 2023 0:00]
David LeBlanc
Example C++ application for Visual Studio 2022 that reproduces the error
Attachment: MySQL-ADOTest.cpp (application/octet-stream, text), 4.57 KiB.
[14 Sep 2023 14:07]
David LeBlanc
Modified this issue to critical because there is no workaround except to use a driver that has critical CVEs reported in it.
[15 Sep 2023 6:07]
Bogdan Degtyariov
Hi David,
Thank you for the detailed description of the problem and for the test code you attached to your report.
I was able to repeat the error. The server query log shows that GET_LOCK and RELEASE_LOCK were done from different connections (118 and 119):
2023-09-15T05:50:26.902583Z 118 Connect root@localhost on test using SSL/TLS
2023-09-15T05:50:26.903503Z 118 Query SET NAMES utf8mb4
2023-09-15T05:50:26.903758Z 118 Query SET character_set_results = NULL
2023-09-15T05:50:26.903956Z 118 Query SET SQL_AUTO_IS_NULL = 0
2023-09-15T05:50:26.904470Z 118 Query select database()
2023-09-15T05:50:26.904704Z 118 Query select database()
2023-09-15T05:50:38.957469Z 118 Query SELECT @@transaction_isolation
2023-09-15T05:50:38.957823Z 118 Query set @@max_execution_time=60000
2023-09-15T05:50:38.958138Z 118 Query set @@sql_select_limit=DEFAULT
2023-09-15T05:50:38.958940Z 118 Query SELECT GET_LOCK('MyLock', 60000) as GotLock
2023-09-15T05:51:19.276102Z 119 Connect root@localhost on test using SSL/TLS
2023-09-15T05:51:19.277561Z 119 Query SET NAMES utf8mb4
2023-09-15T05:51:19.277850Z 119 Query SET character_set_results = NULL
2023-09-15T05:51:19.277985Z 119 Query SET SQL_AUTO_IS_NULL = 0
2023-09-15T05:51:28.348526Z 119 Query set @@max_execution_time=60000
2023-09-15T05:51:28.348750Z 119 Query set @@sql_select_limit=DEFAULT
2023-09-15T05:51:28.350077Z 119 Query SELECT RELEASE_LOCK('MyLock') as ReleasedLock
The new connection is created at line 70:
releaseLockCommand->PutRefActiveConnection(conn);
Setting the bug status as Verified.
[28 Nov 2023 9:24]
Bogdan Degtyariov
Posted by developer: The ADO code that shows the issue executes two statements (by opening record sets), first one calls MySQL function `get_lock()` and the other one calls the corresponding `release_lock()` function. Both statements use the same ADO connection object. The reason the lock is not released is because ADO opens a new MySQL session to execute each of the statements. This results in `get_lock()` being called in one MySQL session and `release_lock()` in another session, which leads leaving the lock (it can be released only in the same session). The reason for ADO to open new MySQL session when opening 2nd record set is because the 1st record set is still active at that time -- it was not closed. Because MySQL can handle only one active statement per session, ADO decides to create new session to handle execution of another statement. This can be fixed by closing the first record set before opening the new one. In that case ADO will re-use the same MySQL session and all will work as expected. The reason why it worked with earlier versions of MySQL ODBC driver is because it contained a bug related to incorrect reporting of the maximum number of active statements per connection. Because of that bug the driver was not correctly reporting MySQL server capabilities causing ADO to think that MySQL can handle arbitrary number of active statements per single session. That made the code to use single MySQL session and not opening a new one for `release_lock()`. But once we fixed the bug in the driver, the issue in the test code is now exposed. To summarize, it is not a bug in the MySQL driver -- the issue is that user code executes `releae_lock()` statement without closing a previous `get_lock()` recordset (that is related to ODBC statement mentioned above). The fact that it worked before was an effect of ODBC Driver bug and incorrect test code cancelling each other.
[1 Dec 2023 1:41]
David LeBlanc
Thank you for the updated and detailed reply. Using this guidance we were able to get our application working with the 8.1 and 8.2 versions of the MySQL ODBC connector. One item to note is that both the Recordset and the Command object need to free their reference to the Connection object in order for the RELEASE_LOCK to work. Modifying the sample to do both of the following makes it work: Recordset->Close(); Command->PutRefActiveConnection(NULL);
Description: Calling RELEASE_LOCK on the latest 8.0.33 and 8.1 drivers does not properly release the lock from C++ ADO. How to repeat: Using ADO from C++ issue the following queries: SELECT GET_LOCK('MyLock', 60000) as GotLock; SELECT RELEASE_LOCK('MyLock') as ReleasedLock; The result of the second query will return 0 indicating that the lock release was not called from the same thread. The proper result is for the lock to be released and provide a return value of 1.