MySQL Bugs: #14038: Prepared Statement Gives Bad Value on x86

Bug #14038	Prepared Statement Gives Bad Value on x86_64
Submitted:	14 Oct 2005 19:41	Modified:	6 Jun 2006 20:14
Reporter:	Richard Harms	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	MySQL 5.0.13-rc, Connector/J 3.1.10	OS:	Linux (Fedora Core 4, x86_64)
Assigned to:		CPU Architecture:	Any

Description:
Using PreparedStatements on x86_64 appears to corrupt the values being bound to the statement.

Java version: jdk1.5.0_05 (for x86_64)

How to repeat:
Sample table:

DROP TABLE IF EXISTS `entityids`;
CREATE TABLE `entityids` (
  `eid_entityname` varchar(60) NOT NULL,
  `eid_lasteid` bigint(20) NOT NULL,
  `eid_dbtimestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`eid_entityname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

PreparedStatementTest.java:

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class PreparedStatementTest {
    
    private static final String insertQuery = "INSERT INTO entityids (eid_entityname, eid_lasteid) VALUES (?, ?)";

    public static void main(String args[]) throws Exception {
        Connection testConn = null;
        PreparedStatement ps = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            testConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "test", "test");

            ps = testConn.prepareStatement(insertQuery);
            ps.setString(1, "TestEntityName");
            ps.setLong(2, 50);

            ps.executeUpdate();
        } catch (ClassNotFoundException cnfe) {
            System.err.println("MySQL driver class missing");
        } catch (SQLException se) {
            System.err.println("SQL Error occured:");
            se.printStackTrace();
        } finally {
            if(ps != null) {
                try {
                    ps.close();
                    ps = null;
                } catch (SQLException se) {
                    se.printStackTrace();
                }
            }

            if(testConn != null) {
                try {
                    testConn.close();
                    testConn = null;
                } catch (SQLException se) {
                    se.printStackTrace();
                }
            }
        }
    }
    
}

To test:

javac PreparedStatementTest.java
java -cp lib/mysql-connector-java-bin.jar:. PreparedStatementTest

Incorrect results:

mysql> select * from entityids;
+----------------+---------------------+---------------------+
| eid_entityname | eid_lasteid         | eid_dbtimestamp     |
+----------------+---------------------+---------------------+
|                | 7301476289171488001 | 2005-10-14 14:36:10 |
+----------------+---------------------+---------------------+
1 row in set (0.00 sec)

eid_entityname is empty, and eid_lasteid doesn't contain '50.'

Retested with Connector/J 3.1.11, problem still occurs.

Adding "useServerPrepStmts=false" to the connection URL allows the test to work correctly.

Sorry....I was unable to reproduce the bug on MySQL 5.0.14-rc and connector 3.1.10. The result is follow:

mysql> select * from entityids;
+----------------+-------------+---------------------+
| eid_entityname | eid_lasteid | eid_dbtimestamp     |
+----------------+-------------+---------------------+
| TestEntityName |          50 | 2005-10-16 13:03:59 |
+----------------+-------------+---------------------+
1 row in set (0.00 sec)

Could you please re-test your test case on MySQL 5.0.14rc ?

It doesn't appear as though 5.0.14 is on the downloads page yet. Do you want me to just use the current snapsnot of 5.0 (dated 11-Oct-2005), or use bk to pull the latest version available through it?

You can use snapshot from URL : http://downloads.mysql.com/snapshots.php

Same problem with 5.0.16-nightly-20051017.

Can you generate a testcase script by adding "autoGenerateTestcaseScript=true" to your JDBC URL, and then running that script against your server using the "mysql" command line client, and let us know if the error shows there as well?

/* conn id 0 */ SET NAMES utf8;
/* conn id 0 */ SET character_set_results = NULL;
/* conn id 0 */ SHOW VARIABLES;
/* conn id 0 */ SHOW COLLATION;
/* conn id 0 */ SET autocommit=1;
/* conn id 0 */ PREPARE debug_stmt_3 FROM "INSERT INTO entityids (eid_entityname, eid_lasteid) VALUES (?, ?)";

/* conn id 0 */ SET @debug_stmt_param3_0='TestEntityName';
/* conn id 0 */ SET @debug_stmt_param3_1=50;
/* conn id 0 */ EXECUTE debug_stmt_3 USING @debug_stmt_param3_0, @debug_stmt_param3_1;

/* conn id 0 */ DEALLOCATE PREPARE debug_stmt_3;

Results:

mysql> select * from entityids;
+----------------+-------------+---------------------+
| eid_entityname | eid_lasteid | eid_dbtimestamp     |
+----------------+-------------+---------------------+
| TestEntityName |          50 | 2005-11-17 09:24:03 |
+----------------+-------------+---------------------+
1 row in set (0.00 sec)

Works properly when used with the mysql command line client.

Hi Richard.
I'm having troubles compiling test case under FC5 x64.
When I compile test case under Win32 and run against network MySQL server on FC5 x64, test case succedes. Leaving report in "Analyzing" state until compilation under FC5 x64 succedes.

Please, try to repeat with the latest version of Connector/J, 3.1.13, and latest version of MySQL server (!), 5.0.22. Inform about the results. I was unable to repeat with these versions and jdk1.5.0_07 for AMD64, on WinXP 64-bit as client platform. Your test gave me:

mysql> select * from entityids;
+----------------+-------------+---------------------+
| eid_entityname | eid_lasteid | eid_dbtimestamp     |
+----------------+-------------+---------------------+
| TestEntityName |          50 | 2006-06-06 10:15:34 |
+----------------+-------------+---------------------+
1 row in set (0.00 sec)

Just as expected.

Retested with jdk 1.5.0_05, MySQL 5.0.22, and Connector/J 3.1.13, and it does appear to be working correctly now. Also tested the larger application that the test code was extracted from, and it is working correctly as well.

Richard, glad it works.
I will close this report now. If you expirience reported problem again, please reopen it.