欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Mysql timestamp 字段值为 '0000-00-00 00:00:00' 报错问题解决

程序员文章站 2022-05-02 16:35:44
...

在使用solr7导入mysql 数据的时候,因为表中字段有 timestamp 格式的字段 ,值为'0000-00-00 00:00:00',报以下异常信息

2018-04-27 11:52:32.153 WARN  (Thread-8) [   x:zhaoyl_core] o.a.s.h.d.JdbcDataSource Error reading data 
java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
	at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:937)
	at com.mysql.jdbc.ByteArrayRow.getTimestampFast(ByteArrayRow.java:130)
	at com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:5918)
	at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:5588)
	at com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:4549)
	at com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:4695)
	at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.getARow(JdbcDataSource.java:402)
	at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator$1.next(JdbcDataSource.java:382)
	at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator$1.next(JdbcDataSource.java:374)
	at org.apache.solr.handler.dataimport.EntityProcessorBase.getNext(EntityProcessorBase.java:134)
	at org.apache.solr.handler.dataimport.SqlEntityProcessor.nextRow(SqlEntityProcessor.java:75)
	at org.apache.solr.handler.dataimport.EntityProcessorWrapper.nextRow(EntityProcessorWrapper.java:267)
	at org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:476)
	at org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:415)
	at org.apache.solr.handler.dataimport.DocBuilder.doFullDump(DocBuilder.java:330)
	at org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:233)
	at org.apache.solr.handler.dataimport.DataImporter.doFullImport(DataImporter.java:424)
	at org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:483)
	at org.apache.solr.handler.dataimport.DataImporter.lambda$runAsync$0(DataImporter.java:466)
	at org.apache.solr.handler.dataimport.DataImporter$$Lambda$200/1749073945.run(Unknown Source)
	at java.lang.Thread.run(Thread.java:745)
2018-04-27 11:52:32.160 ERROR (Thread-8) [   x:zhaoyl_core] o.a.s.h.d.EntityProcessorBase getNext() failed for query 'select id,cosmetic_id, uid_wechat,uid,user_name,nick_name,create_time,update_time, content,content_view,auditor,audit_time,audit_state,filter_flag,state from cosmetic_comment':org.apache.solr.handler.dataimport.DataImportHandlerException: Error reading data from database Processing Document # 46
	at org.apache.solr.handler.dataimport.DataImportHandlerException.wrapAndThrow(DataImportHandlerException.java:69)
	at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.getARow(JdbcDataSource.java:442)
	at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator$1.next(JdbcDataSource.java:382)
	at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator$1.next(JdbcDataSource.java:374)
	at org.apache.solr.handler.dataimport.EntityProcessorBase.getNext(EntityProcessorBase.java:134)
	at org.apache.solr.handler.dataimport.SqlEntityProcessor.nextRow(SqlEntityProcessor.java:75)
	at org.apache.solr.handler.dataimport.EntityProcessorWrapper.nextRow(EntityProcessorWrapper.java:267)
	at org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:476)
	at org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:415)
	at org.apache.solr.handler.dataimport.DocBuilder.doFullDump(DocBuilder.java:330)
	at org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:233)
	at org.apache.solr.handler.dataimport.DataImporter.doFullImport(DataImporter.java:424)
	at org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:483)
	at org.apache.solr.handler.dataimport.DataImporter.lambda$runAsync$0(DataImporter.java:466)
	at org.apache.solr.handler.dataimport.DataImporter$$Lambda$200/1749073945.run(Unknown Source)
	at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
	at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:937)
	at com.mysql.jdbc.ByteArrayRow.getTimestampFast(ByteArrayRow.java:130)
	at com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:5918)
	at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:5588)
	at com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:4549)
	at com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:4695)
	at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.getARow(JdbcDataSource.java:402)
	... 14 more

2018-04-27 11:52:32.231 ERROR (Thread-8) [   x:zhaoyl_core] o.a.s.h.d.DocBuilder Exception while processing: cosmetic_comment document : SolrInputDocument(fields: []):org.apache.solr.handler.dataimport.DataImportHandlerException: Error reading data from database Processing Document # 46
	at org.apache.solr.handler.dataimport.DataImportHandlerException.wrapAndThrow(DataImportHandlerException.java:69)
	at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.getARow(JdbcDataSource.java:442)
	at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator$1.next(JdbcDataSource.java:382)
	at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator$1.next(JdbcDataSource.java:374)
	at org.apache.solr.handler.dataimport.EntityProcessorBase.getNext(EntityProcessorBase.java:134)
	at org.apache.solr.handler.dataimport.SqlEntityProcessor.nextRow(SqlEntityProcessor.java:75)
	at org.apache.solr.handler.dataimport.EntityProcessorWrapper.nextRow(EntityProcessorWrapper.java:267)
	at org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:476)
	at org.apache.solr.handler.dataimport.DocBuilder.buildDocument(DocBuilder.java:415)
	at org.apache.solr.handler.dataimport.DocBuilder.doFullDump(DocBuilder.java:330)
	at org.apache.solr.handler.dataimport.DocBuilder.execute(DocBuilder.java:233)
	at org.apache.solr.handler.dataimport.DataImporter.doFullImport(DataImporter.java:424)
	at org.apache.solr.handler.dataimport.DataImporter.runCmd(DataImporter.java:483)
	at org.apache.solr.handler.dataimport.DataImporter.lambda$runAsync$0(DataImporter.java:466)
	at org.apache.solr.handler.dataimport.DataImporter$$Lambda$200/1749073945.run(Unknown Source)
	at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
	at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:937)
	at com.mysql.jdbc.ByteArrayRow.getTimestampFast(ByteArrayRow.java:130)
	at com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:5918)
	at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:5588)
	at com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:4549)
	at com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:4695)
	at org.apache.solr.handler.dataimport.JdbcDataSource$ResultSetIterator.getARow(JdbcDataSource.java:402)
	... 14 more

解决方法:

    给jdbc  url加上  zeroDateTimeBehavior参数: 
datasource.url=jdbc:mysql://127.0.0.1:3306/zhaoyl_material?zeroDateTimeBehavior=convertToNull
zeroDateTimeBehavior=round是为了指定MySql中的DateTime字段默认值查询时的处理方式;默认是抛出异常, 
对于值为0000-00-00 00:00:00(默认值)的纪录,如下两种配置,会返回不同的结果: 

zeroDateTimeBehavior=round  0001-01-01 00:00:00.0

zeroDateTimeBehavior=convertToNull  null   

Mysql timestamp 字段值为 '0000-00-00 00:00:00' 报错问题解决