[SR-Users] SQlite DB Lock with Presence

Sat Apr 2 17:35:09 CEST 2016

>From these errors:

Apr  2 06:30:12 pbx kamailio[9532]: ERROR: db_sqlite [dbase.c:489]: db_sqlite_commit(): sqlite commit failed: NOT NULL constraint failed: pua.call_id
Apr  2 06:30:12 pbx kamailio[9532]: ERROR: db_sqlite [dbase.c:138]: db_sqlite_cleanup_query(): finalize failed: NOT NULL constraint failed: pua.call_id

sqlite is enforcing a NOT NULL constraint on call_id in the pua table,
so kamailio must be trying to insert records with NULL values for call_id.

Question to the group:  Is pua.call_id allowed to be NULL?
* If not, then hopefully someone else on the list can chime in to help
with diagnostics.
* But if call_id may be NULL, then we should strip this constraint from
the source file kamailio/utils/kamctl/db_sqlite/presence-create.sql

For experimenting, I can offer a little help for stripping the NULL
constraint from an existing database.  I was bit by this problem for two
other tables, active_watchers.reason and sip_trace.totag

When I looked into it last year, I didn't find a "1-liner" way to remove
the constraint via sqlite.  So the steps I followed for my errors were:

BEGIN TRANSACTION;
rename the_table to old_the_table
recreate an empty the_table but without the NOT NULL constraint on the particular field
drop the old indices referring to old_the_table
recreate new indices for the_table
run insert query to copy records from old_the_table to the_table
COMMIT;
drop old_the_table

For clarity, I attached the two scripts I used for fixing
active_watchers.reason and sip_trace.totag:
strip_null_constraint_from_active_watchers.reason.sh
strip_null_constraint_from_sip_trace.totag.sh

*If* you're adventurous, I drafted a new script for your case with
pua.call_id
strip_null_constraint_from_pua.call_id.sh

warning: I haven't run this new script myself, so definitely stop
kamailio and backup your DB before running.  To help with this, I
attached "dump_sqlite.sh", which may be invoked via:
./dump_sqlite.sh /path/to/db/kamailio.sqlite > kamailio.sqlite.txt

Also, I wrote these scripts while running the Debian package kamailio
4.3.5-2.  I'm not familiar with enough of kamailio's history to know how
much the database definitions must change if you have a different version.

I hope it helps,
David

On 4/1/16 11:32 PM, Kristian Kielhofner wrote:
> I tried this method and seem to have exchanged one set of errors for another:
>
> Apr  2 06:30:06 pbx kamailio[9526]: INFO: pua_dialoginfo
> [dialog_publish.c:386]: dialog_publish_multi(): CALLING dialog_publish
> for URI sip:9197 at 192.168.111.216;transport=UDP
> Apr  2 06:30:06 pbx kamailio[9519]: INFO: presence [notify.c:1614]:
> send_notify_request(): NOTIFY sip:1001 at 192.168.111.216 via
> sip:1001 at 192.168.111.174:53512;transport=UDP on behalf of
> sip:1000 at 192.168.111.216 for event dialog
> Apr  2 06:30:12 pbx kamailio[9532]: ERROR: db_sqlite [dbase.c:489]:
> db_sqlite_commit(): sqlite commit failed: NOT NULL constraint failed:
> pua.call_id
> Apr  2 06:30:12 pbx kamailio[9532]: ERROR: db_sqlite [dbase.c:138]:
> db_sqlite_cleanup_query(): finalize failed: NOT NULL constraint
> failed: pua.call_id
> Apr  2 06:30:12 pbx kamailio[9532]: ERROR: pua [pua.c:1179]:
> db_update(): while inserting in db table pua
>
> On Fri, Apr 1, 2016 at 5:21 AM, Daniel-Constantin Mierla
> <miconda at gmail.com> wrote:
>>
>> On 31/03/16 21:05, David Holl wrote:
>>
>> I'm curious why the kamailio sqlite module doesn't export a transaction API,
>> especially since the native sqlite API does support transactions according
>> to https://www.sqlite.org/transactional.html   Maybe someday, I'd get time
>> to dig into the kamailio module myself to try lending a hand...
>>
>> I guess nobody from dev team needed transaction operations for sqlite, I
>> expect that most of us use a larger sql server (mysql, postgres) if they
>> have presence services...
>>
>> Your contribution to add them will be very welcome, of course!
>>
>>
>> Anyhow, the following is what I did to work around the locked db errors:
>>
>> I'm not running BLF, but I have been using sqlite for my testing.  At first,
>> I was getting occasional "database is locked" errors, but then I enabled
>> "wal" mode on my database on a hunch to speed up operations.  I didn't
>> measure the actual db performance before/after enabling wal, but the error
>> messages went away after enabling wal.  (Now, I only get an occasional
>> locked message if I happen to have SIP tracing enabled and writing to the
>> same database as the rest of the kamailio tables.  To work around this
>> issue, I just moved the sip_trace table to a separate sqlite db file.)
>>
>> wal is described in detail at https://www.sqlite.org/wal.html
>>
>> The following is the short script I wrote to remind myself how I enabled
>> wal.  It just runs  "journal_mode=WAL;" on the database, and the database
>> supposedly can be converted back to the default via " journal_mode=DELETE;".
>> (Though, the usual "cya" disclaimer of backing up your DB always applies...)
>>
>> Contents of make_siptrace_db.sh
>>
>> #!/bin/sh
>> set -e
>> # Instructions:
>> #   /etc/init.d/kamailio stop
>> #   #Run this script with db file as the command line parameter:
>> #   ./make_siptrace_db.sh /path/to/db/kamailio.sqlite
>> #   /etc/init.d/kamailio start
>> #
>> # For details on Write-Ahead Logging (WAL), see
>> https://www.sqlite.org/wal.html
>> exec sqlite3 -bail -batch "$1" << 'EOF'
>> PRAGMA journal_mode=WAL;
>> EOF
>> # If this works, sqlite3 should print out "wal"
>>
>>
>>
>> Very useful hint, many thanks for sharing all these details!
>>
>> Cheers,
>> Daniel
>>
>>
>>
>>
>> On 3/31/16 11:25 AM, Kristian Kielhofner wrote:
>>
>> Hi Daniel,
>>
>>   I switched to mysql and haven't looked back but I will take another
>> look with sqlite because it *should* work.
>>
>>   Thanks!
>>
>> On Thu, Mar 31, 2016 at 12:11 PM, Daniel-Constantin Mierla
>> <miconda at gmail.com> wrote:
>>
>> Hello,
>>
>> I looked quickly at sqlite and the module doesn't export db transaction API,
>> so it is not what I thought in the first place (some presence operations try
>> to use db transactions if supported by db module). So it is something in
>> sqlite that locks the table. Do you see any error message in the logs before
>> the one with the table locked?
>>
>> Cheers,
>> Daniel
>>
>>
>> On 30/03/16 14:28, Kristian Kielhofner wrote:
>>
>> Hi Daniel,
>>
>>  First call.
>>
>> Thanks!
>>
>> On Wednesday, March 30, 2016, Daniel-Constantin Mierla <miconda at gmail.com>
>> wrote:
>>
>> Hello,
>>
>> not using sqlite here, but could be related to presence modules doing db
>> transactions and not releasing the locks. Do you get the issue after the
>> first call, or it runs for a while and at some point it breaks?
>>
>> Cheers,
>> Daniel
>>
>> On 30/03/16 02:50, Kristian Kielhofner wrote:
>>
>> Hello everyone,
>>
>>   I'm trying to use BLF with the config here (except with SQlite):
>>
>> http://kb.asipto.com/kamailio:presence:k43-blf
>>
>>   BLF seems to work perfectly except after the call is ended I get
>> "sqlite commit failed: database is locked" error messages as seen
>> here:
>>
>> http://pastebin.com/xVijj98H
>>
>> BLF is then broken at this point.
>>
>> kamailio -v
>> version: kamailio 4.3.5 (x86_64/linux) 950657
>> flags: STATS: Off, USE_TCP, USE_TLS, USE_SCTP, TLS_HOOKS,
>> USE_RAW_SOCKS, DISABLE_NAGLE, USE_MCAST, DNS_IP_HACK, SHM_MEM,
>> SHM_MMAP, PKG_MALLOC, DBG_QM_MALLOC, USE_FUTEX,
>> FAST_LOCK-ADAPTIVE_WAIT, USE_PTHREAD_MUTEX, USE_DNS_CACHE,
>> USE_DNS_FAILOVER, USE_NAPTR, USE_DST_BLACKLIST, HAVE_RESOLV_RES
>> ADAPTIVE_WAIT_LOOPS=1024, MAX_RECV_BUFFER_SIZE 262144, MAX_LISTEN 16,
>> MAX_URI_SIZE 1024, BUF_SIZE 65535, DEFAULT PKG_SIZE 8MB
>> poll method support: poll, epoll_lt, epoll_et, sigio_rt, select.
>> id: 950657
>> compiled on 01:33:01 Mar 29 2016 with x86_64-openwrt-linux-gnu-gcc 4.8.3
>>
>> Has anyone ever seen this before?
>>
>> Thanks!
>>
>> --
>> Daniel-Constantin Mierla
>> http://www.asipto.com
>> http://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda
>> Kamailio World Conference, Berlin, May 18-20, 2016 -
>> http://www.kamailioworld.com
>>
>>
>> _______________________________________________
>> SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list
>> sr-users at lists.sip-router.org
>> http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users
>>
>> --
>> Sent from mobile device
>>
>>
>> --
>> Daniel-Constantin Mierla
>> http://www.asipto.com
>> http://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda
>> Kamailio World Conference, Berlin, May 18-20, 2016 -
>> http://www.kamailioworld.com
>>
>>
>>
>>
>> _______________________________________________
>> SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list
>> sr-users at lists.sip-router.org
>> http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users
>>
>>
>> --
>> Daniel-Constantin Mierla
>> http://www.asipto.com
>> http://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda
>> Kamailio World Conference, Berlin, May 18-20, 2016 -
>> http://www.kamailioworld.com
>>
>>
>> _______________________________________________
>> SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list
>> sr-users at lists.sip-router.org
>> http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users
>>
>
>

-------------- next part --------------
A non-text attachment was scrubbed...
Name: strip_null_constraint_from_active_watchers.reason.sh
Type: application/x-sh
Size: 2226 bytes
Desc: not available
URL: <http://lists.sip-router.org/pipermail/sr-users/attachments/20160402/444ea102/attachment.sh>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: strip_null_constraint_from_sip_trace.totag.sh
Type: application/x-sh
Size: 1359 bytes
Desc: not available
URL: <http://lists.sip-router.org/pipermail/sr-users/attachments/20160402/444ea102/attachment-0001.sh>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: strip_null_constraint_from_pua.call_id.sh
Type: application/x-sh
Size: 1605 bytes
Desc: not available
URL: <http://lists.sip-router.org/pipermail/sr-users/attachments/20160402/444ea102/attachment-0002.sh>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: dump_sqlite.sh
Type: application/x-sh
Size: 56 bytes
Desc: not available
URL: <http://lists.sip-router.org/pipermail/sr-users/attachments/20160402/444ea102/attachment-0003.sh>