Bug #4311 Error in JDBC retrieval of mediumint column (mysql4.1.2b-alpha-win and java-3.1
Submitted: 28 Jun 2004 17:54 Modified: 28 Jun 2004 17:55
Reporter: Mark Matthews Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:3.1.2 OS:NA
Assigned to: Mark Matthews CPU Architecture:Any

[28 Jun 2004 17:54] Mark Matthews
Description:
To be able to use subqueries, I'm now using mysql-4.1.2b-alpha-win.zip
I'm using mysql-connector-java-3.1.2-alpha.zip as my jdbc driver to connect
to the db...

I'm selecting some columns from a table where the column is created using
mediumint.
Somehow, after creation, it becomes mediumint(9)
PreparedStatement.executeQuery throws SQLException.

I'm thinking maybe the datatype is somehow not recognized.

java.sql.SQLException: Unknown type '9 in column 0 of 3 in binary-encoded
result set.
 at com.mysql.jdbc.MysqlIO.unpackBinaryResultSetRow(MysqlIO.java:3888)
 at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1211)
 at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:2036)
 at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:395)
 at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:1824)
 at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1278)
 at
com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement
.java:1283)
 at
com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStateme
nt.java:903)
 at
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1622)
...

Any ideas why this is happening? And any workaround available?

Thanks in advance.

How to repeat:
public void testBinaryProtocolMediumInt() throws Exception {
    	try {
    		int lowValue = -8388608;
    		int highValue = 8388607;
    		
    		this.stmt.executeUpdate("DROP TABLE IF EXISTS testBinaryProtocolMediumInt");
    		this.stmt.executeUpdate("CREATE TABLE testBinaryProtocolMediumInt (low MEDIUMINT, high MEDIUMINT)");
    		this.stmt.executeUpdate("INSERT INTO testBinaryProtocolMediumInt VALUES (" + lowValue + ", " + highValue + ")");
    		
    		PreparedStatement pStmt = this.conn.prepareStatement("SELECT low, high FROM testBinaryProtocolMediumInt");
    		this.rs = pStmt.executeQuery();
    		assertTrue(this.rs.next());
    		assertTrue(this.rs.getInt(1) == lowValue);
    		assertTrue(this.rs.getInt(2) == highValue);	
    	} finally {
    		this.stmt.executeUpdate("DROP TABLE IF EXISTS testBinaryProtocolMediumInt");
    	}

Suggested fix:
Add the type(s) to MysqlIO.unpackBinaryResultSetRow().
[28 Jun 2004 17:55] Mark Matthews
Fixed for 3.1.3, will be in nightly snapshot after 00:00 GMT on the 29th of June.