mysql执行插入操作，抛出异常，但是该条数据已经插入

Question

异常信息如下，insert操作时报异常了，但是在后台能够查到这条orderlog,各位有碰到类似的吗

17/04/2016 15:47:00  WARN Proxool:149 - #16395 was active for 53547 milliseconds and has been removed automaticaly. The Thread responsible was named 'catalina-exec-52', but the last SQL it performed is unknown because the trace property is not enabled.

17/04/2016 15:47:00  WARN Proxool:149 - #16396 was active for 45967 milliseconds and has been removed automaticaly. The Thread responsible was named 'catalina-exec-32', but the last SQL it performed is unknown because the trace property is not enabled.
17/04/2016 15:47:00 ERROR BaseDAO:540 - create Statement failed.com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 46,837 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.
17/04/2016 15:47:00 ERROR BaseDAO:540 - create Statement failed.com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 53,547 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.
17/04/2016 15:47:00  WARN EPGAuthImpl:689 - serviceOrder exception
com.*.*.SQLExecFailedException: insert into orderlog (userId,providerId,productGroupId,productId,serviceId,contentId,orderTime,cancelTime,activeTime,deactiveTime,duration,count,flux,status,purchaseType,paymentType,fee,balance,accountNo,productType,screenClass,orderType,continueFlag,continueStatus) values ('25822700385',null,51,'H20131031',null,'00000001000000010000000001250524','2016-04-17 15:44:54',null,'2016-04-17 15:44:54','2016-04-19 15:44:54',0,0,0,1,3,1,500,0,null,1,null,1,0,0)
    at com.*.*.dao.BaseDAO.save(BaseDAO.java:544)
    at com.*.*.dao.OrderLogDAOImpl.save(OrderLogDAOImpl.java:322)
    at com.*.*.manager.OrderManagerImpl.orderProduct(OrderManagerImpl.java:981)
    at com.*.*.manager.OrderManagerImpl.orderMultiScreenProduct(OrderManagerImpl.java:336)
    at com.*.*.ws.EPGAuthImpl.serviceOrder(EPGAuthImpl.java:622)
    at sun.reflect.GeneratedMethodAccessor142.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.apache.cxf.service.invoker.AbstractInvoker.performInvocation(AbstractInvoker.java:180)
    at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:96)
    at org.apache.cxf.jaxws.AbstractJAXWSMethodInvoker.invoke(AbstractJAXWSMethodInvoker.java:178)
    at org.apache.cxf.jaxws.JAXWSMethodInvoker.invoke(JAXWSMethodInvoker.java:64)
    at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:75)
    at org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:58)
    at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
    at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
    at java.util.concurrent.FutureTask.run(Unknown Source)
    at org.apache.cxf.workqueue.SynchronousExecutor.execute(SynchronousExecutor.java:37)
    at org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:107)
    at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:262)
    at org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:122)
    at org.apache.cxf.transport.http.AbstractHTTPDestination.invoke(AbstractHTTPDestination.java:211)
    at org.apache.cxf.transport.servlet.ServletController.invokeDestination(ServletController.java:213)
    at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:193)
    at org.apache.cxf.transport.servlet.CXFNonSpringServlet.invoke(CXFNonSpringServlet.java:129)
    at org.apache.cxf.transport.servlet.AbstractHTTPServlet.handleRequest(AbstractHTTPServlet.java:187)
    at org.apache.cxf.transport.servlet.AbstractHTTPServlet.doPost(AbstractHTTPServlet.java:110)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
    at org.apache.cxf.transport.servlet.AbstractHTTPServlet.service(AbstractHTTPServlet.java:166)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:354)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)

Answer 1

读的是缓存，一般情况并不查库

Answer 2

回复
之前遇到过jdbc驱动 读写分离 导致的这样的问题，也是莫名其妙，但是换驱动就好了

Answer 3

做读写分离了么？

Answer 4

这个问题复现有点困难，debug没用，已经上线的系统了

Answer 5

回复
几十万的用户量，那你们后台应该用到了集群吧，缓存也应该用到了吧。

Answer 6

用户ID并不是自增长的主键ID ，实际是几十万的用户吧

Answer 7

回复
恩，都有的

Answer 8

打断点调试

insert into orderlog (userId,providerId,productGroupId,productId,serviceId,contentId,orderTime,cancelTime,activeTime,deactiveTime,duration,count,flux,status,purchaseType,paymentType,fee,balance,accountNo,productType,screenClass,orderType,continueFlag,continueStatus) values ('25822700385',null,51,'H20131031',null,'00000001000000010000000001250524','2016-04-17 15:44:54',null,'2016-04-17 15:44:54','2016-04-19 15:44:54',0,0,0,1,3,1,500,0,null,1,null,1,0,0)

查看这条语句执行返回的结果。

题外话：

1.你们的用户量已经这么多了？25822700385

2.contentId指的是哈，怎么这么长啊？00000001000000010000000001250524

3.我公司的下单时间一般设置成14位的字符串，看来你们是date类型了

Answer 9

的确没有事务，前面的异常是连接池抛的异常，在EPGAuthImp serviceOrder 订购只有最后一下插入数据库的操作

Answer 10

是不是没有配置事务，有好几次操作数据库的动作，insert是其中较早的一次，执行下一个动作的时候出现异常，又没有回滚，所以出现如上情况