### Description
I noticed that sql_query does not work (using latest 5.8 on Debian 12) when called from `tcp:closed` event_route: ``` event_route [tcp:closed] { # Handle TCP connection close # Delete possible leftover registration if ($conid) sql_query("sip_proxy_cache", "DELETE FROM location WHERE server_id=0 AND connection_id=$conid", "result"); } ``` The query produces errors: ``` 2024-05-07T11:30:14.785915+03:00 siika /usr/bin/sip-proxy[2848078]: CRITICAL: sqlops [sqlops.c:213]: sql_check_connection(): no database handle with reconnect disabled 2024-05-07T11:30:14.785978+03:00 siika /usr/bin/sip-proxy[2848078]: ERROR: sqlops [sqlops.c:232]: sql_query(): invalid connection to database 2024-05-07T11:30:14.786013+03:00 siika /usr/bin/sip-proxy[2848078]: ERROR: sqlops [sqlops.c:237]: sql_query(): with query [DELETE FROM location WHERE server_id=0 AND connection_id=7] ``` I added the last debug message in order to find out where the error comes from.
The same query works fine when executed from regular route and earlier (but don't remember when), the same query worked fine also from `tcp:closed` event_route.
This may be related to https://github.com/kamailio/kamailio/issues/3768.
Same error with `event_route [websocket:closed]`.
Connection close event is executed from TCP main process, which likely has no SQL connection open. Till some point in the past, connection close event was triggered from a few places (from tcp worker processes), but actually it was not done for all cases when the connections were closed, some of these events missing (there are situations when only tcp main process close the connections).
Now, I would strongly advise doing anything time expensive in the TCP main process, it was designed only as tcp connections manager, so in this case, I would rather delegate the sql query to an async worker (via async module or via mqueue+rtimer).
As I wrote, this used to work for many years and now it does not. It is very important to be able to delete the possible leftover registration if tcp connection from the SIP UA get closed due to some failure situation.
Is there somewhere an example how to migrate the above event route action to an async worker?
In order to avoid any performance issues, I tried to change the query from ``` sql_query("sip_proxy_cache", "DELETE FROM location WHERE server_id=0 AND connection_id=$conid", "result"); ``` to ``` sql_query_async("sip_proxy_cache", "DELETE FROM location WHERE server_id=0 AND connection_id=$conid"); ``` but got the same error.
So please make the sql connections available in event routes as they have been before.
I tried with async module, but always got error ``` 2024-05-07T17:31:16.143660+03:00 siika /usr/bin/sip-proxy[2910444]: ERROR: async [async_mod.c:462]: ki_async_task_group_route(): invalid usage for faked message ``` and when I read async module README, all its functions deal with SIP request processing. `tcp:closed` event route does not have any SIP request.
So please restore the old working behavior.
Looking at the code, `sql_query_async(...)` should work, do you get exactly the same error message as with `sql_query()`?
As for the use of async module, can you use the `async_task_data()` or `async_task_group_data()` functions? The other ones are intended for processing of SIP requests because they attempt to suspend the transaction, but in this case is no real SIP request under processing.
Otherwise, repeating somehow, the TCP main process never had a database connection and it should not have. As I said, the old code was triggering connection close events in a few cases when a tcp worker predicted (or instructed) that connection is going (or has to) to be closed by TCP main, but never for the cases when the connection was closed by TCP main alone.
Yes, I'm getting the same error message with sql_query_async: ``` 2024-05-07T19:02:24.167462+03:00 siika /usr/bin/sip-proxy[2948257]: CRITICAL: sqlops [sqlops.c:213]: sql_check_connection(): no database handle with reconnect disabled 2024-05-07T19:02:24.167640+03:00 siika /usr/bin/sip-proxy[2948257]: ERROR: sqlops [sqlops.c:251]: sql_query_async(): invalid connection to database ``` I can try with `async_task_data()`.
Indeed `sql_query_async(...)` makes db connection checks, failing in this case. The db_mysql API function for async query would need only DB URL as a string. I will try to see what alternative can be done for sqlops.
With async_task_data() I got it working like this: ``` event_route [tcp:closed] { # Handle TCP connection close # Delete possible leftover registration if ($conid) { async_task_data("clean_location", "$conid"); }
route [clean_location] { sql_query_async("sip_proxy_cache", "DELETE FROM location WHERE server_id=0 AND connection_id=$async(data)"); }
I'm fine with the above solution and can close this issue.
Closed #3843 as completed.
-
Daniel-Constantin Mierla -
juha-h