MySQL Bugs: #13593: Prepared statement doesn't work

Bug #13593	Prepared statement doesn't work
Submitted:	29 Sep 2005 4:10	Modified:	29 Sep 2005 12:06
Reporter:	[ name withheld ]	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1	OS:	Linux (linux, 64bit)
Assigned to:		CPU Architecture:	Any

Description:
When I compiled mysql server from source on a 64bit linux platform, everything seemed fine during the compilation process, and mysql started properly after installation and configuration. But when I tried to issue some prepared statements from JDBC programs, they didn't work. I at first thought it might be the Connecter/J problem, but after I issued the same prepared statements in mysql command line, I found that they were not only problems of Connector/J, but also problems of mysql server. I tried to do the same thing (using a server compiled from same source with same configure parameters, and issuing same statements) on a 32bit linux machine, everything works great. Therefore, I think this problem is aka compilation problem on 64bit linux.

The precompiled 64bit binary package mysql-max downloaded from mysql.com doesn't have the problem. It works as great as the 32bit version.

The #12150 bug reports the same problem, which is categorized to Connector/J.

How to repeat:
Compile source code on a 64bit linux with --prefix only;
Create a test table with any column you like;
Issue any prepared statements from mysql command line, such as
PREPARE stmt FROM "select id from table_name where id=?";
SET @id=1;
EXECUTE stmt USING @id;
No result will appear whatever the value @id is.

Suggested fix:
Tell us how you compile the mysql-max binary package which works properly on both 32 and 64bit boxes.

I tried to repeat on 4.1.14 I've built on our 64-bit nocona server several days ago:

-bash-2.05b$ uname -a
Linux nocona 2.4.21-32.0.1.ELsmp #1 SMP Tue May 17 17:46:36 EDT 2005 x86_64 x86_64 x86_64 GNU/Linux
-bash-2.05b$ cd ~/dbs/4.1-nocona/
-bash-2.05b$ bin/mysqld_safe --skip-networking &
[1] 19447
-bash-2.05b$ Starting mysqld daemon with databases from /users/vkravchuk/dbs/4.1-nocona/var

-bash-2.05b$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.14

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table tt(id integer);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into tt values(1);
Query OK, 1 row affected (0.00 sec)

mysql> prepare stmt from "select id from tt where id=?";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @id=1;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @id;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

So, I can not repeat the behaviour you described with this version. It was built usong the following configure options:

-bash-2.05b$ CFLAGS="-O3" CC=gcc CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/users/vkravchuk/dbs/4.1-nocona --enable-assembler --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --with-low-memory --with-mysqld-user=vkravchuk --without-bench --with-charset=utf8 --with-extra-charsets=all  --with-big-tables --without-innodb

Oooooooops, it's weird! The problem I reported yesterday now has disappeared! The only difference between today's environment running mysql and last time's is that some software are updated online (but I'm not sure what have been updated), if it's not my mistake.

I've tested for mysql 4.1.12 and 4.1.14 both of which are compiled from source. In mysql command line, prepared statements seem working properly. But the JDBC problem is still unsolved. For the two mysql servers mentioned above, JDBC PreparedStatement doesn't work as expected. While, for the precompiled binary mysql server mysql-max-4.1.14-unknown-linux-gnu-x86_64-glibc23 downloaded from mysql.com, no prepared statement problem is found.

Followings are what I have done on my fc4 64bit version:
# uname -a
Linux home 2.6.12-1.1456_FC4 #1 Thu Sep 22 02:11:36 EDT 2005 x86_64 x86_64 x86_64 GNU/Linux
glibc 2.3.5

configure parameters:

configure --prefix=/usr/local/mysql-4.1.14  '--prefix=/usr/local/mysql-4.1.14' '--enable-assembler' '--with-mysqld-ldflags=-all-static' 'CFLAGS=-O3' 'CXXFLAGS=-O3 -felide-constructors -fno-exceptions -fno-rtti' 'CXX=gcc' CFLAGS='-O3 -DDBUG_OFF -O3 ' CXXFLAGS='-O3 -DDBUG_OFF -O3 -felide-constructors -fno-exceptions -fno-rtti -fno-implicit-templates -fno-exceptions -fno-rtti -DUSE_MYSYS_NEW -DDEFINE_CXA_PURE_VIRTUAL' --cache-file=/dev/null --srcdir=.

Sorry for my mistake ;-)