实战

回到首页 个人中心 反馈问题 注册登录
下载APP
首页 课程 实战 体系课 手记 专栏 慕课教程

更新数据是报错

来源：4-20 用户信息修改接口测试

平_常_心

2018-11-10

在更新数据库是遇到了这个问题

2018-11-10 17:19:02.362 ERROR 13800 --- [p-nio-80-exec-9] c.s.g.r.c.aop.GlobalExceptionHandler     : 运行时异常:

java.lang.RuntimeException: org.springframework.dao.DataIntegrityViolationException: 
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: '1970-01-01' for column 'begin_time' at row 1
### The error may involve com.stylefeng.guns.rest.common.persistence.dao.MoocUserTMapper.updateById-Inline
### The error occurred while setting parameters
### SQL: UPDATE mooc_user_t  SET nick_name=?,  user_sex=?,    email=?,  user_phone=?,  address=?,    biography=?,  life_state=?,  begin_time=?,  update_time=?  WHERE UUID=?
### Cause: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: '1970-01-01' for column 'begin_time' at row 1
; ]; Data truncation: Incorrect datetime value: '1970-01-01' for column 'begin_time' at row 1; nested exception is com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: '1970-01-01' for column 'begin_time' at row 1
org.springframework.dao.DataIntegrityViolationException: 
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: '1970-01-01' for column 'begin_time' at row 1
### The error may involve com.stylefeng.guns.rest.common.persistence.dao.MoocUserTMapper.updateById-Inline
### The error occurred while setting parameters
### SQL: UPDATE mooc_user_t  SET nick_name=?,  user_sex=?,    email=?,  user_phone=?,  address=?,    biography=?,  life_state=?,  begin_time=?,  update_time=?  WHERE UUID=?
### Cause: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: '1970-01-01' for column 'begin_time' at row 1
; ]; Data truncation: Incorrect datetime value: '1970-01-01' for column 'begin_time' at row 1; nested exception is com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: '1970-01-01' for column 'begin_time' at row 1
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:104)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
	at com.sun.proxy.$Proxy91.update(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
	at com.sun.proxy.$Proxy95.updateById(Unknown Source)
	at com.stylefeng.guns.rest.modular.user.UserServiceImpl.updateUserInfo(UserServiceImpl.java:119)
	at com.alibaba.dubbo.common.bytecode.Wrapper1.invokeMethod(Wrapper1.java)
	at com.alibaba.dubbo.rpc.proxy.javassist.JavassistProxyFactory$1.doInvoke(JavassistProxyFactory.java:45)
	at com.alibaba.dubbo.rpc.proxy.AbstractProxyInvoker.invoke(AbstractProxyInvoker.java:71)
	at com.alibaba.dubbo.config.invoker.DelegateProviderMetaDataInvoker.invoke(DelegateProviderMetaDataInvoker.java:48)
	at com.alibaba.dubbo.rpc.protocol.InvokerWrapper.invoke(InvokerWrapper.java:52)
	at com.alibaba.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:61)
	at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
	at com.alibaba.dubbo.monitor.support.MonitorFilter.invoke(MonitorFilter.java:74)
	at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
	at com.alibaba.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:41)
	at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
	at com.alibaba.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:77)
	at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
	at com.alibaba.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:71)
	at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
	at com.alibaba.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:131)
	at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
	at com.alibaba.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:37)
	at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
	at com.alibaba.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:37)
	at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68)
	at com.alibaba.dubbo.rpc.protocol.dubbo.DubboProtocol$1.reply(DubboProtocol.java:98)
	at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.handleRequest(HeaderExchangeHandler.java:96)
	at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.received(HeaderExchangeHandler.java:168)
	at com.alibaba.dubbo.remoting.transport.DecodeHandler.received(DecodeHandler.java:50)
	at com.alibaba.dubbo.remoting.transport.dispatcher.ChannelEventRunnable.run(ChannelEventRunnable.java:79)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect datetime value: '1970-01-01' for column 'begin_time' at row 1
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:960)
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:388)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3409)
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
	at com.alibaba.druid.wall.WallFilter.preparedStatement_execute(WallFilter.java:619)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
	at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_execute(FilterEventAdapter.java:440)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3407)
	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
	at com.sun.proxy.$Proxy117.execute(Unknown Source)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
	at com.sun.proxy.$Proxy115.update(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
	at com.sun.proxy.$Proxy115.update(Unknown Source)
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)
	at com.baomidou.mybatisplus.plugins.OptimisticLockerInterceptor.intercept(OptimisticLockerInterceptor.java:155)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
	at com.sun.proxy.$Proxy114.update(Unknown Source)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
	... 35 more

	at com.alibaba.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:105) ~[dubbo-2.6.0.jar:2.6.0]
	at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68) ~[dubbo-2.6.0.jar:2.6.0]
	at com.alibaba.dubbo.monitor.support.MonitorFilter.invoke(MonitorFilter.java:74) ~[dubbo-2.6.0.jar:2.6.0]
	at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68) ~[dubbo-2.6.0.jar:2.6.0]
	at com.alibaba.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:41) ~[dubbo-2.6.0.jar:2.6.0]
	at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68) ~[dubbo-2.6.0.jar:2.6.0]
	at com.alibaba.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:77) ~[dubbo-2.6.0.jar:2.6.0]
	at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68) ~[dubbo-2.6.0.jar:2.6.0]
	at com.alibaba.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:71) ~[dubbo-2.6.0.jar:2.6.0]
	at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68) ~[dubbo-2.6.0.jar:2.6.0]
	at com.alibaba.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:131) ~[dubbo-2.6.0.jar:2.6.0]
	at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68) ~[dubbo-2.6.0.jar:2.6.0]
	at com.alibaba.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:37) ~[dubbo-2.6.0.jar:2.6.0]
	at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68) ~[dubbo-2.6.0.jar:2.6.0]
	at com.alibaba.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:37) ~[dubbo-2.6.0.jar:2.6.0]
	at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:68) ~[dubbo-2.6.0.jar:2.6.0]
	at com.alibaba.dubbo.rpc.protocol.dubbo.DubboProtocol$1.reply(DubboProtocol.java:98) ~[dubbo-2.6.0.jar:2.6.0]
	at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.handleRequest(HeaderExchangeHandler.java:96) ~[dubbo-2.6.0.jar:2.6.0]
	at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.received(HeaderExchangeHandler.java:168) ~[dubbo-2.6.0.jar:2.6.0]
	at com.alibaba.dubbo.remoting.transport.DecodeHandler.received(DecodeHandler.java:50) ~[dubbo-2.6.0.jar:2.6.0]
	at com.alibaba.dubbo.remoting.transport.dispatcher.ChannelEventRunnable.run(ChannelEventRunnable.java:79) ~[dubbo-2.6.0.jar:2.6.0]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[na:1.8.0_181]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[na:1.8.0_181]
	at java.lang.Thread.run(Thread.java:748) ~[na:1.8.0_181]

数据版本使用的是MySql5.7
数据库版本

字符集使用的是utf8
字符集

老师在4-13章节中只是对Begin_Time字段进行了long到Date(sql包中的Date)的转换
long到Date的转换

写回答

2回答

ChlZhYa

2018-12-30

这个问题我今天遇到了。搞了好久，最后发现是因为timestamp范围的问题。timestamp的默认范围是1970-01-01 00:00:00 到 2037-12-31 23:59:59（经过测试得出范围为1970-01-01 08:00:01 到2038-01-19 11:14:07）。当我们在更新时如果不传beginTime，那userInfoModel.getBeginTime返回的值是long的默认值0.     time2Date中的处理是return new java.sql.Date(0)。这个值正好就是不在mysql timestamp范围内的值。所以会产生异常。

由这个默认值引发的问题我个人觉得表示时间的参数类型最好还是设为Date。不传值的情况下  默认值是null，这样在更新语句执行的时候，会因为这个参数值为null被过滤掉，所以不会给begintime赋值，就没有异常出现。

当然如果jpa的更新字段过滤条件和mybatis的if标签中的默认判断一致，即beginTime != null && beginTime != ''。那时间参数的类型设置为String也是可以的。

0
0

melanch_0001

2018-11-10

datetime value: '1970-01-01' for column 'begin_time'

可能是程序里时间转换的时候少写了 .getTime() 转成 long导致的。这种错误一般你直接用navicat 试试对应的sql就清楚了。

0
5
asdascwIUVX
回复
平_常_心
回复 平_常_心：啥意思？？？？？
2024-11-22
共5条回复

Dubbo主流版本打造仿猫眼项目 理解微服务核心思想

实战Dubbo项目+面试技巧，老司机带你畅游微服务

1410 学习 · 582 问题

查看课程

相似问题

启动项目报了这个错误

回答 1

consumer启动报错

回答 1

FTPUtil报空指针异常，但7-9和7-10 章节可以测出结果

回答 2

guns_rest连接数据库错误。

回答 2

在guns-rest模块的yml中配置自己的数据库连接，无法启动项目？

回答 1

打开慕课网App查看更多内容