<div dir="ltr"><div>Good morning.<br></div><div>I'm terribly sorry, but I have one more question for OpenSIPS community.</div><div><br></div><div>I've started experiencing a trouble when working with mysql write-back mode, and getting this error:<br><code>"CRITICAL:db_mysql:wrapper_single_mysql_stmt_execute: driver error (1062): Duplicate entry 'XXXXXXXXXXX' for key 'PRIMARY'</code> "<br></div><div><br></div><div>This only happens in the following case:<br>1. Subscriber A registers at instance 1;<br>2. Subscriber A suddenly gets disabled without sending de-registration;<br>2.1. At this point we still have a location record in the (shared) mysql database for subscriber A ;<br>3. Subscriber A gets online and sends register message to instance 2;<br>3.1. Instance 2 gets an error 1062:<br> "CRITICAL:db_mysql:wrapper_single_mysql_stmt_execute: driver error (1062): Duplicate entry 'XXXXXXXXXXX' for key 'PRIMARY' "<br>3.2 old location record (saved by instance 1) is still in the location table;<br><br>The logic for registrations (as well as for re-registrations) is as following:<br>- get a subscriber authorized<br>- delete old location record with: remove("location", "$tu");<br>- save a new location with:<br> if (!save("location")) { sl_reply_error(); }</div><div><br></div><div>For some reason remote() doesn't work when working over write-back, meanwhile with sql-only mode it's ok.</div><div>Could this happen, that save() action was updated to mysql earlier, than a remove() action? (when I was doing a new registering on instance2)<br>
In case this is a true, it makes sense why I get duplicated entries.</div><div><br></div><div>As I understood, I shouldn't use a shared DB for all opensips instances working in the cluster?<br></div><div>And does this mean that I need to use <code>"skip_replicated_db_ops = 1</code>" when using "write-back" and a shared mysql db?</div><div>I've come to this conclusion after reading of this bug case: <a href="https://github.com/OpenSIPS/opensips/issues/1365">https://github.com/OpenSIPS/opensips/issues/1365</a><br></div><div><br></div><div>Many thanks to OpenSIPS community for any hint and advice in advance.<br> </div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Oct 10, 2019 at 12:00 PM Donat Zenichev <<a href="mailto:donat.zenichev@gmail.com">donat.zenichev@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr"><p>Now I understand why does it happen.<br>I just want to leave here my deduction for others, who will search for the same information.<br></p><p>
Firstly we need to understand a correlation of opensips subscribers activity and mysql wait_timout parameter.</p>
<p>wait_timeout - this is a timeout responsible for disconnecting idling
(not used) connections after a given timer for that triggers.<br>
so say we have it set to 300 seconds, in case a certain mysql connection
(worker) is not in use during 300 seconds, this gonna be dropped.<br>
Default value is 28800 seconds, that is 8 hours.</p>
<p>While you have a constant flow of subscribers that work with your
opensips system, you have a bunch of mysql connections that send some
data and are not in idle state (for the mysql server), thus the wait
timer for such workers (on the mysql server side) is always updating and
never reach it's end.</p>
<p>When a certain mysql connection triggers it's wait_timeout it's
gonna be dropped by mysql server. (And I guess opensips will re-connect
this worker again?)</p>
<p>Summarizing, it's a completely normal thing when you have re-connections appearing in you opensips log.<br>
In case you don't want to see them, just play with mysql timers not to
lose any connection from opensips systems, but this has its drawback as
well, like overfilled process-list on the mysql server side, thus you
have to increase a value of max_connections. And this is not such as good idea.</p><p>There are also other useful timers of mysql that can be useful for solving certain problems:<br>max_allowed_packet - responsible for the biggest packet size MySQL server can receive (consider this when you have pretty huge transactions).<br>net_read_timeout and net_write_timeout - for cases when you have troubles with your network (delays, packets loss, jitter etc.)<br>wait_timeout and interactive_timeout - both are responsible for disconnecting idling sessions (different between this couple is in the connection type, read mysql manuals).<br>connect_timeout - the number of seconds that the mysqld server waits for a connect packet before responding with a Bad handshake.</p></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Wed, Oct 9, 2019 at 1:43 PM Liviu Chircu <<a href="mailto:liviu@opensips.org" target="_blank">liviu@opensips.org</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On 09.10.2019 12:07, Donat Zenichev wrote:<br>
> Yes 'wait_timout' has its default value of 8 hours. I would try to <br>
> decrease it, but I guess this can lead to more re-connections.<br>
> Am I right?<br>
<br>
No, it's quite the opposite. The longer MySQL tolerates inactive <br>
connections, the less often it will<br>
have to destroy them. Try setting "wait_timeout" to 10 seconds from the <br>
console and play with it,<br>
you will see what I mean.<br>
<br>
On 09.10.2019 12:07, Donat Zenichev wrote:<br>
> Could you please describe a bit,<br>
> why this "the more MySQL ops you will make them do" correlates to this <br>
> "the less reconnects they will have to make" ?<br>
> Does that mean, that the more job usrloc timer jobs do, the less mysql <br>
> re-connections I have?<br>
<br>
The MySQL "wait_timeout" only tracks inactive connections (no data sent <br>
by the client).<br>
As soon as the client sends data on the conn, MySQL will reset its timer <br>
to "wait_timeout"for that conn.<br>
<br>
If your calls are in the air for 4-5 seconds on a 100 Trying, I suggest <br>
you switch to full DBG logs<br>
and try to understand which MySQL op you're dealing with, and maybe <br>
change the settings for that module.<br>
It cannot be usrloc, since you just switched it to being fully async, <br>
thanks to "write-back" SQL.<br>
<br>
Regards,<br>
<br>
Liviu Chircu<br>
OpenSIPS Developer<br>
<a href="http://www.opensips-solutions.com" rel="noreferrer" target="_blank">http://www.opensips-solutions.com</a><br>
<br>
<br>
_______________________________________________<br>
Devel mailing list<br>
<a href="mailto:Devel@lists.opensips.org" target="_blank">Devel@lists.opensips.org</a><br>
<a href="http://lists.opensips.org/cgi-bin/mailman/listinfo/devel" rel="noreferrer" target="_blank">http://lists.opensips.org/cgi-bin/mailman/listinfo/devel</a><br>
</blockquote></div><br clear="all"><br>-- <br><div dir="ltr"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div dir="ltr"><br></div><div dir="ltr"><div dir="ltr"><font style="background-color:rgb(255,255,255)" color="#0b5394">Best regards,<br></font></div><div dir="ltr"><font style="background-color:rgb(255,255,255)" color="#0b5394">Donat Zenichev<br><br></font></div></div></div></div></div></div></div></div>
</blockquote></div><br clear="all"><br>-- <br><div dir="ltr" class="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div dir="ltr"><br></div><div dir="ltr"><div dir="ltr"><font style="background-color:rgb(255,255,255)" color="#0b5394">Best regards,<br></font></div><div dir="ltr"><font style="background-color:rgb(255,255,255)" color="#0b5394">Donat Zenichev<br><br></font></div></div></div></div></div></div></div></div>