MySQL Bugs: #29812: Error 2014 on mysql_stmt_prepare with overlapping statement operations

Bug #29812	Error 2014 on mysql_stmt_prepare with overlapping statement operations
Submitted:	15 Jul 2007 23:22	Modified:	9 Aug 2007 9:52
Reporter:	Matthew Von-Maszewski	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	4.1.22 Embedded	OS:	Windows (2003 Server)
Assigned to:		CPU Architecture:	Any
Tags:	prepared statements

Description:
pseudo code of problem:

0. stmt1.mysql_stmt_init
1. stmt1.mysql_stmt_prepare
2. stmt1.mysql_stmt_execute
3. loop
3.1.  stmt1.mysql_stmt_fetch
3.2.  stmt2.mysql_stmt_init
3.3.  stmt2.mysql_stmt_prepare
3.4.  stmt2.mysql_stmt_execute
3.5.  stmt2.mysql_stmt_fetch ... error 2030
3.6.  stmt2.mysql_stmt_close 
4. (loop end)
5. stmt1.mysql_stmt_close

Discussion:

My assumption is that statements are independent objects.  Therefore I should be able to create and utilize more than one at a time (on a single thread).

It the above example, I am required to close the first statement to eliminate the 2030 error on the second statement (i.e. insert mysql_stmt_close after line 3.1. in the above psuedo code.

How to repeat:
See pseudo code above.

Thank you for the report.

Could you please provide example code?

My apologies for the delay.  Took time to get back to this bug and create the requested test program.

First, I must note an error in my original bug report.  The 2030 error does occur, but is a secondary error.  The first error is a 2014 on mysql_stmt_prepare.  

The following code demonstrates the problem.  Note the comments relating to mysql_stmt_close and the subsequent mysql_stmt_prepare.

// bug.cpp : Defines the entry point for the console application.
//

#include "stdafx.h"
#include "windows.h"

#if defined(WIN32) && !defined(_WIN32)
#define _WIN32
#endif

#ifndef _mysql_h_
#include "../mysql.h"
#endif

const char * server_options[]=
{
    "ignored",
    "--no-defaults",
    "--basedir=c:\\bug",
    "--datadir=c:\\bug/data",
    "--skip-innodb"
};

typedef struct
{
    unsigned m_FileId;
    unsigned m_FileSize;
    MYSQL_TIME m_WriteTime;
    char m_FilePath[256];
    char m_FileName[256];
    unsigned m_PartnerId;
    unsigned m_PatchSize;
    
    MYSQL_BIND bind[7];
    my_bool is_null[7];
    unsigned long length[7];

    MYSQL_STMT * stmt;

    void Init()
    {
        memset(bind, 0, sizeof(bind));

        bind[0].buffer_type=MYSQL_TYPE_LONG;
        bind[0].buffer=(char *)&m_FileId;
        bind[0].buffer_length=4;
        bind[0].is_null=&is_null[0];
        bind[0].length=&length[0];
        is_null[0]=false;
        length[0]=sizeof(m_FileId);

        bind[1].buffer_type=MYSQL_TYPE_LONG;
        bind[1].buffer=(char *)&m_FileSize;
        bind[1].buffer_length=4;
        bind[1].is_null=&is_null[1];
        bind[1].length=&length[1];
        is_null[1]=false;
        length[1]=sizeof(m_FileSize);

        bind[2].buffer_type=MYSQL_TYPE_TIMESTAMP;
        bind[2].buffer=(char *)&m_WriteTime;
        bind[2].buffer_length=4;
        bind[2].is_null=&is_null[2];
        bind[2].length=&length[2];
        is_null[2]=false;
        length[2]=sizeof(m_WriteTime);

        bind[3].buffer_type=MYSQL_TYPE_STRING;
        bind[3].buffer=(char *)&m_FilePath;
        bind[3].buffer_length=sizeof(m_FilePath);
        bind[3].is_null=&is_null[3];
        bind[3].length=&length[3];
        is_null[3]=false;
        length[3]=sizeof(m_FilePath);

        bind[4].buffer_type=MYSQL_TYPE_STRING;
        bind[4].buffer=(char *)&m_FileName;
        bind[4].buffer_length=sizeof(m_FileName);
        bind[4].is_null=&is_null[4];
        bind[4].length=&length[4];
        is_null[4]=false;
        length[4]=sizeof(m_FileName);

        bind[5].buffer_type=MYSQL_TYPE_LONG;
        bind[5].buffer=(char *)&m_PartnerId;
        bind[5].buffer_length=4;
        bind[5].is_null=&is_null[5];
        bind[5].length=&length[5];
        is_null[5]=false;
        length[5]=sizeof(m_PartnerId);

        bind[6].buffer_type=MYSQL_TYPE_LONG;
        bind[6].buffer=(char *)&m_PatchSize;
        bind[6].buffer_length=4;
        bind[6].is_null=&is_null[6];
        bind[6].length=&length[6];
        is_null[6]=false;
        length[6]=sizeof(m_PatchSize);

    };
} Record_t;

int _tmain(int argc, _TCHAR* argv[])
{
    MYSQL * m_Conn, * ret_ptr;                // mysql's internal connection info,
    const char * str;
    int ret_val;

//    mysql_debug("d:t:i:0,c:/mysqld_embedded.trace");

    ret_val=mysql_library_init(5, (char**)server_options, NULL);

    m_Conn=mysql_init(NULL);

    ret_val=mysql_options(m_Conn, MYSQL_OPT_USE_EMBEDDED_CONNECTION, NULL);

    ret_ptr=mysql_real_connect(m_Conn, NULL, NULL, NULL, NULL,
                                           0, NULL, 0);

    str="create database IF NOT EXISTS bug";
    ret_val=mysql_real_query(m_Conn, str, (long)strlen(str));

    str="use bug";
    ret_val=mysql_real_query(m_Conn, str, (long)strlen(str));

    str="create table bug_test"
        "("
        "FileId int unsigned auto_increment not null,"
        "FileSize int unsigned not null,"
        "WriteTime datetime not null,"
        "FilePath text not null,"
        "FileName text not null,"
        "PartnerId int unsigned,"
        "PatchSize int unsigned,"
        "FileData longblob,"
        "unique index name_index(FileSize, WriteTime, FileName(40), FilePath(80)),"
        "unique index id_index(FileId),"
        "index match_index(FileName(40), PatchSize, FileSize),"
        "index work_index(PatchSize, WriteTime)"
        ") AUTO_INCREMENT=1";

    ret_val=mysql_real_query(m_Conn, str, (long)strlen(str));

    str="Insert into bug_test (FileSize, WriteTime, FilePath, FileName, PartnerId, PatchSize) values "
        " (708608, 20070610062200, \"ver1\", \"UnitTest.exe\", 2, 58279), "
        " (716800, 20070614180432, \"ver2\", \"UnitTest.exe\", 3, 78538), "
        " (749568, 20070616195104, \"ver3\", \"UnitTest.exe\", 0, 0)";

    ret_val=mysql_real_query(m_Conn, str, (long)strlen(str));

    // setup first query
    Record_t query_one;

    query_one.Init();

    query_one.stmt=mysql_stmt_init(m_Conn);

    str="Select FileId, FileSize, WriteTime, FilePath, FileName, PartnerId, PatchSize from bug_test "
        " WHERE FileName=\"UnitTest.exe\" AND PatchSize=0 AND FileSize Between 50 and 750000";
    ret_val=mysql_stmt_prepare(query_one.stmt, str, (long)strlen(str));

    ret_val=mysql_stmt_bind_result(query_one.stmt, query_one.bind);

    ret_val=mysql_stmt_execute(query_one.stmt);
    
    ret_val=mysql_stmt_fetch(query_one.stmt);

    // without _stmt_close, error below.
    //   make the following line active, and second prepared statement works
//    ret_val=mysql_stmt_close(query_one.stmt);

    // attempt overlapped query
    Record_t query_two;

    query_two.Init();

    query_two.stmt=mysql_stmt_init(m_Conn);

    str="Select FileId, FileSize, WriteTime, FilePath, FileName, PartnerId, PatchSize from bug_test "
        " WHERE FileId=2";

    // without mysql_stmt_close above ... the following error happens on this line
    //    2014 ... Commands out of sync, you can't run this command now ...
    ret_val=mysql_stmt_prepare(query_two.stmt, str, (long)strlen(str));
    if (ret_val)
    {
        str=mysql_stmt_error(query_two.stmt);
        ret_val=mysql_stmt_errno(query_two.stmt);
    };

    ret_val=mysql_stmt_bind_result(query_two.stmt, query_two.bind);
    if (ret_val)
    {
        str=mysql_stmt_error(query_two.stmt);
        ret_val=mysql_stmt_errno(query_two.stmt);
    };

    ret_val=mysql_stmt_execute(query_two.stmt);
    if (ret_val)
    {
        str=mysql_stmt_error(query_two.stmt);
        ret_val=mysql_stmt_errno(query_two.stmt);
    };

    
    ret_val=mysql_stmt_fetch(query_two.stmt);
    if (ret_val)
    {
        str=mysql_stmt_error(query_two.stmt);
        ret_val=mysql_stmt_errno(query_two.stmt);
    };

    return 0;
}

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

You should free resources aquired by first prepared statement before using second. See also http://dev.mysql.com/doc/refman/5.0/en/mysql-stmt-free-result.html