Alex, Could you supply me a bit more information about your setup?

And for anyone with a bit more knowledge of the db_postgres module:

I've been testing a lot now and really have an issue with the receiving an incorrect resultset. I think I finally have found what's really going wrong.

The permissions module forks into several threads, and then every thread is requesting "select table_version from version where table_name='trusted'". But all threads do this on the same database connection. And I see that sometimes thread A sends the query, then thread B sends the query, on the same database connection. Then thread B is reading the the resultset, and then thread A is reading the resultset. So it is possible that thread A receives the resultset, which is actually the answer of the query of thread B.

When all threads are requesting the same query (select table_version from version where table_name='trusted') that's not a big issue, but when at the same time I receive a SIP REGISTER, Another query is sent on the same database connection as the query from the permissions module. Then the results are swapped, and the table_version function will stop kamailio.

Shouldn't there be some kind locking mechanisch, that the thread that's sending a query to the database server, through a database connection, will also be the thread that's receiving the first resultset. It appears that such kind of locking is not in use at the moment.

With kind regards, Robert Verspuy

On 10/12/2010 05:01 PM, Alex Balashov wrote:

Robert,

We use Postgres 8.4 as well with the permissions module, but have not had this problem...

-- Alex Balashov - Principal Evariste Systems LLC 1170 Peachtree Street 12th Floor, Suite 1200 Atlanta, GA 30309 Tel: +1-678-954-0670 Fax: +1-404-961-1892 Web: http://www.evaristesys.com/

On Oct 12, 2010, at 10:30 AM, Robert Verspuy <robert@exa-omicron.nl mailto:robert@exa-omicron.nl> wrote:

...

I've got a strange issue with the latest kamailio. I also had this problem with kamailio 3.0.3 downloaded from the webpage.

I can start kamailio, but after a while (sometimes within one minuten, sometimes after an hour), I get the following log errors:

Oct 12 15:46:24 sp01 /sbin/kamailio[26621]: ERROR: <core> [db.c:387]: invalid type (3) or nul (0) version columns for trusted Oct 12 15:46:24 sp01 /sbin/kamailio[26621]: ERROR: <core> [db.c:405]: querying version for table trusted Oct 12 15:46:24 sp01 /sbin/kamailio[26621]: ERROR: permissions [trusted.c:249]: error during table version check. Oct 12 15:46:24 sp01 /sbin/kamailio[26621]: ERROR: <core> [sr_module.c:832]: init_mod_child(): Error while initializing module permissions (//lib/kamailio/modules_k/permissions.so) Oct 12 15:46:24 sp01 /sbin/kamailio[26621]: ERROR: <core> [pt.c:481]: ERROR: fork_tcp_process(): init_child failed for process 10, pid 26621, "tcp receiver child=0" Oct 12 15:46:24 sp01 /sbin/kamailio[26621]: ERROR: <core> [tcp_main.c:4811]: ERROR: tcp_main: fork failed: Success Oct 12 15:46:24 sp01 /sbin/kamailio[26625]: : <core> [pass_fd.c:103]: ERROR: recv_all: 1st recv on 21 failed: Connection reset by peer Oct 12 15:46:24 sp01 /sbin/kamailio[26625]: : <core> [tcp_main.c:3323]: ERROR: handle_tcp_child: read from tcp child 0 (pid 26621, no 10) Connection reset by peer [104] Oct 12 15:46:24 sp01 /sbin/kamailio[26625]: : <core> [pass_fd.c:293]: ERROR: receive_fd: EOF on 19 Oct 12 15:46:24 sp01 /sbin/kamailio[26611]: ALERT: <core> [main.c:738]: child process 26621 exited normally, status=255 Oct 12 15:46:24 sp01 /sbin/kamailio[26611]: INFO: <core> [main.c:756]: INFO: terminating due to SIGCHLD Oct 12 15:46:24 sp01 /sbin/kamailio[26625]: INFO: <core> [main.c:807]: INFO: signal 15 received

Looking at the error message, it thinks that the column type of table_version in the table version is a string (type 3), and it should be an int (type 1). But in the database it really is an int.

When I run kamailio with debugging on level 5, I see that it's trying to run the "select table_version from version where table_name='trusted'" several times when kamailio starts (for every child thread). And then minutes, of even an hour later, it suddenly continues with that check, but based on a database response from another query. This is the "select password from subscriber ....etc...". And that column is indeed an string.

I'm using PostgreSQL 8.4 as server, but maybe someone has seen this before, or now where I have to start looking? I first saw it yesterday when started to use the permissions module. I only see this with the trusted table, And always it uses the "select password from subscriber ... " response when giving this error.

So is this an error in the permissions module / trusted.c file? or something in auth_db that's not freeing the response correctly?

Strange thing is, that is sometimes happens very quick, and sometimes it can take an half hour or hour.

I can mail the full log if anybody is interested and thinks he/she can help me.

With kind regards, Robert Verspuy

-- *Exa-Omicron* Patroonsweg 10 3892 DB Zeewolde Tel.: 088-OMICRON (66 427 66) http://www.exa-omicron.nl _______________________________________________ sr-dev mailing list sr-dev@lists.sip-router.org mailto:sr-dev@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev

On 10/21/2010 06:12 PM, Alex Balashov wrote:

Personally, I have not had any issues. My setup is with PostgreSQL 8.4 and lately PostgreSQL 9.0, and involves use of 'permissions', a few other minor DB-backed modules, and also very heavy use of sqlops. Some of these are very high-volume / concurrent call dense.

How many threads do you have running? And do you have the permissions module setup as cached or not?

I found that the cause is not in the permission module, but the permissions module (without caching), together with "childeren = 32", does help to get the problem happen more often.

I have now about 100 threads when kamailio is running ( with childeren = 32 ) on a server with two quad-core processors (8 cores total).

Regards, Robert

I found the cause of my issue.

In the source lib/srdb1/* some kind of pooling is used. When kamailio needs a database connection, the cmp_db_id function checks to see if there is already a database connection with the exact same params (username, database, server etc..).

According to the PQ library every proces / thread should have it's own database connection. The db_postgres is setup to use asynchronous I/O with the PQ library. So when two threads are using the same database connection it is possible that, the resultset from a query of thread A is returned to thread B. The cmp_db_id function in lib/srdb1/db_id.c does _not_ check the PID.

I've created a small patch to fix the problems mentioned above.

The changes are made in lib/srdb1/* files So if you apply them, it will be active for _every_ database module. Maybe it's not needed for the mysql database, but I *assume* it will not break when used on a mysql database. (not tested) Maybe you will get a bit more database connections, but that depends on which modules you're using.

What did I do:

* Added pid to the db_id struct. * During initialization the pid is saved into the db_id struct. * In cmp_db_id the pid is also checked when searching for a database connection to be reused.

With kind Regards, Robert Verspuy