From miconda@gmail.com Wed Feb 23 08:35:43 2022
From: Daniel-Constantin Mierla <miconda@gmail.com>
To: sr-users@lists.kamailio.org
Subject:
 Re: [SR-Users] SQLOPS sql_query_async() not functioning in an async manner?
Date: Wed, 23 Feb 2022 08:35:37 +0100
Message-ID: <bdd9d225-45c3-e616-b8eb-7cf88e36589f@gmail.com>
In-Reply-To: <BA4E3E10-7526-454C-A359-B4045B2A6923@evaristesys.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============0977852056=="

--===============0977852056==
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable

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 unrespon=
sive? vs.=20
>
> b) A db_url that is not reachable in principle?
>
> =E2=80=94 Alex
>
>> On Feb 22, 2022, at 7:11 PM, Ben Kaufman <bkaufman(a)bcmone.com> wrote:
>>
>> I=E2=80=99m not sure if this is by design or not.  I have things I want to=
 log to a SQL DB, but it=E2=80=99s 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=E2=
=80=99m using just a short hostname which isn=E2=80=99t resolvable, but if I =
have an IP address there that isn=E2=80=99t really assigned (for example if t=
he DB server is down), the same blocking behavior is exhibited, but the error=
 is slightly different (can=E2=80=99t connect instead of unknown host). =20
>> =20
>> =20
>> =20
>> Here=E2=80=99s my config.  This specific simple config was tested on Kamai=
lio 5.5, but I observe similar behavior (in a more complicated config) in the=
 dev branch as well.
>> =20
>> =20
>> - - - -=20
>> =20
>> #!KAMAILIO
>> =20
>> async_workers=3D2
>> =20
>> loadmodule "db_mysql"
>> loadmodule "sqlops"
>> loadmodule "sl"
>> loadmodule "xlog"
>> loadmodule "pv"
>> =20
>> force_rport=3Dyes
>> =20
>> ## Host sql does not resolve.
>> modparam("sqlops","sqlcon","cb=3D>mysql://kamailio:kamailiorw(a)sql/kamail=
io")
>> ## But start anyways.
>> modparam("sqlops", "connect_mode", 1)
>> =20
>> request_route {
>>     sl_send_reply("100", "Trying"); ## Prevent re-transmissions from the U=
AC
>> =20
>>     xlog("L_NOTICE", "Time before sql_query_async(): [$TV(Sn)]\n");
>>     sql_query_async("cb", "INSERT INTO table1 (col) VALUES ('val')");
>> =20
>>     xlog("L_NOTICE", "Time before sl_send_reply(): [$TV(Sn)]\n");
>>     sl_send_reply("404", "Not found");
>> }
>> =20
>> =20
>> Here=E2=80=99s the log if I send a simple INVITE with SIPp. Note the times=
tamps
>> =20
>> 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 conn=
ect 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]
>> =20
>> =20
>> =20
>> =20
>> =20
>> =20
>> =20
>> Ben Kaufman
>> Sr. VoIP Engineer - BCM One
>> =20
>> +1.612.735.9309
>> bkaufman(a)bcmone.com
>> 24 hour client support: +1.855.639.6300
>> =20
>>       =20
>> =20
>> NOTE: This e-mail and any attachments are from BCM One, Inc. and are inten=
ded
>> 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 an=
d do
>> not copy or disclose it to anyone else.
>> =20
>> __________________________________________________________
>> 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 t=
he sender!
>> Edit mailing list options or unsubscribe:
>>  * https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
> --=20
> 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 th=
e sender!
> Edit mailing list options or unsubscribe:
>   * https://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users

--=20
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/



--===============0977852056==--