| Bug #24645 | Error with mysql_stmt_bind_param | ||
|---|---|---|---|
| Submitted: | 28 Nov 2006 5:42 | Modified: | 20 Mar 2007 15:45 |
| Reporter: | Mike Rollins | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 4.1.21, 4.1.22 | OS: | Linux (linux) |
| Assigned to: | CPU Architecture: | Any | |
[28 Nov 2006 5:46]
Mike Rollins
Create Tables
Attachment: create_tables.bash (application/octet-stream, text), 674 bytes.
[28 Nov 2006 5:46]
Mike Rollins
Makefile
Attachment: Makefile (application/octet-stream, text), 410 bytes.
[28 Nov 2006 5:47]
Mike Rollins
Example C program
Attachment: sqltest.c (application/octet-stream, text), 4.26 KiB.
[28 Nov 2006 8:13]
Valeriy Kravchuk
Thank you for a problem report. Please, send the exact configure command line you had used.
[29 Nov 2006 0:04]
Mike Rollins
The configure command is as follows: ./configure --prefix=/usr/local/mysql --with-mysqld-user=mysql I will attach a copy of the config.log file.
[29 Nov 2006 0:07]
Mike Rollins
config.log file
Attachment: config.log.gz (application/x-gzip, text), 31.44 KiB.
[3 Dec 2006 9:27]
Valeriy Kravchuk
Please, try to repeat with a newer version, 4.1.22, and inform about the results.
[3 Dec 2006 13:15]
Mike Rollins
I reproduced the problem with 4.1.22
[3 Dec 2006 22:15]
Mike Rollins
This is interesting. If I add the --with-debug to my configure command, the resulting binary will work correctly. I was going to see what I could learn from the trace output and realized my test program worked. I then configured/compiled/installed without the debug option and the problem came back.
[4 Dec 2006 20:26]
Sveta Smirnova
Thank you for the report. I can not repeat it using binaries compiled with --debug option and without it.
[5 Dec 2006 2:39]
Mike Rollins
I did some digging in the code and have found the following:
I added a print statement to the set_param_int32 function in the file sql/sql_prepare.cc.
static void set_param_int32(Item_param *param, uchar **pos, ulong len)
{
int32 value;
uchar *mike = *pos;
for (ulong i=0; i<len; i++)
fprintf(stderr,"MIKE set_param_int32 pos[%d]: %x\n",i,*(mike+i));
When running the program I see the following:
MIKE set_param_int32 pos[0]: 0
MIKE set_param_int32 pos[1]: 1
MIKE set_param_int32 pos[2]: 3
MIKE set_param_int32 pos[3]: 0
MIKE set_param_int32 pos[4]: 1
MIKE set_param_int32 pos[5]: 0
MIKE set_param_int32 pos[6]: 0
MIKE set_param_int32 pos[7]: 0
When 0130 is cast to a long, the value becomes 196864. This is what I see in the query log. The next four bytes 1000 is the value of my parameter.
It appears that this function should be called without the first four bytes, just the last four bytes of this packet.
[6 Dec 2006 10:21]
Sveta Smirnova
Please indicate accurate platform you use
[6 Dec 2006 11:31]
Mike Rollins
cat /proc/version Linux version 2.6.18 (rollins@bobby.goodhotdogs.com) (gcc version 4.1.0 20060304 (Red Hat 4.1.0-3)) #1 SMP Wed Nov 15 20:16:34 EST 2006 [rollins@bobby bug]$ uname -s Linux [rollins@bobby bug]$ uname -r 2.6.18 [rollins@bobby bug]$ uname -v #1 SMP Wed Nov 15 20:16:34 EST 2006 [rollins@bobby bug]$ uname -m i686 [rollins@bobby bug]$ uname -p i686 [rollins@bobby bug]$ uname -i i386 [rollins@bobby bug]$ uname -o GNU/Linux
[7 Dec 2006 3:27]
Mike Rollins
I found something interesting. The following three steps will show what I have found:
Step 1:
Modify the file: sql/sql_prepare.cc
Find the function: insert_params_withlog
After the DBUG_ENTER statement add the following:
fprintf(stderr,"MIKE: insert_params_withlog: read_pos = %x\n",read_pos);
for (uchar* mike = read_pos; mike <=data_end; mike++)
fprintf(stderr,"MIKE: insert_params_withlog: %x\n",*mike);
The output of the test program will yield the following in the log file:
MIKE: insert_params_withlog: read_pos = 897048a
MIKE: insert_params_withlog: 0
MIKE: insert_params_withlog: 1
MIKE: insert_params_withlog: 3
MIKE: insert_params_withlog: 0
MIKE: insert_params_withlog: 1
MIKE: insert_params_withlog: 0
MIKE: insert_params_withlog: 0
MIKE: insert_params_withlog: 0
MIKE: insert_params_withlog: 0
Step 2:
Find the function: setup_conversion_functions
The end of the function is as follows:
}
}
*data= read_pos;
DBUG_RETURN(0);
}
Add a print statement
}
fprintf(stderr,"MIKE: E: setup_conversion_functions %x\n",read_pos);
}
*data= read_pos;
DBUG_RETURN(0);
}
The output is now as follows:
MIKE: E: setup_conversion_functions 897048e
MIKE: insert_params_withlog: read_pos = 897048a
MIKE: insert_params_withlog: 0
MIKE: insert_params_withlog: 1
MIKE: insert_params_withlog: 3
MIKE: insert_params_withlog: 0
MIKE: insert_params_withlog: 1
MIKE: insert_params_withlog: 0
MIKE: insert_params_withlog: 0
MIKE: insert_params_withlog: 0
MIKE: insert_params_withlog: 0
For some reason, the "*data= read_pos" line does not seem to be
updating the *data variable.
Step 3:
The last step is "magical". In this step, add one more print statement
to the end of the setup_conversion_functions function.
}
fprintf(stderr,"MIKE: E: setup_conversion_functions %x\n",read_pos);
}
fprintf(stderr,"MIKE\n");
*data= read_pos;
DBUG_RETURN(0);
}
The output is now:
MIKE: E: setup_conversion_functions 897048e
MIKE
MIKE: insert_params_withlog: read_pos = 897048e
MIKE: insert_params_withlog: 1
MIKE: insert_params_withlog: 0
MIKE: insert_params_withlog: 0
MIKE: insert_params_withlog: 0
MIKE: insert_params_withlog: 0
Conclusion:
Somehow, adding a print statement before the "*data= read_pos;" statement
will allow the *data value to be updated successfully. I don't understand
this.
[8 Dec 2006 2:18]
Mike Rollins
In the file "sql/sql_prepare.cc", I found that the problem goes away if I remove the static from the function setup_conversion_functions.
Change
static bool setup_conversion_functions(Prepared_statement *stmt,
uchar **data, uchar *data_end)
to
bool setup_conversion_functions(Prepared_statement *stmt,
uchar **data, uchar *data_end)
[30 Dec 2006 9:40]
Valeriy Kravchuk
Please, send the results of gcc --version If it is the same gcc version 4.1.0 as mentioned in uname results, it can be gcc bug (or bug/problem in MySQL that gcc 4.x.y can pinpoint).
[30 Dec 2006 12:38]
Mike Rollins
My current version of gcc:
[rollins@bobby bug]$ gcc --version
gcc (GCC) 4.1.0 20060304 (Red Hat 4.1.0-3)
WITH STATIC:
I did the test with the "static bool"
vi sql/sql_prepare.cc
static bool setup_conversion_functions(Prepared_statement *stmt,
uchar **data, uchar *data_end)
The log file shows
2 Execute [1] select PK, VALUE from TEST where PK = 196864
WITHOUT STATIC:
I did the test without the "static bool"
vi sql/sql_prepare.cc
bool setup_conversion_functions(Prepared_statement *stmt,
uchar **data, uchar *data_end)
The log file shows
2 Execute [1] select PK, VALUE from TEST where PK = 1
I updated GCC
yum update gcc
Dependencies Resolved
=============================================================================
Package Arch Version Repository Size
=============================================================================
Updating:
gcc i386 4.1.1-1.fc5 updates 4.6 M
Updating for dependencies:
cpp i386 4.1.1-1.fc5 updates 2.6 M
gcc-c++ i386 4.1.1-1.fc5 updates 3.3 M
gcc-gfortran i386 4.1.1-1.fc5 updates 2.8 M
gcc-java i386 4.1.1-1.fc5 updates 2.8 M
libgcc i386 4.1.1-1.fc5 updates 61 k
libgcj i386 4.1.1-1.fc5 updates 12 M
libgcj-devel i386 4.1.1-1.fc5 updates 1.3 M
libgcj-src i386 4.1.1-1.fc5 updates 8.3 M
libgfortran i386 4.1.1-1.fc5 updates 194 k
libgomp i386 4.1.1-1.fc5 updates 48 k
libstdc++ i386 4.1.1-1.fc5 updates 324 k
libstdc++-devel i386 4.1.1-1.fc5 updates 9.5 M
libtool i386 1.5.22-2.3 updates 678 k
Now, my current version of GCC is
gcc (GCC) 4.1.1 20060525 (Red Hat 4.1.1-1)
I recompiled mysql-4.1.22
make clean
make && make install
WITH STATIC:
I did the test with the "static bool"
vi sql/sql_prepare.cc
static bool setup_conversion_functions(Prepared_statement *stmt,
uchar **data, uchar *data_end)
The log file shows
2 Execute [1] select PK, VALUE from TEST where PK = 196864
WITHOUT STATIC:
I did the test without the "static bool"
vi sql/sql_prepare.cc
bool setup_conversion_functions(Prepared_statement *stmt,
uchar **data, uchar *data_end)
The log file shows
2 Execute [1] select PK, VALUE from TEST where PK = 1
I even rebooted and performed a clean install of 4.1.22 and had the same results.
[20 Feb 2007 15:45]
Valeriy Kravchuk
Please, try to compile with a newer version of gcc, 4.1.1 (or 4.1.2). In case of the same problem, please, send all CFLAGS/CXXFLAGS used. I still think it was a compiler bug.
[21 Mar 2007 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: Attempts to bind values (integer values) to a prepared statment is not successfull with the compiled version of mysql. However, the same C program works correctly if I use the pre-compiled verion of mysql. Compiled Version: mysql: 4.1.21 gcc: 4.1.0 glibc: 2.4-4 Linux: 2.6.18 CFLAGS = -I/usr/local/mysql/include/mysql CLIBS = -L/usr/local/mysql/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm If I use the binary version of mysql with the static 2.2 glibc the same C program works correctly: Linux (x86, glibc-2.2, "standard" is static, gcc) mysql-standard-4.1.21-pc-linux-gnu-i686.tar.gz CFLAGS = -I/usr/local/mysql/include -mcpu=pentiumpro CLIBS = -L/usr/local/lib -L/usr/local/mysql/lib -lmysqlclient -lz \ -lcrypt -lnsl -lm -lc -lnss_files -lnss_dns -lresolv -lc \ -lnss_files -lnss_dns -lresolv How to repeat: #!/bin/bash MYSQLADMIN=/usr/local/mysql/bin/mysqladmin MYSQL=/usr/local/mysql/bin/mysql ${MYSQLADMIN} -u root drop testdb --password=XXXX -f ${MYSQLADMIN} -u root create testdb --password=XXXX ${MYSQL} -u root testdb --password=XXXX <<EOF GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'localhost' IDENTIFIED BY 'testpassword'; quit EOF ${MYSQL} -u testuser testdb --password=testpassword <<EOF create table TEST ( PK int, VALUE int ) ENGINE=MyISAM; insert into TEST (PK,VALUE) values (1,10); insert into TEST (PK,VALUE) values (2,20); insert into TEST (PK,VALUE) values (3,30); EOF echo ${MYSQL} -u testuser testdb --password=testpassword +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ #include <mysql.h> #include <stdio.h> #include <stdlib.h> #include <string.h> #define DB_DBNAME "testdb" #define DB_DBUSER "testuser" #define DB_DBPWD "testpassword" #define DB_DBHOST "localhost" #define DB_DBPORT 3306 int main() { /* this query returns all the rows successfully */ //char *query = "select PK, VALUE from TEST"; /* This query, with an input_buffer value of 1, does not return anything. */ /* The query log shows that the value of the bind variable is 196864, not the value of 1 */ /* that is in the buffer. The following shows the contents of the query log: */ /* 061127 21:47:31 28 Connect testuser@localhost on testdb */ /* 28 Prepare [1] select PK, VALUE from TEST where PK = ? */ /* 28 Execute [1] select PK, VALUE from TEST where PK = 196864 */ /* 28 Quit */ /* The query work correctly if I use the binary distribution of mysql instead of the */ /* version that I compiled myself: */ /* 061127 23:31:36 12 Connect testuser@localhost on testdb */ /* 12 Prepare [1] select PK, VALUE from TEST where PK = ? */ /* 12 Execute [1] select PK, VALUE from TEST where PK = 1 */ /* 12 Quit */ char *query = "select PK, VALUE from TEST where PK = ?"; MYSQL *mysql; MYSQL_STMT *stmt; MYSQL_BIND params[1]; int input_buffer; /* Setup prepared statement parameters */ memset(params, 0, sizeof(params)); params[0].buffer_type = MYSQL_TYPE_LONG; params[0].buffer = (int*) &input_buffer; input_buffer = 1; /*******************/ /* Result set data */ /*******************/ MYSQL_BIND col[2]; /* Result Set Buffers */ my_bool is_null[2]; unsigned long length[2]; int pk_buffer; int value_buffer; /* initialize mysql */ mysql = mysql_init((void *)NULL); if (!mysql) { printf("mysql_init(): %s\n", mysql_error(mysql)); return 1; } /* connect to the database */ if (mysql_real_connect(mysql, DB_DBHOST, DB_DBUSER, DB_DBPWD, DB_DBNAME, DB_DBPORT, NULL, 0) == NULL) { printf("mysql_real_connect(): %s\n", mysql_error(mysql)); return 1; } /* Create a prepared statement */ stmt = mysql_stmt_init(mysql); if (!stmt) { printf("mysql_stmt_init(): %s\n", mysql_error(mysql)); return 1; } /* Attach the query */ if (mysql_stmt_prepare(stmt, query, strlen(query))) { printf("mysql_stmt_prepare(): %s\n", mysql_error(mysql)); return 1; } /* Bind the input buffers */ if (mysql_stmt_bind_param(stmt, params)) { fprintf(stderr, " mysql_stmt_bind_param() failed\n"); fprintf(stderr, " %s\n", mysql_stmt_error(stmt)); return 1; } /* Execute the query */ if (mysql_stmt_execute(stmt)) { printf(" mysql_stmt_execute(), 1 failed\n"); printf(" %s\n", mysql_stmt_error(stmt)); return 1; } /* Setup the result buffers */ /* INTEGER COLUMN */ col[0].buffer_type= MYSQL_TYPE_LONG; col[0].buffer= (char *)&pk_buffer; col[0].is_null= &is_null[0]; col[0].length= &length[0]; /* INTEGER COLUMN */ col[1].buffer_type= MYSQL_TYPE_LONG; col[1].buffer= (char *)&value_buffer; col[1].is_null= &is_null[1]; col[1].length= &length[1]; /* Bind the result buffers */ if (mysql_stmt_bind_result(stmt, col)) { printf(" mysql_stmt_bind_result(), 1 failed\n"); printf(" %s\n", mysql_stmt_error(stmt)); return ; } /* fetch the results */ while (!mysql_stmt_fetch(stmt)) { printf("PK: %d, Value: %d\n",pk_buffer,value_buffer); } if (mysql_stmt_close(stmt)) { fprintf(stderr, " failed while closing the statement\n"); fprintf(stderr, " %s\n", mysql_stmt_error(stmt)); exit(0); } mysql_close(mysql); return 0; }