MySQL Bugs: #12150: PreparedStatement and not working on Linux

Bug #12150	PreparedStatement and not working on Linux
Submitted:	25 Jul 2005 11:35	Modified:	14 Nov 2005 0:15
Reporter:	Tom Schindl	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	3.1.10+nightly-20050725	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
This bug seems to affect only windows because everything is working on win32. When using PreparedStatements somehow the SQL does do what it is supposed to do. 
Running against a the test database:
---------------------8<---------------------
PreparedStatement stmt = connection.prepareStatement("SELECT us_id, us_loginname, us_password FROM users WHERE delmark = 0 AND us_loginname = ? AND us_password = ?");
stmt.setString(1,"beso");
stmt.setString(2,"beso");
// no result => not expected
---------------------8<---------------------

where as:
---------------------8<---------------------
stmt2.executeQuery("SELECT us_id, us_loginname, us_password FROM users WHERE delmark = 0 AND us_loginname = 'beso' AND us_password = 'beso'");
// one result => expected
---------------------8<---------------------

Using the older 3.0.16 driver the prepared statement is also working

How to repeat:
Use the example database and script provided

The database

Attachment: jdbcTest.dump (application/octet-stream, text), 2.18 KiB.

The java file

Attachment: TestSelect.java (text/x-java), 1.19 KiB.

sorry I meant I can only reproduce the bug on linux!!!

Hi! Can't repeat it. When i run your testcase I got:

bash-2.05b$ /opt/java/bin/java -cp my10.jar:. TestSelect 
Found item
Found item

What mysql server version do you use (i tested it aginst 5.0.10)?

Server: 
- 4.1.12 Linux kernel 2.6.11-6 

Client:
- Linux kernel 2.6.11-6 
- java is 1.5.02

Maybe the the language setting also makes a difference mine is the following:
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

I cannot reproduce it against 4.1.12 as well. 

 /opt/java/bin/java -cp .:my10.jar TestSelect
Found item
Found item
walrus@hnt02951:/data3/mysql-connector-java-3.1.10> mysql -e "select version();"
+-----------------+
| version()       |
+-----------------+
| 4.1.12-standard |
+-----------------+

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".

I had the same happen when I changed my Linux OS from Fedora Core2 32-bit to Fedora Core4 64-bit. The exact same database that used to work on the 32-bit kernel now returns empty resultsets when ever I use a PreparedStatement. My current kernel is 2.6.11-2.1369_FC4.

 This bug renders the MySQL database useless on my Linux machine. I have tried 4.1.11 (with the fix on MySQL Bugs: #2173 for NPTL/Linuxthreads),  4.1.13, 4.1.14 on linux and have 4.1.11 running with the same database files on a windows machine. The windows installation works without problems, none of the Linux installations have worked.

 I did check the wire format with Ethereal, I'm using Connector/J 3.1.10 and there was no differences there except for the data returned.

I got the same problem of my FC4 on an AMD x86-64 machine. The mysql server is 4.1.12 compiled from source, and the connector/j is 3.1.10. Other machine's information I think is not related to this problem. I will have a try on a 32bit linux machine to see whether this problem occurs some day in a short later. I do think this problem is aka 64bit problems.

I've tested for a precompiled mysql 4.1.14 64bit version downloaded from mysql.com, and the problem has been solved. So I'm sure that the PreparedStatement problem is related to the server side on a 64bit linux machine.

PS my name is withheld from public. I don't know what can be done to make it displayed ;-( Sorry.

I've also tested compiling version 4.1.14 from source code with CFLAGS="-O3" CXX=gcc CXXFLAGS="-O3 -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql-4.1.14 --enable-assembler --with-mysqld-ldflags=-all-static. When I use mysql command line client to test some prepared statements by using SQLs like 
PREPARE STMT FROM "SELECT * from t1 where mycolumn=?";
set @a='sth';
EXECUTE STMT USING @a;
I get wrong result just as what I did with my java program which uses PreparedStatement. Therefore, there must be something wrong with the compilation process, some parameters of the configure scripts maybe. Is there anybody who knows how the precompiled mysql-max 4.1.14 which runs properly on my 64bit linux machine was compiled?

Well I'm on a amd-32bit machine and only face the problem when querying the database using JDBC. When running the queries in the mysql-shell like you didn't I get the expected results.

Sorry should read like this:
When running the queries in the mysql-shell like you did I get the expected results.

Ok. I have now tracked down the problem to be an issue with the Mandrake-Binary I'm using, when using a pre-compile binary from mysql.com everything is working as expected. This points once again the finger on the compile-time configuration or some libary problems. I'm trying to contact the Mandrake-Maintainer to see how MySQL is compiled.

Just for cross-references: http://qa.mandriva.com/show_bug.cgi?id=18864

I've tested on a 32bit linux machine (fc4), everything is ok. The mysql server is compiled from source, the same way as what I've done on the 64bit fc4. So the problem is very likely  related to the compilation process, or run-time library. The workaround is to use the precompiled binary package from mysql.com.

Tested on Suse 9.3 and Windows XP. I was unable to repeat the behavior
reported like was done before by Aleksey.

hegel:/home/miguel/java # java TestSelect
Found item
Found item