SQLOPS sql_query_async() not functioning in an async manner?

List overview All Threads
Download

newer

older

Dispatcher Load Balance with SIP...

kamailio :?==?utf-8?q? ROUTE[AUTH]...

Ben Kaufman

23 Feb 2022 23 Feb '22

1:11 a.m.

I'm not sure if this is by design or not. I have things I want to log to a SQL DB, but it's not nearly as important as call handling, so the sql_query_async() function looked ideal, but in testing, if the DB is not responsive, the call processing gets blocked. In the example below, I'm using just a short hostname which isn't resolvable, but if I have an IP address there that isn't really assigned (for example if the DB server is down), the same blocking behavior is exhibited, but the error is slightly different (can't connect instead of unknown host). Here's my config. This specific simple config was tested on Kamailio 5.5, but I observe similar behavior (in a more complicated config) in the dev branch as well. - - - - #!KAMAILIO async_workers=2 loadmodule "db_mysql" loadmodule "sqlops" loadmodule "sl" loadmodule "xlog" loadmodule "pv" force_rport=yes ## Host sql does not resolve. modparam("sqlops","sqlcon","cb=>mysql://kamailio:kamailiorw@sql/kamailio") ## But start anyways. modparam("sqlops", "connect_mode", 1) request_route { sl_send_reply("100", "Trying"); ## Prevent re-transmissions from the UAC xlog("L_NOTICE", "Time before sql_query_async(): [$TV(Sn)]\n"); sql_query_async("cb", "INSERT INTO table1 (col) VALUES ('val')"); xlog("L_NOTICE", "Time before sl_send_reply(): [$TV(Sn)]\n"); sl_send_reply("404", "Not found"); } Here's the log if I send a simple INVITE with SIPp. Note the timestamps 9(16) NOTICE: <script>: Time before sql_query_async(): [1645574694.546165] 9(16) ERROR: db_mysql [km_my_con.c:163]: db_mysql_new_connection(): driver error: Unknown MySQL server host 'sql' (-3) 9(16) ERROR: <core> [db.c:319]: db_do_init2(): could not add connection to the pool 9(16) ERROR: sqlops [sql_api.c:190]: sql_reconnect(): failed to connect to the database [cb] 9(16) ERROR: sqlops [sqlops.c:232]: sql_check_connection(): failed to connect to database 9(16) ERROR: sqlops [sqlops.c:264]: sql_query_async(): invalid connection to database 9(16) NOTICE: <script>: Time before sl_send_reply(): [1645574699.552440] Ben Kaufman Sr. VoIP Engineer - BCM One +1.612.735.9309 bkaufman@bcmone.com<mailto:bkaufman@bcmone.com> 24 hour client support: +1.855.639.6300 [https://www.bcmone.com/files/bcmone-logo.png]<https://www.bcmone.com/> [https://www.bcmone.com/files/twitter-icon-circle.png] <https://www.facebook.com/bcmonenyc/> [https://www.bcmone.com/files/twitter-icon-circle.png] <https://twitter.com/bcm_one/> [https://www.bcmone.com/files/linkedin-icon-circle.png] <https://www.linkedin.com/company/business-communications-management/> NOTE: This e-mail and any attachments are from BCM One, Inc. and are intended solely for the use and review of the individual(s) to whom it is addressed. If you believe you received this e-mail in error, please notify the sender immediately, delete the e-mail (and any attachments) from your computer and do not copy or disclose it to anyone else.

Attachments:

attachment.html (text/html — 10.4 KB)

Show replies by date

Alex Balashov

23 Feb 23 Feb

3:06 a.m.

Is there a difference in how it behaves when: a) A database that was previously reachable becomes unreachable or unresponsive? vs. b) A db_url that is not reachable in principle? — Alex

...

On Feb 22, 2022, at 7:11 PM, Ben Kaufman <bkaufman(a)bcmone.com> wrote: I’m not sure if this is by design or not. I have things I want to log to a SQL DB, but it’s not nearly as important as call handling, so the sql_query_async() function looked ideal, but in testing, if the DB is not responsive, the call processing gets blocked. In the example below, I’m using just a short hostname which isn’t resolvable, but if I have an IP address there that isn’t really assigned (for example if the DB server is down), the same blocking behavior is exhibited, but the error is slightly different (can’t connect instead of unknown host). Here’s my config. This specific simple config was tested on Kamailio 5.5, but I observe similar behavior (in a more complicated config) in the dev branch as well. - - - - #!KAMAILIO async_workers=2 loadmodule "db_mysql" loadmodule "sqlops" loadmodule "sl" loadmodule "xlog" loadmodule "pv" force_rport=yes ## Host sql does not resolve. modparam("sqlops","sqlcon","cb=>mysql://kamailio:kamailiorw@sql/kamailio") ## But start anyways. modparam("sqlops", "connect_mode", 1) request_route { sl_send_reply("100", "Trying"); ## Prevent re-transmissions from the UAC xlog("L_NOTICE", "Time before sql_query_async(): [$TV(Sn)]\n"); sql_query_async("cb", "INSERT INTO table1 (col) VALUES ('val')"); xlog("L_NOTICE", "Time before sl_send_reply(): [$TV(Sn)]\n"); sl_send_reply("404", "Not found"); } Here’s the log if I send a simple INVITE with SIPp. Note the timestamps 9(16) NOTICE: <script>: Time before sql_query_async(): [1645574694.546165] 9(16) ERROR: db_mysql [km_my_con.c:163]: db_mysql_new_connection(): driver error: Unknown MySQL server host 'sql' (-3) 9(16) ERROR: <core> [db.c:319]: db_do_init2(): could not add connection to the pool 9(16) ERROR: sqlops [sql_api.c:190]: sql_reconnect(): failed to connect to the database [cb] 9(16) ERROR: sqlops [sqlops.c:232]: sql_check_connection(): failed to connect to database 9(16) ERROR: sqlops [sqlops.c:264]: sql_query_async(): invalid connection to database 9(16) NOTICE: <script>: Time before sl_send_reply(): [1645574699.552440] Ben Kaufman Sr. VoIP Engineer - BCM One +1.612.735.9309 bkaufman(a)bcmone.com 24 hour client support: +1.855.639.6300 NOTE: This e-mail and any attachments are from BCM One, Inc. and are intended solely for the use and review of the individual(s) to whom it is addressed. If you believe you received this e-mail in error, please notify the sender immediately, delete the e-mail (and any attachments) from your computer and do not copy or disclose it to anyone else. __________________________________________________________ Kamailio - Users Mailing List - Non Commercial Discussions * sr-users(a)lists.kamailio.org Important: keep the mailing list in the recipients, do not reply only to the sender! Edit mailing list options or unsubscribe: * https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users

-- Alex Balashov | Principal | Evariste Systems LLC Tel: +1-706-510-6800 / +1-800-250-5920 (toll-free) Web: http://www.evaristesys.com/, http://www.csrpswitch.com/

Daniel-Constantin Mierla

8:35 a.m.

Hello, the current async query API requires that the connection is established, because it uses the database handle pointer as one of the parameter. That means the process initiating sql_query_async() attempts to connect if the connection is not on, and can get stuck for a while if the database server is not available. Probably can be refactored to do connect in the async task worker, but it's a change that has to be propagated to other modules like db_mysql, db_postgres and any other module using the async query db api function. Not expecting to be complex coding, but requiring time to do it. While I added the async query db api function, it was at the time when Kamailio would fail to run if connection could not be initialized at the start up. Later someone else added sqlops connect_mode, so the connect is delayed, no longer done at the start up if its value is 1, but it is done by the process running the async query function. In other words, only the query itself is executed by async task process. The connection must be active in the other process. At this moment, an alternative is to use mqueue+rtimer, to pass the query from sip worker process to rtimer process, which then executes the query. Cheers, Daniel On 23.02.22 03:06, Alex Balashov wrote:

...

Is there a difference in how it behaves when: a) A database that was previously reachable becomes unreachable or unresponsive? vs. b) A db_url that is not reachable in principle? — Alex

-- Alex Balashov | Principal | Evariste Systems LLC Tel: +1-706-510-6800 / +1-800-250-5920 (toll-free) Web: http://www.evaristesys.com/, http://www.csrpswitch.com/ __________________________________________________________ Kamailio - Users Mailing List - Non Commercial Discussions * sr-users(a)lists.kamailio.org Important: keep the mailing list in the recipients, do not reply only to the sender! Edit mailing list options or unsubscribe: * https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users

Henning Westerholt

10:45 a.m.

Hello, just a quick addition - you can try to play with timeout to see if you can make it faster fail on connection error: https://www.kamailio.org/docs/modules/devel/modules/db_mysql.html#idm61 Cheers, Henning -- Henning Westerholt – https://skalatan.de/blog/ Kamailio services – https://gilawa.com -----Original Message----- From: sr-users <sr-users-bounces(a)lists.kamailio.org> On Behalf Of Daniel-Constantin Mierla Sent: Wednesday, February 23, 2022 8:36 AM To: Kamailio (SER) - Users Mailing List <sr-users(a)lists.kamailio.org> Subject: Re: [SR-Users] SQLOPS sql_query_async() not functioning in an async manner? Hello, the current async query API requires that the connection is established, because it uses the database handle pointer as one of the parameter. That means the process initiating sql_query_async() attempts to connect if the connection is not on, and can get stuck for a while if the database server is not available. Probably can be refactored to do connect in the async task worker, but it's a change that has to be propagated to other modules like db_mysql, db_postgres and any other module using the async query db api function. Not expecting to be complex coding, but requiring time to do it. While I added the async query db api function, it was at the time when Kamailio would fail to run if connection could not be initialized at the start up. Later someone else added sqlops connect_mode, so the connect is delayed, no longer done at the start up if its value is 1, but it is done by the process running the async query function. In other words, only the query itself is executed by async task process. The connection must be active in the other process. At this moment, an alternative is to use mqueue+rtimer, to pass the query from sip worker process to rtimer process, which then executes the query. Cheers, Daniel On 23.02.22 03:06, Alex Balashov wrote:

...

Is there a difference in how it behaves when: a) A database that was previously reachable becomes unreachable or unresponsive? vs. b) A db_url that is not reachable in principle? — Alex

On Feb 22, 2022, at 7:11 PM, Ben Kaufman <bkaufman(a)bcmone.com> wrote: I’m not sure if this is by design or not. I have things I want to log to a SQL DB, but it’s not nearly as important as call handling, so the sql_query_async() function looked ideal, but in testing, if the DB is not responsive, the call processing gets blocked. In the example below, I’m using just a short hostname which isn’t resolvable, but if I have an IP address there that isn’t really assigned (for example if the DB server is down), the same blocking behavior is exhibited, but the error is slightly different (can’t connect instead of unknown host). Here’s my config. This specific simple config was tested on Kamailio 5.5, but I observe similar behavior (in a more complicated config) in the dev branch as well. - - - - #!KAMAILIO async_workers=2 loadmodule "db_mysql" loadmodule "sqlops" loadmodule "sl" loadmodule "xlog" loadmodule "pv" force_rport=yes ## Host sql does not resolve. modparam("sqlops","sqlcon","cb=>mysql://kamailio:kamailiorw@sql/kamai lio") ## But start anyways. modparam("sqlops", "connect_mode", 1) request_route { sl_send_reply("100", "Trying"); ## Prevent re-transmissions from the UAC xlog("L_NOTICE", "Time before sql_query_async(): [$TV(Sn)]\n"); sql_query_async("cb", "INSERT INTO table1 (col) VALUES ('val')"); xlog("L_NOTICE", "Time before sl_send_reply(): [$TV(Sn)]\n"); sl_send_reply("404", "Not found"); } Here’s the log if I send a simple INVITE with SIPp. Note the timestamps 9(16) NOTICE: <script>: Time before sql_query_async(): [1645574694.546165] 9(16) ERROR: db_mysql [km_my_con.c:163]: db_mysql_new_connection(): driver error: Unknown MySQL server host 'sql' (-3) 9(16) ERROR: <core> [db.c:319]: db_do_init2(): could not add connection to the pool 9(16) ERROR: sqlops [sql_api.c:190]: sql_reconnect(): failed to connect to the database [cb] 9(16) ERROR: sqlops [sqlops.c:232]: sql_check_connection(): failed to connect to database 9(16) ERROR: sqlops [sqlops.c:264]: sql_query_async(): invalid connection to database 9(16) NOTICE: <script>: Time before sl_send_reply(): [1645574699.552440] Ben Kaufman Sr. VoIP Engineer - BCM One +1.612.735.9309 bkaufman(a)bcmone.com 24 hour client support: +1.855.639.6300 NOTE: This e-mail and any attachments are from BCM One, Inc. and are intended solely for the use and review of the individual(s) to whom it is addressed. If you believe you received this e-mail in error, please notify the sender immediately, delete the e-mail (and any attachments) from your computer and do not copy or disclose it to anyone else. __________________________________________________________ Kamailio - Users Mailing List - Non Commercial Discussions * sr-users(a)lists.kamailio.org Important: keep the mailing list in the recipients, do not reply only to the sender! Edit mailing list options or unsubscribe: * https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users

-- Daniel-Constantin Mierla -- www.asipto.com www.twitter.com/miconda -- www.linkedin.com/in/miconda Kamailio Advanced Training - Online Mar 28-31, 2022 (Europe Timezone) * https://www.asipto.com/sw/kamailio-advanced-training-online/ __________________________________________________________ Kamailio - Users Mailing List - Non Commercial Discussions * sr-users(a)lists.kamailio.org Important: keep the mailing list in the recipients, do not reply only to the sender! Edit mailing list options or unsubscribe: * https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users

Ben Kaufman

4:38 p.m.

Thank you for the clarification, though unfortunate for my use case, this does make sense. And, from a confirmation standpoint, yes, the failure is if the initial connection is never made. I'll have to go another option like mentioned. Ben Kaufman Sr. VoIP Engineer - BCM One +1.612.735.9309 bkaufman(a)bcmone.com 24 hour client support: +1.855.639.6300 -----Original Message----- From: sr-users <sr-users-bounces(a)lists.kamailio.org> On Behalf Of Daniel-Constantin Mierla Sent: Wednesday, February 23, 2022 1:36 AM To: Kamailio (SER) - Users Mailing List <sr-users(a)lists.kamailio.org> Subject: Re: [SR-Users] SQLOPS sql_query_async() not functioning in an async manner? Hello, the current async query API requires that the connection is established, because it uses the database handle pointer as one of the parameter. That means the process initiating sql_query_async() attempts to connect if the connection is not on, and can get stuck for a while if the database server is not available. Probably can be refactored to do connect in the async task worker, but it's a change that has to be propagated to other modules like db_mysql, db_postgres and any other module using the async query db api function. Not expecting to be complex coding, but requiring time to do it. While I added the async query db api function, it was at the time when Kamailio would fail to run if connection could not be initialized at the start up. Later someone else added sqlops connect_mode, so the connect is delayed, no longer done at the start up if its value is 1, but it is done by the process running the async query function. In other words, only the query itself is executed by async task process. The connection must be active in the other process. At this moment, an alternative is to use mqueue+rtimer, to pass the query from sip worker process to rtimer process, which then executes the query. Cheers, Daniel On 23.02.22 03:06, Alex Balashov wrote:

...

Is there a difference in how it behaves when: a) A database that was previously reachable becomes unreachable or unresponsive? vs. b) A db_url that is not reachable in principle? - Alex

On Feb 22, 2022, at 7:11 PM, Ben Kaufman <bkaufman(a)bcmone.com> wrote: I'm not sure if this is by design or not. I have things I want to log to a SQL DB, but it's not nearly as important as call handling, so the sql_query_async() function looked ideal, but in testing, if the DB is not responsive, the call processing gets blocked. In the example below, I'm using just a short hostname which isn't resolvable, but if I have an IP address there that isn't really assigned (for example if the DB server is down), the same blocking behavior is exhibited, but the error is slightly different (can't connect instead of unknown host). Here's my config. This specific simple config was tested on Kamailio 5.5, but I observe similar behavior (in a more complicated config) in the dev branch as well. - - - - #!KAMAILIO async_workers=2 loadmodule "db_mysql" loadmodule "sqlops" loadmodule "sl" loadmodule "xlog" loadmodule "pv" force_rport=yes ## Host sql does not resolve. modparam("sqlops","sqlcon","cb=>mysql://kamailio:kamailiorw@sql/kamai lio") ## But start anyways. modparam("sqlops", "connect_mode", 1) request_route { sl_send_reply("100", "Trying"); ## Prevent re-transmissions from the UAC xlog("L_NOTICE", "Time before sql_query_async(): [$TV(Sn)]\n"); sql_query_async("cb", "INSERT INTO table1 (col) VALUES ('val')"); xlog("L_NOTICE", "Time before sl_send_reply(): [$TV(Sn)]\n"); sl_send_reply("404", "Not found"); } Here's the log if I send a simple INVITE with SIPp. Note the timestamps 9(16) NOTICE: <script>: Time before sql_query_async(): [1645574694.546165] 9(16) ERROR: db_mysql [km_my_con.c:163]: db_mysql_new_connection(): driver error: Unknown MySQL server host 'sql' (-3) 9(16) ERROR: <core> [db.c:319]: db_do_init2(): could not add connection to the pool 9(16) ERROR: sqlops [sql_api.c:190]: sql_reconnect(): failed to connect to the database [cb] 9(16) ERROR: sqlops [sqlops.c:232]: sql_check_connection(): failed to connect to database 9(16) ERROR: sqlops [sqlops.c:264]: sql_query_async(): invalid connection to database 9(16) NOTICE: <script>: Time before sl_send_reply(): [1645574699.552440] Ben Kaufman Sr. VoIP Engineer - BCM One +1.612.735.9309 bkaufman(a)bcmone.com 24 hour client support: +1.855.639.6300 NOTE: This e-mail and any attachments are from BCM One, Inc. and are intended solely for the use and review of the individual(s) to whom it is addressed. If you believe you received this e-mail in error, please notify the sender immediately, delete the e-mail (and any attachments) from your computer and do not copy or disclose it to anyone else. __________________________________________________________ Kamailio - Users Mailing List - Non Commercial Discussions * sr-users(a)lists.kamailio.org Important: keep the mailing list in the recipients, do not reply only to the sender! Edit mailing list options or unsubscribe: * https://nam11.safelinks.protection.outlook.com/?url=https%3A%2F%2Flis ts.kamailio.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsr-users&data=04 %7C01%7Cbkaufman%40bcmone.com%7C33ab3f4ec3bc4ea8f16508d9f69f37d0%7Caf c1818e7b6848568913201b9396c4fc%7C1%7C0%7C637811985984162833%7CUnknown %7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLC JXVCI6Mn0%3D%7C3000&sdata=WF910Gll17QJvO%2Fmnoy%2FwLKTPbx0N0PBpmH XiRyr%2BS8%3D&reserved=0

-- Alex Balashov | Principal | Evariste Systems LLC Tel: +1-706-510-6800 / +1-800-250-5920 (toll-free) Web: https://nam11.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.e varistesys.com%2F&data=04%7C01%7Cbkaufman%40bcmone.com%7C33ab3f4ec 3bc4ea8f16508d9f69f37d0%7Cafc1818e7b6848568913201b9396c4fc%7C1%7C0%7C6 37811985984162833%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoi V2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=tjG0%2Fm1vIFtN 8w7l%2BxRdyK%2BRE3LpkqhyvBRUfwEGqbM%3D&reserved=0, https://nam11.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.c srpswitch.com%2F&data=04%7C01%7Cbkaufman%40bcmone.com%7C33ab3f4ec3 bc4ea8f16508d9f69f37d0%7Cafc1818e7b6848568913201b9396c4fc%7C1%7C0%7C63 7811985984162833%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV 2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=AlNIqm6e1IA%2Fu 0QVbOG28BuCXTCqaXqcTiLFxyfx5ac%3D&reserved=0 __________________________________________________________ Kamailio - Users Mailing List - Non Commercial Discussions * sr-users(a)lists.kamailio.org Important: keep the mailing list in the recipients, do not reply only to the sender! Edit mailing list options or unsubscribe: * https://nam11.safelinks.protection.outlook.com/?url=https%3A%2F%2Flist s.kamailio.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsr-users&data=04%7 C01%7Cbkaufman%40bcmone.com%7C33ab3f4ec3bc4ea8f16508d9f69f37d0%7Cafc18 18e7b6848568913201b9396c4fc%7C1%7C0%7C637811985984162833%7CUnknown%7CT WFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI 6Mn0%3D%7C3000&sdata=WF910Gll17QJvO%2Fmnoy%2FwLKTPbx0N0PBpmHXiRyr% 2BS8%3D&reserved=0

-- Daniel-Constantin Mierla -- https://nam11.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.asipto… https://nam11.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.twitte… -- https://nam11.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.linked… Kamailio Advanced Training - Online Mar 28-31, 2022 (Europe Timezone) * https://nam11.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.asipt… __________________________________________________________ Kamailio - Users Mailing List - Non Commercial Discussions * sr-users(a)lists.kamailio.org Important: keep the mailing list in the recipients, do not reply only to the sender! Edit mailing list options or unsubscribe: * https://nam11.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.kam…

801

days inactive

801

days old

sr-users@lists.kamailio.org

Manage subscription

4 comments

4 participants

tags (0)

participants (4)

Alex Balashov
Ben Kaufman
Daniel-Constantin Mierla
Henning Westerholt