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个回答

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

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

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

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

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问