[SR-Users] SQlite DB Lock with Presence

Fri Apr 1 11:21:30 CEST 2016

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;
> <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"
>
>

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sip-router.org/pipermail/sr-users/attachments/20160401/dcd2ff05/attachment.html>