<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
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 <a
href="https://www.sqlite.org/transactional.html"><a class="moz-txt-link-freetext" href="https://www.sqlite.org/transactional.html">https://www.sqlite.org/transactional.html</a></a>
Maybe
someday, I'd get time to dig into the kamailio module myself to try
lending a hand...<br>
<br>
Anyhow, the following is what I did to work around the locked db
errors:<br>
<br>
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.)<br>
<br>
wal is described in detail at <a
href="https://www.sqlite.org/wal.html"><a class="moz-txt-link-freetext" href="https://www.sqlite.org/wal.html">https://www.sqlite.org/wal.html</a></a><br>
<br>
The following is the short script I wrote to remind myself how I
enabled wal. It just runs "<a
href="https://www.sqlite.org/pragma.html#pragma_journal_mode">journal_mode=WAL;</a>"
on the database, and the database supposedly can be converted back
to the default via "
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<a href="https://www.sqlite.org/pragma.html#pragma_journal_mode">journal_mode=DELETE;</a>".
(Though, the usual "cya" disclaimer of backing up your DB always
applies...)<br>
<br>
Contents of make_siptrace_db.sh<br>
<br>
#!/bin/sh<br>
set -e<br>
# Instructions:<br>
# /etc/init.d/kamailio stop<br>
# #Run this script with db file as the command line parameter:<br>
# ./make_siptrace_db.sh /path/to/db/kamailio.sqlite<br>
# /etc/init.d/kamailio start<br>
#<br>
# For details on Write-Ahead Logging (WAL), see
<a class="moz-txt-link-freetext" href="https://www.sqlite.org/wal.html">https://www.sqlite.org/wal.html</a><br>
exec sqlite3 -bail -batch "$1" << 'EOF'<br>
PRAGMA journal_mode=WAL;<br>
EOF<br>
# If this works, sqlite3 should print out "wal"<br>
<br>
<br>
<br>
<br>
<div class="moz-cite-prefix">On 3/31/16 11:25 AM, Kristian
Kielhofner wrote:<br>
</div>
<blockquote
cite="mid:CAKDfjgeQz3zjOqTfsh=Xyd6PeKUNt7VvpeN31oOfYfkFBE2Bfw@mail.gmail.com"
type="cite">
<pre wrap="">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
<a class="moz-txt-link-rfc2396E" href="mailto:miconda@gmail.com"><miconda@gmail.com></a> wrote:
</pre>
<blockquote type="cite">
<pre wrap="">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 <a class="moz-txt-link-rfc2396E" href="mailto:miconda@gmail.com"><miconda@gmail.com></a>
wrote:
</pre>
<blockquote type="cite">
<pre wrap="">
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:
</pre>
<blockquote type="cite">
<pre wrap="">Hello everyone,
I'm trying to use BLF with the config here (except with SQlite):
<a class="moz-txt-link-freetext" href="http://kb.asipto.com/kamailio:presence:k43-blf">http://kb.asipto.com/kamailio:presence:k43-blf</a>
BLF seems to work perfectly except after the call is ended I get
"sqlite commit failed: database is locked" error messages as seen
here:
<a class="moz-txt-link-freetext" href="http://pastebin.com/xVijj98H">http://pastebin.com/xVijj98H</a>
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!
</pre>
</blockquote>
<pre wrap="">
--
Daniel-Constantin Mierla
<a class="moz-txt-link-freetext" href="http://www.asipto.com">http://www.asipto.com</a>
<a class="moz-txt-link-freetext" href="http://twitter.com/#!/miconda">http://twitter.com/#!/miconda</a> - <a class="moz-txt-link-freetext" href="http://www.linkedin.com/in/miconda">http://www.linkedin.com/in/miconda</a>
Kamailio World Conference, Berlin, May 18-20, 2016 -
<a class="moz-txt-link-freetext" href="http://www.kamailioworld.com">http://www.kamailioworld.com</a>
_______________________________________________
SIP Express Router (SER) and Kamailio (OpenSER) - sr-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:sr-users@lists.sip-router.org">sr-users@lists.sip-router.org</a>
<a class="moz-txt-link-freetext" href="http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users">http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users</a>
</pre>
</blockquote>
<pre wrap="">
--
Sent from mobile device
--
Daniel-Constantin Mierla
<a class="moz-txt-link-freetext" href="http://www.asipto.com">http://www.asipto.com</a>
<a class="moz-txt-link-freetext" href="http://twitter.com/#!/miconda">http://twitter.com/#!/miconda</a> - <a class="moz-txt-link-freetext" href="http://www.linkedin.com/in/miconda">http://www.linkedin.com/in/miconda</a>
Kamailio World Conference, Berlin, May 18-20, 2016 -
<a class="moz-txt-link-freetext" href="http://www.kamailioworld.com">http://www.kamailioworld.com</a>
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
<br>
</body>
</html>