| Bug #21562 | simple select "connection lost during query" | ||
|---|---|---|---|
| Submitted: | 10 Aug 2006 10:09 | Modified: | 18 Sep 2006 13:53 |
| Reporter: | [ name withheld ] | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: C API (client library) | Severity: | S1 (Critical) |
| Version: | 5.0.22 | OS: | Linux (Fedora Core 5) |
| Assigned to: | Magnus Blåudd | CPU Architecture: | Any |
[11 Aug 2006 19:23]
roger andrews
I see the same on windwos selecting just a few rows when using a stored procedure - no ryhme or reason - no timeout being exceeded.
[12 Aug 2006 10:53]
[ name withheld ]
Hi Andrew, while trying to solve my problem I read somewhere a workaround for your problem. Continue searching the bug list. I hope you find it. It had something to do about a stored procedure returning more data than expected so an extra read must be done... Something like that. Good luck! Anybody has any clues on my bug? Thanks
[26 Aug 2006 11:39]
Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 5.0.24, and inform about the results.
[3 Sep 2006 9:42]
[ name withheld ]
There is no rpm for the 5.0.24 on FC5 yet. So can't upgrade. How is it that you don't provide rpms for FC5. I think it's quite widely used? Anyway, I will report once 5.0.24 is available. Am I the only one with that problem?
[14 Sep 2006 17:34]
Magnus Blåudd
Loading a table with 50000 records and then running the supplied testprogram reproduces the problem.
[14 Sep 2006 17:36]
Magnus Blåudd
Counter: 6859 Counter: 6860 Counter: 6861 Counter: 6862 Counter: 6863 Counter: 6864 Counter: 6865 Counter: 6866 Error: Lost connection to MySQL server during query Counter: 6867
[14 Sep 2006 18:15]
Magnus Blåudd
T@-1296610: | | | | | | | <_mi_read_rnd_dynamic_record T@-1296610: | | | | | | | info: select cond 0x0 T@-1296610: | | | | | | | >end_send T@-1296610: | | | | | | | | >select_send::send_data T@-1296610: | | | | | | | | <select_send::send_data T@-1296610: | | | | | | | | >Protocol::write T@-1296610: | | | | | | | | <Protocol::write T@-1296610: | | | | | | | | packet_header: Memory: 0xb2b72cb0 Bytes: (4) 1B 00 00 A7 T@-1296610: | | | | | | | | >vio_is_blocking T@-1296610: | | | | | | | | | exit: 0 T@-1296610: | | | | | | | | <vio_is_blocking T@-1296610: | | | | | | | | >net_real_write T@-1296610: | | | | | | | | | >query_cache_insert T@-1296610: | | | | | | | | | <query_cache_insert T@-1296610: | | | | | | | | | >vio_write T@-1296610: | | | | | | | | | | enter: sd: 32, buf: 0x0x9e2ffb8, size: 16384 T@-1296610: | | | | | | | | | | vio_error: Got error on write: 11 T@-1296610: | | | | | | | | | | exit: -1 T@-1296610: | | | | | | | | | <vio_write T@-1296610: | | | | | | | | | >thr_alarm T@-1296610: | | | | | | | | | | enter: thread: T@-1296610 sec: 60 T@-1296610: | | | | | | | | | | info: reschedule T@-1296610: | | | | | | | | | <thr_alarm T@-1296208: | >process_alarm T@-1296208: | | info: sig: 14 active alarms: 2 T@-1296610: | | | | | | | | | >vio_blocking T@-1296610: | | | | | | | | | | enter: set_blocking_mode: 1 old_mode: 0 T@-1296208: | <process_alarm T@-1296610: | | | | | | | | | | exit: 0 T@-1296610: | | | | | | | | | <vio_blocking T@-1296610: | | | | | | | | | >vio_write T@-1296610: | | | | | | | | | | enter: sd: 32, buf: 0x0x9e2ffb8, size: 16384 T@-1296208: | >process_alarm T@-1296208: | | info: sig: 14 active alarms: 2 T@-1296208: | | info: sending signal to waiting thread T@-1296208: | <process_alarm T@-1296610: | | | | | | | | | | vio_error: Got error on write: 4 T@-1296610: | | | | | | | | | | exit: -1 T@-1296610: | | | | | | | | | <vio_write T@-1296610: | | | | | | | | | >thr_end_alarm T@-1296610: | | | | | | | | | <thr_end_alarm T@-1296610: | | | | | | | | | >vio_blocking T@-1296610: | | | | | | | | | | enter: set_blocking_mode: 0 old_mode: 1 T@-1296610: | | | | | | | | | | exit: 0 T@-1296610: | | | | | | | | | <vio_blocking T@-1296610: | | | | | | | | <net_real_write It appears that first error 11 and then error 4 is returnd when net_real_write tries to write a 16k buffer to the socket. It has written several times before during the query but now it's most likely full since the client hasn't been reading more than a few records during this time. [msvensson@host mysql-test]$ ../extra/perror 11 OS error code 11: Resource temporarily unavailable [msvensson@host mysql-test]$ ../extra/perror 4 OS error code 4: Interrupted system call
[18 Sep 2006 12:18]
Magnus Blåudd
Hi, I have now looked carefully at the trace files and the code in 'net_real_write' and it looks like this is the expected behaviour. When the server get the order to execute the SELECT it will do that and start sending the rows out onto the network. Since the client is not reading much from the network on the other side, the send and rececive buffers will be full and eventually the MySQL server cant' write anymore. At this point it can't start to sleep and wait for the netwaork buffers to become writeable again since that would mean other threads working with the same table(s) would block. It will however retry the write a number of times before giving up and disconnecting this client, which potentially could have crashed. To workaround you can do one of trhe following solutions. 1. Call the 'mysql_store_result' function instead of 'mysql_use_result' before you start the record processing loop. The 'mysql_store_result' will then buffer all the rows of the query in memory allocated in the client and read. The loop will then read the records from memory. To avoid allocating too much client side memory do a select only of the primary keys for each record. 2. Create a temporary table that holds all the records you want to process. Then read just a couple of records from that table at a time with "SELECT * FROM tmp_table LIMIT 1000", process those records and then delete the proicessed records from the temporary table. When there are no more records in the temporary table all rows has been processed. 3. You could use a cursor from the MySQL Prepared Statement API, that would allow you to tell the server to send you records one by one. See http://dev.mysql.com/doc/refman/5.0/en/mysql-stmt-attr-set.html There are other ways this can be done too. Good Luck! Regards Magnus
[18 Sep 2006 12:37]
Magnus Blåudd
C++ file for reproducing test
Attachment: bug21562.cc (text/x-c++src), 2.09 KiB.
[18 Sep 2006 12:38]
Magnus Blåudd
Test file for running test case from mysql-test-run.pl
Attachment: bug21562.test (application/octet-stream, text), 266 bytes.
[18 Sep 2006 13:53]
[ name withheld ]
Thanks for your reply. I thought MySQL claimed to be capable of handling BIG databases: gigs or even teras big. Solutions 1 & 2 clearly can't be used for a 1 Tera database i.e. Solution 3 requires changes in code. I think MySQL should give this scenario further thought to compare to the big ones: I didn't have this problem on Informix. Thanks for your time and answer anyway.
[18 Sep 2006 14:00]
Magnus Blåudd
Fully agree, will have a look around to see what else can be found.
[18 Sep 2006 17:09]
roger andrews
THis post shows that its nothing to do with large tables of data - and prepared statements dont fix the problem.
We need a MySQL engineer looking at this.
#include <mysql.h>
#include <mysqld_error.h>
#define al_mem(S) malloc(S)
#define NULL (char *)0
#define nil 0
#define false 0
#define true 1
#define call_db_null(RP,FUNC) { RP = FUNC; if (!RP) goto errexit; }
#define call_db(FUNC) { rc = FUNC; if (rc) goto errexit; }
#define call_db_void(FUNC) { FUNC; }
#define allign8(off) off = ((off + 7) / 8) * 8
typedef struct { my_bool nul;
unsigned int len;
} ils;
int main(argc,argv)
int argc;
char **argv;
{
MYSQL *cnp = 0; /* connection handle
*/
char **cpa[3];
int rc;
MYSQL_STMT *stp = 0;
MYSQL_STMT *st2p = 0;
MYSQL_STMT *st3p = 0;
MYSQL_STMT *erp = 0;
MYSQL_BIND *pdp,*pd2p,*bdp,*bd2p;
MYSQL_RES *rsp = 0;
MYSQL_FIELD *fldp;
ils *ilp,*il2p,*il3p,*il4p;
char buf[100];
char buf2[100];
char buf3[100];
char buf4[32767];
int id;
int off;
int i;
int cnt;
char *stnp = nil;
if (argc < 4) { printf("Expected uid pwd dbn [svr]\n"); exit(1); }
if (!(cnp=mysql_init(cnp))) { printf("mysql_init failed\n"); exit(1); }
if (!mysql_real_connect(cnp,(argc > 4 ? argv[4] : NULL),
argv[1],argv[2],argv[3],
0,NULL,CLIENT_MULTI_STATEMENTS))
goto errexit;
call_db(mysql_autocommit(cnp,false))
/******************************************************************************/
/* Initialize all the statement handles
*/
/******************************************************************************/
call_db_null(stp, mysql_stmt_init(cnp))
call_db_null(st2p,mysql_stmt_init(cnp))
call_db_null(st3p,mysql_stmt_init(cnp))
if ((pdp = (MYSQL_BIND *)al_mem(3*sizeof(MYSQL_BIND))) == nil)
goto mem_err;
if ((ilp = (ils *)al_mem(3*sizeof(ils))) == nil)
goto mem_err;
/******************************************************************************/
/* Send down the stored procedure variables
*/
/******************************************************************************/
erp = stp;
strcpy(buf,"set @v3=?,@v2=?,@v1=?");
call_db(mysql_stmt_prepare(stp,buf,strlen(buf)))
id = 1;
pd2p = pdp;
il2p = ilp;
pd2p->buffer_type = MYSQL_TYPE_LONG;
pd2p->buffer = (char *)&id;
pd2p->buffer_length = 4;
pd2p->length = (unsigned long *)&(il2p->len);
pd2p->is_null = &(il2p->nul);
il2p->len = 4;
il2p->nul = false;
pd2p++;
il2p++;
pd2p->buffer_type = FIELD_TYPE_STRING;
pd2p->buffer = 0;
pd2p->buffer_length = 0;
pd2p->length = (unsigned long *)&(il2p->len);
pd2p->is_null = &(il2p->nul);
il2p->nul = true;
pd2p++;
il2p++;
pd2p->buffer_type = FIELD_TYPE_STRING;
pd2p->buffer = 0;
pd2p->buffer_length = 0;
pd2p->length = (unsigned long *)&(il2p->len);
pd2p->is_null = &(il2p->nul);
il2p->nul = true;
call_db(mysql_stmt_bind_param(stp,pdp))
call_db(mysql_stmt_execute(stp))
/******************************************************************************/
/* Execute the stored procedure
*/
/******************************************************************************/
strcpy(buf2,"CALL read_salary(@v3,@v2,@v1)");
erp = st2p;
printf("Prepare/execute the CALL statement\n");
call_db(mysql_stmt_prepare(st2p,buf2,strlen(buf2)))
call_db(mysql_stmt_execute(st2p))
call_db(mysql_stmt_store_result(st2p)) /* otherwise only one open rs */
/******************************************************************************/
/* Fetch the results
*/
/******************************************************************************/
strcpy(buf3,"select @v2,@v1");
erp = st3p;
printf("Prepare/execute the select statement\n");
call_db(mysql_stmt_prepare(st3p,buf3,strlen(buf3)))
printf("Get the result metadata\n");
call_db_null(rsp,mysql_stmt_result_metadata(st3p))
printf("%d result columns\n",mysql_num_fields(rsp));
call_db(mysql_stmt_execute(st3p))
if ((bdp=(MYSQL_BIND *)al_mem(2*sizeof(MYSQL_BIND))) == nil)
goto mem_err;
if ((il3p = (ils *)al_mem(3*sizeof(ils))) == nil)
goto mem_err;
memset(bdp, 0,2*sizeof(MYSQL_BIND));
memset(il3p,0,3*sizeof(ils));
bd2p = bdp;
il4p = il3p;
for (i=off=0;i<2;i++,bd2p++,il4p++) {
call_db_null(fldp,mysql_fetch_field_direct(rsp,0))
bd2p->buffer_type = fldp->type;
bd2p->buffer = buf4 + off;
bd2p->buffer_length = fldp->length;
bd2p->length = (unsigned long *)&(il4p->len);
bd2p->is_null = &(il4p->nul);
off += fldp->length;
allign8(off);
}
call_db(mysql_stmt_bind_result(st3p,bdp))
rc = mysql_stmt_fetch(st3p);
mysql_free_result(rsp);
while ((rc=mysql_stmt_fetch(st3p)) != MYSQL_NO_DATA)
if (rc && rc != MYSQL_DATA_TRUNCATED) goto errexit;
/******************************************************************************/
/* Now repeat 1000 times
*/
/******************************************************************************/
for (cnt=1000;cnt;cnt--) {
if (!(cnt % 50)) printf("cnt: %d\n",cnt);
erp = stp;
stnp = "mysql_stmt_bind_param";
call_db(mysql_stmt_bind_param(stp,pdp))
stnp = "mysql_stmt_execute";
call_db(mysql_stmt_execute(stp))
erp = st2p;
stnp = "mysql_stmt_execute(2)";
call_db(mysql_stmt_execute(st2p))
stnp = "mysql_stmt_store_result";
call_db(mysql_stmt_store_result(st2p)) /* otherwise only one open rs */
erp = st3p;
stnp = "mysql_stmt_bind_result";
call_db(mysql_stmt_bind_result(st3p,bdp))
stnp = "mysql_stmt_execute(3)";
call_db(mysql_stmt_execute(st3p))
stnp = "mysql_stmt_fetch";
rc = mysql_stmt_fetch(st3p);
while ((rc=mysql_stmt_fetch(st3p)) != MYSQL_NO_DATA)
if (rc && rc != MYSQL_DATA_TRUNCATED) goto errexit;
}
mysql_close(cnp);
exit(0);
errexit:;
printf("Error!!\n");
if (mysql_stmt_errno(erp))
printf("Errno: %d, %s\n",mysql_stmt_errno(erp),mysql_stmt_error(erp));
if (stnp) printf("Statement was %s\n",stnp);
exit(1);
mem_err:;
printf("Memory error\n");
exit(1);
}
[19 Sep 2006 13:16]
Magnus Blåudd
Roger, could you please open a separate bug report for your problem. "2013 Lost connection to server" is a symptom of many various problems. If I understand you correctly there are no sleeps involved in your problem and you are using windows?
[19 Sep 2006 16:50]
roger andrews
magnus I filled 22483 but for this. howewver the suymptons are the same - using stored procedures...
[28 Mar 2007 14:46]
Martin Marinov
Hello
I have also some problem with the connection. The query returns 271 rows as result. The data base is small, but when I try to fetch rows, connection is lost.
I use Eclipse Version: 3.2.2
MySQL 5.0.37
mingw32 3.80.0-3 to build the project for Windows.
Here is the problem code
vector< string > executeQuery(mysql_params params, string query)
{
vector< string > vecDocs;
MYSQL conn;
MYSQL_RES *res;
MYSQL_ROW row;
mysql_init(&conn);
if (!mysql_real_connect(&conn, params.server,
params.user, params.pass, params.sid, params.port, NULL, 0)) {
cout << "Failed 1 - Cant connect to database!" << endl;
fprintf(stderr, "%s\n", mysql_error(&conn));
exit(0);
}
// string query = "SELECT doc_parsed_name FROM opt_documents";
/* execute SQL query */
if (mysql_real_query(&conn, query.c_str(), strlen(query.c_str()))) {
cout << "Failed 2 - Query is not executed!" << endl;
fprintf(stderr, "%s\n", mysql_error(&conn));
exit(0);
}
res = mysql_use_result(&conn);
while (row = mysql_fetch_row(res))
{
//here the connection is lost!
vecDocs.push_back(row[0]);
}
/* Release memory used to store results */
mysql_free_result(res);
mysql_close(&conn);
return vecDocs;
}
It passes 3 or 4 iterations and thats all. I am not sure if the problem is in mySQL or in MingW. When the program is in debug mode evrything seems to be OK until mysql_fetch_row(res) appears. It returns at best five rows of 271 and the program crushes.
When I use mysql_store_result(), the number of the rows is proper, so mysql_num_rows() returns the right value, but mysql_fetch_row(res) fails again after several iterations.
Does anyone suggest what could be the problem?
Thanks in advance!
M.C.
Description: Hi and thanks in advance for viewing this bug that is holding me back!! I have a table with 45K rows. If I do a select with the C/C++ API and everything is fine and I get 45K rows back. If I do some work for each row I retrieve before retrieving the next one I get a 'Lost connection to MySQL server during query' when I reach around 5,3K or 5,7K rows returned. So the obvious is to think that this is a timeout problem. I have checked values of connec_timeout, interactive_timeout, net_read_timeout, slave_net_timeout and wait_timeout and no value is near the 20 minutes it takes for the error occur. So why a delay in the row retrieval causes the connection to be dropped??? Is there some hidden timeout that we can't configure preventing long queries?? MySQL 5.0.22 gcc-c++-4.1.1-1.fc5 libgcc-4.1.1-1.fc5 gcc-4.1.1-1.fc5 Fedora Core 5 P.D.:No, I am not hitting the 8 hours query time. I get the error after 20 minutes aprox. P.D.: No, no max_connections hit. I only use one connection P.D.: No, no max_packet_size or similar as without the delay everything is alright... How to repeat: //Try removing the usleep for some big table of yours and it works //Put some delay and the connection is dropped #include <string> #include "mysql/mysql.h" using namespace std; int main(int argc, char *argv[]) { MYSQL* mysqlInitStruct = NULL; MYSQL_RES* mysqlRes = NULL; MYSQL_ROW res; char* mysqlErrorText; size_t counter = 0; if ( (mysqlInitStruct = mysql_init( NULL )) == NULL ) exit(1); if ( mysql_real_connect( mysqlInitStruct, "localhost", "xxxx", "xxxx", "xxxx", 0, NULL, 0) == NULL ) exit(2); if( mysql_real_query(mysqlInitStruct, "select * from xxxxx", 19) ) exit(3); mysqlRes = mysql_use_result( mysqlInitStruct ) ; for ( counter=0 ; res != NULL ; counter++ ) { usleep(125000); if ( (res=mysql_fetch_row( mysqlRes )) == NULL ) { if(mysql_errno(mysqlInitStruct)) { // an error occurred mysqlErrorText = strdup(mysql_error(mysqlInitStruct)); printf("Error: %s\n",mysqlErrorText); printf("Counter: %lu\n",counter); mysql_free_result( mysqlRes ); mysql_close( mysqlInitStruct ); exit(4); } else { mysql_free_result( mysqlRes ); mysql_close( mysqlInitStruct ); printf("Counter: %lu\n",counter); exit(5); } } printf("Counter: %lu\n",counter); } mysql_free_result( mysqlRes ); mysql_close( mysqlInitStruct ); }