Do you still know what was previous version/last commit you run and was ok? Maybe the issue can be spotted in commit logs. Lately I haven't worked on presence, maybe Peter or Hugh have more clues.

Cheers, Daniel

On 9/26/13 11:05 AM, Juha Heinanen wrote:

i restarted presence server and it started fine at

Sep 26 11:57:30 siika /usr/sbin/pres-serv[2671]: INFO: ctl [io_listener.c:225]: io_listen_loop(): io_listen_loop: using epoll_lt io watch method (config)

mysql related errors started to appear 1 min 40 seconds later and the first was this:

Sep 26 11:59:10 siika /usr/sbin/pres-serv[2669]: ERROR: db_mysql [km_dbase.c:122]: db_mysql_submit_query(): driver error on query: Table 'rls_presentity' was not locked with LOCK TABLES Sep 26 11:59:10 siika /usr/sbin/pres-serv[2669]: ERROR: <core> [db_query.c:291]: db_do_delete(): error while submitting query Sep 26 11:59:10 siika /usr/sbin/pres-serv[2669]: ERROR: rls [resource_notify.c:1193]: rls_presentity_clean(): in sql delete

after that mysql errors started to repeat themselves at regular intervals:

Sep 26 11:59:14 siika /usr/sbin/pres-serv[2672]: ERROR: db_mysql [km_dbase.c:122]: db_mysql_submit_query(): driver error on query: Lost connection to MySQL server during query Sep 26 11:59:14 siika /usr/sbin/pres-serv[2672]: ERROR: <core> [db_query.c:181]: db_do_raw_query(): error while submitting query Sep 26 11:59:14 siika /usr/sbin/pres-serv[2672]: ERROR: db_mysql [km_dbase.c:557]: db_mysql_start_transaction(): executing raw_query Sep 26 11:59:14 siika /usr/sbin/pres-serv[2672]: ERROR: rls [resource_notify.c:960]: timer_send_full_state_notifies(): in start_transaction Sep 26 11:59:18 siika /usr/sbin/pres-serv[2672]: ERROR: db_mysql [km_dbase.c:122]: db_mysql_submit_query(): driver error on query: Lost connection to MySQL server during query Sep 26 11:59:18 siika /usr/sbin/pres-serv[2672]: ERROR: <core> [db_query.c:181]: db_do_raw_query(): error while submitting query Sep 26 11:59:18 siika /usr/sbin/pres-serv[2672]: ERROR: db_mysql [km_dbase.c:557]: db_mysql_start_transaction(): executing raw_query Sep 26 11:59:18 siika /usr/sbin/pres-serv[2672]: ERROR: rls [resource_notify.c:960]: timer_send_full_state_notifies(): in start_transaction ...

-- juha

---

sr-dev mailing list sr-dev@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev

I don't know of any changes to RLS (certainly none by me, and I think none by Hugh) for several months.

We have only ever used it with PostgreSQL here. I do know some work was done to the MySQL DB driver some months back to support transactions, etc (needed for separate notifier processes in presence). Has anybody tested those changes with RLS before, or were they just tested with presence?

On 26 September 2013 10:16, Juha Heinanen jh@tutpro.com wrote:

eDaniel-Constantin Mierla writes:

...

Do you still know what was previous version/last commit you run and was ok? Maybe the issue can be spotted in commit logs. Lately I haven't worked on presence, maybe Peter or Hugh have more clues.

i searched, but unfortunately i didn't have the previous debian package anymore available.

-- juha

---

sr-dev mailing list sr-dev@lists.sip-router.org http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev

On 9/26/13 12:03 PM, Juha Heinanen wrote:

Peter Dunkley writes:

...

We have only ever used it with PostgreSQL here. I do know some work was done to the MySQL DB driver some months back to support transactions, etc (needed for separate notifier processes in presence). Has anybody tested those changes with RLS before, or were they just tested with presence?

i generated new debian package from master of april 1, 2013 and it worked fine. next day there was big commit on db_mysql module by olle:

http://git.sip-router.org/cgi-bin/gitweb.cgi?p=sip-router;a=commit;h=fe977df...

i have not yet tried to generate debian package after the commit.

Looks like this commit is the reason -- I see 'lock tables' statement but no 'unlock tables'.

Docs for mysql shows some examples where this case can occur:

- http://dev.mysql.com/doc/refman/5.6/en/lock-tables.html

and I guess it is the case here when a table was locked and a new query is done on a different table.

I can look deeper at it later if no one else is getting to it before.

Cheers, Daniel

On 9/26/13 12:43 PM, Juha Heinanen wrote:

Daniel-Constantin Mierla writes:

...

...

i have not yet tried to generate debian package after the commit.

Looks like this commit is the reason -- I see 'lock tables' statement but no 'unlock tables'.

yes, this commit caused the errors. i verified by generating debian package from april 3 master and started to get the errors.

I pushed a commit trying to fix it -- can you test it? I don't have an environment for it, as these functions are used only in some cases from presence modules. I coded it looking at mysql docs.

Provided that, it seems transactions and locking tables are not working together in mysql: -http://dev.mysql.com/doc/refman/5.6/en/lock-tables-and-transactions.html

BEGIN is an alias to START TRANSACTION according to:

-http://dev.mysql.com/doc/refman/5.6/en/commit.html

Apparently, set autocommit=0 should be used instead of START TRANSACTION, but then is not clear if we have to set autocommit back to one after COMMIT or ROLLBACK.

Can anyone read the details provided at the links above and share his/her understanding?

Cheers, Daniel

On 9/26/13 4:02 PM, Juha Heinanen wrote:

Daniel-Constantin Mierla writes:

...

I pushed a commit trying to fix it -- can you test it? I don't have an environment for it, as these functions are used only in some cases from presence modules. I coded it looking at mysql docs.

i build new debian package and don't anymore get the errors.

ok.

...

Provided that, it seems transactions and locking tables are not working together in mysql: -http://dev.mysql.com/doc/refman/5.6/en/lock-tables-and-transactions.html

BEGIN is an alias to START TRANSACTION according to:

-http://dev.mysql.com/doc/refman/5.6/en/commit.html

Apparently, set autocommit=0 should be used instead of START TRANSACTION, but then is not clear if we have to set autocommit back to one after COMMIT or ROLLBACK.

my understanding is that there is no need to set autocommit=0 if START TRANSACTION is used to start transaction, because START TRANSACTION implicitly does that and autocommit=0 stays in effect until COMMIT or ROLLBACK is executed.

The problem is ending the transaction with locak tables.

Quoting from mysql site - http://dev.mysql.com/doc/refman/5.6/en/lock-tables-and-transactions.html:

"LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables. " That seems to say, if you start transaction then do lock tables, the transaction is committed.

In the code I saw that transaction start function can lock tables based on a parameter, but after start transaction -- which on the light of above statements mean closing the just opened transaction. Then I assume several queries are done from the module, which are supposed to be part of a transaction, but it might not exist anymore if I understand correctly the documentation.

Cheers, Daniel

Juha Heinanen writes:

that leads to the conclusion that transactions and table locking cannot be used together in mysql.

unless SET autocommit = 0 is done instead of START TRANSACTION:

The correct way to use LOCK TABLES and UNLOCK TABLES with transactional tables, such as InnoDB tables, is to begin a transaction with SET autocommit = 0 (not START TRANSACTION) followed by LOCK TABLES, and to not call UNLOCK TABLES until you commit the transaction explicitly. For example, if you need to write to table t1 and read from table t2, you can do this:

SET autocommit=0; LOCK TABLES t1 WRITE, t2 READ, ...; ... do something with tables t1 and t2 here ... COMMIT; UNLOCK TABLES;

i don't know that that implies regarding db_mysql module.

-- juha

Hello,

On 9/26/13 8:59 PM, Juha Heinanen wrote:

daniel, olle,

what is the conclusion regarding mysql transaction/table locking support in db_mysqbl? is the current implementation correct?

as I said, it doesn't look for me as properly working, due to the info I found in docs. But I am not the initial author, so getting a comment from there would be good.

Then, my open issue with the alternative of using autocommit=0 is whether it has to be set back to 1 after COMMIT, or COMMIT is making it 1 implicitly.

Cheers, Daniel