[Users] Problem with SQL queries in 1.1.0?

[Federico Giannici]

Further reading the MySQL documentation, I found that:

"Prior to MySQL 5.0.19 [or 5.1.8], even if the reconnect flag in the 
MYSQL structure is equal to 1, MySQL does not automatically reconnect 
and re-issue the query as it doesn't know if the server did get the 
original query or not."

And I was using just version 5.0.18!

Now I upgraded to version 5.0.22 and the problem now seems to be resolved.

Bye.


Federico Giannici wrote:
> 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
___________________________________________________