[Devel] mysql module failed to reconnect after mysql restart

[Norman Brandinger]

Hi Daniel / Raymond

Note that upon connection, mysql_real_connect() sets the reconnect flag 
(part of the MYSQL structure) to a value of 1 in versions of the API 
older than 5.0.3, or 0 in newer versions. A value of 1 for this flag 
indicates that if a statement cannot be performed because of a lost 
connection, to try reconnecting to the server before giving up. As of 
MySQL 5.0.13, you can use the MYSQL_OPT_RECONNECT option to  
mysql_options() to control reconnection behavior.

I have tested the following code (note that only the important pieces 
are listed below) and my application was able to automatically reconnect 
to MySQL.  The example below uses BOTH the "MYSQL_OPT_RECONNECT" and the 
"conn->reconnect = 1" forms to set the reconnect flag.  As memory 
serves, "MYSQL_OPT_RECONNECT" didn't work for me.

Regards,
Norm


static MYSQL *conn;
static int reconnect = 1;
static char *dbhost = NULL, *dbname = NULL, *dbuser = NULL, *dbpass = 
NULL, *dbsock = NULL;
static int dbport = 0;
              
conn = mysql_init(NULL);
 if (conn == NULL) {
  return -99;
 }

mysql_options(conn,MYSQL_OPT_RECONNECT,(char *)&reconnect);

if (mysql_real_connect(conn, dbhost, dbuser, dbpass, dbname, dbport, 
dbsock, 0) == NULL) {
    mysql_close(conn);
  connected = 0;
} else {
  conn->reconnect = 1;
}



Daniel-Constantin Mierla wrote:

> Hello Raymond,
>
> what version of MySQL server do you use? There are several issues with 
> some 5.x versions and reconnect mechanism. I tried with my MySql 
> 4.1.11 and it is working to reconnect.
>
> Cheers,
> Daniel
>
>
> On 01/12/06 11:06, Raymond Chen wrote:
>
>> Hi Bodan,
>>
>> Here is the debug log
>>
>> 0(9520) avpops:ops_subst: subst to 0 avps
>>  0(9520) submit_query: MySQL server has gone away
>>  0(9520) db_query: Error while submitting query
>>  0(9520) ERROR:avpops:load_avps: db_load failed
>>
>> Seems that mysql not able to perform reconnect after mysql restart.
>>
>> Thanks
>>
>> Ray
>>
>> -----Original Message-----
>> From: Bogdan-Andrei Iancu [mailto:bogdan at voice-system.ro] Sent: 
>> Tuesday, January 10, 2006 11:58 AM
>> To: Raymond Chen
>> Cc: devel at openser.org
>> Subject: Re: [Devel] mysql module failed to reconnect after mysql 
>> restart
>>
>> Hi Ray,
>>
>> do you get into debug mode some logs about any attempt to reconnect?
>>
>> regards,
>> bogdan
>>
>> Raymond Chen wrote:
>>
>>  
>>
>>> Dear all,
>>>
>>> We have noticed openser's mysql module failed to reconnect after we
>>> intentionally restart mysql server.  Thereafter, all registration 
>>> process
>>> failed to work.  Is openser behaving as designed or just 
>>> mis-configuration
>>> problem on our part?
>>>
>>> Thanks
>>>
>>> Ray
>>>
>>>
>>>
>>> _______________________________________________
>>> Devel mailing list
>>> Devel at openser.org
>>> http://openser.org/cgi-bin/mailman/listinfo/devel
>>>
>>>  
>>>
>>>     
>>
>>
>>
>>
>>
>>
>> _______________________________________________
>> Devel mailing list
>> Devel at openser.org
>> http://openser.org/cgi-bin/mailman/listinfo/devel
>>
>>   
>
>
> _______________________________________________
> Devel mailing list
> Devel at openser.org
> http://openser.org/cgi-bin/mailman/listinfo/devel
>
>
>