[Serusers] mysql timeout in ser 2.0
Jan Janak
jan at iptel.org
Mon Dec 10 14:52:58 CET 2007
2.0 should reconnect automatically, I turn the corresponding variable in
MYSQL structure on after mysql_real_connect.
But, after looking into mysql documentation, I just found that that even
2.1 will not reconnect correctly with some versions of mysql:
----------
MYSQL_OPT_RECONNECT (argument type: my_bool *)
Enable or disable automatic reconnection to the server if the connection
is found to have been lost. Reconnect has been off by default since
MySQL 5.0.3; this option is new in 5.0.13 and provides a way to set
reconnection behavior explicitly.
Note: mysql_real_connect() incorrectly reset the MYSQL_OPT_RECONNECT
option to its default value before MySQL 5.0.19. Therefore, prior to
that version, if you want reconnect to be enabled for each connection,
you must call mysql_options() with the MYSQL_OPT_RECONNECT option after
each call to mysql_real_connect(). This is not necessary as of 5.0.19:
Call mysql_options() only before mysql_real_connect() as usual.
-----------
Isn't this the problem? Aren't you using mysql version >=5.0.3 and
<5.0.19? If so then neither 2.1 nor 2.0 will reconnect. It appears we
will need to modify the code even more, because 2.1 calls mysql_options
before mysql_real_connect.
Jan.
Andrei Pelinescu-Onciul wrote:
> On Dec 10, 2007 at 07:20, Greger V. Teigre <greger at teigre.com> wrote:
>> [removed sems and semsdev from cc]
>>
>>
>> SIP wrote:
>>>>> Secondary (but no less important) items that are a must-have:
>>>>> -MySQL (I'm voting for 5 series here, but that will require my patch for
>>>>> the mysql code in SER 2.0)
>>>>>
>>>>>
>>>> why the patch?
>>>>
>>>>
>>> MySQL 5.0.X automatically times out its sockets after a hardcoded period
>>> of time. Unless you have configured the socket to auto reconnect (see
>>> http://www.ideasip.com/support/utils/my_con.c.SER2 ), after a little
>>> while, the socket will just time out and you will have to restart SER in
>>> order to connect to the database (it will throw errors).
>>>
>> I thought that issue was resolved. Can you check the tracker ticket and
>> verify that it is scheduled for ser 2.0 release?
>
>
> It is fixed in a different way in 2.1, but it was not backported to 2.0
> (see http://lists.iptel.org/pipermail/serdev/2007-June/010460.html).
>
> Jan, have you forgotten to backport it or is there some other reason?
>
> (patch for 2.0 attached)
>
>
> Andrei
>
>
> ------------------------------------------------------------------------
>
> ? modules/mysql/.db_mod.c.swp
> Index: modules/mysql/db_mod.c
> ===================================================================
> RCS file: /cvsroot/ser/sip_router/modules/mysql/Attic/db_mod.c,v
> retrieving revision 1.29
> diff -u -r1.29 db_mod.c
> --- modules/mysql/db_mod.c 8 Jan 2006 22:43:17 -0000 1.29
> +++ modules/mysql/db_mod.c 22 Jun 2007 14:33:02 -0000
> @@ -41,6 +41,14 @@
>
> int ping_interval = 5 * 60; /* Default is 5 minutes */
> int auto_reconnect = 1; /* Default is enabled */
> +unsigned int my_connect_to=2; /* 2 s by default */
> +unsigned int my_send_to=0; /* enabled only for mysql >= 5.25 */
> +unsigned int my_recv_to=0; /* enabled only for mysql >= 5.25 */
> +
> +unsigned long my_client_ver=0;
> +
> +#define DEFAULT_MY_SEND_TO 2 /* s */
> +#define DEFAULT_MY_RECV_TO 4 /* s */
>
> static int mysql_mod_init(void);
>
> @@ -71,6 +79,9 @@
> static param_export_t params[] = {
> {"ping_interval", PARAM_INT, &ping_interval},
> {"auto_reconnect", PARAM_INT, &auto_reconnect},
> + {"connect_timeout", PARAM_INT, &my_connect_to},
> + {"send_timeout", PARAM_INT, &my_send_to},
> + {"receive_timeout", PARAM_INT, &my_recv_to},
> {0, 0, 0}
> };
>
> @@ -90,6 +101,28 @@
>
> static int mysql_mod_init(void)
> {
> +#if MYSQL_VERSION_ID >= 40101
> + my_client_ver=mysql_get_client_version();
> + if ((my_client_ver>=50025) || ((my_client_ver >= 40122) &&
> + (my_client_ver < 50000))){
> + if (my_send_to==0)
> + my_send_to= DEFAULT_MY_SEND_TO;
> + if (my_recv_to==0)
> + my_recv_to= DEFAULT_MY_RECV_TO;
> + }else if (my_recv_to || my_send_to){
> + LOG(L_WARN, "WARNING: mysql send or received timeout set, but "
> + " not supported by the installed mysql client library"
> + " (needed at least 4.1.22 or 5.0.25, but installed %ld)\n",
> + my_client_ver);
> + }
> +#else
> + if (my_recv_to || my_send_to){
> + LOG(L_WARN, "WARNING: mysql send or received timeout set, but "
> + " not supported by the mysql client library used to compile"
> + " the mysql module (needed at least 4.1.1 but "
> + " compiled against %ld)\n", MYSQL_VERSION_ID);
> + }
> +#endif
> DBG("mysql: MySQL client version is %s\n", mysql_get_client_info());
> return 0;
> }
> Index: modules/mysql/db_mod.h
> ===================================================================
> RCS file: /cvsroot/ser/sip_router/modules/mysql/Attic/db_mod.h,v
> retrieving revision 1.3
> diff -u -r1.3 db_mod.h
> --- modules/mysql/db_mod.h 28 Oct 2004 23:36:14 -0000 1.3
> +++ modules/mysql/db_mod.h 22 Jun 2007 14:33:02 -0000
> @@ -38,5 +38,10 @@
>
> extern int ping_interval;
> extern int auto_reconnect;
> +extern unsigned int my_connect_to; /* 2 s by default */
> +extern unsigned int my_send_to; /* enabled only for mysql >= 5.25 */
> +extern unsigned int my_recv_to; /* enabled only for mysql >= 5.25 */
> +
> +extern unsigned long my_client_ver;
>
> #endif /* DB_MOD_H */
> Index: modules/mysql/dbase.c
> ===================================================================
> RCS file: /cvsroot/ser/sip_router/modules/mysql/Attic/dbase.c,v
> retrieving revision 1.48.2.1
> diff -u -r1.48.2.1 dbase.c
> --- modules/mysql/dbase.c 23 Feb 2007 21:19:31 -0000 1.48.2.1
> +++ modules/mysql/dbase.c 22 Jun 2007 14:33:02 -0000
> @@ -68,7 +68,7 @@
> t = time(0);
> if ((t - CON_TIMESTAMP(_h)) > ping_interval) {
> if (mysql_ping(CON_CONNECTION(_h))) {
> - DBG("submit_query: mysql_ping failed\n");
> + ERR("mysql: submit_query: mysql_ping failed\n");
> }
> }
> CON_TIMESTAMP(_h) = t;
> Index: modules/mysql/my_con.c
> ===================================================================
> RCS file: /cvsroot/ser/sip_router/modules/mysql/my_con.c,v
> retrieving revision 1.7
> diff -u -r1.7 my_con.c
> --- modules/mysql/my_con.c 30 Jan 2006 16:49:51 -0000 1.7
> +++ modules/mysql/my_con.c 22 Jun 2007 14:33:02 -0000
> @@ -26,6 +26,7 @@
> */
>
> #include "my_con.h"
> +#include "db_mod.h"
> #include "../../mem/mem.h"
> #include "../../dprint.h"
> #include "../../ut.h"
> @@ -41,6 +42,9 @@
> struct my_con* new_connection(struct db_id* id)
> {
> struct my_con* ptr;
> +#if MYSQL_VERSION_ID >= 50013
> + my_bool my_auto_reconnect;
> +#endif
>
> if (!id) {
> LOG(L_ERR, "new_connection: Invalid parameter value\n");
> @@ -82,6 +86,34 @@
> ZSW(id->database)
> );
> }
> +#if MYSQL_VERSION_ID >= 50013
> + my_auto_reconnect=1;
> + if (my_client_ver>=50013){
> + if (mysql_options(ptr->con, MYSQL_OPT_RECONNECT ,
> + (char*)&my_auto_reconnect))
> + WARN("mysql: failed to set MYSQL_OPT_RECONNECT\n");
> + }
> +#endif
> + if (my_connect_to){
> + if (mysql_options(ptr->con, MYSQL_OPT_CONNECT_TIMEOUT,
> + (char*)&my_connect_to))
> + WARN("mysql: failed to set MYSQL_OPT_CONNECT_TIMEOUT\n");
> + }
> +#if MYSQL_VERSION_ID >= 40101
> + if ((my_client_ver>=50025) || ((my_client_ver >= 40122) &&
> + (my_client_ver < 50000))){
> + if (my_send_to){
> + if (mysql_options(ptr->con, MYSQL_OPT_WRITE_TIMEOUT ,
> + (char*)&my_send_to))
> + WARN("mysql: failed to set MYSQL_OPT_WRITE_TIMEOUT\n");
> + }
> + if (my_recv_to){
> + if (mysql_options(ptr->con, MYSQL_OPT_READ_TIMEOUT ,
> + (char*)&my_recv_to))
> + WARN("mysql: failed to set MYSQL_OPT_READ_TIMEOUT\n");
> + }
> + }
> +#endif
>
> if (!mysql_real_connect(ptr->con, id->host, id->username, id->password, id->database, id->port, 0, 0)) {
> LOG(L_ERR, "new_connection: %s\n", mysql_error(ptr->con));
More information about the sr-users
mailing list