Bug #3412 Prepared statements returning rows fail in embedded database
Submitted: 7 Apr 2004 7:09 Modified: 1 Jun 2004 20:02
Reporter: Richard Tibbetts Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Embedded Library ( libmysqld ) Severity:S1 (Critical)
Version:4.1.2-alpha-nightly-20040402 OS:Linux (Linux)
Assigned to: Alexey Botchkov CPU Architecture:Any

[7 Apr 2004 7:09] Richard Tibbetts
Description:
Prepared select statements, when executed against an embedded server, return no rows.

I built a small test program. When linked against libmysqlclient and run against a regular server from this snapshot, it works fine. When linked against libmysqld and run it fails, because the query returns no rows.

I believe that this problem did not exist in the snapshot from 2004-01-28. I belive that it did exists in the snapshot from 2004-03-15, though I have not yet validated that with this test program.

Please advise ASAP when we might expect this bug to be fixed. If it will not be done in the next week or so, we need to look at some contingency plans.

How to repeat:
Use the following test program, linked against libmysqld. You should see the message "ERROR: Got MYSQL_NO_DATA rather than the first row expected."

#include <mysql.h>
#include <stdio.h>

/// Test that a prepared query returning results works properly
/// using raw mysql_ API calls. This will notice regressions or
/// API breakage on MySQL's part.

#define CREATE_TEST_DATABASE "CREATE DATABASE IF NOT EXISTS test"
#define DROP_SAMPLE_TABLE "DROP TABLE IF EXISTS test_table"
#define CREATE_SAMPLE_TABLE "CREATE TABLE test_table(f1 INT, f2 INT)"
#define INSERT_SAMPLE "INSERT INTO test_table(f1,f2) VALUES(1,1),(2,2),(3,3)"
#define SELECT_SAMPLE "SELECT f2 FROM test_table WHERE f1 = 1"

void error(const char *when) {
    fprintf(stderr, "ERROR: %s\n", when);
    mysql_server_end();
    exit(1);
}

void my_mysql_error(MYSQL *mysql, const char *when) {
    fprintf(stderr, "ERROR: %s: %s\n", when, mysql_error(mysql));
    mysql_server_end();
    exit(1);
}

void my_query(MYSQL *mysql, const char *query) {
    if (mysql_query(mysql, query))
        my_mysql_error(mysql, "simple query failed");
}

int main(int argc, char **argv) {
    MYSQL mysql;

    mysql_server_init(argc, argv, NULL);

    mysql_init(&mysql);
    mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,"your_prog_name");
    if (!mysql_real_connect(&mysql,"localhost","","","test",0,NULL,0))
        my_mysql_error(&mysql, "failed to connect to database");

    my_query(&mysql, CREATE_TEST_DATABASE);
    my_query(&mysql, DROP_SAMPLE_TABLE);
    my_query(&mysql, CREATE_SAMPLE_TABLE);
    my_query(&mysql, INSERT_SAMPLE);

    MYSQL_STMT *stmt = mysql_prepare(&mysql, SELECT_SAMPLE, strlen(SELECT_SAMPLE));
    if (!stmt)
        my_mysql_error(&mysql, "failed to prepare");
                
    if (mysql_param_count(stmt) != 0)
        error("param count not zero");

    MYSQL_RES *prepared_result = mysql_get_metadata(stmt);
    if (!prepared_result)
        my_mysql_error(&mysql, "failed to get prepared result");

    if (mysql_num_fields(prepared_result) != 1)
        error("result count not one");

    MYSQL_BIND result;
    int result_value;
    unsigned long result_length;
    my_bool result_is_null;
    result.buffer_type = MYSQL_TYPE_LONG;
    result.buffer = (char *)&result_value;
    result.length = &result_length;
    result.is_null = &result_is_null;
    result.buffer_length = sizeof(int);
    
    if (mysql_bind_result(stmt, &result))
        my_mysql_error(&mysql, "failed to bind result");

    if (mysql_execute(stmt))
        my_mysql_error(&mysql, "failed to execute");

    if (mysql_stmt_store_result(stmt))
        my_mysql_error(&mysql, "failed to store result");

    int ret = mysql_fetch(stmt);
    if (ret == MYSQL_NO_DATA)
        error("Got MYSQL_NO_DATA rather than the first row expected.");
    else if (ret != 0)
        my_mysql_error(&mysql, "bad return val from mysql_fetch");

    if (result_value != 1)
        error("Wrong result value.");

    ret = mysql_fetch(stmt);
    if (ret != MYSQL_NO_DATA)
        error("Didn't get MYSQL_NO_DATA but there should only be one row.");

    mysql_server_end();
    printf("success\n");
    return 0;
}

Suggested fix:
If it will help get this bug fixed sooner, I can work on fixing it myself. Please let me know what the plan is on your end.
[14 Apr 2004 14:51] Timothy Smith
Hi, I tried your program under Linux 2.4.25 kernel, libc.so.6.  I pulled the lastest source code for 4.1.2 today (2004-04-14).

I saved your program as prep.c, compiled and ran it as follows.  Can you please provide similar information on how you build and run your test program?

By the way, I used the BUILD/compile-pentium-debug script in the source tree to build MySQL 4.1.2-alpha.  I didn't specify any flags (I forgot to add --with-embedded-server), so I had to cd into libmysqld and type 'make' after the build finished.  I did not install the build; instead I set up a directory with symbolic links to make it look like an installed version of MySQL.

tsmith@build:~/m/41m/t> rm data/build.log
tsmith@build:~/m/41m/t> gmake clean
rm -f prep t prep.o t.o *.core
tsmith@build:~/m/41m/t> gmake
g++ -g -W -Wall -fno-exceptions -fno-rtti -felide-constructors -I../include/mysql -D_THREAD_SAFE -D_REENTRANT   prep.c  -L../lib -lmysqld -lz -lm -lcrypt -lpthread -o prep
tsmith@build:~/m/41m/t> ./prep --basedir=.. --datadir=./data --log
success
tsmith@build:~/m/41m/t> cat data/build.log
mysql_embedded, Version: 4.1.2-alpha-embedded-log. embedded library
Time                 Id Command    Argument
040414 23:40:28       1 Query       CREATE DATABASE IF NOT EXISTS test
                      1 Query       DROP TABLE IF EXISTS test_table
                      1 Query       CREATE TABLE test_table(f1 INT, f2 INT)
                      1 Query       INSERT INTO test_table(f1,f2) VALUES(1,1),(2,2),(3,3)
                      1 Prepare     SELECT f2 FROM test_table WHERE f1 = 1
tsmith@build:~/m/41m/t>
[15 Apr 2004 18:12] Timothy Smith
This bug doesn't show up if I use ./BUILD/compile-pentium-debug --with-embedded-server; but it *does* show up if I use ./BUILD/compile-pentium --with-embedded-server.

I haven't taken it beyond that, yet.
[4 May 2004 13:58] Alexey Botchkov
I tried both BUILD/compile_pentium_debug and BUILD/compile_pentium (--with-embedded-server) on my Linux with latest 4.1 tree.
Both worked ok with the example.
[6 May 2004 22:22] Dean Ellis
I have re-tested this and am verifying it against 4.1.2 current (as I write).

As noted, the problem only occurs against libmysqld; it succeeds against the normal server.
[28 May 2004 12:36] Alexey Botchkov
bk commit - 4.1 tree (hf:1.1860) BUG#3412
[1 Jun 2004 20:02] Alexey Botchkov
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html