[SR-Users] Error in db_mysql while using presence

Thu Jan 10 08:22:31 CET 2019

Hello,

just throwing blindly a suggestion here: check the database table types,
if you did the installation very long time ago, it might be MyISAM and
iirc, it may require to change it to InnoDB in order to enable some of
the features related to transactions and locking.

Cheers,
Daniel

On 09.01.19 21:46, Henning Westerholt wrote:
> Am Mittwoch, 9. Januar 2019, 09:47:07 CET schrieb Ravindrakumar Bhatt:
>>  I tried on command line with LOCK TABLES watchers write; and it works
>> fine. also i am usinf root at localhost with all permissions still same errors
>> show continuously. please tell if any more details are required for same.
>> Errors:
> Hello Ravindrakumar,
>
> I would suggest then to enable mysql query logging and have a look to the 
> actual query that is executed from the module and match to the errors below.
>
> Please keep the sr-users list in CC.
>
> Best regards,
>
> Henning
>
>> Jan  9 00:11:23 client1 /usr/local/sbin/kamailio[2704]: ERROR: {1 1
>> SUBSCRIBE NGFiMGJmM2VlYWQyNTQ5NWJjNmVmYTExY2ZhMWM1NDQ.} db_mysql
>> [km_dbase.c:128]: db_mysql_submit_query(): driver error on query: Table
>> 'watchers' was not locked with LOCK TABLES (1100)
>> Jan  9 00:11:23 client1 /usr/local/sbin/kamailio[2704]: ERROR: {1 1
>> SUBSCRIBE NGFiMGJmM2VlYWQyNTQ5NWJjNmVmYTExY2ZhMWM1NDQ.} <core>
>> [db_query.c:132]: db_do_query_internal(): error while submitting query
>> Jan  9 00:11:23 client1 /usr/local/sbin/kamailio[2704]: ERROR: {1 1
>> SUBSCRIBE NGFiMGJmM2VlYWQyNTQ5NWJjNmVmYTExY2ZhMWM1NDQ.} presence
>> [subscribe.c:2827]: get_db_subs_auth(): while querying watchers table
>> Jan  9 00:11:23 client1 /usr/local/sbin/kamailio[2704]: ERROR: {1 1
>> SUBSCRIBE NGFiMGJmM2VlYWQyNTQ5NWJjNmVmYTExY2ZhMWM1NDQ.} presence
>> [subscribe.c:1217]: handle_subscribe(): getting subscription status from
>> watchers table
>> Jan  9 00:11:56 client1 /usr/local/sbin/kamailio[2713]: ERROR: db_mysql
>> [km_dbase.c:128]: db_mysql_submit_query(): driver error on query: Table
>> 'active_watchers' was not locked with LOCK TABLES (1100)
>> Jan  9 00:11:56 client1 /usr/local/sbin/kamailio[2713]: ERROR: <core>
>> [db_query.c:132]: db_do_query_internal(): error while submitting query
>> Jan  9 00:11:56 client1 /usr/local/sbin/kamailio[2713]: ERROR: presence
>> [notify.c:1370]: publ_notify_notifier(): Can't query db
>> Jan  9 00:11:56 client1 /usr/local/sbin/kamailio[2713]: ERROR: presence
>> [publish.c:178]: msg_presentity_clean(): Updating watcher records
>> Jan  9 00:12:36 client1 /usr/local/sbin/kamailio[2713]: ERROR: db_mysql
>> [km_dbase.c:128]: db_mysql_submit_query(): driver error on query: Table
>> 'active_watchers' was not locked with LOCK TABLES (1100)
>> Jan  9 00:12:36 client1 /usr/local/sbin/kamailio[2713]: ERROR: <core>
>> [db_query.c:132]: db_do_query_internal(): error while submitting query
>> Jan  9 00:12:36 client1 /usr/local/sbin/kamailio[2713]: ERROR: presence
>> [notify.c:1370]: publ_notify_notifier(): Can't query db
>> Jan  9 00:12:36 client1 /usr/local/sbin/kamailio[2713]: ERROR: presence
>> [publish.c:178]: msg_presentity_clean(): Updating watcher records
>> Jan  9 00:13:16 client1 /usr/local/sbin/kamailio[2713]: ERROR: db_mysql
>> [km_dbase.c:128]: db_mysql_submit_query(): driver error on query: Table
>> 'active_watchers' was not locked with LOCK TABLES (1100)
>> Jan  9 00:13:16 client1 /usr/local/sbin/kamailio[2713]: ERROR: <core>
>> [db_query.c:132]: db_do_query_internal(): error while submitting query
>> Jan  9 00:13:16 client1 /usr/local/sbin/kamailio[2713]: ERROR: presence
>> [notify.c:1370]: publ_notify_notifier(): Can't query db
>> Jan  9 00:13:16 client1 /usr/local/sbin/kamailio[2713]: ERROR: presence
>> [publish.c:178]: msg_presentity_clean(): Updating watcher records
>>
>> On Wed, Jan 9, 2019 at 1:55 AM Henning Westerholt <hw at kamailio.org> wrote:
>>> Am Dienstag, 8. Januar 2019, 14:57:21 CET schrieb Ravindrakumar Bhatt:
>>>> I am using latest kamailio stable version. i have used it for presenece
>>> and
>>>
>>>> BLF. i am facing issue in presence related method when i am setting
>>>> sub_db_mode to any db related mode like 3.
>>> Hello Ravindrakumar,
>>>
>>> the presence module uses a database LOCK TABLE command and this fails. Can
>>> you
>>> check if the database user that is used from Kamailio has the appropriate
>>> permissions to issue this command on the database? You can try e.g. with
>>> mysql
>>> command line client.
>>>
>>>> Version :
>>>> version: kamailio 5.2.0 (x86_64/linux) 535e13
>>>> 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,
>>>> Q_MALLOC, F_MALLOC, TLSF_MALLOC, DBG_SR_MEMORY, USE_FUTEX,
>>>> FAST_LOCK-ADAPTIVE_WAIT, USE_DNS_CACHE, USE_DNS_FAILOVER, USE_NAPTR,
>>>> USE_DST_BLACKLIST, HAVE_RESOLV_RES
>>>> ADAPTIVE_WAIT_LOOPS=1024, MAX_RECV_BUFFER_SIZE 262144 MAX_URI_SIZE 1024,
>>>> BUF_SIZE 65535, DEFAULT PKG_SIZE 8MB
>>>> poll method support: poll, epoll_lt, epoll_et, sigio_rt, select.
>>>> id: 535e13
>>>> compiled on 06:32:22 Jan  8 2019 with gcc 4.8.5
>>>>
>>>> Kamailio.cfg setup:
>>>> modparam("presence|presence_xml", "db_url", DBURL)
>>>> modparam("presence", "server_address", "sip:XX.XX.XX.XX:5050")
>>>> modparam("presence", "send_fast_notify", 0)
>>>> modparam("presence", "db_update_period", 20)
>>>> modparam("presence", "clean_period", 40)
>>>> modparam("presence", "subs_db_mode", 0)
>>>> modparam("presence", "fetch_rows", 1000)
>>>>
>>>> # ----- presence_xml params -----
>>>> #modparam("presence_xml", "db_url", DBURL)
>>>> modparam("presence_xml", "force_active", 1)
>>>>
>>>> # ----- pua params -----
>>>> modparam("pua", "db_url", DBURL)
>>>> modparam("pua", "db_mode", 2)
>>>> modparam("pua", "update_period", 60)
>>>> modparam("pua", "dlginfo_increase_version", 0)
>>>> modparam("pua", "reginfo_increase_version", 0)
>>>> modparam("pua", "check_remote_contact", 1)
>>>> modparam("pua", "fetch_rows", 1000)
>>>>
>>>> # ----- pua_dialoginfo params -----
>>>> modparam("pua_dialoginfo", "include_callid", 1)
>>>> modparam("pua_dialoginfo", "send_publish_flag", FLT_DLGINFO)
>>>> modparam("pua_dialoginfo", "caller_confirmed", 0)
>>>> modparam("pua_dialoginfo", "include_tags", 1)
>>>> modparam("pua_dialoginfo", "override_lifetime", 124)
>>>>
>>>> # ----- dialog params -----
>>>> modparam("dialog", "db_url", DBURL)
>>>> modparam("dialog", "enable_stats", 1)
>>>> modparam("dialog", "db_mode", 1)
>>>> modparam("dialog", "dlg_flag", FLT_DLG)
>>>> modparam("presence_dialoginfo", "force_single_dialog", 0)
>>>> Error:
>>>> Jan  8 04:34:37 client1 /usr/local/sbin/kamailio[19523]: {1 1 PUBLISH
>>>> MzQ4ZjNhOGRlYWZlY2QyMDQwMGZlZjg0N2JlZGE2YWM.} ERROR: db_mysql
>>>> [km_dbase.c:128]: db_mysql_submit_query(): driver error on query: Table
>>>> 'active_watchers' was not locked with LOCK TABLES (1100)
>>>> Jan  8 04:34:37 client1 /usr/local/sbin/kamailio[19523]: {1 1 PUBLISH
>>>> MzQ4ZjNhOGRlYWZlY2QyMDQwMGZlZjg0N2JlZGE2YWM.} ERROR: <core>
>>>> [db_query.c:132]: db_do_query_internal(): error while submitting query
>>>> Jan  8 04:34:37 client1 /usr/local/sbin/kamailio[19523]: {1 1 PUBLISH
>>>> MzQ4ZjNhOGRlYWZlY2QyMDQwMGZlZjg0N2JlZGE2YWM.} ERROR: presence
>>>> [notify.c:1369]: publ_notify_notifier(): Can't query db
>>>> Jan  8 04:34:37 client1 /usr/local/sbin/kamailio[19523]: {1 1 PUBLISH
>>>> MzQ4ZjNhOGRlYWZlY2QyMDQwMGZlZjg0N2JlZGE2YWM.} ERROR: presence
>>>> [presentity.c:1152]: update_presentity(): updating watcher records
>>>> Jan  8 04:34:37 client1 /usr/local/sbin/kamailio[19527]: {1 1 SUBSCRIBE
>>>> MTlkYjI2NTBhYTFlMTBjYmYyMDgzNzQxYTM3OTE4OGE.} ERROR: db_mysql
>>>> [km_dbase.c:128]: db_mysql_submit_query(): driver error on query: Table
>>>> 'watchers' was not locked with LOCK TABLES (1100)
>>>> Jan  8 04:34:37 client1 /usr/local/sbin/kamailio[19527]: {1 1 SUBSCRIBE
>>>> MTlkYjI2NTBhYTFlMTBjYmYyMDgzNzQxYTM3OTE4OGE.} ERROR: <core>
>>>> [db_query.c:132]: db_do_query_internal(): error while submitting query
>>>> Jan  8 04:34:37 client1 /usr/local/sbin/kamailio[19527]: {1 1 SUBSCRIBE
>>>> MTlkYjI2NTBhYTFlMTBjYmYyMDgzNzQxYTM3OTE4OGE.} ERROR: presence
>>>> [subscribe.c:2827]: get_db_subs_auth(): while querying watchers table
>>>> Jan  8 04:34:37 client1 /usr/local/sbin/kamailio[19527]: {1 1 SUBSCRIBE
>>>> MTlkYjI2NTBhYTFlMTBjYmYyMDgzNzQxYTM3OTE4OGE.} ERROR: presence
>>>> [subscribe.c:1217]: handle_subscribe(): getting subscription status from
>>>> watchers table
>>>> Jan  8 04:34:37 client1 /usr/local/sbin/kamailio[19523]: {1 1 PUBLISH
>>>> MzQ4ZjNhOGRlYWZlY2QyMDQwMGZlZjg0N2JlZGE2YWM.} ERROR: presence
>>>> [publish.c:499]: ki_handle_publish_uri(): when updating presentity
>>>>
>>>> I am currently using presence with memory only db_mode but for
>>> persistence
>>>
>>>> i would like to use db_only mode which is not possible due to this
>>>> issue.
>>>> Any idea or suggetions for this issue.
>>> Best regards,
>>>
>>> Henning
>
-- 
Daniel-Constantin Mierla -- www.asipto.com
www.twitter.com/miconda -- www.linkedin.com/in/miconda
Kamailio World Conference - May 6-8, 2019 -- www.kamailioworld.com
Kamailio Advanced Training - Mar 4-6, 2019 in Berlin; Mar 25-27, 2019, in Washington, DC, USA -- www.asipto.com