Am Mittwoch, 9. Januar 2019, 09:47:07 CET schrieb Ravindrakumar Bhatt:
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
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:
Thanks Daniel for your suggestion but i am using default database created by kamdbctl create so database engine is surely InnoDB. 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. 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.
LOGS: 2019-01-10T06:51:07.356897Z 67 Query SET autocommit=0 2019-01-10T06:51:07.357157Z 67 Query LOCK TABLES active_watchers WRITE 2019-01-10T06:51:07.357461Z 67 Query select `presentity_uri`,`callid`,`to_tag`,`from_tag`,`event` from `active_watchers` where `updated`=15 AND `event`<>'presence.winfo' 2019-01-10T06:51:07.357796Z 67 Query ROLLBACK 2019-01-10T06:51:07.357916Z 67 Query SET autocommit=1 2019-01-10T06:51:07.358047Z 67 Query UNLOCK TABLES 2019-01-10T06:51:07.358162Z 67 Query SET autocommit=0 2019-01-10T06:51:07.358261Z 67 Query LOCK TABLES active_watchers WRITE 2019-01-10T06:51:07.358470Z 67 Query select `presentity_uri`,`callid`,`to_tag`,`from_tag`,`event` from `active_watchers` where `updated`=15 AND `event`='presence.winfo' 2019-01-10T06:51:07.358689Z 67 Query ROLLBACK 2019-01-10T06:51:07.359066Z 67 Query SET autocommit=1 2019-01-10T06:51:07.359191Z 67 Query UNLOCK TABLES ########################### NO locking of tables for this two queires ################################# 2019-01-10T06:51:07.390846Z 61 Query select `username`,`domain`,`etag`,`event` from `presentity` where `expires`<1547103067 AND `expires`>0 order by username 2019-01-10T06:51:07.391516Z 61 Query delete from `watchers` where `inserted_time`<1547016667 AND `status`=2 ####################################################################################### 2019-01-10T06:51:07.391779Z 61 Query SET autocommit=0 2019-01-10T06:51:07.391930Z 61 Query LOCK TABLES active_watchers WRITE ##### LOCKS table active watcher ???? 2019-01-10T06:51:07.392276Z 61 Query select `callid`,`to_tag`,`from_tag` from `active_watchers` where `expires`<1547103067 AND `updated`=-1 2019-01-10T06:51:07.392617Z 61 Query COMMIT 2019-01-10T06:51:07.392790Z 61 Query SET autocommit=1 2019-01-10T06:51:07.392936Z 61 Query UNLOCK TABLES 2019-01-10T06:51:07.459480Z 67 Query SET autocommit=0 2019-01-10T06:51:07.459651Z 67 Query LOCK TABLES active_watchers WRITE 2019-01-10T06:51:07.459926Z 67 Query select `presentity_uri`,`callid`,`to_tag`,`from_tag`,`event` from `active_watchers` where `updated`=16 AND `event`<>'presence.winfo' 2019-01-10T06:51:07.460225Z 67 Query ROLLBACK 2019-01-10T06:51:07.460322Z 67 Query SET autocommit=1 2019-01-10T06:51:07.460445Z 67 Query UNLOCK TABLES 2019-01-10T06:51:03.369484Z 53 Query SET autocommit=0 2019-01-10T06:51:03.369760Z 53 Query LOCK TABLES active_watchers WRITE ##### LOCKS table active watcher ???? 2019-01-10T06:51:03.369962Z 67 Query SET autocommit=0 2019-01-10T06:51:03.370104Z 67 Query LOCK TABLES active_watchers WRITE 2019-01-10T06:51:03.371851Z 53 Query select `status`,`reason` from `watchers` where `presentity_uri`='sip:4040@172.16.16.149' AND `watcher_username`='3030' AND `watcher_domain`='172.16.16.149' AND `event`='presence' 2019-01-10T06:51:03.376684Z 53 Query ROLLBACK 2019-01-10T06:51:03.376836Z 53 Query SET autocommit=1 2019-01-10T06:51:03.377321Z 53 Query UNLOCK TABLES 2019-01-10T06:51:03.379047Z 67 Query select `presentity_uri`,`callid`,`to_tag`,`from_tag`,`event` from `active_watchers` where `updated`=26 AND `event`<>'presence.winfo' 2019-01-10T06:51:03.379571Z 67 Query ROLLBACK 2019-01-10T06:51:03.380294Z 67 Query SET autocommit=1 2019-01-10T06:51:03.383371Z 67 Query UNLOCK TABLES
On Thu, Jan 10, 2019 at 2:16 AM Henning Westerholt hw@kamailio.org wrote:
Am Donnerstag, 10. Januar 2019, 11:01:27 CET schrieb Ravindrakumar Bhatt:
Hello Ravindrakumar,
I noticed that you've set db_subst_mode=0 in the presence module configuration. This should disable the database storage normally. Have you tried to use the default mode 2?
Another thing, if the locking is somehow not working for you, you can disable it with the following parameter:
3.27. db_table_lock_type (integer)
Enable (=1) or disable (=0) the Locks for table during an transaction. Locking only the "current" table causes problems with a MySQL-Databases in "DB-Only" mode.
In order to use the Presence-Module in "DB_ONLY"-mode with a MySQL-Backend, set this parameter to "0", otherwise the MySQL-Operations will fail. The Presence-Module will generate a "500 Server error" due to the failed MySQL-queries.
Default value is 1 (Write Lock for the Tables).
Example 1.27. Set db_table_lock_type parameter ... modparam("presence", "db_table_lock_type", 0) ...
Best regards,
Henning
-
Daniel-Constantin Mierla -
Henning Westerholt -
Ravindrakumar Bhatt