7 May
2024
7 May
'24
12:13 p.m.
### 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.
--
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/3843
You are receiving this because you are subscribed to this thread.
Message ID: <kamailio/kamailio/issues/3843(a)github.com>
7 May
7 May
1 p.m.
Same error with `event_route [websocket:closed]`.
--
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/3843#issuecomment-2097918824
You are receiving this because you are subscribed to this thread.
Message ID: <kamailio/kamailio/issues/3843/2097918824(a)github.com>
1:32 p.m.
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).
--
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/3843#issuecomment-2098023946
You are receiving this because you are subscribed to this thread.
Message ID: <kamailio/kamailio/issues/3843/2098023946(a)github.com>
4:34 p.m.
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?
--
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/3843#issuecomment-2098424031
You are receiving this because you are subscribed to this thread.
Message ID: <kamailio/kamailio/issues/3843/2098424031(a)github.com>
5:04 p.m.
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.
--
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/3843#issuecomment-2098488770
You are receiving this because you are subscribed to this thread.
Message ID: <kamailio/kamailio/issues/3843/2098488770(a)github.com>
5:37 p.m.
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.
--
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/3843#issuecomment-2098560918
You are receiving this because you are subscribed to this thread.
Message ID: <kamailio/kamailio/issues/3843/2098560918(a)github.com>
6:57 p.m.
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.
--
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/3843#issuecomment-2098793894
You are receiving this because you are subscribed to this thread.
Message ID: <kamailio/kamailio/issues/3843/2098793894(a)github.com>
7:06 p.m.
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()`.
--
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/3843#issuecomment-2098813053
You are receiving this because you are subscribed to this thread.
Message ID: <kamailio/kamailio/issues/3843/2098813053(a)github.com>
7:24 p.m.
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.
--
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/3843#issuecomment-2098847857
You are receiving this because you are subscribed to this thread.
Message ID: <kamailio/kamailio/issues/3843/2098847857(a)github.com>
7:43 p.m.
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)");
}
--
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/3843#issuecomment-2098879746
You are receiving this because you are subscribed to this thread.
Message ID: <kamailio/kamailio/issues/3843/2098879746(a)github.com>
8:19 p.m.
I'm fine with the above solution and can close this issue.
--
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/3843#issuecomment-2098939975
You are receiving this because you are subscribed to this thread.
Message ID: <kamailio/kamailio/issues/3843/2098939975(a)github.com>
8:19 p.m.
Closed #3843 as completed.
--
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/3843#event-12732879781
You are receiving this because you are subscribed to this thread.
Message ID: <kamailio/kamailio/issue/3843/issue_event/12732879781(a)github.com>
229
days inactive
229
days old
11 comments
2 participants
participants (2)
-
Daniel-Constantin Mierla -
juha-h