| 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: | |
| 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 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".
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; }