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...
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; <https://www.sqlite.org/pragma.html#pragma_journal_mode>" on the database, and the database supposedly can be converted back to the default via " journal_mode=DELETE; <https://www.sqlite.org/pragma.html#pragma_journal_mode>". (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" 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 > <mico...@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 <mico...@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@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@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users
