gwy13620867 2010-12-28 11:03
浏览 254
已采纳

SSH2 访问多个数据库出现的问题！经常远程连接不上！

SSH2 框架 系统运行了几个月 现在数据才1W多条 但是关联太多 导致系统显示分页100条记录就很慢

现在接手这个项目很多地方都要重新修改。。

现在框架重新搭的时候碰到一个问题 最痛苦

美国有一台服务器主要是读取其他服务器数据库的所有订单 客户信息，国内服务器连接国外把数据库读取回来。

我用spring 配置了2个数据源 配置信息如下：
[code="java"]
















<bean id="bDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
    <property name="jdbcUrl" value="jdbc:mysql://*********:3306/yilainetwork?characterEncoding=utf-8"></property>
    <property name="user" value="*********"></property>
    <property name="password" value="*********"></property>
    <property name="minPoolSize" value="5" ></property>
    <property name="maxPoolSize" value="50"></property>
    <property name="maxIdleTime" value="60"></property>
    <property name="acquireIncrement" value="3"></property>
    <property name="maxStatements" value="0"></property>
    <property name="idleConnectionTestPeriod" value="60"></property>
    <property name="acquireRetryAttempts" value="10"></property>
    <property name="breakAfterAcquireFailure" value="false"></property>
    <property name="acquireRetryDelay" value="10000"></property>
    <property name="testConnectionOnCheckin" value="true"></property>
    <property name="testConnectionOnCheckout" value="true"></property>
</bean>

[/code]

经常性报错 错误信息
[code="java"]
Exception in thread "timerFactory" org.springframework.dao.DataAccessResourceFailureException: could not execute query; nested exception is org.hibernate.exception.JDBCConnectionException: could not execute query
at org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:625)
at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:411)
at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
at org.springframework.orm.hibernate3.HibernateTemplate.find(HibernateTemplate.java:912)
at com.yilainetwork.dao.CommonDao.findByHqlC(CommonDao.java:96)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:108)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at $Proxy16.findByHqlC(Unknown Source)
at com.yilainetwork.service.impl.OrdersService.findOrdersC(OrdersService.java:229)
at com.yilainetwork.util.testConnectionData.run(testConnectionData.java:31)
at java.util.TimerThread.mainLoop(Timer.java:512)
at java.util.TimerThread.run(Timer.java:462)
Caused by: org.hibernate.exception.JDBCConnectionException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:97)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2235)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
at org.hibernate.loader.Loader.list(Loader.java:2124)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1149)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:921)
at org.springframework.orm.hibernate3.HibernateTemplate$30.doInHibernate(HibernateTemplate.java:1)
at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406)
... 20 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 84640 ms ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3009)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2895)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3438)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2554)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1761)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1912)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1812)
at org.hibernate.loader.Loader.doQuery(Loader.java:697)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2232)
... 30 more
Caused by: java.net.SocketException: Connection reset
at java.net.SocketInputStream.read(SocketInputStream.java:168)
at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:113)
at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:160)
at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:188)
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2452)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2906)
... 43 more
[/code]

或者前面一样最后的错误信息不是Connection reset
[code="java"]
Caused by: java.io.EOFException: Can not read response from server. Expected to read 255 bytes, read 0 bytes before connection was unexpectedly lost.
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2455)
at com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:1626)
... 47 more
[/code]

或者报下面这个错误！！
[code="java"]
Exception in thread "timerFactory" org.springframework.transaction.TransactionSystemException: Could not commit Hibernate transaction; nested exception is org.hibernate.TransactionException: JDBC commit failed
at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:660)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:754)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:723)
at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:412)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at $Proxy16.findByHqlC(Unknown Source)
at com.yilainetwork.service.impl.OrdersService.findOrdersC(OrdersService.java:229)
at com.yilainetwork.util.testConnectionData.run(testConnectionData.java:33)
at java.util.TimerThread.mainLoop(Timer.java:512)
at java.util.TimerThread.run(Timer.java:462)
Caused by: org.hibernate.TransactionException: JDBC commit failed
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:161)
at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:656)
... 13 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Communications link failure during commit(). Transaction resolution unknown.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
at com.mysql.jdbc.ConnectionImpl.commit(ConnectionImpl.java:1569)
at com.mchange.v2.c3p0.impl.NewProxyConnection.commit(NewProxyConnection.java:803)
at org.hibernate.transaction.JDBCTransaction.commitAndResetAutoCommit(JDBCTransaction.java:170)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:146)
... 14 more
[/code]

原本读取美国服务器的订单是通过直接JAVA JDBC Connection 来连接 也是会经常报
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 84640 ms ago.
这个错误

数据库都能连接上去 但是就是直接SQL的时候
ResultSet rs = pstmt.executeQuery();
获取ResultSet值的时候报错！

在网上找了一些资料 修改MYSQL wait_timeout 8小时等待 时间 也是不行！

一直测试MYSQL的连接 SPRING 配置的C3P0 连接池参数 还是不行！！

如果用C3P0做连接池的话 执行HQL 错误的几率大！！
如果是执行SQL的话 因为读取订单信息关联3 4张表的信息！！数据得重新封装！！但是还是会报错！
有的时候会报事务没有提交！！

Spring 事务配置信息
[code="java"]
<!-- 事务管理 -->
<!-- Transaction beans start -->
<!-- 数据源1事务托管 -->




<tx:advice id="txAdvice" transaction-manager="transactionManager">
    <tx:attributes>
        <tx:method name="sav*" propagation="REQUIRED"/>
        <tx:method name="dele*" propagation="REQUIRED"/>
        <tx:method name="inse*" propagation="REQUIRED"/>
        <tx:method name="upda*" propagation="REQUIRED"/>
        <tx:method name="*" read-only="true"/>
    </tx:attributes>
</tx:advice>

<aop:config>
    <aop:pointcut id="allManagerMethod" expression="execution(* com.**.dao.*.*(..))" />
    <aop:advisor advice-ref="txAdvice" pointcut-ref="allManagerMethod"/>
</aop:config>
<!-- Transaction beans end -->

[/code]

因为Spring 配置了俩个 数据源 我有一个切换数据库的方法 网上找的 事务我就直接还是根据其他人的 这样写！

这个问题 到底应该如何解决！！

我对MYSQL my.ini 的[mysqld]增加了 skip-name-resolve

  • 写回答

1条回答 默认 最新

  • luxixi3600 2010-12-29 11:56
    关注

    1、经常报的那个错误我遇到一次，问题出在hibernate映射关系上，因为有多对一这类关系，每秒并发N多sql，造成了这种错误。关联几张表的那个可以用视图去解决，这个相对简单些。

    2、修改MYSQL wait_timeout 8小时，这个是mysql的等待超时，如果超过8小时没有操作，就会自动断开链接。好像和你的报错没多大关系，这个和c3p0的保持链接的那个属性有关系。

    3、有的时候会报事务没有提交！！，这个可以将事务的超时设置的稍微长点进行测试，不过一般如果出这种问题一般都是程序的问题。

    如果都是innerDB的话，我想两个库应该涉及到多数据源的分布式事务，建议用用atomikos。

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题？

悬赏问题

  • ¥50 我在一个购物网站的排队系统排队，这个排队到号后重新定向到目标网站进行购物，但是有技术牛通过技术方法直接跳过排队系统进入目标网址购物，有没有什么软件或者脚本可以用
  • ¥15 ios可以实现ymodem-1k协议 1024字节传输吗？
  • ¥300 寻抓云闪付tn组成网页付款链接
  • ¥15 请问Ubuntu要怎么安装chrome呀？
  • ¥15 视频编码 十六进制问题
  • ¥15 unity terrain打包后地形错位，跟建筑不在同一个位置，怎么办
  • ¥15 FileNotFoundError 解决方案
  • ¥15 uniapp实现如下图的图表功能
  • ¥15 u-subsection如何修改相邻两个节点样式
  • ¥30 vs2010开发 WFP（windows filtering platform）