>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 > <mico...@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 >> <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 >> >> >> -- >> 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 >> > >
strip_null_constraint_from_active_watchers.reason.sh
Description: Bourne shell script
strip_null_constraint_from_sip_trace.totag.sh
Description: Bourne shell script
strip_null_constraint_from_pua.call_id.sh
Description: Bourne shell script
dump_sqlite.sh
Description: Bourne shell script
_______________________________________________ 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
