一 问题:
今天收到告警邮件:
2017-02-27 10:21:29
<br></br>参数:
***********
status=0
<br></br>异常:org.springframework.dao.DataIntegrityViolationException:
### Error updating database. Cause: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '19719700-01-01 00:01:00' for column 'excu_date' at row 1
### The error may involve com.bj58.daojia.crm.crmCustom.ext.CrmCustomPushExtDao.insert-Inline
### The error occurred while setting parameters
### SQL: insert into ************ values (?, ?, ?, ?, ?,?)
### Cause: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '19719700-01-01 00:01:00' for column 'excu_date' at row 1
; SQL []; Data truncation: Incorrect datetime value: '19719700-01-01 00:01:00' for column 'excu_date' at row 1; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '19719700-01-01 00:01:00' for column 'excu_date' at row 1<br></br>org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:100)
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)
org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:365)
$Proxy38.insert(Unknown Source)
org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:237)
org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:79)
org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:40)
$Proxy83.insert(Unknown Source)
sun.reflect.GeneratedMethodAccessor931.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:597)
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:85)
com.bj58.daojia.crm.helper.utils.ExceptionHandleUtil.around(ExceptionHandleUtil.java:40)
sun.reflect.GeneratedMethodAccessor107.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:597)
org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:621)
org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:610)
org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:68)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
$Proxy84.insert(Unknown Source)
com.bj58.daojia.crm.crmCustom.service.impl.IcrmCustomPushService.insert(IcrmCustomPushService.java:44)
sun.reflect.GeneratedMethodAccessor1007.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:597)
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:85)
com.bj58.daojia.crm.helper.utils.ExceptionHandleUtil.around(ExceptionHandleUtil.java:40)
sun.reflect.GeneratedMethodAccessor107.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:597)
org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:621)
org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:610)
org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:68)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
$Proxy85.insert(Unknown Source)
com.bj58.daojia.crm.crmCustomService.controller.CrmCustomServiceController.deletePosition(CrmCustomServiceController.java:363)
com.bj58.daojia.crm.crmCustomService.controller.CrmCustomServiceController$$FastClassBySpringCGLIB$$d71598e4.invoke(<generated>)
org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:708)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:85)
com.bj58.daojia.crm.helper.utils.ExceptionHandleUtil.around(ExceptionHandleUtil.java:40)
sun.reflect.GeneratedMethodAccessor107.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:597)
org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:621)
org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:610)
org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:68)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:644)
com.bj58.daojia.crm.crmCustomService.controller.CrmCustomServiceController$$EnhancerBySpringCGLIB$$59368b1a.deletePosition(<generated>)
sun.reflect.GeneratedMethodAccessor2053.invoke(Unknown Source)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:597)
org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:215)
org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:749)
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:689)
org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:83)
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:938)
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:870)
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:961)
org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:863)
javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:837)
javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
com.bj58.daojia.crm.filter.ControllerFilter.doFilter(ControllerFilter.java:29)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
com.bj58.daojia.sso.filter.LoginFilter.doFilter(LoginFilter.java:58)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
com.daojia.spat.dtracker.springmvc.plugin.TrakerPlugin.doFilter(TrakerPlugin.java:118)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1023)
org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
java.lang.Thread.run(Thread.java:662)
出错原因,告警邮件上面写的比较明确了,就是往数据库插入数据的时候,“执行时间”数据异常校验不过,导致异常。
二 分析:
为啥异常呢?看下对应代码:
@Autowired
CrmCustomPushExtDao crmCustomPushExtDao;
private static DateFormat df = new SimpleDateFormat("yyyy-mm-dd");
private static final Logger log = LoggerFactory.getLogger(IcrmCustomPushService.class);
@Override
public int insert(Long customId,Integer type) { //1新增 2删除
CrmCustomPush crmCustomPush=new CrmCustomPush();
crmCustomPush.setCustomId(customId);
crmCustomPush.setStatus(0);//待处理
crmCustomPush.setCreateDate(new Date());
crmCustomPush.setCustomType(type);
try {
Date excuDate=df.parse("1970-01-01");
crmCustomPush.setExcuDate(excuDate);
} catch (ParseException e) {
e.printStackTrace();
}
List<CrmCustomPush> crmCustomPushs=crmCustomPushExtDao.selectByCustomId(customId);
if(crmCustomPushs.size()>0){
log.error("已存在商家id未推送:"+customId);
}else {
crmCustomPushExtDao.insert(crmCustomPush);
}
return 1;
对比一下,就发现这个异常的字段不是页面传入的,排除了入参校验不严问题。那就落在
Date excuDate=df.parse("1970-01-01");
这一行上了,我们知道要尽量少的创建SimpleDateFormat实例对象,每次处理一个时间信息的时候,就需要创建一个SimpleDateFormat实例对象,然后再丢弃这个对象,耗费内存。所以这段代码里面改了为静态的方法。结合日志来看,不是每次都有问题,有时候抛异常就怀疑是并发问题。
那么可以验证下自己的想法:
。。。。
public static void main(String[] agrs){
ExecutorService tmpool = Executors.newFixedThreadPool(100);
for(int i=0;i<100;i++)
{
Thread t1 = new MyThread();
tmpool.execute(t1);
}
tmpool.shutdown();
System.out.println("pool close");
}
public static Date getDate(){
try {
return df.parse("1970-01-01");
} catch (ParseException e) {
// TODO Auto-generated catch block
System.out.println(e.getMessage());
e.printStackTrace();
}
return null;
}
}
class MyThread extends Thread {
@Override
public void run() {
System.out.println(Thread.currentThread().getName() + ":date:"+IcrmCustomPushService.getDate());
}
}
可以看到问题复现,比如时间不一致,报错线程启动异常等。
at java.text.SimpleDateFormat.parse(SimpleDateFormat.java:1514)
at java.text.DateFormat.parse(DateFormat.java:364)
at com.bj58.daojia.crm.crmCustom.service.impl.IcrmCustomPushService.getDate(IcrmCustomPushService.java:81)
at com.bj58.daojia.crm.crmCustom.service.impl.MyThread.run(IcrmCustomPushService.java:94)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Exception in thread "pool-1-thread-40" java.lang.NumberFormatException: For input string: ""
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
at java.lang.Long.parseLong(Long.java:601)
at java.lang.Long.parseLong(Long.java:631)
at java.text.DigitList.getLong(DigitList.java:195)
at java.text.DecimalFormat.parse(DecimalFormat.java:2051)
at java.text.SimpleDateFormat.subParse(SimpleDateFormat.java:1869)
at java.text.SimpleDateFormat.parse(SimpleDateFormat.java:1514)
at java.text.DateFormat.parse(DateFormat.java:364)
at com.bj58.daojia.crm.crmCustom.service.impl.IcrmCustomPushService.getDate(IcrmCustomPushService.java:81)
at com.bj58.daojia.crm.crmCustom.service.impl.MyThread.run(IcrmCustomPushService.java:94)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
java.lang.NumberFormatException: For input string: ""
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
at java.lang.Long.parseLong(Long.java:601)
at java.lang.Long.parseLong(Long.java:631)
at java.text.DigitList.getLong(DigitList.java:195)
at java.text.DecimalFormat.parse(DecimalFormat.java:2051)
at java.text.SimpleDateFormat.subParse(SimpleDateFormat.java:2162)
at java.text.SimpleDateFormat.parse(SimpleDateFormat.java:1514)
at java.text.DateFormat.parse(DateFormat.java:364)
at com.bj58.daojia.crm.crmCustom.service.impl.IcrmCustomPushService.getDate(IcrmCustomPushService.java:81)
at com.bj58.daojia.crm.crmCustom.service.impl.MyThread.run(IcrmCustomPushService.java:94)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
java.lang.NumberFormatException: For input string: ""
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
at java.lang.Long.parseLong(Long.java:601)
at java.lang.Long.parseLong(Long.java:631)
at java.text.DigitList.getLong(DigitList.java:195)
at java.text.DecimalFormat.parse(DecimalFormat.java:2051)
at java.text.SimpleDateFormat.subParse(SimpleDateFormat.java:2162)
at java.text.SimpleDateFormat.parse(SimpleDateFormat.java:1514)
at java.text.DateFormat.parse(DateFormat.java:364)
at com.bj58.daojia.crm.crmCustom.service.impl.IcrmCustomPushService.getDate(IcrmCustomPushService.java:81)
at com.bj58.daojia.crm.crmCustom.service.impl.MyThread.run(IcrmCustomPushService.java:94)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Exception in thread "pool-1-thread-35" java.lang.NumberFormatException: multiple points
at sun.misc.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:1890)
at sun.misc.FloatingDecimal.parseDouble(FloatingDecimal.java:110)
at java.lang.Double.parseDouble(Double.java:538)
at java.text.DigitList.getDouble(DigitList.java:169)
at java.text.DecimalFormat.parse(DecimalFormat.java:2056)
at java.text.SimpleDateFormat.subParse(SimpleDateFormat.java:1869)
at java.text.SimpleDateFormat.parse(SimpleDateFormat.java:1514)
at java.text.DateFormat.parse(DateFormat.java:364)
at com.bj58.daojia.crm.crmCustom.service.impl.IcrmCustomPushService.getDate(IcrmCustomPushService.java:81)
at com.bj58.daojia.crm.crmCustom.service.impl.MyThread.run(IcrmCustomPushService.java:94)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
pool-1-thread-39:date:Thu Jan 01 00:01:00 CST 1970
pool-1-thread-88:date:Thu Jan 01 00:01:00 CST 1970
pool-1-thread-33:date:Thu Jan 01 00:01:00 CST 1970
pool-1-thread-30:date:Thu Jan 01 00:01:00 CST 1970
pool-1-thread-29:date:Thu Jan 01 00:01:00 CST 1970
pool-1-thread-26:date:Thu Jan 01 00:01:00 CST 1970
pool-1-thread-86:date:Thu Jan 01 00:01:00 CST 1970
pool-1-thread-25:date:Tue Sep 11 00:00:00 CST 4981
pool-1-thread-21:date:Sat Jan 01 00:01:00 CST 11000000
pool-1-thread-18:date:Mon Jan 11 00:11:00 CST 19719700
pool-1-thread-90:date:Mon Jan 11 00:11:00 CST 19719700
pool-1-thread-17:date:Mon Jan 11 00:11:00 CST 19719700
三 解决:
既然定位是容易忽视的并发的问题,我们看下jdk文档介绍:
这个太隐蔽了,不熟悉的容易忽略掉,因为习惯了使用工程中的dateutil来处理代码的问题。
看一下代码:
start = subParse(text, start, tag, count, obeyCount,
ambiguousYear, pos,
useFollowingMinusSignAsDelimiter, calb);
大概实现思路就是根据截取字符串,给calendar设置,我理解就是多个线程同时拥有程持有了同一个SimpleDateFormat的实例,分别调用parse方法,
线程1处理部分,设置calendar值。中断。
线程2处理部分,设置calendar值。中断。线程1再来calendar值已经被修改了,就会引发各种问题。
解决方法:
1.创建新实例。
DateFormat df = new SimpleDateFormat("yyyy-mm-dd")
Date excuDate=df.parse("1970-01-01");
优点:将有线程安全问题的对象由共享变为局部私有都能避免多线程问题,
缺点:不过也加重了创建对象的负担。一般情况影响不大,尤其是负载不高的时候。
2. 同步:
可以看见打到效果,线程执行的不是顺序的,看结果即可。
缺点:高并发时有一定性能问题。
3.不用jdk,改为其他线程安全的类,比如:apache的commons-lang包的DateUtils和DateFormatUtils类,这两个类的方法是线程安全的。
4. threadlocal
private static ThreadLocal<DateFormat> df = new ThreadLocal<DateFormat>(){
@Override
protected DateFormat initialValue() {
return new SimpleDateFormat("yyyy-mm-dd");
}
};
public static void main(String[] agrs){
ExecutorService tmpool = Executors.newFixedThreadPool(100);
for(int i=0;i<100;i++)
{
Thread t1 = new MyThread();
tmpool.execute(t1);
}
tmpool.shutdown();
System.out.println("pool close");
}
public static Date getDate(){
try {
return df.get().parse("1970-01-01");
} catch (ParseException e) {
// TODO Auto-generated catch block
System.out.println(e.getMessage());
e.printStackTrace();
}
return null;
}
}
class MyThread extends Thread {
@Override
public void run() {
System.out.println(Thread.currentThread().getName() + ":date:"+IcrmCustomPushService.getDate());
}
}
对比下同步机制:线程局部变量(threadlocal)本质上是线程对应一个map,map的key就是threadLocal,而值就是我们set的那个值,每次线程在get的时候,都从自己的变量中取值,既然从自己的变量中取值,那肯定就不存在线程安全问题,总体来讲,对比同步方式就是:空间换取时间。
可以结合源码看看背后实现。