[SR-Users] SQlite DB Lock with Presence

[David Holl]

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

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