[OpenSIPS-Users] OpenSIPs dies when connection to MySQL is lost.

[Bogdan-Andrei Iancu]

Hi Chris,

Yesterday I discovered some serious issues with the libmysqlclient 
(source for all the mysql related crashed that were reported). I  had to 
rework some part of the code (for dealing with reconnects at application 
level instead of lib level, as the one in lib is not reliable at all).

So if you have a non-critical system, could you test the latest version 
of db_mysql module from SVN trunk (the branches were not yet updated) - 
the trunk version is fully compatible with the 1.5 tree.

Thanks and regards,
Bogdan

Chris Maciejewski wrote:
> I use USERLOC and AUTH to handle my REGISTER requests.
>
> libmysqlclient is the one shipped with Ubunty Server 8.04:
>
> $ dpkg-query --status libmysqlclient15-dev
> Package: libmysqlclient15-dev
> Version: 5.0.51a-3ubuntu5.4
>
> Regards,
> Chris
>
> 2009/6/24 Bogdan-Andrei Iancu <bogdan at voice-system.ro>
>   
>> Hi Chris,
>>
>> Thanks for the feedback - in regards to your setup - for REGISTER, do you use only USRLOC or also some other modules doing DB ops (like auth, avpops, etc) ?
>>
>> Also, what version of libmysqlclient do you have?
>>
>> Regards,
>> Bogdan
>>
>> Chris Maciejewski wrote:
>>     
>>> Hi Bogdan,
>>>
>>> I applied the patch, but unfortunately OpenSIPs still crashes with the
>>> following output in the log:
>>>
>>> ERROR:db_mysql:re_init_statement: failed while mysql_stmt_prepare:
>>> (1243) Unknown prepared statement handler (2) given to
>>> mysql_stmt_close
>>> ERROR:db_mysql:db_mysql_do_prepared_query: failed to re-init statement!
>>> ERROR:usrloc:db_timer_udomain: failed to delete from table location
>>> ERROR:usrloc:timer: synchronizing cache failed
>>> CRITICAL:core:receive_fd: EOF on 12
>>> INFO:core:handle_sigs: child process 11842 exited by a signal 11
>>> INFO:core:handle_sigs: core was generated
>>> INFO:core:handle_sigs: terminating due to SIGCHLD
>>> INFO:core:sig_usr: signal 15 received
>>>
>>> To reproduce:
>>> 1. Start MySQL.
>>> 2. Start opensips.
>>> 3. Register a SIP phone (Twinkle in my case).
>>> 4. Deregister / Register SIP phone couple of times.
>>> 5. Run the following on MySQL server: "FLUSH TABLES WITH READ LOCK;"
>>> 6. Deregister / Register SIP phone couple of times. OpenSIPs will show
>>> the following in the log:
>>>
>>> ERROR:db_mysql:get_new_stmt_ctx: failed while mysql_stmt_prepare:
>>> (2013) Lost connection to MySQL server during query
>>> ERROR:db_mysql:db_mysql_do_prepared_query: failed to create new context
>>> ERROR:usrloc:db_update_ucontact: updating database failed
>>> ERROR:usrloc:update_ucontact: failed to update database
>>>
>>> 7. Run the following on MySQL server: "UNLOCK TABLES;"
>>> 8. OpenSIPs will crash after a little while.
>>>
>>> If there is anything more I can do to help solving this problem,
>>> please let me know.
>>>
>>> Best regards,
>>> Chris
>>>
>>>
>>> 2009/6/23 Bogdan-Andrei Iancu <bogdan at voice-system.ro>
>>>
>>>       
>>>> Hi Chris,
>>>>
>>>> following other reports, I made a fix on the SVN (trunk) - I did not commited yet on branch as there is still under testing. I would really appreciate your help in confirming the correctness of this fix, by simply testing it - apply the attached patch to your 1.5 sources and give it a try.
>>>>
>>>> Thanks and regards,
>>>> Bogdan
>>>>
>>>> Chris Maciejewski wrote:
>>>>
>>>>         
>>>>> Hi,
>>>>>
>>>>> I just noticed OpenSIPs dies with the following output in the log,
>>>>> when MySQL goes down:
>>>>>
>>>>> ERROR:db_mysql:re_init_statement: failed while mysql_stmt_prepare:
>>>>> (1243) Unknown prepared statement handler (4) given to
>>>>> mysql_stmt_close
>>>>> ERROR:db_mysql:db_mysql_do_prepared_query: failed to re-init statement!
>>>>> ERROR:usrloc:db_timer_udomain: failed to delete from table location
>>>>> ERROR:usrloc:timer: synchronizing cache failed
>>>>> ERROR:db_mysql:re_init_statement: failed while mysql_stmt_prepare:
>>>>> (2013) Lost connection to MySQL server during query
>>>>> ERROR:db_mysql:db_mysql_do_prepared_query: failed to re-init statement!
>>>>> ERROR:presence:update_db_subs: deleting expired information from database
>>>>> INFO:core:handle_sigs: child process 8655 exited by a signal 11
>>>>> INFO:core:handle_sigs: core was generated
>>>>> INFO:core:handle_sigs: terminating due to SIGCHLD
>>>>>
>>>>> Would it be possible to change this behaviour, so for example:
>>>>>
>>>>> 1. Both OpenSIPs and MySQL is running.
>>>>> 2. MySQL goes down.
>>>>> 3. OpenSIPs sends "500 Server error" but the process stays up and running.
>>>>> 4. MySQL is back online.
>>>>> 5. OpenSIPs resumes normal operation.
>>>>>
>>>>> Best regards,
>>>>> Chris
>>>>>
>>>>>           
>
>