1. 问题描述
最近（2022年）做项目查询数据库操作的时候，有一个表的查询操作一直报错，Java 使用 JDBC 连接 MySQL 数据库，在操作值为0的timestamp类型时不能正确的处理，而是默认抛出一个异常：java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 7 to TIMESTAMP。这一问题在官方网站中有详细说明，详见如下链接：bugs.mysql.com/bug.php?id=…
在 JDBC 连接串中有一项属性：zeroDateTimeBehavior，可以用来配置出现这种情况时的处理策略，该属性有下列三个属性值：
| 值 | 描述 |
|---|---|
| exception | 默认值，即抛出SQL state [S1009]. Cannot convert value....的异常 |
| convertToNull | 将日期转换成NULL值 |
| round | 替换成最近的日期即0001-01-01 |
因此对于这类异常，可以考虑通过修改连接串，附加zeroDateTimeBehavior=convertToNull属性的方式予以规避，例如：
jdbc:mysql://localhost:3306/mydbname?characterEncoding=utf8&useSSL=false&zeroDateTimeBehavior=convertToNull
从另一个层面讲，这类异常的触发也与timestamp赋值的操作有关，如果能够在设计阶段和记录写入阶段做好逻辑判断，避免写入'0000-00-00 00:00:00'这类值，那么也可以避免出现Cannot convert value '0000-00-00 00:00:00' from column N to TIMESTAMP的错误。比如设定一个默认值为当前时间：
alter table 表名 modify CREATE_TIME timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间';
2. 问题数据
出现问题的数据和数据表结构。
3. 报错日志
java.sql.SQLException: Value '83533 7dad37625f4a193ba83cc87b7cd4fd783964019829美国第七舰队司令美国第七舰队%https://www.dvidshub.net/unit/USNF-7F40201005001026
dvidshub.net
dvidshub.net!DVIDS - Commander, U.S. 7th Fleet%https://www.dvidshub.net/unit/USNF-7F%https://www.dvidshub.net/unit/USNF-7F00100000-00-00 00:00:00
1659606065
1659606065
16596060652000
172.22.74.127vPhttps://d34w7g4gy10iej.cloudfront.net/video/2207/DOD_109138880/DOD_109138880.mp4Ynews_article_396401_a259e747c3648c47625c226be3ad8298_9f1efc70f1653de4fb1cb73a08f9c9ab.mp4Ynews_article_396401_a259e747c3648c47625c226be3ad8298_9f1efc70f1653de4fb1cb73a08f9c9ab.mp4
' can not be represented as java.sql.Timestamp
4. 详细日志
INFO - 本次 HYNewsCustomV2 轮询起始id是：83514，执行SQL：SELECT * FROM articles_news WHERE id > ? ORDER BY id LIMIT 100
ERROR - error getting messages from database
java.sql.SQLException: Value '83533 7dad37625f4a193ba83cc87b7cd4fd783964019829美国第七舰队司令美国第七舰队%https://www.dvidshub.net/unit/USNF-7F40201005001026
dvidshub.net
dvidshub.net!DVIDS - Commander, U.S. 7th Fleet%https://www.dvidshub.net/unit/USNF-7F%https://www.dvidshub.net/unit/USNF-7F00100000-00-00 00:00:00
1659606065
1659606065
16596060652000
172.22.74.127vPhttps://d34w7g4gy10iej.cloudfront.net/video/2207/DOD_109138880/DOD_109138880.mp4Ynews_article_396401_a259e747c3648c47625c226be3ad8298_9f1efc70f1653de4fb1cb73a08f9c9ab.mp4Ynews_article_396401_a259e747c3648c47625c226be3ad8298_9f1efc70f1653de4fb1cb73a08f9c9ab.mp4
' can not be represented as java.sql.Timestamp
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:947)
at com.mysql.jdbc.BufferRow.getTimestampFast(BufferRow.java:559)
at com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:5913)
at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:5581)
at com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:4541)
at com.zyyt.data.access.sources.DatabasePollableSource.rs2Message(DatabasePollableSource.java:78)
at com.zyyt.data.access.sources.DatabasePollableSource.getMessages(DatabasePollableSource.java:43)
at com.zyyt.data.access.sources.news.HYDatabaseSource.doPoll(HYDatabaseSource.java:55)
at com.zyyt.data.access.StatefulPollableSource.doProcess(StatefulPollableSource.java:51)
at org.apache.flume.source.AbstractPollableSource.process(AbstractPollableSource.java:60)
at org.apache.flume.source.PollableSourceRunner$PollingRunner.run(PollableSourceRunner.java:133)
at java.base/java.lang.Thread.run(Unknown Source)
ERROR - Unhandled exception, logging and sleeping for 5000ms
org.apache.flume.FlumeException: error getting messages from database
at com.zyyt.data.access.sources.DatabasePollableSource.getMessages(DatabasePollableSource.java:50)
at com.zyyt.data.access.sources.news.HYDatabaseSource.doPoll(HYDatabaseSource.java:55)
at com.zyyt.data.access.StatefulPollableSource.doProcess(StatefulPollableSource.java:51)
at org.apache.flume.source.AbstractPollableSource.process(AbstractPollableSource.java:60)
at org.apache.flume.source.PollableSourceRunner$PollingRunner.run(PollableSourceRunner.java:133)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Value '83533 7dad37625f4a193ba83cc87b7cd4fd783964019829美国第七舰队司令美国第七舰队%https://www.dvidshub.net/unit/USNF-7F40201005001026
dvidshub.net
dvidshub.net!DVIDS - Commander, U.S. 7th Fleet%https://www.dvidshub.net/unit/USNF-7F%https://www.dvidshub.net/unit/USNF-7F00100000-00-00 00:00:00
1659606065
1659606065
16596060652000
172.22.74.127vPhttps://d34w7g4gy10iej.cloudfront.net/video/2207/DOD_109138880/DOD_109138880.mp4Ynews_article_396401_a259e747c3648c47625c226be3ad8298_9f1efc70f1653de4fb1cb73a08f9c9ab.mp4Ynews_article_396401_a259e747c3648c47625c226be3ad8298_9f1efc70f1653de4fb1cb73a08f9c9ab.mp4
' can not be represented as java.sql.Timestamp
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:947)
at com.mysql.jdbc.BufferRow.getTimestampFast(BufferRow.java:559)
at com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:5913)
at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:5581)
at com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:4541)
at com.zyyt.data.access.sources.DatabasePollableSource.rs2Message(DatabasePollableSource.java:78)
at com.zyyt.data.access.sources.DatabasePollableSource.getMessages(DatabasePollableSource.java:43)
... 5 more
