| Bug #22483 | Connection lost during query using stored procedure and fetching results | ||
|---|---|---|---|
| Submitted: | 19 Sep 2006 16:48 | Modified: | 21 Sep 2006 16:23 |
| Reporter: | roger andrews | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.0.24 | OS: | Windows (windows, linux) |
| Assigned to: | CPU Architecture: | Any | |
[20 Sep 2006 20:01]
Sveta Smirnova
Thank you for the report. Could you please provide output of SHOW CREATE PROCEDURE read_salary \G statement?
[20 Sep 2006 20:07]
roger andrews
CREATE procedure read_salary (_id INT, OUT _deptnum INT , OUT _salary NUMERIC) BEGIN SELECT deptnum,salary FROM org1 WHERE deptnum = _id; END
[21 Sep 2006 11:07]
Sveta Smirnova
To get result to OUT params of read_salary procedure I changed its definition to: CREATE DEFINER=`root`@`localhost` PROCEDURE `read_salary`(_id INT, OUT _deptnum INT , OUT _salary NUMERIC) BEGIN SELECT deptnum into _deptnum FROM org1 WHERE deptnum = _id; SELECT salary into _salary FROM org1 WHERE deptnum = _id; END Also I created table org1 as: CREATE TABLE `org1` ( `deptnum` int(11) default NULL, `salary` decimal(10,0) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; With these data I can not repeat error using current BK sources: $./bug22483 sveta ******** test Prepare/execute the CALL statement Prepare/execute the select statement Get the result metadata 2 result columns cnt: 1000 cnt: 950 cnt: 900 cnt: 850 cnt: 800 cnt: 750 cnt: 700 cnt: 650 cnt: 600 cnt: 550 cnt: 500 cnt: 450 cnt: 400 cnt: 350 cnt: 300 cnt: 250 cnt: 200 cnt: 150 cnt: 100 cnt: 50 If you think my test is incorrect, please, provide accurate output of SHOW CREATE PROCEDURE read_salary \G, SHOW CREATE TABLE org1 \G, CALL read_salary(1, @v1, @v2); and SELECT @v1, @v2; statements issued in mysql command line client. Then reopen the bug.
[21 Sep 2006 16:23]
roger andrews
unfortunately this is not normal stored procedure syntax - and would be inefficient to use on any database(why execute two select statements to return 2 columns from the same database row). My Sql specialists all agree this synatx to be very unusual. Please fix the original bug as reported. rememeber the statement works - it just randomly crashes the database. Many other uses are reporting stored procedure issues from multiple sources - and a well formed stored procedure should work. here is the create table statement to re-try CREATE TABLE ORG1 (DEPTNUM int NOT NULL, DEPTNAME char(6) NOT NULL MANAGER int NOT NULL, DIVISION char(15) NOT NULL, HRDATE date, SALARY decimal(8,2), ROW_TIMESTAMP TIMESTAMP ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP) create unique index org1_ix ON org1(deptnum)
[21 Sep 2006 16:35]
Sveta Smirnova
Results with "original" stored procedure: $./bug22483 sveta ***** test Prepare/execute the CALL statement Prepare/execute the select statement Error!! Errno: 2014,Commands out of sync; you can't run this command now As you can see there is not crash.
Description: executing a stored procedure that returns rows will likely cause "connection lost" occurs on linux (redhat) or windows related issue describing similar problems is 21562. How to repeat: run this test program 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); }