[OpenSER-Users] OpenSER failure when MySQL redundant database fails

[Henning Westerholt]

On Friday 22 February 2008, Arturo Díaz Almagro wrote:

(forward to user list for the archives)

> This is my case. We have a SIP telephony system based on OpenSER with a
> redundant MySQL database ring, so the OpenSER queries always to the primary
> DB unless it fails. When the "master" database dies, a monit process
> detects the failure and modify properly the database entry in the DNS zone
> file. We have tested this redundancy in two way I expose you here:
>
>    - mysql process is killed in the database host machine. In this case
>    OpenSER realizes the failure and asks the DNS inmediately for the
> database IP address and the service keeps working as expected. No problem
> here.
>
>    - shutdown the interface of the machine where mysql is hosted. In this
>    case OpenSER does not ask the DNS for the new address. It keeps trying
> over and over the connection with the master and until the interface is not
> turn on the system keeps hung.
>
> The difference between the two cases is that in the first case, the host
> machine responses with an ICMP (port destination unreachable) when OpenSER
> queries the database. In the second case we have no answer from the MySQL
> host machine.
>
> So, if the mysql's host interface is shutdown the system hangs but if the
> mysql process dies the system works properly. In the first case both
> machines (OpenSER and MySQL) has IP visibility, not in the second, so the
> problem should be related to that. Do you know why this is happening? Might
> you, please, be so kind to tell me how to fix this problem or at list how
> to take a workaround to get it working properly?

Hi Arturo,

Well, this problem is probably caused because the mysql_query function works 
synchronly, i.e. it blocks the process if no progress on the query could be 
made. You could easily reproduce this with simple IP filtering with iptables. 
If you don't get a negative reply to the query, it will simply hang for the 
TCP timeout from the kernel.

We've written in the past a custom extension to the mysql module, called 
mysql-mt. This elevates this problem by uses a multi-threading approach. But 
this worked not that great.

For recent mysql driver versions this is not needed anymore, i've committed in 
revision 3606 a improvement to the trunk version of openser that uses the 
recently introduced extensions in the mysql lib. This could be easily 
backported to your version. Take a look into the mysql documentation if this 
different timeouts are supported on your driver version.

Cheers,

Henning