[Users] Problem with SQL queries in 1.1.0?

[Federico Giannici]

I have found that now the correct way to set the reconnection in MySQL 
is to use the mysql_options() function. Here it is the description of 
the MYSQL_OPT_RECONNECT option: "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.".

So I replaced the "ptr->con->reconnect = 1;" instruction in 
"mysql/my_con.c" with the following lines BEFORE the call to 
mysql_real_connect():

my_bool reconnect;
reconnect=1;
mysql_options(ptr->con,MYSQL_OPT_RECONNECT,(char*)&reconnect);

Well, it continues to DON'T work, but the behavior changed: it no longer 
fails with error 0 (?). Now it fails with error 2013 and then 2006:

Aug 26 17:38:53 eowyn OpenSER[10042]: submit_query: errno code is 2013
Aug 26 17:38:53 eowyn OpenSER[10042]: submit_query: errno code is 2006
Aug 26 17:38:53 eowyn OpenSER[10042]: submit_query: errno code is 2006
Aug 26 17:38:53 eowyn OpenSER[10042]: submit_query: MySQL server has 
gone away
Aug 26 17:38:53 eowyn OpenSER[10042]: db_insert: Error while submitting 
query
Aug 26 17:38:53 eowyn OpenSER[10042]: db_insert_ucontact(): Error while 
inserting contact
Aug 26 17:38:53 eowyn OpenSER[10042]: ERROR:usrloc:insert_ucontact: 
failed to insert in database

Moreover, this time NO connection to the MySQL is actually done 
(previously a connection was done, but without any query).

In conclusion, it appears to me that the reconnections under MySQL are a 
great mess, and they probably depend on the actual version of client and 
server used.

So, I suggest to don't depend on them. On the submit_query() function, 
after the 3 attempts, if the error is still CR_SERVER_GONE_ERROR or 
CR_SERVER_LOST, we should close the connection and try to reopen it.

What do you think?

Bye.



Federico Giannici wrote:
> Any news on this problem?
> 
> Today I found that the problem occurs even when MySQL is NOT restarted. 
> Here it is example of a query that seems to fail but without an error 
> code (no previous error 2013):
> 
> Aug 23 16:36:58 eowyn OpenSER[28950]: submit_query: errno code is 0
> Aug 23 16:36:58 eowyn OpenSER[28950]: submit_query:
> Aug 23 16:36:58 eowyn OpenSER[28950]: db_query: Error while submitting 
> query
> 
> As said before, a new connection is done to the MySQL server, but no 
> query is executed and the connection is immediately closed (instead of 
> keeping it open).
> 
> 
> Bye.
> 
> 
> Federico Giannici wrote:
>> Bogdan-Andrei Iancu wrote:
>>> Federico Giannici wrote:
>>>
>>>> Aug 22 15:50:34 eowyn OpenSER[16778]: submit_query:
>>>> Aug 22 15:50:34 eowyn OpenSER[16778]: db_query: Error while 
>>>> submitting query
>>>
>>> can you check on the mysql server if the query was performed or not? 
>>> because the strange part is that submit_query returns  a non zero 
>>> value but the mysql_error reports no error...
>>
>> After the SQL server is restarted then every time a query is done by 
>> OpenSER a new connection is opened (and then closed) ma the query is 
>> NOT executed.
>>
>> Here it is an example:
>>
>> 26 Connect     ser at eowyn.neomedia.it on ser
>> 26 Query       SET NAMES latin1
>> 26 Quit
>>
>>
>> Bye.
>>
> 
> 


-- 
___________________________________________________
     __
    |-                      giannici at neomedia.it
    |ederico Giannici      http://www.neomedia.it
___________________________________________________