Bug #15719 MYSQL_OPT_RECONNECT option is modified by mysql_real_connect
Submitted: 13 Dec 2005 21:31 Modified: 2 Feb 2006 17:31
Reporter: Edmund Mierzwa Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.16-nt-max/Linux BK OS:Windows (XP, with SP2/Linux)
Assigned to: Magnus Blåudd CPU Architecture:Any

[13 Dec 2005 21:31] Edmund Mierzwa
Description:
Function mysql_options( ,MYSQL_OPT_RECONNECT, ) returns a non-zero value indicating an unknown option. If I disregard the return code there is no change in behavior for timeout. 

Client and server are both at 5.0.16-nt-max.

How to repeat:
[mysqld]
skip-bdb
skip-innodb
skip-ndbcluster
enable-named-pipe
basedir =c:/mysql_5.0
datadir =c:/mysql_5.0/data
language=c:/mysql_5.0/share/english
sql-mode=ANSI_QUOTES,ERROR_FOR_DIVISION_BY_ZERO,IGNORE_SPACE,NO_AUTO_CREATE_USER,NO_BACKSLASH_ESCAPES,NO_ZERO_DATE,NO_ZERO_IN_DATE,PIPES_AS_CONCAT,REAL_AS_FLOAT,STRICT_ALL_TABLES
set-variable = wait_timeout=4
set-variable = interactive_timeout=5

MYSQL *
db_connect(const char *dbname)
{
	my_bool  my_true = true;
	MYSQL *db = mysql_init(NULL);
									if (!db)
		die(db, "mysql_init failed: no memory");

	if (mysql_options(db, MYSQL_OPT_RECONNECT, &my_true))
		die(db, "mysql_options failed: unknown option MYSQL_OPT_RECONNECT");

	if (!mysql_real_connect(db, "localhost", "oven", "xxx", dbname, 0, NULL, CLIENT_MULTI_STATEMENTS ))
		die(db, "mysql_real_connect() failed: %s", mysql_error(db));
	
	return db;
}
[13 Dec 2005 23:12] MySQL Verification Team
I was unable to repeat with latest BK source and using the C API
application at the bottom. Could you please test on your side.

C:\temp>bug15719
reconnect before mysql_options: 0
reconnect after mysql_options: 1
Connected to the server: 5.0.18-nt

#include <my_global.h>
#include <m_string.h>
#include "mysql.h"

#define DB_HOST			"localhost"
#define DB_USER			"root"
#define DB_PASSW		""
#define DB_NAME			"test"
#define DB_PORT			5017
#define DB_UNIX_SOCKET	NULL

void main( void )
{
  MYSQL mysql;
  my_bool  my_true = TRUE;
   
  mysql_init(&mysql);
  
  printf("reconnect before mysql_options: %d\n", mysql.reconnect); 

  if (mysql_options(&mysql, MYSQL_OPT_RECONNECT, &my_true))
  {
      printf("mysql_options failed: unknown option MYSQL_OPT_RECONNECT\n");
	  return;
  }
  
  printf("reconnect after mysql_options: %d\n", mysql.reconnect);

  if (!mysql_real_connect(&mysql,DB_HOST,DB_USER,DB_PASSW,DB_NAME,
						   DB_PORT,DB_UNIX_SOCKET,0))
  {
    printf("Error: %s\n",mysql_error(&mysql));
    return;
  }
  else
	printf("Connected to the server: %s\n",mysql_get_server_info(&mysql));
    
  mysql_close(&mysql);
}
[14 Dec 2005 15:45] Edmund Mierzwa
It still fails, however, I can get it to work if I use static(mysqlclient) rather than dynamic(libmysql) lib?

---------------------dynamic lib fails-----------------------------------
reconnect before mysql_options: 0
mysql_options failed: unknown option MYSQL_OPT_RECONNECT

Using: libmysql.lib wsock32.lib odbc32.lib odbccp32.lib
Runtime Library: Multi-threaded Debug DLL (/MDd)

---------------------static lib works------------------------------------
reconnect before mysql_options: 0
reconnect after mysql_options: 1
Connected to the server: 5.0.16-nt-max

Using: mysqlclient.lib wsock32.lib odbc32.lib odbccp32.lib
Runtime Library: Multi-threaded Debug (/MTd)
[14 Dec 2005 17:51] MySQL Verification Team
I was able to repeat the issue when in my \windows\system32 directory
there is an libmysql.dll with different version than the 5.0 and this
option is only supported since 5.0.13. Then please verify which
libmysql.dll is own your environnment path:

C:\temp>bug15719
reconnect before mysql_options: -92
mysql_options failed: unknown option MYSQL_OPT_RECONNECT

C:\temp>dir c:\windows\system32\libmy*
 O volume na unidade C não tem nome.
 O número de série do volume é E8BA-3E5D

 Pasta de c:\windows\system32

08/12/2005  10:20           434.236 libmysql.dll
               1 arquivo(s)        434.236 bytes
               0 pasta(s) 22.169.354.240 bytes disponíveis

C:\temp>del c:\windows\system32\libmy*

C:\temp>bug15719
reconnect before mysql_options: 0
reconnect after mysql_options: 1
Connected to the server: 5.0.18-nt

C:\temp>dir
 O volume na unidade C não tem nome.
 O número de série do volume é E8BA-3E5D

 Pasta de C:\temp

14/12/2005  15:46    <DIR>          .
14/12/2005  15:46    <DIR>          ..
14/12/2005  15:42            45.056 bug15719.exe
14/12/2005  15:41         1.302.528 libmysql.dll
               2 arquivo(s)      1.347.584 bytes
               2 pasta(s) 22.168.031.232 bytes disponíveis
[14 Dec 2005 19:13] Edmund Mierzwa
You are correct, after removing this file all works correctly. I have no idea what installs this here, I will check all of our mfg floor installations...Thank you for your help.

C:\WINNT\system32>dir libmy*
 Directory of C:\WINNT\system32

04/30/2004  11:33 AM         1,159,244 libmysql.dll
               1 File(s)      1,159,244 bytes
               0 Dir(s)  20,605,100,032 bytes free
[16 Dec 2005 22:51] MySQL Verification Team
We have noticed that after mysql_real_connect the reconnect is to
reset to 0:

C:\temp>bug15719
reconnect before mysql_options: 0
reconnect after mysql_options: 1
Connected to the server: 5.0.18-nt
reconnect after mysql_real_connect: 0
[16 Dec 2005 23:11] MySQL Verification Team
Version 4.1.XX isn't affected by this bug:

C:\temp>testrecon
reconnect before mysql_real_connect: 1
Connected to the server: 4.1.16-nt-max
reconnect after mysql_real_connect: 1
[25 Jan 2006 9:15] Magnus Blåudd
There is a test called "wait_timeout" in our test suite. It shows that if MYSQL_OPT_RECONNECT is set to 0, the error "2006: MySQL server has gone away" will be displayed after a sleep that is longer than the wait_timeout value. And if MYSQL_OPT_RECONNECT is set to 1 the connection should reconnect without any error message.

That particular test case can be run as:
>cd mysql-test
>./mysq-test-run.pl --do-test=wait_timeout

I can see that this particular test case fails on our latest build and test on Windows. 

> wait_timeout                   [ fail ]
>
> mysqltest: At line 9: query 'select 2' failed with wrong errno 2013: 'Lost connection to MySQL server during query', instead of 2006...

Reconnect does not seem to work as expected on Windows.

It's also true that mysql_real_connect always sets the reconnect flag to 0 as of mysql-5.0.3, and thus the "mysql_option(mysql, MYSQL_OPT_RECONNECT, 1)" has to be called after mysql_real_connect. 

I will try to see if it works and move the initialisation of the reconnect value to mysql_init so what it says about mysql_init, mysql_options and mysql_real_connect in the manual becomes true.
[25 Jan 2006 9:21] Magnus Blåudd
There is one refernce to the reconnect behaviour in the manul, last on the page http://dev.mysql.com/doc/refman/5.0/en/mysql-real-connect.html you find: 

"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."
[25 Jan 2006 9:22] Magnus Blåudd
And on the page describing mysql_options http://dev.mysql.com/doc/refman/5.0/en/mysql-options.html there is the folling text:

"mysql_options() should be called after mysql_init() and before mysql_connect() or mysql_real_connect(). "
[25 Jan 2006 9:46] Magnus Blåudd
>Reconnect does not seem to work as expected on Windows.

A correction, reconnect works if mysql_option(mysql, MYSQL_OPT_RECONNECT) is called after mysql_real_connect. 

The error message is produced when reconnect is turned off and unfortunately error 2013 is returned instead of the expected 2006.
[25 Jan 2006 14:13] Magnus Blåudd
There is another bug that describes the problem with the "wait_timeout" test returning error 2013 instead of 2006.

http://bugs.mysql.com/bug.php?id=15348
[25 Jan 2006 14:22] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/1607
[26 Jan 2006 10:21] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/1664
[30 Jan 2006 14:16] Magnus Blåudd
Pushed to 5.0.19 and 5.1.6
[2 Feb 2006 17:31] Mike Hillyer
Documented in 5.0.19 and 5.1.6 changelogs:

 <listitem>
        <para>
          <literal>MYSQL_OPT_RECONNECT</literal> option was modified by
          calls to the <function>mysql_real_connect()</function>
          function. (Bug #15719)
        </para>
      </listitem>