MySQL Bugs: #21187: Unable to use TINYINT UNSIGNED data with values

Bug #21187	Unable to use TINYINT UNSIGNED data with values > 128
Submitted:	20 Jul 2006 16:23	Modified:	26 Jul 2006 15:22
Reporter:	Darryl Miles	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	3.1.13	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I have upgraded from 3.1.12+patch to 3.1.13 and my application has stopped working.

The column in question is defined as TINYINT UNSIGNED, but even through we are using the getShort() API this was somehow internally converted to getNativeByte() which throws an exception for out of range values.

I did file this bug 7 months ago which was marked as resolved in the 3.1.13 release.  http://bugs.mysql.com/bug.php?id=15677

There was a testcase inside that bug which I provided which I'm pretty sure, should have failed when validating the 3.1.13 driver.  Because the test case specifically stores values from 129 to 255 into a TINYINT UNSIGNED column and confirms that getShort() JDBC API call returns the correct value.  The test case is at http://bugs.mysql.com/file.php?id=2563

Can you confirm if the testcase I created made it verbatim into the MySQL development tree ?  If it did can you confirm it passed ?  If it did not is there a reason why not, as this testcase was to provide me piece of mind that the problem is fixed forever.

I have been running with 3.1.12+ourpatch from that bug report until recently upgrading to 3.1.13.  I have now downgraded back to that version and my problem is resolved.

I was waiting to be able to verify this fix for a general release version before  checking I am happy with the current test cases and looking at the optional fail fast flag discussed in the other report.

I'm pretty sure my test case if checked against the 3.1.13 driver should fail, although I'm not geared up at this minute to do that myself.

From java the following stack track occurs

Caused by: java.sql.SQLException: '128' in column '9' is outside valid range for the datatype TINYINT.
	at com.mysql.jdbc.ResultSet.throwRangeException(ResultSet.java:6559)
	at com.mysql.jdbc.ResultSet.getNativeByte(ResultSet.java:7500)
	at com.mysql.jdbc.ResultSet.getNativeByte(ResultSet.java:7444)
	at com.mysql.jdbc.ResultSet.getNativeShort(ResultSet.java:7900)
	at com.mysql.jdbc.ResultSet.getNativeShort(ResultSet.java:7868)
	at com.mysql.jdbc.ResultSet.getShort(ResultSet.java:4620)
	at com.mysql.jdbc.ResultSet.getShort(ResultSet.java:4663)
	at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.getShort(DelegatingResultSet.java:233)
	at org.hibernate.type.ShortType.get(ShortType.java:28)
	at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:113)
	at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:102)
	at org.hibernate.type.AbstractType.hydrate(AbstractType.java:81)
	at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:1899)
	at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1372)
	at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1300)
	at org.hibernate.loader.Loader.getRow(Loader.java:1197)
	at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:569)
	at org.hibernate.loader.Loader.doQuery(Loader.java:689)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
	at org.hibernate.loader.Loader.loadEntity(Loader.java:1785)
	... 79 more

How to repeat:
I would like validation against my testcase http://bugs.mysql.com/file.php?id=2563 or an explaination why my testcase is invalid.

Suggested fix:
I think the problem must be in relation to other changes that were made between 3.1.12 and 3.1.13, since 3.1.12+ourpatch from the bug #15677 works for me.  But the full 3.1.13 GA release driver does not.

Hi Darryl and thanks for your problem report.
Changes in MySQL Connector/J 3.1.14 (not yet released)
  • Fixed ResultSet.getShort() for UNSIGNED TINYINT returns incorrect values when using serverside prepared statements. (Fixes Bug#20306 [http://bugs.mysql.com/20306])

If you can't wait for official release I suggest you use (nightly snapshot [http://downloads.mysql.com/snapshots/mysql-connector-java-3.1/]). To produce correct results with test case in question I used mysql-connector-java-3.1-nightly-20060704-bin.jar with JDK 1.6.

Can you please add my Testcase to the suite, this is my piece of mind on my original issue.

Re-opened as my comment is just not getting any attention otherwise.

Sorry, I didn't comment on it. Your testcase has been added (it might pay to watch the commits@lists.mysql.com mailing list too, since you'll see when stuff changes).