Hi everybody; I'm trying to use db_cluster to connect my kamailio installation to a mariadb galera cluster, and it's driving me crazy.
In my lab it works without issues, but when I try the setup in the production node, I get some strange errors:
Apr 11 14:21:31 ERROR: db_mysql [km_dbase.c:127]: db_mysql_submit_query(): driver error on query: Commands out of sync; you can't run this command now (2014) Apr 11 14:21:31 ERROR: <core> [db_query.c:132]: db_do_query_internal(): error while submitting query
This is my configuration:
modparam("db_cluster", "connection","con1=>mysql://openser:PASSWD@X.X.X.30/ openser") modparam("db_cluster", "connection","con2=>mysql://openser:PASSWD@X.X.X.31/ openser") modparam("db_cluster", "connection","con3=>mysql://openser:PASSWD@X.X.X.32/ openser") modparam("db_cluster", "cluster", "cls1=>con1=9r8r;con2=9r8r;con3=9r8r") #!define DBURL "cluster://cls1" #!define SQLOPSURL "database=>cluster://cls1"
modparam("dispatcher", "db_url", DBURL) modparam("acc", "db_url", DBURL) modparam("usrloc", "db_url", DBURL) modparam("auth_db", "db_url", DBURL) modparam("permissions", "db_url", DBURL) modparam("alias_db", "db_url", DBURL) modparam( "sqlops", "sqlcon", SQLOPSURL )
I'm using version 5.1.4 of kamailio, freshly compiled from source on a debian 9.
Any ideas?
*Simone Lazzaris* *Qcom S.p.A.* simone.lazzaris@qcom.it[1] | www.qcom.it[2] * LinkedIn[3]* | *Facebook[4]* [5]
-------- [1] mailto:simone.lazzaris@qcom.it [2] https://www.qcom.it [3] https://www.linkedin.com/company/qcom-spa [4] http://www.facebook.com/qcomspa [5] https://www.qcom.it/includes/email-banner.gif
In data giovedì 11 aprile 2019 14:29:22 CEST, Simone Lazzaris ha scritto:
Apr 11 14:21:31 ERROR: db_mysql [km_dbase.c:127]: db_mysql_submit_query(): driver error on query: Commands out of sync; you can't run this command now (2014) Apr 11 14:21:31 ERROR: <core> [db_query.c:132]: db_do_query_internal(): error while submitting query
Replying to myself:
I've found that the issue is in some stored procedures on the database that my code invokes.
To fix this, I have to replace this code
sql_query( "database", "CALL reg_start('$au','$ua','$si')", "result");
with the stored procedure content:
sql_query( "database", "SET @next_seq_id=0;SELECT (@next_seq_id:=((seq_id+1)%32)) AS next_seq_id FROM reg_history WHERE auth_username='$au' ORDER BY start DESC LIMIT 1;INSERT INTO reg_history SET auth_username='$au', seq_id=@next_seq_id, user_agent='$ua', remote_ip='$si' ON DUPLICATE KEY UPDATE start=NOW(), stop=0, user_agent='$ua', remote_ip='$si';" );
Hi Simone, Not really an answer to the db_cluster issue but I use MariaDB's Maxscale for managing connections to the a Galera cluster, maybe you can try that instead if you can't solve the problem.
On Thu, 11 Apr 2019, at 10:31 PM, Simone Lazzaris wrote:
Hi everybody;
I'm trying to use db_cluster to connect my kamailio installation to a mariadb galera cluster, and it's driving me crazy.
In my lab it works without issues, but when I try the setup in the production node, I get some strange errors:
Apr 11 14:21:31 ERROR: db_mysql [km_dbase.c:127]: db_mysql_submit_query(): driver error on query: Commands out of sync; you can't run this command now (2014)
Apr 11 14:21:31 ERROR: <core> [db_query.c:132]: db_do_query_internal(): error while submitting query
This is my configuration:
modparam("db_cluster", "connection","con1=>mysql://openser:PASSWD@X.X.X.30/openser")
modparam("db_cluster", "connection","con2=>mysql://openser:PASSWD@X.X.X.31/openser")
modparam("db_cluster", "connection","con3=>mysql://openser:PASSWD@X.X.X.32/openser")
modparam("db_cluster", "cluster", "cls1=>con1=9r8r;con2=9r8r;con3=9r8r")
#!define DBURL "cluster://cls1"
#!define SQLOPSURL "database=>cluster://cls1"
modparam("dispatcher", "db_url", DBURL)
modparam("acc", "db_url", DBURL)
modparam("usrloc", "db_url", DBURL)
modparam("auth_db", "db_url", DBURL)
modparam("permissions", "db_url", DBURL)
modparam("alias_db", "db_url", DBURL)
modparam( "sqlops", "sqlcon", SQLOPSURL )
I'm using version 5.1.4 of kamailio, freshly compiled from source on a debian 9.
Any ideas?
--
Simone Lazzaris Responsabile datacenter
Qcom S.p.A. Via Roggia Vignola, 9 | 24047 Treviglio (BG) T +39036347905 | D +3903631970352| M +393938111237 _simone.lazzaris@qcom.it_ | _www.qcom.it_
Qcom Official Pages _LinkedIn_ https://www.linkedin.com/company/qcom-spa | _Facebook_ http://www.facebook.com/qcomspa
Kamailio (SER) - Users Mailing List sr-users@lists.kamailio.org https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
-
Boden Garman -
Simone Lazzaris