[SR-Users] SQlite DB Lock with Presence

Sat Apr 2 08:32:36 CEST 2016

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
>

-- 
Kristian Kielhofner