Bug #99433 select intTypeField,dataTimeTypeField from mysql-table throws SQLDataException
Submitted: 4 May 9:19 Modified: 5 May 7:14
Reporter: lao wei lao Email Updates:
Status: Open 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

[4 May 9:19] lao wei lao
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.
[5 May 1:18] lao wei lao
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.
[5 May 1:25] lao wei lao
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.
[5 May 7:14] lao wei lao
fixed it by adding default constructor in the Mybatis DO class which only had a parameter constructor for the issue table.
[26 May 9:24] Anthony Gerrard
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