19 Oct
2018
19 Oct
'18
12:42 a.m.
### Description
I´m using kamailio´s sqlops-module on a postgresql database.
If the database server gets unreachable during operation, sqlops detects this and tries to
reconnect:
```
14:40:48.784 WARNING: db_postgres [km_dbase.c:259]: db_postgres_submit_query(): postgres
query command failed, connection status 1, error [no connection to the server]
14:40:48.784 DEBUG: db_postgres [km_dbase.c:262]: db_postgres_submit_query(): resetting
the connection to postgress server
14:40:48.784 ERROR: db_postgres [km_dbase.c:267]: db_postgres_submit_query():
0x7f80a8093680 PQsendQuery Error: could not connect to server: Connection refused
```
and finally, when the DB comes up again, succeeds in executing queries:
```
14:41:39.738 WARNING: db_postgres [km_dbase.c:259]: db_postgres_submit_query(): postgres
query command failed, connection status 1, error [SSL connection has been closed
unexpectedly]
14:41:39.739 DEBUG: db_postgres [km_dbase.c:262]: db_postgres_submit_query(): resetting
the connection to postgress server
14:41:39.810 DEBUG: db_postgres [km_dbase.c:249]: db_postgres_submit_query(): sending
query ok: 0x7f80a8093680 (1)
```
So, obviously the module can handle database outages well.
But if the DB is already down when kamailio is started, the cyclic reconnect does not
work. Instead, kamailio fails to start immediately:
```
14:57:54.187 CRITICAL: <core> [main.c:1614]: main_loop(): Cannot fork
14:57:54.188 DEBUG: db_postgres [km_pg_con.c:112]: db_postgres_new_connection():
PQconnectdbParams(0x136bfb0)
14:57:54.188 DEBUG: <core> [core/sr_module.c:938]: init_mod_child(): idx 12 rank -1:
rtimer [timer]
14:57:54.188 DEBUG: <core> [core/sr_module.c:938]: init_mod_child(): idx 12 rank -1:
sqlops [timer]
14:57:54.188 DEBUG: <core> [core/sr_module.c:708]: find_mod_export_record(): found
export of <db_bind_api> in module db_postgres
[/usr/lib64/kamailio/modules/db_postgres.so]
14:57:54.188 DEBUG: <core> [db.c:209]: db_bind_mod(): using db bind api for
db_postgres
14:57:54.188 DEBUG: <core> [db.c:314]: db_do_init2(): connection 0x7f23c0154120 not
found in pool
14:57:54.188 DEBUG: db_postgres [km_pg_con.c:56]: db_postgres_new_connection(): db_id =
0x7f23c0154120
14:57:54.188 DEBUG: db_postgres [km_pg_con.c:70]: db_postgres_new_connection():
0x7f23c0154380=pkg_malloc(80)
14:57:54.188 DEBUG: db_postgres [km_pg_con.c:84]: db_postgres_new_connection(): opening
connection: postgres://xxxx:xxxx@xxxxxxxxxxxxxxxxxx
14:57:54.188 DEBUG: db_postgres [km_pg_con.c:112]: db_postgres_new_connection():
PQconnectdbParams(0x13a41e0)
14:57:54.188 ERROR: db_postgres [km_pg_con.c:115]: db_postgres_new_connection(): could not
connect to server: Connection refused Is the server running on host
"xxxxxxxxxxxx" () and accepting TCP/IP connections on port 5432?
14:57:54.188 ERROR: db_postgres [km_pg_con.c:148]: db_postgres_new_connection(): cleaning
up 0x7f23c0154380=pkg_free()
14:57:54.188 ERROR: <core> [db.c:318]: db_do_init2(): could not add connection to
the pool
14:57:54.188 ERROR: sqlops [sql_api.c:164]: sql_connect(): failed to connect to the
database [cb]
14:57:54.188 ERROR: <core> [core/sr_module.c:942]: init_mod_child(): error while
initializing module sqlops (/usr/lib64/kamailio/modules/sqlops.so) (idx: 12 rank: -1 desc:
[timer])
14:57:54.189 ERROR: <core> [core/pt.c:340]: fork_process(): init_child failed for
process 12, pid 24036, "timer"
14:57:54.189 CRITICAL: <core> [main.c:1691]: main_loop(): cannot fork timer
process
14:57:54.189 ALERT: <core> [main.c:743]: handle_sigs(): child process 24009 exited
normally, status=255
14:57:54.190 ALERT: <core> [main.c:743]: handle_sigs(): child process 24010 exited
normally, status=255
14:57:54.190 ALERT: <core> [main.c:743]: handle_sigs(): child process 24011 exited
normally, status=255
14:57:54.191 ALERT: <core> [main.c:743]: handle_sigs(): child process 24013 exited
normally, status=255
14:57:54.191 ALERT: <core> [main.c:743]: handle_sigs(): child process 24015 exited
normally, status=255
14:57:54.191 ALERT: <core> [main.c:743]: handle_sigs(): child process 24019 exited
normally, status=255
14:57:54.191 ALERT: <core> [main.c:743]: handle_sigs(): child process 24030 exited
normally, status=255
14:57:54.192 INFO: <core> [main.c:771]: handle_sigs(): terminating due to SIGCHLD
14:57:54.192 INFO: <core> [main.c:826]: sig_usr(): signal 15 received
14:57:54.192 INFO: <core> [main.c:826]: sig_usr(): signal 15 received
```
#### Reproduction
- use kamailio-script with sqlops
- stop kamailio
- stop database
- try to start kamailio
#### Log Messages
see above
### Additional Information
* **Kamailio Version** - output of `kamailio -v`
kamailio 5.1.2 (x86_64/linux)
* **Operating System**:
RHEL 7.4 on x86_64
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1681
19 Oct
19 Oct
7:14 a.m.
Hello,
thank you for the report. This is the current expected behavior of Kamailio. Modules that
need a database will block the start if the database is not available.
You can start a discussion on the sr-dev list about this, if you plan to change this for
the sqlops module e.g. with a pull request.
Best regards,
Henning
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1681#issuecomment-431247062
1:30 p.m.
I like this default behaviour, but if that's not what you need one could add a
configuration option to make it optional.
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1681#issuecomment-431333063
29 Oct
29 Oct
8:50 p.m.
Hi,
thanks for the responses.
While I agree that the default behavior makes fully sense for most (or nearly all)
modules, still there are usecases where a more tolerant handling would be nice.
In my case, this is a multi-node homer-installation (as advised here:
https://github.com/sipcapture/homer/issues/254 ) - the kamailio (used as
sipcapture-server) uses a local sipcapture database, but all nodes share the same
statistics database (accessed by sqlops) - in case the statistics-DB is not reachable
temporarily during startup, I'd rather keep the sipcapture DB (and lose the
statistics) than blocking startup of kamailio and losing both.
I'm not experienced in kamailio-development, still I tried to implement that
functionality - in
https://github.com/kamailio/kamailio/blob/master/src/modules/sqlops/sqlops.c I changed the
static int child_init(int rank) to always return 0 (regardless of the return-value of
sql_connect() ):
```
static int child_init(int rank)
{
if (rank==PROC_INIT || rank==PROC_MAIN || rank==PROC_TCP_MAIN)
return 0;
int sql_result = sql_connect();
LM_INFO("SQL result: %d \n", sql_result);
return 0;
}
```
The effect was, that kamailio starts even if the database is not reachable (and
sql_connect() returns -1):
```
INFO: sqlops [sqlops.c:156]: child_init(): SQL result: -1
ERROR: db_postgres [km_pg_con.c:115]: db_postgres_new_connection(): could not connect to
server: Connection refused
ERROR: db_postgres [km_pg_con.c:148]: db_postgres_new_connection(): cleaning up
0x7f858b5d6f88=pkg_free()
ERROR: <core> [db.c:318]: db_do_init2(): could not add connection to the pool
ERROR: sqlops [sql_api.c:164]: sql_connect(): failed to connect to the database [cb]
INFO: sqlops [sqlops.c:156]: child_init(): SQL result: -1
INFO: ctl [io_listener.c:210]: io_listen_loop(): io_listen_loop: using epoll_lt as the io
watch method (auto detected)
ERROR: db_postgres [km_pg_con.c:115]: db_postgres_new_connection(): could not connect to
server: Connection refused
ERROR: db_postgres [km_pg_con.c:148]: db_postgres_new_connection(): cleaning up
0x7f858b5d6f88=pkg_free()
ERROR: <core> [db.c:318]: db_do_init2(): could not add connection to the pool
ERROR: sqlops [sql_api.c:164]: sql_connect(): failed to connect to the database [cb]
INFO: sqlops [sqlops.c:156]: child_init(): SQL result: -1
INFO: <script>: INSERT INTO stats_ip_mem as stats ( method, source_ip, total)
VALUES('INVITE', ...
ERROR: <core> [db_query.c:176]: db_do_raw_query(): invalid parameter value
ERROR: sqlops [sql_api.c:265]: sql_do_query(): cannot do the query [INSERT INTO
stats_ip_mem as stats ( method, source_ip, total) VA]
```
However, the module does not "recover" if the database gets reachable afterwards
- I still get the error-message
`ERROR: <core> [db_query.c:176]: db_do_raw_query(): invalid parameter value`
for each sql_query in the script.
Can somebod advise my where/when I could trigger the re-connect after the DB comes up
again?
As sqlops does that properly for DB-outages after a successful sql_connect(), I hoped it
would work also in this case - but it seems that this case needs separate handling.
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1681#issuecomment-434053799
30 Oct
30 Oct
9:56 p.m.
New subject: [kamailio/kamailio] sqlops offline database start and live re-connect (#1681)
The issue here is that the sql_connect() bind and initialize the internal database handle
structure. If this is not properly done, the module can't operate later on the
database. If you want to implement a change like this, I would suggest to dig a bit deeper
in the code.
One idea would be to periodically trigger (e.g. with a timer) a check if the DB got
available, and then initialize it properly. Probably also necessary would be a protection
of every DB access with a lock, to prevent errors during the initialization (because of
the Kamailio multi-process architecture).
If you are interested in digging into that feel free to discuss your approach on our
sr-dev list. If you managed to get a first working version, then just open a pull request
for a review.
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1681#issuecomment-434464872
4 Nov
4 Nov
4:16 p.m.
New subject: [kamailio/kamailio] sqlops offline database start and live re-connect (#1681)
Thanks for your suggestions. I first tried the periodic timer, but I somehow did not get
it working for the child-processes: altough I started a timer in each child, and the
timer-method was even executed per child, it was always executed in the same process
(verified with getpid()), so that the "postponed" initialization happen only in
one process.
Anyhow, I then focussed on another approach, where the initialization is triggered with
each sql-query, instead of periodically (which is anyway the more efficient way).
A first version worked quite well in my tests, and I am already trying to figure out how
to open a PR ;-)
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1681#issuecomment-435677963
9:16 p.m.
New subject: [kamailio/kamailio] sqlops offline database start and live re-connect (#1681)
Just opened https://github.com/kamailio/kamailio/pull/1706 - as this is my first pull
request at all, I hope I did everything correctly ...
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1681#issuecomment-435702016
5 Dec
5 Dec
5:49 p.m.
New subject: [kamailio/kamailio] sqlops offline database start and live re-connect (#1681)
PR was merged.
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1681#issuecomment-444556814
5:49 p.m.
New subject: [kamailio/kamailio] sqlops offline database start and live re-connect (#1681)
Closed #1681.
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1681#event-2007670557
1995
days inactive
2043
days old
8 comments
4 participants
participants (4)
-
Daniel-Constantin Mierla -
Henning Westerholt
-
mihovilkolaric
-
Olle E. Johansson