Bug #35937 MySQL 5.0.45: Connection timeout, Reconnect option
Submitted: 9 Apr 2008 14:04 Modified: 16 May 2008 9:01
Reporter: Yan Nagler Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: C API (client library) Severity:S1 (Critical)
Version:5.0.45 OS:Linux (PowerPC)
Assigned to: CPU Architecture:Any
Tags: 5.0.45, MySQL, reconnect, timeout, Unknown prepared statement handler (1) given to mysql_stmt_execute

[9 Apr 2008 14:04] Yan Nagler
Description:
Hello.

We're running a C API client versus MySQL DBMS ver.: 5.0.45, OS: Linux.

We have a connection timeout problem.
The error we've got: "Lost connection to MySQL server during query", no.: 2013.

So we've added:
mysql_options() with MYSQL_OPT_RECONNECT option

It seems that we still observe the same problem, but the error is different now: "Unknown prepared statement handler (1) given to mysql_stmt_execute", no.: 1234.

Is there any solution available on configuretion/options level?
Please help!

How to repeat:
Attached is the connection open function:
1. mysql_init
2. mysql_options with MYSQL_OPT_RECONNECT
3. mysql_real_connect

DB_STAT_T db_open_conn (DB_CONN_T** const	pp_db_conn,
						const char* const	db_host,
						const BOOLEAN		consider_param_db_host)
{
	int		res_int;
	MYSQL*	res_mysql;

    my_bool	arg = 1;

	char db_host_act[MAX_VAL_LEN_DB_HOST];

	// Allocate Database connection structure
	*pp_db_conn = infra_malloc(sizeof(DB_CONN_T));
	if (! *pp_db_conn)
	{
		infra_log_error("Failed to allocate memory for Database connection structure.");
		return DB_STAT_ERROR;
	}

	// Nullify connection structure (Set NULL: 0)
	memset((void*) *pp_db_conn, 0, sizeof(DB_CONN_T));

	// Open connection
	(*pp_db_conn)->p_conn = mysql_init(NULL);
	if ((*pp_db_conn)->p_conn == NULL)
	{
		infra_log_error("Failed to initialize connection. Msg.: %s; No. %u.",
						mysql_error((*pp_db_conn)->p_conn),
						mysql_errno((*pp_db_conn)->p_conn));
		return DB_STAT_ERROR;
	}

	// Set connection option: auto-reconnect
	res_int = mysql_options((*pp_db_conn)->p_conn, MYSQL_OPT_RECONNECT, &arg);
	if (res_int != 0)
	{
		infra_log_error("Failed to set connection option. Msg.: %s; No. %u.",
						mysql_error((*pp_db_conn)->p_conn),
						mysql_errno((*pp_db_conn)->p_conn));
		return DB_STAT_ERROR;
	}

	// Determine the actual Database host
	if (consider_param_db_host == TRUE)
	{
		if (db_host == NULL)
		{
			strcpy(db_host_act, LOCALHOST);
		}
		else
		{
			strcpy(db_host_act, db_host);
		}
	}
	else
	{
		strcpy(db_host_act, s_db_conf.db_host);
	}

	// Connect to the Database
	res_mysql = mysql_real_connect((*pp_db_conn)->p_conn, db_host_act,
								   s_db_conf.db_user, s_db_conf.db_pswd, s_db_conf.db_db,
								   0, NULL, 0);
	if (res_mysql == NULL)
	{
		infra_log_error("Failed to connect to Database. Msg.: %s; No. %u.",
						mysql_error((*pp_db_conn)->p_conn),
						mysql_errno((*pp_db_conn)->p_conn));
		return DB_STAT_ERROR;
	}

	return DB_STAT_OK;
}
[9 Apr 2008 17:15] MySQL Verification Team
Thank you for the bug report. Are you using prepared statements? If yes then
when the reconnect is done that prepared statement handler is unknown.
Otherwise could you please provide a complete test case which shows that
behavior. Thanks in advance.
[10 Apr 2008 7:16] Yan Nagler
Miguel, thanks a lot for a quick response!

It happens under following circumstances:
We hold the structure, containing both the connection itself and all the handles of the prepared statements, so we allocate each statement only once. You can see this structure below.
We do not use the DB intensively, so if there is a long period withou accessing the server, we get this timeout problem.

Attached the connection structure, mentioned above:

typedef struct _db_conn_t
{
	MYSQL* p_conn;

	
	// CM

	// Table: 'Params'
	MYSQL_STMT* p_stmt_add_param;
	MYSQL_STMT* p_stmt_get_param;
	MYSQL_STMT* p_stmt_foreach_param;
	MYSQL_STMT* p_stmt_set_param_val;
	MYSQL_STMT* p_stmt_get_param_val;

	// EM

	// Table: 'Events'
	MYSQL_STMT* p_stmt_add_event;
	MYSQL_STMT* p_stmt_remove_event;
	MYSQL_STMT* p_stmt_remove_events;	// All
	MYSQL_STMT* p_stmt_get_events;		// All
	MYSQL_STMT* p_stmt_count_events;
	MYSQL_STMT* p_stmt_foreach_event;
}
DB_CONN_T;

Waiting for you reply.
[16 Apr 2008 9:01] Susanne Ebrecht
You will get an overview of our timeout variables by using: 

mysql> show variables like '%timeout%';

I think, what's necessary for you is wait_timeout. Is it possible that your client will use connection because it idled longer then wait_timeout value?
[16 May 2008 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".