usrloc DB RPC commands don't work with PSQL in 5.6

[false-vacuum]

Description

I'm noticing an issue with certain usrloc RPC commands 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
  

[miconda]

It can be a matter of db mode for usrloc, what it is its value?

[false-vacuum]

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())

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;

---

This doesn't explain the invalid connection parameter error though. I guess this is just another unrelated bug with postgresql?

[miconda]

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.

[false-vacuum]

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.

[miconda]

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.

[false-vacuum]

@miconda Thanks so much the clarification. I didn't realize it was done automatically.