Re: [SR-Users] SQlite DB Lock with Presence

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 &lt;miconda(a)gmail.com&gt; 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 &lt;miconda(a)gmail.com&gt; 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(a)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(a)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(a)lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users