26 Sep
2013
26 Sep
'13
10:31 a.m.
after upgrading my presence server to latest master, i started to get
continuous rls related db_mysql error messages like these
Sep 26 10:27:56 siika /usr/sbin/pres-serv[578]: ERROR: db_mysql [km_dbase.c:122]:
db_mysql_submit_query(): driver error on query: Lost connection to MySQL server during
query
Sep 26 10:27:56 siika /usr/sbin/pres-serv[578]: ERROR: <core> [db_query.c:181]:
db_do_raw_query(): error while submitting query
Sep 26 10:27:56 siika /usr/sbin/pres-serv[578]: ERROR: db_mysql [km_dbase.c:557]:
db_mysql_start_transaction(): executing raw_query
Sep 26 10:27:56 siika /usr/sbin/pres-serv[578]: ERROR: rls [resource_notify.c:960]:
timer_send_full_state_notifies(): in start_transaction
i have not made any changes to my config file where i have
modparam("rls", "db_mode", 2)
-- juha
26 Sep
26 Sep
12:05 p.m.
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
12:10 p.m.
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(a)lists.sip-router.org
http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
--
Daniel-Constantin Mierla - http://www.asipto.com
http://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda
Kamailio Advanced Trainings - Berlin, Oct 21-24; Miami, Nov 11-13, 2013
- more details about Kamailio trainings at http://www.asipto.com -
12:16 p.m.
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
12:25 p.m.
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(a)tutpro.com> wrote:
eDaniel-Constantin Mierla writes:
--
Peter Dunkley
Technical Director
Crocodile RCS Ltd
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(a)lists.sip-router.org
http://lists.sip-router.org/cgi-bin/mailman/listinfo/sr-dev
1:03 p.m.
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=fe977d…
i have not yet tried to generate debian package after the commit.
-- juha
1:23 p.m.
On 9/26/13 12:03 PM, Juha Heinanen wrote:
Peter Dunkley writes:
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
--
Daniel-Constantin Mierla - http://www.asipto.com
http://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda
Kamailio Advanced Trainings - Berlin, Nov 25-28; Miami, Nov 18-20, 2013
- more details about Kamailio trainings at http://www.asipto.com -
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=fe977d…
i have not yet tried to generate debian package after the commit.
1:43 p.m.
Daniel-Constantin Mierla writes:
yes, this commit caused the errors. i verified by generating debian
package from april 3 master and started to get the errors.
-- juha
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'.
4:22 p.m.
On 9/26/13 12:43 PM, Juha Heinanen wrote:
Daniel-Constantin Mierla writes:
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
--
Daniel-Constantin Mierla - http://www.asipto.com
http://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda
Kamailio Advanced Trainings - Berlin, Nov 25-28; Miami, Nov 18-20, 2013
- more details about Kamailio trainings at http://www.asipto.com -
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'.
5:02 p.m.
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.
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.
-- juha
5:42 p.m.
On 9/26/13 4:02 PM, Juha Heinanen wrote:
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
--
Daniel-Constantin Mierla - http://www.asipto.com
http://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda
Kamailio Advanced Trainings - Berlin, Nov 25-28; Miami, Nov 18-20, 2013
- more details about Kamailio trainings at http://www.asipto.com -
Daniel-Constantin Mierla writes:
ok.
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. 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.
6:05 p.m.
Daniel-Constantin Mierla writes:
"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.
yes. does not sound like a good idea to lock tables between START
TRANSACTION and COMMIT or ROLLBACK.
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.
it seems that tables cannot be locked before calling START TRANSACTION
either because of this text:
If a session begins a transaction (for example, with START TRANSACTION),
an implicit UNLOCK TABLES is performed, which causes existing locks to
be released.
that leads to the conclusion that transactions and table locking cannot
be used together in mysql.
-- juha
6:11 p.m.
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
9:59 p.m.
daniel, olle,
what is the conclusion regarding mysql transaction/table locking support
in db_mysqbl? is the current implementation correct?
-- juha
27 Sep
27 Sep
12:55 p.m.
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
--
Daniel-Constantin Mierla - http://www.asipto.com
http://twitter.com/#!/miconda - http://www.linkedin.com/in/miconda
Kamailio Advanced Trainings - Berlin, Nov 25-28; Miami, Nov 18-20, 2013
- more details about Kamailio trainings at http://www.asipto.com -
3:12 p.m.
Daniel-Constantin Mierla writes:
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.
COMMIT does not affect autocommit value:
mysql> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> LOCK TABLES htable WRITE, dialplan READ;
Query OK, 0 rows affected (0.02 sec)
mysql> update htable set id=50 where id=5;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.01 sec)
it thus has to be set back to 1 explicitly after COMMIT.
-- juha
4075
days inactive
4076
days old
15 comments
3 participants
participants (3)
-
Daniel-Constantin Mierla -
Juha Heinanen -
Peter Dunkley