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:
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!
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:
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!
Hi Daniel,
First call.
Thanks!
On Wednesday, March 30, 2016, Daniel-Constantin Mierla miconda@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:
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 javascript:; http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users
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@gmail.com mailto:miconda@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://twitter.com/#%21/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 <javascript:;> http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-users-- Sent from mobile device
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@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@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:
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
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@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@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:
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
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@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@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:
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
I tried this method and seem to have exchanged one set of errors for another:
Apr 2 06:30:06 pbx kamailio[9526]: INFO: pua_dialoginfo [dialog_publish.c:386]: dialog_publish_multi(): CALLING dialog_publish for URI sip:9197@192.168.111.216;transport=UDP Apr 2 06:30:06 pbx kamailio[9519]: INFO: presence [notify.c:1614]: send_notify_request(): NOTIFY sip:1001@192.168.111.216 via sip:1001@192.168.111.174:53512;transport=UDP on behalf of sip:1000@192.168.111.216 for event dialog Apr 2 06:30:12 pbx kamailio[9532]: ERROR: db_sqlite [dbase.c:489]: db_sqlite_commit(): sqlite commit failed: NOT NULL constraint failed: pua.call_id Apr 2 06:30:12 pbx kamailio[9532]: ERROR: db_sqlite [dbase.c:138]: db_sqlite_cleanup_query(): finalize failed: NOT NULL constraint failed: pua.call_id Apr 2 06:30:12 pbx kamailio[9532]: ERROR: pua [pua.c:1179]: db_update(): while inserting in db table pua
On Fri, Apr 1, 2016 at 5:21 AM, Daniel-Constantin Mierla miconda@gmail.com wrote:
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 miconda@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@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:
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
-- 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
From these errors:
Apr 2 06:30:12 pbx kamailio[9532]: ERROR: db_sqlite [dbase.c:489]: db_sqlite_commit(): sqlite commit failed: NOT NULL constraint failed: pua.call_id Apr 2 06:30:12 pbx kamailio[9532]: ERROR: db_sqlite [dbase.c:138]: db_sqlite_cleanup_query(): finalize failed: NOT NULL constraint failed: pua.call_id
sqlite is enforcing a NOT NULL constraint on call_id in the pua table, so kamailio must be trying to insert records with NULL values for call_id.
Question to the group: Is pua.call_id allowed to be NULL? * If not, then hopefully someone else on the list can chime in to help with diagnostics. * But if call_id may be NULL, then we should strip this constraint from the source file kamailio/utils/kamctl/db_sqlite/presence-create.sql
For experimenting, I can offer a little help for stripping the NULL constraint from an existing database. I was bit by this problem for two other tables, active_watchers.reason and sip_trace.totag
When I looked into it last year, I didn't find a "1-liner" way to remove the constraint via sqlite. So the steps I followed for my errors were:
BEGIN TRANSACTION; rename the_table to old_the_table recreate an empty the_table but without the NOT NULL constraint on the particular field drop the old indices referring to old_the_table recreate new indices for the_table run insert query to copy records from old_the_table to the_table COMMIT; drop old_the_table
For clarity, I attached the two scripts I used for fixing active_watchers.reason and sip_trace.totag: strip_null_constraint_from_active_watchers.reason.sh strip_null_constraint_from_sip_trace.totag.sh
*If* you're adventurous, I drafted a new script for your case with pua.call_id strip_null_constraint_from_pua.call_id.sh
warning: I haven't run this new script myself, so definitely stop kamailio and backup your DB before running. To help with this, I attached "dump_sqlite.sh", which may be invoked via: ./dump_sqlite.sh /path/to/db/kamailio.sqlite > kamailio.sqlite.txt
Also, I wrote these scripts while running the Debian package kamailio 4.3.5-2. I'm not familiar with enough of kamailio's history to know how much the database definitions must change if you have a different version.
I hope it helps, David
On 4/1/16 11:32 PM, Kristian Kielhofner wrote:
I tried this method and seem to have exchanged one set of errors for another:
Apr 2 06:30:06 pbx kamailio[9526]: INFO: pua_dialoginfo [dialog_publish.c:386]: dialog_publish_multi(): CALLING dialog_publish for URI sip:9197@192.168.111.216;transport=UDP Apr 2 06:30:06 pbx kamailio[9519]: INFO: presence [notify.c:1614]: send_notify_request(): NOTIFY sip:1001@192.168.111.216 via sip:1001@192.168.111.174:53512;transport=UDP on behalf of sip:1000@192.168.111.216 for event dialog Apr 2 06:30:12 pbx kamailio[9532]: ERROR: db_sqlite [dbase.c:489]: db_sqlite_commit(): sqlite commit failed: NOT NULL constraint failed: pua.call_id Apr 2 06:30:12 pbx kamailio[9532]: ERROR: db_sqlite [dbase.c:138]: db_sqlite_cleanup_query(): finalize failed: NOT NULL constraint failed: pua.call_id Apr 2 06:30:12 pbx kamailio[9532]: ERROR: pua [pua.c:1179]: db_update(): while inserting in db table pua
On Fri, Apr 1, 2016 at 5:21 AM, Daniel-Constantin Mierla miconda@gmail.com wrote:
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 miconda@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@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:
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
-- 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
Say if I'm working on a Debian system, and would like to assist the project...
1) Is there a script-able means to build .deb packages directly from the git repo? Or are the generic Debian instructions suitable such as pbuilder or git-buildpackage? https://www.debian.org/doc/manuals/maint-guide/build.en.html
I'd like to build .deb packages, similar to how kamailio is distributed, except I'd have my own systems pull from my modified packages for testing. Any tips or kamailio-specific build guidelines would be helpful so that I don't re-invent any wheels.
2) Regarding database modules, are there existing tests that I could run to confirm that any of my local changes to db_sqlite do not break the expected API usage patterns of kamailio? (automated / regression / unit tests / whatever...)
Otherwise, if just testing by loading the modified module on my own systems, I would worry that my own loads would not be sufficient to stress test my changes.
Thank you, David
ps: I'm still reading through the devel guide: http://www.kamailio.org/docs/kamailio-devel-guide/#c09apistructure
On 4/1/16 2:21 AM, Daniel-Constantin Mierla wrote:
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!
-
Daniel-Constantin Mierla -
David Holl -
Kristian Kielhofner