MySQL Bugs: #11874: ResultSet getInt method gives wrong value for small int column in database

Bug #11874	ResultSet getInt method gives wrong value for small int column in database
Submitted:	12 Jul 2005 4:13	Modified:	10 Mar 2006 20:24
Reporter:	Anindya Mozumdar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	mysql connector 3.1.10	OS:	Linux (Linux)
Assigned to:	Aleksey Kishkin	CPU Architecture:	Any

Description:
I have a table called Test

 CREATE TABLE Test ( i SMALLINT UNSIGNED );

in a database called Test. I have also inserted a row into this table - 

insert into Test values (1);

The Java J2SE5 Documentation clearly says that the SMALLINT JDBC Type must be mapped to an Integer object. ( Please refer http://java.sun.com/j2se/1.5.0/docs/guide/jdbc/getstart/mapping.html  - Table 8.9.3)

However, when I retrieve the value using Java, it stores 65537 ( 65536 + 1 ??) in the Integer object where I am retrieving the value ( Please see the How to repeat section for  the complete code - the url of your mysql server may be different ).

I have installed the mysql server from mysql-standard-4.1.12-pc-linux-gnu-i686.tar.gz

IMPORTANT INFORMATION:
The code works perfectly fine with mysql-connector-3.1.6, and prints the expected result 1, while the error comes only with mysql-connector-3.1.10

How to repeat:
import java.sql.*;
                                                                                                                             
class Test
{
        public static void main(String args[]) {
                try {
                        Connection con;
                        Class.forName("com.mysql.jdbc.Driver").newInstance();
                        con = DriverManager.getConnection("jdbc:mysql://192.168.52.6/Test", "root", "");
                        String selectStmt =
                                "SELECT i FROM Test";
                        PreparedStatement pstmt = con.prepareStatement(selectStmt);
                        ResultSet rs = pstmt.executeQuery();
                        if(rs.next()) {
                                Integer i = rs.getInt(1);
                                System.out.println(i);
                        }
                } catch(Exception e) {
                        e.printStackTrace();
                }
        }
}

at least on windows it works properly. Plan to test on linux. Attached testcase that I used

.Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.

Done.

Connected to 4.1.12a-nt
1

Time: 0,5

OK (1 test)

test case

Attachment: Bug11874.java (text/java), 872 bytes.

Since you have said that it works for Windows, here are a few more details:
1. I am using RedHat Linux 9.
2. Also, please not the version of the connector I am using is 3.1.10. Here are the exact commands I entered in the command prompt.

[anindya@Narmada anindya]$ java -classpath mysql-connector-java-3.1.6-bin.jar:. Test
1
[anindya@Narmada anindya]$ java -classpath mysql-connector-java-3.1.10-bin.jar:. Test
65537

Known issue, already fixed for 3.1.11, see nightly snapshots at http://downloads.mysql.com/snapshots.php#connector-j

Known issue, already fixed for 3.1.11, see nightly snapshots at http://downloads.mysql.com/snapshots.php#connector-j

it again happens to me with

mysql-connector-java-3.1-nightly-20051124-bin.jar

and mysql --version
mysql  Ver 14.7 Distrib 4.1.11, for pc-linux-gnu (i386)
on Debian Linux 2.4.27 #3 Mon Nov 15 13:15:32 CET 2004 i686 GNU/Linux

Ralph, 

We have testcase coverage for this, so we'd like to see a testcase from you to figure out what's going on in this case, as either you're in a new code path we didn't account for, or you're actually picking up an old version of the driver somewhere in your classpath.

I have to report the same problem.
Starting point : an Java application running on WinXP/MySQL 4.1.12a-nt, JDK 1.5_04 :task inserting INT and BIGINT(20) into a MySQL table, using a prepared statement. The application runs fine.

The same application is tested under Linux : inserted integer values got transformed to crazy values (looking like  {INT}.max_values).
Config : Mandrake 2006 Free, JDK 1.5._06, Mysql 4.1.12
noteworthy : this happens whatever Connector/J version I use : 
3.1.10, .3.2, latest night build.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/297

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/299

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

Fix will be available in 5.0.1 and 3.1.13. You can try a nightly snapshot from http://downloads.mysql.com/snapshots.php#connector-j if you want to test the fix before it's officially released. Thanks for the bug report.