[sr-dev] [kamailio/kamailio] kamailio mariadb error lock table active_watcher (#2805)

[ReznikovAlexei]

### Description

I am using Kamailio with Mariadb module
And i am get in Kamailio log some error messages:
```
"ERROR: db_mysql [km_dbase.c:122]: db_mysql_submit_query(): driver error on query: Table 'active_watchers' was not locked with LOCK TABLES (1100)"
"ERROR: db_mysql [km_dbase.c:122]: db_mysql_submit_query(): driver error on query: Table 'active_watchers' was not locked with LOCK TABLES (1100)"
"ERROR: db_mysql [km_dbase.c:122]: db_mysql_submit_query(): driver error on query: Table 'active_watchers' was not locked with LOCK TABLES (1100)"
```
After start debug log queries in Mariadb, i see queries in logfile like this: 
```
1141 Query     SET autocommit=0
1141 Query     LOCK TABLES presentity WRITE
1141 Query     update `presentity` set `etag`='a.1620236063.17716.2514.101',`expires`=1625215189,`received_time`=1625214589,`priority`=205144189,`body`='<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<presence xmlns:dm=\"urn:ietf:params:xml:ns:pidf:data-model\" xmlns:rpid=\"urn:ietf:params:xml:ns:pidf:rpid\" xmlns:pidfonline=\"http://www.linphone.org/xsds/pidfonline.xsd\" entity=\"sip:name_user at office-dev.com\" xmlns=\"urn:ietf:params:xml:ns:pidf\">\n <tuple id=\"bephbl\">\n  <status>\n   <basic>open</basic>\n   <pidfonline:online/>\n  </status>\n  <contact priority=\"0.8\">sip:name_user at office-dev.com</contact>\n  <timestamp>2021-07-01T17:20:41Z</timestamp>\n </tuple>\n</presence>\n' where `domain`='office-dev.com' AND `username`='name_user' AND `event`='presence' AND `etag`='a.1620236063.17716.2513.100'
1141 Query     select `to_user`,`to_domain`,`from_user`,`from_domain`,`watcher_username`,`watcher_domain`,`event_id`,`from_tag`,`to_tag`,`callid`,`local_cseq`,`record_route`,`contact`,`expires`,`reason`,`socket_info`,`local_contact`,`version`,`flags`,`user_agent` from `active_watchers` where `presentity_uri`='sip:name_user at office-dev.com' AND `event`='presence' AND `status`=1 AND `contact`<>''
1141 Query     COMMIT
1141 Query     SET autocommit=1
1141 Query     UNLOCK TABLES
```
or this one
```
1141 Query     LOCK TABLES presentity WRITE
1141 Query     insert into `presentity` (`domain`,`username`,`event`,`etag`,`sender`,`body`,`received_time`,`priority`,`expires` ) values ('dev.com','+121342','presence','a.1620236063.17716.2532.0','','<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<presence xmlns:dm=\"urn:ietf:params:xml:ns:pidf:data-model\" xmlns:rpid=\"urn:ietf:params:xml:ns:pidf:rpid\" xmlns:pidfonline=\"http://www.linphone.org/xsds/pidfonline.xsd\" entity=\"sip:+121342 at dev.com\" xmlns=\"urn:ietf:params:xml:ns:pidf\">\n <tuple id=\"wumhh7\">\n  <status>\n   <basic>open</basic>\n   <pidfonline:online/>\n  </status>\n  <contact priority=\"0.8\">sip:+121342 at dev.com</contact>\n  <timestamp>2021-07-02T10:55:26Z</timestamp>\n </tuple>\n</presence>\n',1625223329,205152929,1625226929)
1141 Query     select `to_user`,`to_domain`,`from_user`,`from_domain`,`watcher_username`,`watcher_domain`,`event_id`,`from_tag`,`to_tag`,`callid`,`local_cseq`,`record_route`,`contact`,`expires`,`reason`,`socket_info`,`local_contact`,`version`,`flags`,`user_agent` from `active_watchers` where `presentity_uri`='sip:+121342 at dev.com' AND `event`='presence' AND `status`=1 AND `contact`<>''
1141 Query     COMMIT
1141 Query     SET autocommit=1
1141 Query     UNLOCK TABLES
```

This queries matches for time with reproduced errors in above kamailio log.

#### Reproduction
In test lab
```
MariaDB [kamailio]> LOCK TABLES presentity WRITE;
Query OK, 0 rows affected (0.00 sec)

MariaDB [kamailio]> select * from presentity;
Empty set (0.00 sec)

MariaDB [kamailio]> select * from active_watcher;
ERROR 1100 (HY000): Table 'active_watcher' was not locked with LOCK TABLES
MariaDB [kamailio]> 
```
I get same error

Also i am found in additional documentation from Mysql next: 
```
A session that requires locks must acquire all the locks that it needs in a single LOCK TABLES statement. While the locks thus obtained are held, the session can access only the locked tables. 
```
or from Mariadb docs

```
While a connection holds an explicit lock on a table, it cannot access a non-locked table. If you try, the following error will be produced: 

ERROR 1100 (HY000): Table 'tab_name' was not locked with LOCK TABLES

```
### Additional Information

  * **Kamailio Version** * 

```
Kamailio ver 5.4.5 or 5.6.0
```

So, maybe need add additional lock for table active_watchers or need take out query select from space LOCK - UNLOCK for resolve this issue? 


-- 
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/2805
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.kamailio.org/pipermail/sr-dev/attachments/20210723/bfdad176/attachment.htm>