MySQL Bugs: #99433: select intTypeField，dataTimeTypeField from mysql-table throws SQLDataException

Bug #99433	select intTypeField，dataTimeTypeField from mysql-table throws SQLDataException
Submitted:	4 May 2020 9:19	Modified:	3 Sep 2020 21:48
Reporter:	lao wei lao	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	8.0.19	OS:	CentOS (7)
Assigned to:		CPU Architecture:	x86
Tags:	select SQLDataException

Description:
select intTypeField，dataTimeTypeField from mysql-table throws SQLDataException: Unsupported conversion from LONG to java.sql.Timestamp since mysql jdbc driver update to 8.0.16

How to repeat:
mysql database version: 5.7.24-log
springboot 2.2.6 release (include mysql-connector-java v8.0.19)
mybatis-spring-boot-starter: 2.1.2
mapper.xml:
1. select id, intTypeField, dataTimeTypeField from mysqltable where id=123 for update
2. select id, dataTimeTypeField, intTypeField from mysqltable where id=123 for update
the first sql statement will throws java.sql.SQLDataException: Unsupported conversion from LONG to java.sql.Timestamp,
but the second sql statement (only exchange location of the last two fields) will be ok. 
These two sql statements executed well under springboot 2.2.6 release + mysql-connector-java v8.0.15, but since 8.0.16 running the first sql  began to throw java.sql.SQLDataException: Unsupported conversion from LONG to java.sql.Timestamp.

Suggested fix:
I had submit the issue to springboot,
https://github.com/spring-projects/spring-boot/issues/21300
they updated mysql jdbc driver from 8.0.15 to 8.0.16 since springboot 2.1.5.
please tell me mysql will fix it or not.

1. prepare
CREATE TABLE test (
  id bigint(18) unsigned zerofill NOT NULL AUTO_INCREMENT,
  name varchar(30) COLLATE utf8mb4_bin NOT NULL,
  type varchar(10)  COLLATE utf8mb4_bin NOT NULL,
  msg text COLLATE utf8mb4_bin NOT NULL,
  status varchar(1) COLLATE utf8mb4_bin NOT NULL,
  count int(5) unsigned NOT NULL,
  create_time datetime NOT NULL,
  modified_time datetime DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY idx_type (name, type) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

insert into test values('lao', 'type0', 'message...', 'W', 0, '2020-05-04 12:05:55', '2020-05-04 12:05:55');

2. mapper.xml settings
Columns                     Jdbctype        Java-Type
-------------------------------------------------------------
name、type、status          VARCHAR         String
count                       INTEGER         Integer
create_time、modified_time  TIMESTAMP       Date
msg                         LONGVARCHAR     String

3. SQLDataException 
(1) exception-A
SQL: select id, name, type, status, count, create_time, modified_time, msg from test 
where  name=#{name} and type=#{type}
for update
SQLDataException: Unsupported coversion from LONG to java.sql.Timestamp
it seemed the issue has something to do with count(which type is unsigned int) and create_time/modified_time columns. 

(2) exception-B
SQL: select id, name, type, msg, status, count, create_time, modified_time from test 
where  name=#{name} and type=#{type}
for update
SQLDataException: Unsupported coversions from String ('W') to java.sql.Timestamp
it seemed the issue has something to do with status(which value is 'W') and create_time/modified_time columns. 

4. Weird
at the time of writing, i can't reproduce the issue:
1. create a test table which has samed columns with the issue table, and copy all datas into it.
2. change the springboot project: do exception-A/exception-B query from the test table and the issue table successively.
3. debug the project, got correct result from the test table but got SQLDataException from the issue table.

Correct last comment in (2) exception-B:
SQL: select id, name, type, msg, status, count, create_time, modified_time from test 
where  name=#{name} and type=#{type}
for update
SQLDataException: Cannot convert string 'W' to java.sql.Timestamp value
it seemed the issue has something to do with status(which value is 'W') and create_time/modified_time columns.

fixed it by adding default constructor in the Mybatis DO class which only had a parameter constructor for the issue table.

I can confirm there has been a change in behaviour.  varchar2 column that stores date time strings that are typed as ZonedDateTime.  null values sometimes now result in this SQLException.  Still effecting 8.0.20

Hi lao wei lao,

Thank you for this bug report. It's not clear for me if you figured it out or not, but if you want us to check this and maybe fix something, then please take away all external components from your test case and present us a test case just using Connector/J alone.

The same goes to Anthony Gerrard. If you have a test case for us to check then please get all details and we'll gladly do it.

Thanks,

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