[Serusers] Re: SER 0.9.6 and MySQL 5.0 issues revisited

[Kostas Marneris]

Hello N. ,

Indeed, the patched 'my_con.c' you sent, solves the problems
of MySQL re-connections on timeout.
We recompiled the mysql module with the new 'my_con.c' and seems that
it's working without problems for many hours now..


thanks for your help,
Kostas


SIP wrote:
> Yes. It's a problem with the way the new MySQL was written in its
> defaults. When 5.0.x came around, they defaulted to timing out the
> connections. There's a fix, but it ONLY works on MySQL 5.0.19 and above
> (they implemented the fix around 5.0.12, but it didn't work until 5.0.19).
> 
> IF you have 5.0.19 and above, I've attached a patched my_con.c file
> (goes in $SER-SRC/modules/mysql). Recompile with the new file and it
> should work (I've added an option to reconnect on timeout).
> 
> Let me know if you have any questions concerning its implementation.
> 
> N.
> 
> 
> 
> Kostas Marneris wrote:
>> Hello all,
>>
>> Has anyone experience a similar problem with SER-0.9.6 and MySQL 5.0.x ?
>>
>> The problem also appears even if we don't restart the MySQL server
>> but just wait with SER "up & running" for some time.
>>
>> Any feedback or ideas ?
>>
>>
>> thanks
>> Kostas
>>
>> Kostas Marneris wrote:
>>   
>>> Greetings to the list,
>>>
>>> In a previous thread
>>> (http://lists.iptel.org/pipermail/serusers/2006-April/027961.html)
>>> SER and mysql 5.0 issues were discussed. The main idea was that if MySQL was
>>> restarted, SER should be able to do auto-reconnect to the database server.
>>> According to the thread (Jan Janak), the latest SER version
>>> (0.9.6) should contain a fix to the problem.
>>>
>>> However, we recently upgraded MySQL from version 4.1 to 5.0 as part of an upgade
>>> of Debian Sarge to Etch in our servers. We had no problems with SER and MySQL 4.1.
>>> We recompiled SER and linked mysql module with the MySQL 5 client library
>>> (libmysqlclient.so.15). The auto-reconnect problem seems to persist.
>>>
>>> We start MySQL, then SER and after a few SIP clients get registered,
>>> we restart the MySQL server (using Debian init scripts).
>>> We then see the following  messages in ser's log:
>>> ./ser[24006]: submit_query(): MySQL server has gone away
>>> ./ser[24006]: db_insert: Error while submitting query
>>> ./ser[24006]: db_insert_ucontact(): Error while inserting contact
>>> ./ser[24006]: insert_ucontact(): Error while inserting in database
>>>
>>>
>>> Our SER config contains the following lines:
>>>
>>> fifo_db_url="mysql://ser:XXXXXX@localhost/ser"
>>>
>>> # --------------------------------------------------------------------
>>> # Modules Section
>>> # --------------------------------------------------------------------
>>> ...
>>> loadmodule "/opt/ser-0.9.6/lib/ser/modules/mysql.so"
>>> loadmodule "/opt/ser-0.9.6/lib/ser/modules/usrloc.so"
>>> loadmodule "/opt/ser-0.9.6/lib/ser/modules/uri_db.so"
>>> ...
>>> modparam("usrloc", "db_url", "mysql://ser:XXXXXX@localhost/ser")
>>> modparam("uri_db", "db_url", "mysql://ser:XXXXXX@localhost/ser")
>>> ...
>>>
>>> Our MySQL version is 5.0.32 (standard Debian etch packages) and we
>>> have compiled ourselves the latest stable SER version (0.9.6).
>>>
>>> Any comments are highly welcome, since we are thinking of deploying a
>>> large scale SER setup.
>>>
>>>
>>> thanks,
>>> Kostas
>>>
>>>
>>>     
>> _______________________________________________
>> Serusers mailing list
>> Serusers at lists.iptel.org
>> http://lists.iptel.org/mailman/listinfo/serusers
>>   
> 
> 
> ------------------------------------------------------------------------
> 
> /* 
>  * $Id: my_con.c,v 1.2 2004/08/03 17:22:06 janakj Exp $
>  *
>  *
>  * Copyright (C) 2001-2004 iptel.org
>  *
>  * This file is part of ser, a free SIP server.
>  *
>  * ser is free software; you can redistribute it and/or modify
>  * it under the terms of the GNU General Public License as published by
>  * the Free Software Foundation; either version 2 of the License, or
>  * (at your option) any later version
>  *
>  * For a license to use the ser software under conditions
>  * other than those described here, or to purchase support for this
>  * software, please contact iptel.org by e-mail at the following addresses:
>  *    info at iptel.org
>  *
>  * ser is distributed in the hope that it will be useful,
>  * but WITHOUT ANY WARRANTY; without even the implied warranty of
>  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
>  * GNU General Public License for more details.
>  *
>  * You should have received a copy of the GNU General Public License 
>  * along with this program; if not, write to the Free Software 
>  * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
>  */
> 
> #include <string.h>
> #include <time.h>
> #include "my_con.h"
> #include "../../mem/mem.h"
> #include "../../dprint.h"
> #include "utils.h"
> 
> 
> /*
>  * Create a new connection structure,
>  * open the MySQL connection and set reference count to 1
>  */
> struct my_con* new_connection(struct my_id* id)
> {
> 	struct my_con* ptr;
> 	my_bool my_true;
>         my_true=1;
> 
> 	if (!id) {
> 		LOG(L_ERR, "new_connection(): Invalid parameter value\n");
> 		return 0;
> 	}
> 
> 	ptr = (struct my_con*)pkg_malloc(sizeof(struct my_con));
> 	if (!ptr) {
> 		LOG(L_ERR, "new_connection(): No memory left\n");
> 		return 0;
> 	}
> 
> 	memset(ptr, 0, sizeof(struct my_con));
> 	ptr->ref = 1;
> 	
> 	ptr->con = (MYSQL*)pkg_malloc(sizeof(MYSQL));
> 	if (!ptr->con) {
> 		LOG(L_ERR, "new_connection(): No enough memory\n");
> 		goto err;
> 	}
> 
> 	mysql_init(ptr->con);
> 	mysql_options(ptr->con, MYSQL_OPT_RECONNECT, &my_true);
> 
> 	if (!mysql_real_connect(ptr->con, id->host.s, id->username.s, id->password.s, id->database.s, id->port, 0, 0)) {
> 		LOG(L_ERR, "new_connection(): %s\n", mysql_error(ptr->con));
> 		mysql_close(ptr->con);
> 		goto err;
> 	}
> 
> 	ptr->timestamp = time(0);
> 
> 	ptr->id = id;
> 	return ptr;
> 
>  err:
> 	if (ptr && ptr->con) pkg_free(ptr->con);
> 	if (ptr) pkg_free(ptr);
> 	return 0;
> }
> 
> 
> /*
>  * Close the connection and release memory
>  */
> void free_connection(struct my_con* con)
> {
> 	if (!con) return;
> 	if (con->res) mysql_free_result(con->res);
> 	if (con->id) free_my_id(con->id);
> 	if (con->con) {
> 		mysql_close(con->con);
> 		pkg_free(con->con);
> 	}
> 	pkg_free(con);
> }