`

【转】MySQLNonTransientConnectionException: No operations allowed after connection

阅读更多

MySQLNonTransientConnectionException: No operations allowed after statement closed


    之所以会出现这个异常,是因为Mysql在5以后针对超长时间DB连接做了一个处理,那就是如果一个DB连接在无任何操作情况下过了8个小时 后,Mysql会自动把这个连接关闭。所以使用连接池的时候虽然连接对象还在但是链接数据库的时候会一直报这个异常。解决方法很简单在Mysql的官方网 站上就可以找到。

 

有两个方法,

 

第一种是在DB连接字符串后面加一个参数。

 

这样的话,如果当前链接因为超时断掉了,那么驱动程序会自动重新连接数据库。

 

     

 

jdbc:mysql://localhost:3306/makhtutat?autoReconnect=true

 

不过Mysql并不建议使用这个方法。因为第一个DB操作失败的后,第二DB成功前如果出现了重新连接的效果。这个失败操作将不会处于一个事务以内,第二DB操作如果成功的话,这个事务将被提交。

 

conn.createStatement().execute(
  "UPDATE checking_account SET balance = balance - 1000.00 WHERE customer='Smith'");
conn.createStatement().execute(
  "UPDATE savings_account SET balance = balance + 1000.00 WHERE customer='Smith'");
conn.commit();

 

当然如果出现了重新连接,一些用户变量和临时表的信息也会丢失。

 

另一种方法是Mysql推荐的,需要程序员手动处理异常。

 

  1. <span style="font-family:Microsoft YaHei;font-size:12px;">public void doBusinessOp() throws SQLException {  
  2.     Connection conn = null;  
  3.     Statement stmt = null;  
  4.     ResultSet rs = null;  
  5.     int retryCount = 5;  
  6.     boolean transactionCompleted = false;  
  7.     do {  
  8.         try {  
  9.             conn = getConnection(); // assume getting this from a  
  10.                                     // javax.sql.DataSource, or the  
  11.                                     // java.sql.DriverManager  
  12.             conn.setAutoCommit(false);  
  13.             retryCount = 0;  
  14.             stmt = conn.createStatement();  
  15.             String query = "SELECT foo FROM bar ORDER BY baz";  
  16.             rs = stmt.executeQuery(query);  
  17.             while (rs.next()) {  
  18.             }  
  19.             all.close()  
  20.             transactionCompleted = true;  
  21.         } catch (SQLException sqlEx) {  
  22.             String sqlState = sqlEx.getSQLState();  
  23.            // 这个08S01就是这个异常的sql状态。单独处理手动重新链接就可以了。  
  24.             if ("08S01".equals(sqlState) || "40001".equals(sqlState))   
  25.                 {                  
  26.                     retryCount--;              
  27.                  } else {                  
  28.                      retryCount = 0;              
  29.                      }          
  30.          } finally {              
  31.                  all close:          
  32.              }      
  33.       } while (!transactionCompleted && (retryCount > 0));}  
  34. }</span>  

 

 

转自: http://blog.csdn.net/bedweather/article/details/6743951

分享到:
评论

相关推荐

    cp30报错No operations allowed after connection closed

    使用mysql+cp30连接池时,报错No operations allowed after connection closed。从报错信息来看,是connection断开导致的错误。在网上搜索后发现,较新版本的mysql配置了connection的默认时间,默认时间一般为8个...

    BURNINTEST--硬件检测工具

    - Video playback, Hard disk and CD/DVD test 'no operations' error reporting changed. - When BurnInTest crashes, it will not generate a "minidump" file. Minidumps will need to be sent to Microsoft ...

    php.ini-development

    There is no name validation. If PHP can't find an expected ; directive because it is not set or is mistyped, a default value will be used. ; The value can be a string, a number, a PHP constant (e.g....

    微软内部资料-SQL性能优化3

    If no rows satisfy the WHERE condition the first time the range is scanned, no rows should be returned on any subsequent scans. Key range locks are similar to row locks on index keys (whether ...

    FlexGraphics_V_1.79_D4-XE10.2_Downloadly.ir

    - FIX: After deleting the selected points in the TFlexPanel.DeleteSelectedPoints the all figures with one point also deleted. - FIX: When the curve contain more then one figure and they were all ...

    微软内部资料-SQL性能优化2

    To reserve or commit memory and unintentionally not release it when it is no longer being used. A process can leak resources such as process memory, pool memory, user and GDI objects, handles, threads...

    a project model for the FreeBSD Project.7z

    After providing definitions of terms used, this document will outline the organisational structure (including role descriptions and communication lines), discuss the methodology model and after ...

Global site tag (gtag.js) - Google Analytics