| Bug #29733 | Using stored procedures as prepared statements can cause aborted connections. | ||
|---|---|---|---|
| Submitted: | 11 Jul 2007 18:46 | Modified: | 25 Jul 2007 19:56 |
| Reporter: | Jeff Ward | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: C API (client library) | Severity: | S3 (Non-critical) |
| Version: | 5.1.19 | OS: | Windows (Only tested on windows) |
| Assigned to: | CPU Architecture: | Any | |
[13 Jul 2007 9:54]
Sveta Smirnova
Thank you for the report. I can not repeat described behaviour with current development sourses: always get expected result "Commands out of sync; you can't run this command now" Please upgrade to current version 5.1.20, try with it and inform us if problems is still repeatable in your environment.
[13 Jul 2007 14:19]
Jeff Ward
Still getting the same error on 5.1.20, windows client testing on a windows server located on the same box. However, I don't understand why the expected error is "Commands out of sync." I was pretty sure all the commands were being executed in the correct order.
[25 Jul 2007 15:10]
Jeff Ward
Put a database on a Win2k3 machine and it produces the commands out of sync error. XP was causing the dropped connections. Still don't understand why "Commands out of sync" is expected though.
[25 Jul 2007 19:47]
Jeff Ward
Found the reason I was getting the dropped connection. Dll hell issue. Also found that the reason it is giving me Commands out of sync is because, for some reason, SQL server thinks there are multiple result sets from the stored procedure, even thought it's only running a single SELECT. By adding mysql_next_result (simply to reset the status), the problem goes away, but I'm not sure what type of memory I might be leaking from that, since prepared statements aren't supposed to be able to support multiple result sets.
[25 Jul 2007 19:56]
Sveta Smirnova
Thank you for the feedback. Closed as "Can't repeat" because you found problem was your environment.
[3 Jul 2008 11:27]
MySQL Verification Team
see the last paragraph of this: http://dev.mysql.com/doc/refman/5.1/en/c-api-prepared-statement-problems.html
[28 Apr 2009 22:08]
Alexey Parshin
First of all, this bug still exist with the latest version of MySQL available to me at the moment (that is 5.1). Treating it as "Can't repeat" just makes the problem worst. Second, the documentantion incorrectly states that returning result sets from stored procedures isn't supported in C-API for prepared statements. The described by Jeff Ward way to read the result set from CALL-type queries does work. I'd humbly suggest fixing C-API for prepared statements so (if returning multiple result sets isn't supported) returning a single result set would clear the result set status.
[28 Apr 2009 22:09]
Alexey Parshin
I forgot to mention. I got the bug confirmation on RHEL 5 Linux.
Description: Using "call" statements to execute stored procedures that return data can cause connections to be dropped when attempting to execute a second procedure. The following test program almost always drops the connection and fails. I say almost always because, occasionally, the program is able to get through completely, but not often. Note, this problem goes away completely if you replace pSelectTestString with "SELECT * FROM DefaultTable;", which is why I'm specifying it as a stored procedure issue. How to repeat: C++ Test Program: ---------------------- #include <iostream> #include <windows.h> #include <mysql.h> const char* pSelectTestString = "call SelectStarTest"; const char* pInsertTestString = "call InsertRowTest(?, ?)"; const char* pMyString = "Testing"; void HandleError(MYSQL* psql) { std::cout << mysql_error(psql) << std::endl; } int main() { int ivar = 5; bool trueVar = true; MYSQL mysql; mysql_init(&mysql); mysql_real_connect(&mysql, "localhost", "connect_test", "connect_test", "ConnectionTestDb", MYSQL_PORT, NULL, CLIENT_MULTI_RESULTS | CLIENT_MULTI_STATEMENTS); mysql_autocommit(&mysql, false); MYSQL_STMT* pselectStatement = mysql_stmt_init(&mysql); mysql_stmt_prepare(pselectStatement, pSelectTestString, strlen(pSelectTestString)); if(mysql_stmt_execute(pselectStatement) == 0) { int irows = 0; while(!mysql_stmt_fetch(pselectStatement)) irows++; std::cout << irows << std::endl; mysql_stmt_free_result(pselectStatement); } else HandleError(&mysql); mysql_stmt_close(pselectStatement); MYSQL_STMT* pinsertStatement = mysql_stmt_init(&mysql); if(mysql_stmt_prepare(pinsertStatement, pInsertTestString, strlen(pInsertTestString)) != 0) HandleError(&mysql); // Can cause Lost connection to MySQL server durring query MYSQL_BIND bindings[2]; memset(bindings, 0, sizeof(MYSQL_BIND) * 2); bindings[0].buffer_type = MYSQL_TYPE_STRING; bindings[0].buffer_length = (unsigned long)strlen(pMyString); bindings[0].buffer = (void *)pMyString; bindings[1].buffer_type = MYSQL_TYPE_LONG; bindings[1].buffer_length = sizeof(int); bindings[1].buffer = &ivar; mysql_stmt_bind_param(pinsertStatement, &bindings[0]); if(mysql_stmt_execute(pinsertStatement) == 0) { int iidentity = 0; MYSQL_BIND myBind; memset(&myBind, 0, sizeof(MYSQL_BIND)); myBind.buffer = &iidentity; myBind.buffer_length = sizeof(int); myBind.buffer_type = MYSQL_TYPE_LONG; mysql_stmt_bind_result(pinsertStatement, &myBind); mysql_stmt_store_result(pinsertStatement); int fetchRet; do { fetchRet = mysql_stmt_fetch(pinsertStatement); if(fetchRet == 1) HandleError(&mysql); } while(fetchRet == 0); std::cout << iidentity << std::endl; if(mysql_stmt_free_result(pinsertStatement) != 0) HandleError(&mysql); } else HandleError(&mysql); // Can cause MySQL server has gone away mysql_stmt_close(pinsertStatement); mysql_rollback(&mysql); mysql_autocommit(&mysql, true); mysql_close(&mysql); return 0; } Stored procedures: -------------------------- CREATE PROCEDURE `InsertRowTest`(Param1 VARCHAR(50), Param2 INT) BEGIN INSERT INTO DefaultTable(Data1, Data2) VALUES( Param1, Param2 ); END $$ CREATE DEFINER=`connection_user`@`%` PROCEDURE `SelectStarTest`() BEGIN SELECT * FROM DefaultTable; END $$ Table: ---------------------------- CREATE TABLE "connectiontestdb"."defaulttable" ( "MyPK" int(11) NOT NULL AUTO_INCREMENT, "Data1" varchar(50) NOT NULL, "Data2" int(11) DEFAULT NULL, PRIMARY KEY ("MyPK") );