| Bug #69786 | Java throws exception fetching TIME(6) column | ||
|---|---|---|---|
| Submitted: | 18 Jul 2013 23:24 | Modified: | 29 Jul 2013 17:33 |
| Reporter: | Bill Karwin (Candidate Quality Contributor) (OCA) | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | 5.1.22 | OS: | Any |
| Assigned to: | Alexander Soklakov | CPU Architecture: | Any |
[18 Jul 2013 23:39]
Bill Karwin
test JDBC type mapping
Attachment: TestTypeMap.java (application/octet-stream, text), 3.81 KiB.
[19 Jul 2013 6:03]
MySQL Verification Team
Hello Bill, Thank you for the bug report and the test case. Verified as described. Thanks, Umesh
[19 Jul 2013 6:05]
MySQL Verification Team
//
java -cp ".:mysql-connector-java-commercial-5.1.22-bin.jar" TestTypeMap
Column=1 ColumnName=bi SQLType=-5 SQLTypeName=BIGINT Precision=20 Scale=0 MetadataClassName=java.lang.Long
Column=2 ColumnName=biu SQLType=-5 SQLTypeName=BIGINT UNSIGNED Precision=20 Scale=0 MetadataClassName=java.math.BigInteger
Column=3 ColumnName=i SQLType=4 SQLTypeName=INT Precision=11 Scale=0 MetadataClassName=java.lang.Integer
ObjectClassName=java.lang.Integer Value="123"
Column=4 ColumnName=iu SQLType=4 SQLTypeName=INT UNSIGNED Precision=10 Scale=0 MetadataClassName=java.lang.Long
ObjectClassName=java.lang.Long Value="123"
Column=5 ColumnName=mi SQLType=4 SQLTypeName=MEDIUMINT Precision=9 Scale=0 MetadataClassName=java.lang.Integer
ObjectClassName=java.lang.Integer Value="123"
Column=6 ColumnName=miu SQLType=4 SQLTypeName=MEDIUMINT UNSIGNED Precision=8 Scale=0 MetadataClassName=java.lang.Integer
ObjectClassName=java.lang.Integer Value="123"
Column=7 ColumnName=si SQLType=5 SQLTypeName=SMALLINT Precision=6 Scale=0 MetadataClassName=java.lang.Integer
ObjectClassName=java.lang.Integer Value="123"
Column=8 ColumnName=siu SQLType=5 SQLTypeName=SMALLINT UNSIGNED Precision=5 Scale=0 MetadataClassName=java.lang.Integer
ObjectClassName=java.lang.Integer Value="123"
Column=9 ColumnName=ti SQLType=-6 SQLTypeName=TINYINT Precision=4 Scale=0 MetadataClassName=java.lang.Integer
Column=10 ColumnName=tiu SQLType=-6 SQLTypeName=TINYINT UNSIGNED Precision=3 Scale=0 MetadataClassName=java.lang.Integer
Column=11 ColumnName=f SQLType=7 SQLTypeName=FLOAT Precision=12 Scale=31 MetadataClassName=java.lang.Float
ObjectClassName=java.lang.Float Value="3.14"
Column=12 ColumnName=ff SQLType=8 SQLTypeName=DOUBLE Precision=22 Scale=31 MetadataClassName=java.lang.Double
ObjectClassName=java.lang.Double Value="3.14"
Column=13 ColumnName=n SQLType=3 SQLTypeName=DECIMAL Precision=9 Scale=2 MetadataClassName=java.math.BigDecimal
ObjectClassName=java.math.BigDecimal Value="3.14"
Column=14 ColumnName=d SQLType=91 SQLTypeName=DATE Precision=10 Scale=0 MetadataClassName=java.sql.Date
ObjectClassName=java.sql.Date Value="2013-07-19"
Column=15 ColumnName=dt SQLType=93 SQLTypeName=DATETIME Precision=19 Scale=0 MetadataClassName=java.sql.Timestamp
ObjectClassName=java.sql.Timestamp Value="2013-07-19 16:45:49.0"
Column=16 ColumnName=dtp SQLType=93 SQLTypeName=DATETIME Precision=26 Scale=0 MetadataClassName=java.sql.Timestamp
ObjectClassName=java.sql.Timestamp Value="2013-07-19 16:45:49.216083"
Column=17 ColumnName=t SQLType=92 SQLTypeName=TIME Precision=10 Scale=0 MetadataClassName=java.sql.Time
ObjectClassName=java.sql.Time Value="16:45:49"
Column=18 ColumnName=tp SQLType=92 SQLTypeName=TIME Precision=17 Scale=0 MetadataClassName=java.sql.Time
SQLException: Bad format for Time '1231231231231231231231231231233.143.143.14
2013-07-192013-07-19 16:45:492013-07-19 16:45:49.2160816:45:4916:45:49.2160832013-07-19 16:45:492013-07-19 16:45:49.2160832013char�varchar
longvarcharïlongblob
mediumblobblotinyblolongtext
mediumtexttextinytextAA,B' in column 18
SQLState: S1009
VendorError: 0
Column=19 ColumnName=ts SQLType=93 SQLTypeName=TIMESTAMP Precision=19 Scale=0 MetadataClassName=java.sql.Timestamp
ObjectClassName=java.sql.Timestamp Value="2013-07-19 16:45:49.0"
Column=20 ColumnName=tsp SQLType=93 SQLTypeName=TIMESTAMP Precision=26 Scale=0 MetadataClassName=java.sql.Timestamp
ObjectClassName=java.sql.Timestamp Value="2013-07-19 16:45:49.216083"
Column=21 ColumnName=y SQLType=91 SQLTypeName=YEAR Precision=4 Scale=0 MetadataClassName=java.sql.Date
ObjectClassName=java.sql.Date Value="2013-01-01"
Column=22 ColumnName=c SQLType=1 SQLTypeName=CHAR Precision=255 Scale=0 MetadataClassName=java.lang.String
ObjectClassName=java.lang.String Value="char"
Column=23 ColumnName=bc SQLType=-2 SQLTypeName=BINARY Precision=255 Scale=0 MetadataClassName=[B
Column=24 ColumnName=v SQLType=12 SQLTypeName=VARCHAR Precision=255 Scale=0 MetadataClassName=java.lang.String
ObjectClassName=java.lang.String Value="varchar"
Column=25 ColumnName=lv SQLType=12 SQLTypeName=VARCHAR Precision=32767 Scale=0 MetadataClassName=java.lang.String
ObjectClassName=java.lang.String Value="longvarchar"
Column=26 ColumnName=vb SQLType=-3 SQLTypeName=VARBINARY Precision=255 Scale=0 MetadataClassName=[B
Column=27 ColumnName=b1 SQLType=-7 SQLTypeName=BIT Precision=1 Scale=0 MetadataClassName=java.lang.Boolean
Column=28 ColumnName=b2 SQLType=-3 SQLTypeName=BIT Precision=64 Scale=0 MetadataClassName=[B
Column=29 ColumnName=lb SQLType=-4 SQLTypeName=LONGBLOB Precision=2147483647 Scale=0 MetadataClassName=[B
Column=30 ColumnName=mb SQLType=-4 SQLTypeName=MEDIUMBLOB Precision=16777215 Scale=0 MetadataClassName=[B
Column=31 ColumnName=b SQLType=-4 SQLTypeName=BLOB Precision=65535 Scale=0 MetadataClassName=[B
Column=32 ColumnName=tb SQLType=-3 SQLTypeName=TINYBLOB Precision=255 Scale=0 MetadataClassName=[B
Column=33 ColumnName=lt SQLType=-1 SQLTypeName=VARCHAR Precision=2147483647 Scale=0 MetadataClassName=java.lang.String
ObjectClassName=java.lang.String Value="longtext"
Column=34 ColumnName=mt SQLType=-1 SQLTypeName=VARCHAR Precision=16777215 Scale=0 MetadataClassName=java.lang.String
ObjectClassName=java.lang.String Value="mediumtext"
Column=35 ColumnName=tx SQLType=-1 SQLTypeName=VARCHAR Precision=65535 Scale=0 MetadataClassName=java.lang.String
ObjectClassName=java.lang.String Value="text"
Column=36 ColumnName=tt SQLType=-1 SQLTypeName=VARCHAR Precision=255 Scale=0 MetadataClassName=java.lang.String
ObjectClassName=java.lang.String Value="tinytext"
Column=37 ColumnName=e SQLType=1 SQLTypeName=CHAR Precision=1 Scale=0 MetadataClassName=java.lang.String
ObjectClassName=java.lang.String Value="A"
Column=38 ColumnName=s SQLType=1 SQLTypeName=CHAR Precision=5 Scale=0 MetadataClassName=java.lang.String
ObjectClassName=java.lang.String Value="A,B"
[29 Jul 2013 9:14]
Alexander Soklakov
Hi Bill, Thanks for your report. You need to upgrade c/J to at least 5.1.23 version where we fixed several problems with milliseconds and precision support. Here is the output of your class with 5.1.25 driver and 5.6.11 server: === Column=1 ColumnName=bi SQLType=-5 SQLTypeName=BIGINT Precision=20 Scale=0 MetadataClassName=java.lang.Long Column=2 ColumnName=biu SQLType=-5 SQLTypeName=BIGINT UNSIGNED Precision=20 Scale=0 MetadataClassName=java.math.BigInteger Column=3 ColumnName=i SQLType=4 SQLTypeName=INT Precision=11 Scale=0 MetadataClassName=java.lang.Integer ObjectClassName=java.lang.Integer Value="123" Column=4 ColumnName=iu SQLType=4 SQLTypeName=INT UNSIGNED Precision=10 Scale=0 MetadataClassName=java.lang.Long ObjectClassName=java.lang.Long Value="123" Column=5 ColumnName=mi SQLType=4 SQLTypeName=MEDIUMINT Precision=9 Scale=0 MetadataClassName=java.lang.Integer ObjectClassName=java.lang.Integer Value="123" Column=6 ColumnName=miu SQLType=4 SQLTypeName=MEDIUMINT UNSIGNED Precision=8 Scale=0 MetadataClassName=java.lang.Integer ObjectClassName=java.lang.Integer Value="123" Column=7 ColumnName=si SQLType=5 SQLTypeName=SMALLINT Precision=6 Scale=0 MetadataClassName=java.lang.Integer ObjectClassName=java.lang.Integer Value="123" Column=8 ColumnName=siu SQLType=5 SQLTypeName=SMALLINT UNSIGNED Precision=5 Scale=0 MetadataClassName=java.lang.Integer ObjectClassName=java.lang.Integer Value="123" Column=9 ColumnName=ti SQLType=-6 SQLTypeName=TINYINT Precision=4 Scale=0 MetadataClassName=java.lang.Integer Column=10 ColumnName=tiu SQLType=-6 SQLTypeName=TINYINT UNSIGNED Precision=3 Scale=0 MetadataClassName=java.lang.Integer Column=11 ColumnName=f SQLType=7 SQLTypeName=FLOAT Precision=12 Scale=31 MetadataClassName=java.lang.Float ObjectClassName=java.lang.Float Value="3.14" Column=12 ColumnName=ff SQLType=8 SQLTypeName=DOUBLE Precision=22 Scale=31 MetadataClassName=java.lang.Double ObjectClassName=java.lang.Double Value="3.14" Column=13 ColumnName=n SQLType=3 SQLTypeName=DECIMAL Precision=9 Scale=2 MetadataClassName=java.math.BigDecimal ObjectClassName=java.math.BigDecimal Value="3.14" Column=14 ColumnName=d SQLType=91 SQLTypeName=DATE Precision=10 Scale=0 MetadataClassName=java.sql.Date ObjectClassName=java.sql.Date Value="2013-07-29" Column=15 ColumnName=dt SQLType=93 SQLTypeName=DATETIME Precision=19 Scale=0 MetadataClassName=java.sql.Timestamp ObjectClassName=java.sql.Timestamp Value="2013-07-29 13:02:31.0" Column=16 ColumnName=dtp SQLType=93 SQLTypeName=DATETIME Precision=26 Scale=0 MetadataClassName=java.sql.Timestamp ObjectClassName=java.sql.Timestamp Value="2013-07-29 13:02:31.538874" Column=17 ColumnName=t SQLType=92 SQLTypeName=TIME Precision=10 Scale=0 MetadataClassName=java.sql.Time ObjectClassName=java.sql.Time Value="13:02:31" Column=18 ColumnName=tp SQLType=92 SQLTypeName=TIME Precision=17 Scale=0 MetadataClassName=java.sql.Time ObjectClassName=java.sql.Time Value="13:02:31" Column=19 ColumnName=ts SQLType=93 SQLTypeName=TIMESTAMP Precision=19 Scale=0 MetadataClassName=java.sql.Timestamp ObjectClassName=java.sql.Timestamp Value="2013-07-29 13:02:31.0" Column=20 ColumnName=tsp SQLType=93 SQLTypeName=TIMESTAMP Precision=26 Scale=0 MetadataClassName=java.sql.Timestamp ObjectClassName=java.sql.Timestamp Value="2013-07-29 13:02:31.538874" Column=21 ColumnName=y SQLType=91 SQLTypeName=YEAR Precision=4 Scale=0 MetadataClassName=java.sql.Date ObjectClassName=java.sql.Date Value="2013-01-01" Column=22 ColumnName=c SQLType=1 SQLTypeName=CHAR Precision=255 Scale=0 MetadataClassName=java.lang.String ObjectClassName=java.lang.String Value="char" Column=23 ColumnName=bc SQLType=-2 SQLTypeName=BINARY Precision=255 Scale=0 MetadataClassName=[B Column=24 ColumnName=v SQLType=12 SQLTypeName=VARCHAR Precision=255 Scale=0 MetadataClassName=java.lang.String ObjectClassName=java.lang.String Value="varchar" Column=25 ColumnName=lv SQLType=12 SQLTypeName=VARCHAR Precision=32767 Scale=0 MetadataClassName=java.lang.String ObjectClassName=java.lang.String Value="longvarchar" Column=26 ColumnName=vb SQLType=-3 SQLTypeName=VARBINARY Precision=255 Scale=0 MetadataClassName=[B Column=27 ColumnName=b1 SQLType=-7 SQLTypeName=BIT Precision=1 Scale=0 MetadataClassName=java.lang.Boolean Column=28 ColumnName=b2 SQLType=-3 SQLTypeName=BIT Precision=64 Scale=0 MetadataClassName=[B Column=29 ColumnName=lb SQLType=-4 SQLTypeName=LONGBLOB Precision=2147483647 Scale=0 MetadataClassName=[B Column=30 ColumnName=mb SQLType=-4 SQLTypeName=MEDIUMBLOB Precision=16777215 Scale=0 MetadataClassName=[B Column=31 ColumnName=b SQLType=-4 SQLTypeName=BLOB Precision=65535 Scale=0 MetadataClassName=[B Column=32 ColumnName=tb SQLType=-3 SQLTypeName=TINYBLOB Precision=255 Scale=0 MetadataClassName=[B Column=33 ColumnName=lt SQLType=-1 SQLTypeName=VARCHAR Precision=2147483647 Scale=0 MetadataClassName=java.lang.String ObjectClassName=java.lang.String Value="longtext" Column=34 ColumnName=mt SQLType=-1 SQLTypeName=VARCHAR Precision=16777215 Scale=0 MetadataClassName=java.lang.String ObjectClassName=java.lang.String Value="mediumtext" Column=35 ColumnName=tx SQLType=-1 SQLTypeName=VARCHAR Precision=65535 Scale=0 MetadataClassName=java.lang.String ObjectClassName=java.lang.String Value="text" Column=36 ColumnName=tt SQLType=-1 SQLTypeName=VARCHAR Precision=255 Scale=0 MetadataClassName=java.lang.String ObjectClassName=java.lang.String Value="tinytext" Column=37 ColumnName=e SQLType=1 SQLTypeName=CHAR Precision=1 Scale=0 MetadataClassName=java.lang.String ObjectClassName=java.lang.String Value="A" Column=38 ColumnName=s SQLType=1 SQLTypeName=CHAR Precision=5 Scale=0 MetadataClassName=java.lang.String ObjectClassName=java.lang.String Value="A,B" === I close this report as "Not a Bug", but feel free to reopen it if you find any other problem here.
[29 Jul 2013 17:33]
Bill Karwin
Confirmed; I downloaded Connector/J 5.1.25 and my test runs without throwing an exception. Thanks for the update.
Description: I'm testing Java object type mapping for MySQL 5.6 data types. I found that the new TIME data type with optional precision throws a Java exception when I try to fetch it as an object. How to repeat: Environment: MySQL Connector/J 5.1.22 JDK 1.6.0_51 Client: OS X 10.6.8 Snow Leopard Server: CentOS 6.4 64-bit MySQL 5.6.12 Steps to repeat: 1. Define a table with different data types, including a TIME column and a TIME(6) column. CREATE TABLE test.test_data_types ( bi BIGINT, biu BIGINT UNSIGNED, i INT, iu INT UNSIGNED, mi MEDIUMINT, miu MEDIUMINT UNSIGNED, si SMALLINT, siu SMALLINT UNSIGNED, ti TINYINT, tiu TINYINT UNSIGNED, f FLOAT, ff DOUBLE, n NUMERIC(9,2), d DATE, dt DATETIME, dtp DATETIME(6), t TIME, tp TIME(6), ts TIMESTAMP, tsp TIMESTAMP(6), y YEAR, c CHAR(255), bc BINARY(255), v VARCHAR(255), lv VARCHAR(32767), vb VARBINARY(255), b1 BIT(1), b2 BIT(64), lb LONGBLOB, mb MEDIUMBLOB, b BLOB, tb TINYBLOB, lt LONGTEXT, mt MEDIUMTEXT, tx TEXT, tt TINYTEXT, e ENUM('A', 'B', 'C'), s SET('A', 'B', 'C'), PRIMARY KEY (bi) ) 2. Insert some dummy data: INSERT INTO test.test_data_types SET bi = 123, biu = 123, i = 123, iu = 123, mi = 123, miu = 123, si = 123, siu = 123, ti = 123, tiu = 123, f = 3.14, ff = 3.14, n = 3.14, d = CURRENT_DATE(), dt = CURRENT_TIMESTAMP(), dtp = CURRENT_TIMESTAMP(6), t = CURRENT_TIME(), tp = CURRENT_TIME(6), ts = CURRENT_TIMESTAMP(), tsp = CURRENT_TIMESTAMP(6), y = 2013, c = 'char', v = 'varchar', lv = 'longvarchar', b1 = 1, b2 = 1, lb = 'longblob', mb = 'mediumblob', b = 'blob', tb = 'tinyblob', lt = 'longtext', mt = 'mediumtext', tx = 'text', tt = 'tinytext', e = 'A', s = 'A,B' 3. Query the table. SELECT * FROM test.test_data_types 4. Fetch results with the JDBC ResultSet method rs.next(), and then read columns with getObject(): This works for most data types (but not those that return byte[]). It works for the column defined as TIME data type. But when I fetch the column defined as TIME(6), it throws the following exception: SQLException: Bad format for Time '1231231231231231231231231231233.143.143.14 2013-07-182013-07-18 14:59:452013-07-18 14:59:45.1011414:59:4514:59:45.1011402013-07-18 14:59:452013-07-18 14:59:45.1011402013char?varchar longvarchalongblob mediumblobblotinyblolongtext mediumtexttextinytextAA,B' in column 18 SQLState: S1009 VendorError: 0 It does work correctly for DATETIME(6) and TIMESTAMP(6). It also fails if I used TIME(1) through TIME(5). I will attach my Java class that reproduces the behavior. Suggested fix: Debug why TIME(6) does not behaving the same way that DATETIME(6) and TIMESTAMP(6) do, and make it format TIME with precision correctly.