<div dir="ltr"><div dir="ltr"><div dir="ltr">Thanks Daniel for your suggestion but i am using default database created by kamdbctl create so database engine is surely InnoDB.</div><div>As per your suggestion Henning, i have checked query logs of mysql and i noticed one strange thing that even kamailio is sending query for watcher table it is only locking active_watcher table.</div><div>Also kamailio is constantly firing this query : select `presentity_uri`,`callid`,`to_tag`,`from_tag`,`event` from `active_watchers` where `updated`=15 AND `event`<>'presence.winfo' for ids 0 to 49. what is the perpose if this please explain.</div><div><br></div><div>LOGS:</div><div><div>2019-01-10T06:51:07.356897Z<span style="white-space:pre"> </span> 67 Query<span style="white-space:pre"> </span>SET autocommit=0</div><div>2019-01-10T06:51:07.357157Z<span style="white-space:pre"> </span> 67 Query<span style="white-space:pre"> </span>LOCK TABLES active_watchers WRITE</div><div>2019-01-10T06:51:07.357461Z<span style="white-space:pre"> </span> 67 Query<span style="white-space:pre"> </span>select `presentity_uri`,`callid`,`to_tag`,`from_tag`,`event` from `active_watchers` where `updated`=15 AND `event`<>'presence.winfo'</div><div>2019-01-10T06:51:07.357796Z<span style="white-space:pre"> </span> 67 Query<span style="white-space:pre"> </span>ROLLBACK</div><div>2019-01-10T06:51:07.357916Z<span style="white-space:pre"> </span> 67 Query<span style="white-space:pre"> </span>SET autocommit=1</div><div>2019-01-10T06:51:07.358047Z<span style="white-space:pre"> </span> 67 Query<span style="white-space:pre"> </span>UNLOCK TABLES</div><div>2019-01-10T06:51:07.358162Z<span style="white-space:pre"> </span> 67 Query<span style="white-space:pre"> </span>SET autocommit=0</div><div>2019-01-10T06:51:07.358261Z<span style="white-space:pre"> </span> 67 Query<span style="white-space:pre"> </span>LOCK TABLES active_watchers WRITE</div><div>2019-01-10T06:51:07.358470Z<span style="white-space:pre"> </span> 67 Query<span style="white-space:pre"> </span>select `presentity_uri`,`callid`,`to_tag`,`from_tag`,`event` from `active_watchers` where `updated`=15 AND `event`='presence.winfo'</div><div>2019-01-10T06:51:07.358689Z<span style="white-space:pre"> </span> 67 Query<span style="white-space:pre"> </span>ROLLBACK</div><div>2019-01-10T06:51:07.359066Z<span style="white-space:pre"> </span> 67 Query<span style="white-space:pre"> </span>SET autocommit=1</div><div>2019-01-10T06:51:07.359191Z<span style="white-space:pre"> </span> 67 Query<span style="white-space:pre"> </span>UNLOCK TABLES</div><div>########################### NO locking of tables for this two queires #################################</div><div>2019-01-10T06:51:07.390846Z<span style="white-space:pre"> </span> 61 Query<span style="white-space:pre"> </span>select `username`,`domain`,`etag`,`event` from `presentity` where `expires`<1547103067 AND `expires`>0 order by username</div><div>2019-01-10T06:51:07.391516Z<span style="white-space:pre"> </span> 61 Query<span style="white-space:pre"> </span>delete from `watchers` where `inserted_time`<1547016667 AND `status`=2</div><div>#######################################################################################</div><div>2019-01-10T06:51:07.391779Z<span style="white-space:pre"> </span> 61 Query<span style="white-space:pre"> </span>SET autocommit=0</div><div>2019-01-10T06:51:07.391930Z<span style="white-space:pre"> </span> 61 Query<span style="white-space:pre"> </span>LOCK TABLES active_watchers WRITE ##### LOCKS table active watcher ????</div><div>2019-01-10T06:51:07.392276Z<span style="white-space:pre"> </span> 61 Query<span style="white-space:pre"> </span>select `callid`,`to_tag`,`from_tag` from `active_watchers` where `expires`<1547103067 AND `updated`=-1</div><div>2019-01-10T06:51:07.392617Z<span style="white-space:pre"> </span> 61 Query<span style="white-space:pre"> </span>COMMIT</div><div>2019-01-10T06:51:07.392790Z<span style="white-space:pre"> </span> 61 Query<span style="white-space:pre"> </span>SET autocommit=1</div><div>2019-01-10T06:51:07.392936Z<span style="white-space:pre"> </span> 61 Query<span style="white-space:pre"> </span>UNLOCK TABLES</div><div>2019-01-10T06:51:07.459480Z<span style="white-space:pre"> </span> 67 Query<span style="white-space:pre"> </span>SET autocommit=0</div><div>2019-01-10T06:51:07.459651Z<span style="white-space:pre"> </span> 67 Query<span style="white-space:pre"> </span>LOCK TABLES active_watchers WRITE</div><div>2019-01-10T06:51:07.459926Z<span style="white-space:pre"> </span> 67 Query<span style="white-space:pre"> </span>select `presentity_uri`,`callid`,`to_tag`,`from_tag`,`event` from `active_watchers` where `updated`=16 AND `event`<>'presence.winfo'</div><div>2019-01-10T06:51:07.460225Z<span style="white-space:pre"> </span> 67 Query<span style="white-space:pre"> </span>ROLLBACK</div><div>2019-01-10T06:51:07.460322Z<span style="white-space:pre"> </span> 67 Query<span style="white-space:pre"> </span>SET autocommit=1</div><div>2019-01-10T06:51:07.460445Z<span style="white-space:pre"> </span> 67 Query<span style="white-space:pre"> </span>UNLOCK TABLES</div><div>2019-01-10T06:51:03.369484Z<span style="white-space:pre"> </span> 53 Query<span style="white-space:pre"> </span>SET autocommit=0</div><div>2019-01-10T06:51:03.369760Z<span style="white-space:pre"> </span> 53 Query<span style="white-space:pre"> </span>LOCK TABLES active_watchers WRITE ##### LOCKS table active watcher ????</div><div>2019-01-10T06:51:03.369962Z<span style="white-space:pre"> </span> 67 Query<span style="white-space:pre"> </span>SET autocommit=0</div><div>2019-01-10T06:51:03.370104Z<span style="white-space:pre"> </span> 67 Query<span style="white-space:pre"> </span>LOCK TABLES active_watchers WRITE</div><div>2019-01-10T06:51:03.371851Z<span style="white-space:pre"> </span> 53 Query<span style="white-space:pre"> </span>select `status`,`reason` from `watchers` where `presentity_uri`='<a href="mailto:sip%3A4040@172.16.16.149">sip:4040@172.16.16.149</a>' AND `watcher_username`='3030' AND `watcher_domain`='172.16.16.149' AND `event`='presence'</div><div>2019-01-10T06:51:03.376684Z<span style="white-space:pre"> </span> 53 Query<span style="white-space:pre"> </span>ROLLBACK</div><div>2019-01-10T06:51:03.376836Z<span style="white-space:pre"> </span> 53 Query<span style="white-space:pre"> </span>SET autocommit=1</div><div>2019-01-10T06:51:03.377321Z<span style="white-space:pre"> </span> 53 Query<span style="white-space:pre"> </span>UNLOCK TABLES</div><div>2019-01-10T06:51:03.379047Z<span style="white-space:pre"> </span> 67 Query<span style="white-space:pre"> </span>select `presentity_uri`,`callid`,`to_tag`,`from_tag`,`event` from `active_watchers` where `updated`=26 AND `event`<>'presence.winfo'</div><div>2019-01-10T06:51:03.379571Z<span style="white-space:pre"> </span> 67 Query<span style="white-space:pre"> </span>ROLLBACK</div><div>2019-01-10T06:51:03.380294Z<span style="white-space:pre"> </span> 67 Query<span style="white-space:pre"> </span>SET autocommit=1</div><div>2019-01-10T06:51:03.383371Z<span style="white-space:pre"> </span> 67 Query<span style="white-space:pre"> </span>UNLOCK TABLES</div></div></div></div><br><div class="gmail_quote"><div dir="ltr">On Thu, Jan 10, 2019 at 2:16 AM Henning Westerholt <<a href="mailto:hw@kamailio.org">hw@kamailio.org</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Am Mittwoch, 9. Januar 2019, 09:47:07 CET schrieb Ravindrakumar Bhatt:<br>
> I tried on command line with LOCK TABLES watchers write; and it works<br>
> fine. also i am usinf root@localhost with all permissions still same errors<br>
> show continuously. please tell if any more details are required for same.<br>
> Errors:<br>
<br>
Hello Ravindrakumar,<br>
<br>
I would suggest then to enable mysql query logging and have a look to the <br>
actual query that is executed from the module and match to the errors below.<br>
<br>
Please keep the sr-users list in CC.<br>
<br>
Best regards,<br>
<br>
Henning<br>
<br>
> Jan 9 00:11:23 client1 /usr/local/sbin/kamailio[2704]: ERROR: {1 1<br>
> SUBSCRIBE NGFiMGJmM2VlYWQyNTQ5NWJjNmVmYTExY2ZhMWM1NDQ.} db_mysql<br>
> [km_dbase.c:128]: db_mysql_submit_query(): driver error on query: Table<br>
> 'watchers' was not locked with LOCK TABLES (1100)<br>
> Jan 9 00:11:23 client1 /usr/local/sbin/kamailio[2704]: ERROR: {1 1<br>
> SUBSCRIBE NGFiMGJmM2VlYWQyNTQ5NWJjNmVmYTExY2ZhMWM1NDQ.} <core><br>
> [db_query.c:132]: db_do_query_internal(): error while submitting query<br>
> Jan 9 00:11:23 client1 /usr/local/sbin/kamailio[2704]: ERROR: {1 1<br>
> SUBSCRIBE NGFiMGJmM2VlYWQyNTQ5NWJjNmVmYTExY2ZhMWM1NDQ.} presence<br>
> [subscribe.c:2827]: get_db_subs_auth(): while querying watchers table<br>
> Jan 9 00:11:23 client1 /usr/local/sbin/kamailio[2704]: ERROR: {1 1<br>
> SUBSCRIBE NGFiMGJmM2VlYWQyNTQ5NWJjNmVmYTExY2ZhMWM1NDQ.} presence<br>
> [subscribe.c:1217]: handle_subscribe(): getting subscription status from<br>
> watchers table<br>
> Jan 9 00:11:56 client1 /usr/local/sbin/kamailio[2713]: ERROR: db_mysql<br>
> [km_dbase.c:128]: db_mysql_submit_query(): driver error on query: Table<br>
> 'active_watchers' was not locked with LOCK TABLES (1100)<br>
> Jan 9 00:11:56 client1 /usr/local/sbin/kamailio[2713]: ERROR: <core><br>
> [db_query.c:132]: db_do_query_internal(): error while submitting query<br>
> Jan 9 00:11:56 client1 /usr/local/sbin/kamailio[2713]: ERROR: presence<br>
> [notify.c:1370]: publ_notify_notifier(): Can't query db<br>
> Jan 9 00:11:56 client1 /usr/local/sbin/kamailio[2713]: ERROR: presence<br>
> [publish.c:178]: msg_presentity_clean(): Updating watcher records<br>
> Jan 9 00:12:36 client1 /usr/local/sbin/kamailio[2713]: ERROR: db_mysql<br>
> [km_dbase.c:128]: db_mysql_submit_query(): driver error on query: Table<br>
> 'active_watchers' was not locked with LOCK TABLES (1100)<br>
> Jan 9 00:12:36 client1 /usr/local/sbin/kamailio[2713]: ERROR: <core><br>
> [db_query.c:132]: db_do_query_internal(): error while submitting query<br>
> Jan 9 00:12:36 client1 /usr/local/sbin/kamailio[2713]: ERROR: presence<br>
> [notify.c:1370]: publ_notify_notifier(): Can't query db<br>
> Jan 9 00:12:36 client1 /usr/local/sbin/kamailio[2713]: ERROR: presence<br>
> [publish.c:178]: msg_presentity_clean(): Updating watcher records<br>
> Jan 9 00:13:16 client1 /usr/local/sbin/kamailio[2713]: ERROR: db_mysql<br>
> [km_dbase.c:128]: db_mysql_submit_query(): driver error on query: Table<br>
> 'active_watchers' was not locked with LOCK TABLES (1100)<br>
> Jan 9 00:13:16 client1 /usr/local/sbin/kamailio[2713]: ERROR: <core><br>
> [db_query.c:132]: db_do_query_internal(): error while submitting query<br>
> Jan 9 00:13:16 client1 /usr/local/sbin/kamailio[2713]: ERROR: presence<br>
> [notify.c:1370]: publ_notify_notifier(): Can't query db<br>
> Jan 9 00:13:16 client1 /usr/local/sbin/kamailio[2713]: ERROR: presence<br>
> [publish.c:178]: msg_presentity_clean(): Updating watcher records<br>
> <br>
> On Wed, Jan 9, 2019 at 1:55 AM Henning Westerholt <<a href="mailto:hw@kamailio.org" target="_blank">hw@kamailio.org</a>> wrote:<br>
> > Am Dienstag, 8. Januar 2019, 14:57:21 CET schrieb Ravindrakumar Bhatt:<br>
> > > I am using latest kamailio stable version. i have used it for presenece<br>
> > <br>
> > and<br>
> > <br>
> > > BLF. i am facing issue in presence related method when i am setting<br>
> > > sub_db_mode to any db related mode like 3.<br>
> > <br>
> > Hello Ravindrakumar,<br>
> > <br>
> > the presence module uses a database LOCK TABLE command and this fails. Can<br>
> > you<br>
> > check if the database user that is used from Kamailio has the appropriate<br>
> > permissions to issue this command on the database? You can try e.g. with<br>
> > mysql<br>
> > command line client.<br>
> > <br>
> > > Version :<br>
> > > version: kamailio 5.2.0 (x86_64/linux) 535e13<br>
> > > flags: STATS: Off, USE_TCP, USE_TLS, USE_SCTP, TLS_HOOKS, USE_RAW_SOCKS,<br>
> > > DISABLE_NAGLE, USE_MCAST, DNS_IP_HACK, SHM_MEM, SHM_MMAP, PKG_MALLOC,<br>
> > > Q_MALLOC, F_MALLOC, TLSF_MALLOC, DBG_SR_MEMORY, USE_FUTEX,<br>
> > > FAST_LOCK-ADAPTIVE_WAIT, USE_DNS_CACHE, USE_DNS_FAILOVER, USE_NAPTR,<br>
> > > USE_DST_BLACKLIST, HAVE_RESOLV_RES<br>
> > > ADAPTIVE_WAIT_LOOPS=1024, MAX_RECV_BUFFER_SIZE 262144 MAX_URI_SIZE 1024,<br>
> > > BUF_SIZE 65535, DEFAULT PKG_SIZE 8MB<br>
> > > poll method support: poll, epoll_lt, epoll_et, sigio_rt, select.<br>
> > > id: 535e13<br>
> > > compiled on 06:32:22 Jan 8 2019 with gcc 4.8.5<br>
> > > <br>
> > > Kamailio.cfg setup:<br>
> > > modparam("presence|presence_xml", "db_url", DBURL)<br>
> > > modparam("presence", "server_address", "sip:XX.XX.XX.XX:5050")<br>
> > > modparam("presence", "send_fast_notify", 0)<br>
> > > modparam("presence", "db_update_period", 20)<br>
> > > modparam("presence", "clean_period", 40)<br>
> > > modparam("presence", "subs_db_mode", 0)<br>
> > > modparam("presence", "fetch_rows", 1000)<br>
> > > <br>
> > > # ----- presence_xml params -----<br>
> > > #modparam("presence_xml", "db_url", DBURL)<br>
> > > modparam("presence_xml", "force_active", 1)<br>
> > > <br>
> > > # ----- pua params -----<br>
> > > modparam("pua", "db_url", DBURL)<br>
> > > modparam("pua", "db_mode", 2)<br>
> > > modparam("pua", "update_period", 60)<br>
> > > modparam("pua", "dlginfo_increase_version", 0)<br>
> > > modparam("pua", "reginfo_increase_version", 0)<br>
> > > modparam("pua", "check_remote_contact", 1)<br>
> > > modparam("pua", "fetch_rows", 1000)<br>
> > > <br>
> > > # ----- pua_dialoginfo params -----<br>
> > > modparam("pua_dialoginfo", "include_callid", 1)<br>
> > > modparam("pua_dialoginfo", "send_publish_flag", FLT_DLGINFO)<br>
> > > modparam("pua_dialoginfo", "caller_confirmed", 0)<br>
> > > modparam("pua_dialoginfo", "include_tags", 1)<br>
> > > modparam("pua_dialoginfo", "override_lifetime", 124)<br>
> > > <br>
> > > # ----- dialog params -----<br>
> > > modparam("dialog", "db_url", DBURL)<br>
> > > modparam("dialog", "enable_stats", 1)<br>
> > > modparam("dialog", "db_mode", 1)<br>
> > > modparam("dialog", "dlg_flag", FLT_DLG)<br>
> > > modparam("presence_dialoginfo", "force_single_dialog", 0)<br>
> > > Error:<br>
> > > Jan 8 04:34:37 client1 /usr/local/sbin/kamailio[19523]: {1 1 PUBLISH<br>
> > > MzQ4ZjNhOGRlYWZlY2QyMDQwMGZlZjg0N2JlZGE2YWM.} ERROR: db_mysql<br>
> > > [km_dbase.c:128]: db_mysql_submit_query(): driver error on query: Table<br>
> > > 'active_watchers' was not locked with LOCK TABLES (1100)<br>
> > > Jan 8 04:34:37 client1 /usr/local/sbin/kamailio[19523]: {1 1 PUBLISH<br>
> > > MzQ4ZjNhOGRlYWZlY2QyMDQwMGZlZjg0N2JlZGE2YWM.} ERROR: <core><br>
> > > [db_query.c:132]: db_do_query_internal(): error while submitting query<br>
> > > Jan 8 04:34:37 client1 /usr/local/sbin/kamailio[19523]: {1 1 PUBLISH<br>
> > > MzQ4ZjNhOGRlYWZlY2QyMDQwMGZlZjg0N2JlZGE2YWM.} ERROR: presence<br>
> > > [notify.c:1369]: publ_notify_notifier(): Can't query db<br>
> > > Jan 8 04:34:37 client1 /usr/local/sbin/kamailio[19523]: {1 1 PUBLISH<br>
> > > MzQ4ZjNhOGRlYWZlY2QyMDQwMGZlZjg0N2JlZGE2YWM.} ERROR: presence<br>
> > > [presentity.c:1152]: update_presentity(): updating watcher records<br>
> > > Jan 8 04:34:37 client1 /usr/local/sbin/kamailio[19527]: {1 1 SUBSCRIBE<br>
> > > MTlkYjI2NTBhYTFlMTBjYmYyMDgzNzQxYTM3OTE4OGE.} ERROR: db_mysql<br>
> > > [km_dbase.c:128]: db_mysql_submit_query(): driver error on query: Table<br>
> > > 'watchers' was not locked with LOCK TABLES (1100)<br>
> > > Jan 8 04:34:37 client1 /usr/local/sbin/kamailio[19527]: {1 1 SUBSCRIBE<br>
> > > MTlkYjI2NTBhYTFlMTBjYmYyMDgzNzQxYTM3OTE4OGE.} ERROR: <core><br>
> > > [db_query.c:132]: db_do_query_internal(): error while submitting query<br>
> > > Jan 8 04:34:37 client1 /usr/local/sbin/kamailio[19527]: {1 1 SUBSCRIBE<br>
> > > MTlkYjI2NTBhYTFlMTBjYmYyMDgzNzQxYTM3OTE4OGE.} ERROR: presence<br>
> > > [subscribe.c:2827]: get_db_subs_auth(): while querying watchers table<br>
> > > Jan 8 04:34:37 client1 /usr/local/sbin/kamailio[19527]: {1 1 SUBSCRIBE<br>
> > > MTlkYjI2NTBhYTFlMTBjYmYyMDgzNzQxYTM3OTE4OGE.} ERROR: presence<br>
> > > [subscribe.c:1217]: handle_subscribe(): getting subscription status from<br>
> > > watchers table<br>
> > > Jan 8 04:34:37 client1 /usr/local/sbin/kamailio[19523]: {1 1 PUBLISH<br>
> > > MzQ4ZjNhOGRlYWZlY2QyMDQwMGZlZjg0N2JlZGE2YWM.} ERROR: presence<br>
> > > [publish.c:499]: ki_handle_publish_uri(): when updating presentity<br>
> > > <br>
> > > I am currently using presence with memory only db_mode but for<br>
> > <br>
> > persistence<br>
> > <br>
> > > i would like to use db_only mode which is not possible due to this<br>
> > > issue.<br>
> > > Any idea or suggetions for this issue.<br>
> > <br>
> > Best regards,<br>
> > <br>
> > Henning<br>
<br>
<br>
-- <br>
Henning Westerholt - <a href="https://skalatan.de/blog/" rel="noreferrer" target="_blank">https://skalatan.de/blog/</a><br>
Kamailio services - <a href="https://skalatan.de/services" rel="noreferrer" target="_blank">https://skalatan.de/services</a><br>
Kamailio security assessment - <a href="https://skalatan.de/de/assessment" rel="noreferrer" target="_blank">https://skalatan.de/de/assessment</a><br>
</blockquote></div><br clear="all"><div><br></div>-- <br><div dir="ltr" class="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div><div><div><b>Thanks and Regards,<br></b></div><div><b>Ravindrakumar Bhatt<br></b></div><div dir="ltr"><div>Jr. Software Developer </div><div>Ecosmob Technologies Ltd</div><div>Ahmedabad</div><div>Mo:<b>+918460692402</b></div></div></div></div>
</div></div></div></div></div>