[SR-Users] Lost MySQL connections - Error 500

[Duarte Rocha]

Thanks for the help.

I'm using db_mode 1 - Write-Through scheme. As far as i understand it uses
database and cache correct? Is that the difference from mode 3?

I'll do some testing with the usrloc's behaviour and i'll let you know.

Cheers


Daniel-Constantin Mierla <miconda at gmail.com> escreveu no dia sexta,
8/01/2021 à(s) 17:47:

> Hello,
>
> if there is no external application that accesses the location table and
> you have many records, try to tune the behaviour of usrloc module, by
> setting timer procs and interval to different values. Also, if you use
> database-only mode with a lot of active users, you can also consider
> partitioning users on different tables, so you can have rules like users
> with id starting a-m go to location1 and n-z go to location2.
>
> Cheers,
> Daniel
> On 08.01.21 18:29, Duarte Rocha wrote:
>
> Hello Daniel,
>
> I have Kamailio on an HA setup. One machine has the active Kamailio and if
> something happens, the IP jumps to the another machine where there is a
> Kamailio running. Both Kamailio's share the same remote database. Could the
> reason be both Kamailio's trying to remove expired contacts from the
> location table at the same time?
>
> Cheers,
> Duarte
>
> Daniel-Constantin Mierla <miconda at gmail.com> escreveu no dia quinta,
> 7/01/2021 à(s) 19:28:
>
>> Hello,
>>
>> is there any other application that accesses the location table?
>>
>> That error message usually appears when the update of the record cannot
>> be made because it is locked for long time by something else.
>>
>> Cheers,
>> Daniel
>> On 07.01.21 18:23, Duarte Rocha wrote:
>>
>> Hi all,
>>
>> I've made a post last month regarding losing MySQL connections  -
>> https://lists.kamailio.org/pipermail/sr-users/2020-December/111389.html
>>
>> At the time I thought connections were dying as a consequence of low
>> activity and traffic on the proxy. Meanwhile, I've migrated a great number
>> of equipments to the proxy with Registers being refreshed every 10minutes
>> and the problem still persists.
>>
>> In order to try to fix this i've added timeout_interval and ping_interval
>> from the db_mysql module. My SQL client on the Kamailio machine is
>> mysql-community-client  5.6.50-2.el7. It writes and reads in a remote
>> InnoDB database.
>>
>> This are the logs i get from Kamailio when the problem appears :
>>
>> Jan  7 09:43:27 sbc_bbt01_active
>> /usr/local/kamailio-5.4/sbin/kamailio[21735]: ERROR: {1 27880 REGISTER
>> e5f8f7bc-cbb2-40b3-9037-edacd6276a2b} db_mysql [km_dbase.c:123]:
>> db_mysql_submit_query(): driver error on query: Lock wait timeout exceeded;
>> try restarting transaction (1205)
>> Jan  7 09:43:27 sbc_bbt01_active
>> /usr/local/kamailio-5.4/sbin/kamailio[21735]: ERROR: {1 27880 REGISTER
>> e5f8f7bc-cbb2-40b3-9037-edacd6276a2b} <core> [db_query.c:348]:
>> db_do_update(): error while submitting query
>> Jan  7 09:43:27 sbc_bbt01_active
>> /usr/local/kamailio-5.4/sbin/kamailio[21735]: ERROR: {1 27880 REGISTER
>> e5f8f7bc-cbb2-40b3-9037-edacd6276a2b} usrloc [ucontact.c:1147]:
>> db_update_ucontact_ruid(): updating database failed
>> Jan  7 09:43:27 sbc_bbt01_active
>> /usr/local/kamailio-5.4/sbin/kamailio[21735]: ERROR: {1 27880 REGISTER
>> e5f8f7bc-cbb2-40b3-9037-edacd6276a2b} usrloc [ucontact.c:1663]:
>> update_contact_db(): failed to update database
>> Jan  7 09:43:27 sbc_bbt01_active
>> /usr/local/kamailio-5.4/sbin/kamailio[21735]: ERROR: {1 27880 REGISTER
>> e5f8f7bc-cbb2-40b3-9037-edacd6276a2b} registrar [save.c:784]:
>> update_contacts(): failed to update contact
>> Jan  7 09:43:27 sbc_bbt01_active
>> /usr/local/kamailio-5.4/sbin/kamailio[21735]: ERROR: {1 27880 REGISTER
>> e5f8f7bc-cbb2-40b3-9037-edacd6276a2b} sl [sl_funcs.c:414]:
>> sl_reply_error(): stateless error reply used: I'm terribly sorry, server
>> error occurred (1/SL)
>>
>>
>> Originally I had usrloc db_mode on mode 3 - DB-Only Scheme. In order to
>> try to mitigate the issue I changed it to mode 1 - Write-Through scheme but
>> even then I get the same log errors and an "500" error is still sent to the
>> client. I chose this mode since, as far as I can understand it applies
>> changes directly to DB but also uses cache. Please correct me if i'm wrong
>> on that.
>>
>> Has this issue happened with anyone before? Is there a way to mitigate
>> this issue? My only constraint is that I need the database to be always
>> updated since I have an HA setup, and as such, I can't use cache only
>> methods.
>>
>> Best Regards,
>>
>>
>> _______________________________________________
>> Kamailio (SER) - Users Mailing Listsr-users at lists.kamailio.orghttps://lists.kamailio.org/cgi-bin/mailman/listinfo/sr-users
>>
>> --
>> Daniel-Constantin Mierla -- www.asipto.comwww.twitter.com/miconda -- www.linkedin.com/in/miconda
>> Funding: https://www.paypal.me/dcmierla
>>
>> --
> Daniel-Constantin Mierla -- www.asipto.comwww.twitter.com/miconda -- www.linkedin.com/in/miconda
> Funding: https://www.paypal.me/dcmierla
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.kamailio.org/pipermail/sr-users/attachments/20210108/b28f3671/attachment.htm>