### Description
I'm noticing an issue with certain RPC after upgrading to 5.6. I'm using "db_postgres".
If I run ul.flush like this: ``` kamcmd ul.flush # no output ```
None of the in-memory registrations are flushed to the DB and in the log I see a bunch of lines like this: ``` May 12 20:39:16 <dummy-hostname> /usr/sbin/kamailio[9006]: ERROR: <core> [db.c:481]: db_use_table(): invalid connection parameter May 12 20:39:16 <dummy-hostname> /usr/sbin/kamailio[9006]: ERROR: usrloc [ucontact.c:1142]: db_update_ucontact_ruid(): sql use_table failed May 12 20:39:16 <dummy-hostname> /usr/sbin/kamailio[9006]: ERROR: usrloc [urecord.c:433]: wb_timer(): updating contact in db failed (aor: fake-1@aor.com) May 12 20:39:16 <dummy-hostname> /usr/sbin/kamailio[9006]: ERROR: <core> [db.c:481]: db_use_table(): invalid connection parameter May 12 20:39:16 <dummy-hostname> /usr/sbin/kamailio[9006]: ERROR: usrloc [ucontact.c:1142]: db_update_ucontact_ruid(): sql use_table failed May 12 20:39:16 <dummy-hostname> /usr/sbin/kamailio[9006]: ERROR: usrloc [urecord.c:433]: wb_timer(): updating contact in db failed (aor: fake-2@aor.com) ```
I see the same thing with ul.db_users: ``` kamcmd ul.db_users location # returns the following output error: 500 - Failed to use table
# in the log ERROR: <core> [db.c:481]: db_use_table(): invalid connection parameter
```
### Troubleshooting
#### Reproduction
Seems to happen consistently on 5.6, using psql as the db engine. I tested on 5.4 and it's not happening.
It's easier to test with ```kamcmd ul.db_users location``` because you need registrations that haven't been flushed to the DB for the error to manifest.
* **Operating System**: ``` Distributor ID: Debian Description: Debian GNU/Linux 10 (buster) Release: 10 Codename: buster
Kernel: 4.19.0-21-cloud-amd64 ```
It can be a matter of db mode for usrloc, what it is its value?
It can be a matter of db mode for usrloc, what it is its value?
I'm currently using `db_mode 2`.
I just tested the command `kamcmd ul.db_users location` with `db_mode 1`, and I encountered a different error in the log:
``` May 16 11:15:55 ip-10-0-1-63 /usr/sbin/kamailio[29873]: ERROR: db_postgres [km_dbase.c:267]: db_postgres_submit_query(): 0x7f4a0d8e8148 PQsendQuery Error: ERROR: function count(character varying, character varying) does not exist#012LINE 1: SELECT COUNT(DISTINCT username, domain) FROM location WHERE ...#012 ^#012HINT: No function matches the given name and argument types. You might need to add explicit type casts.#012 Query: SELECT COUNT(DISTINCT username, domain) FROM location WHERE (UNIX_TIMESTAMP(expires) = 0) OR (expires > NOW()) ```
I believe this is another manifestation of the same issue with PostgreSQL and `usrloc`.
It seems that the problem lies in the SQL query:
``` SELECT COUNT(DISTINCT username, domain) FROM location; ```
After conducting some research, I discovered that PostgreSQL does not support the `count` function with more than one column.
To achieve the same functionality, we could use the following query instead: ``` SELECT COUNT(DISTINCT CONCAT(username, '@', domain)) FROM location; ```
The ul.flush depends on db connection being available in the rpc process, I added a small note to the docs.
I am not sure who added and what is the real purpose for the other RPC commands with direct db operations, because they are using raw sql queries which should not be done inside the c code of the modules, because they are practically useless with text, redis or mongodb backends. Maybe they are just some old commands.
Do you need them for some specific reasons?
I recommend to use directly kamctl (or kamcli) to execute the queries over the database.
The only command that is practically useful is `ul.flush`. I have this being called in a ExecStop directive in the unit file.
When I restart the service, my goal is to ensure that the in-memory registrations are properly saved to the database, so they are not lost. However, it's unclear if this process happens automatically. I have noticed that sometimes I encounter lost registrations when restarting the service.
Writing to database is done automatically on shutdown for db mode 2. For db mode 1 is done immediately when registration is processed, but if the database is not available, then some records might not be there and could be the only useful case for ul.flush to be called when the db was not available for db mode 1. For db mode 2 the write to db it is done on timer basis as well, the rpc process cannot write to db for mode 2.
@miconda Thanks so much the clarification. I didn't realize it was done automatically.
Closed #3452 as completed.
-
Daniel-Constantin Mierla -
Jake Greene