[MySQL] 生產環境MySQL資料庫事務一直在RUNNING，mysqlrunning

[MySQL] 生產環境MySQL資料庫事務一直在RUNNING，mysqlrunning

前言：

運營人員反映，有一單子提交卡住了，頁面一直沒有返回。

1，剛開始懷疑是應用伺服器或者db壓力過高hang住了，馬上去check應用伺服器以及db的負載，看起來都OK，蠻低的，應該不是DB效能問題。

2，最後去看下是否是表鎖住了，查看到有2個事務一直RUNNING，沒有結束。,

mysql> select * from INNODB_TRX\G;
*************************** 1. row ***************************
                    trx_id: 28573155
                 trx_state: RUNNING
               trx_started: 2014-08-12 15:27:57
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 1662333
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 376
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 7188
          trx_is_read_only: 0
trx_autocommit_non_locking: 0


*************************** 2. row ***************************
                    trx_id: 28573065
                 trx_state: RUNNING
               trx_started: 2014-08-12 15:27:51
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 11
       trx_mysql_thread_id: 1662332
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 6
     trx_lock_memory_bytes: 1248
           trx_rows_locked: 1
         trx_rows_modified: 5
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 9995
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)


ERROR: 
No query specified


mysql> 


3，通過trx_mysql_thread_id: 1662332的去查詢information_schema.processlist找到執行事務的用戶端請求的SQL線程


mysql> select * from information_schema.processlist where id=1662333;
+---------+--------------+-------------------+-------------+---------+------+-------+------+
| ID      | USER         | HOST              | DB          | COMMAND | TIME | STATE | INFO |
+---------+--------------+-------------------+-------------+---------+------+-------+------+
| 1662333 | business_web | 10.2xx.3.xx:23452 | business_db | Sleep   | 1780 |       | NULL |
+---------+--------------+-------------------+-------------+---------+------+-------+------+
1 row in set (0.00 sec)



4，通過SQL線程，找到應用程式的IP地址以及連接埠10.2xx.3.xx:23452
 
進入應用伺服器，通過netstat檢索連接埠23452找到正在啟動並執行應用工程的PID
[root@lb-lan-2 ~]# netstat -nlatp |grep 23452
tcp        0      0 ::ffff:10.xx.3.2x:23452    ::ffff:10.xx4.3.x1:3306     ESTABLISHED 12059/java          
[root@lb-lan-2 ~]# ps -eaf|grep 12059
tomcat   12059     1  1 Aug09 ?        01:19:36 /usr/java/jdk1.6.0_45/jre/bin/java -Djava.util.logging.config.file=/usr/local/app/apache-tomcat-6.0.37_7000/conf/logging.properties -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Xms4096m -Xmx4096m -Xss2048K -XX:PermSize=256m -XX:MaxPermSize=512m -XX:NewSize=1536m -XX:MaxNewSize=3072m -Djava.endorsed.dirs=/usr/local/app/apache-tomcat-6.0.37_7000/endorsed -classpath /usr/local/app/apache-tomcat-6.0.37_7000/bin/bootstrap.jar -Dcatalina.base=/usr/local/app/apache-tomcat-6.0.37_7000 -Dcatalina.home=/usr/local/app/apache-tomcat-6.0.37_7000 -Djava.io.tmpdir=/usr/local/app/apache-tomcat-6.0.37_7000/temp org.apache.catalina.startup.Bootstrap start
root     21039 21007  0 16:00 pts/2    00:00:00 grep 12059
[root@lb-lan-2 ~]# cd /usr/local/app/apache-tomcat-6.0.37_7000/webapps/
[root@lb-lan-2 webapps]# ll
總用量 68292
drwxrwxr-x. 6 tomcat tomcat     4096 8月   5 21:33 shopAdminWeb
-rw-r--r--. 1 tomcat tomcat 39126457 8月   5 21:33 shopAdminWeb.war
drwxrwxr-x. 4 tomcat tomcat     4096 8月   5 21:33 shop_service
-rw-r--r--. 1 tomcat tomcat 30792513 8月   5 21:33 shop_service.war
[root@lb-lan-2 webapps]# 



5，找到工程之後，再去查看tomcat的應用報錯資訊，如下：
--- Check the      select ID, SHOP_NAME, SHOP_CLASSIFICATION_ID, LEGAL_PERSON, CORPORATION, SHOP_TEL, ADDRESS,       ZIP_CODE, LEGAL_PERSON_MOBILE, STAT, AUTHENTICATE, CREDIT, LEVEL, SCORE, LICENSE_PATH,       BUSSINESS_RANGE, MALL_ID, FLOOR, BIS_COMPANY, STORE_NO, MANAGER_NAME, MANAGER_TEL, BRIEF, LOGO,       FAVOUR_NUM, HAS_WAREHOUSE, DESCRIPTION_FIT, SERVICE, SPEED, BACKGROUND, BIS_CONT_ID,BIS_SHOP_ID,       CREATED_DATE, UPDATED_DATE,ENGLISH_PREF     from SHOP     where SHOP_NAME = ?   .  
--- Check the SQL Statement (preparation failed).  
--- Cause: org.springframework.transaction.TransactionTimedOutException: Transaction timed out: deadline was Tue Aug 12 15:31:00 CST 2014
2014-08-12 16:23:57,746 [http-7000-6:ERROR] com.plocc.framework.exception.handler.impl.LogHandlerImpl - 出現系統異常或未處理的異常, 需要維護人員處理！異常資訊：資料庫異常！
com.plocc.framework.exception.SystemException: 資料庫異常！
at com.plocc.shop.service.impl.ShopServiceImpl.selectShopByShopName(ShopServiceImpl.java:200)
at com.plocc.shop.web.admin.ShopManagerController.addShop(ShopManagerController.java:338)
at sun.reflect.GeneratedMethodAccessor639.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:838)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:643)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.plocc.auth.filter.AuthenticationFilter.doFilter(AuthenticationFilter.java:110)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.plocc.sso.filter.SingleSingOnFilter.doFilter(SingleSingOnFilter.java:85)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
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:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:662)




6，現在很明顯了，應該是事務未提交，配合開發去check所有的java代碼，發現有2處地方，在Exception e裡面忘記寫rollback了。
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
// 這裡缺乏了rollback。

}

總結：開發人員，寫資料庫事務的時候，記得在異常處理Exception的時候，別忘記了rollback。

 
串連MYSQL資料庫總顯示Can not connect to MySQL server

1.可能資料庫服務沒有啟動
2.可能指定的資料庫連接使用者名稱或密碼不正確
3.可能資料庫連接語句寫得不正確
4.可能資料庫中沒有指定的資料庫存在
5.可能沒引sql包或沒有驅動程式
 
配置新環境後，怎遷移老環境下的資料？mysql資料庫

1、如果是windows環境，且mysql的資料還在（原來的機器），在只需將mysql變成服務既可以了，具體操作，
A、修改my.ini檔案，裡面的配置必須和實際的目錄一致（特別是資料目錄），將my.ini檔案放置指定的目錄
B、進入mysql的bin目錄，執行
mysqld-nt --install mysql --defaults-file=(my.ini檔案的實際位置)\my.ini，將mysql變成服務
C、啟動mysql服務

2、如果是不同的機器和版本，則使用mysqldump命令匯出整個資料庫再恢複
參考資料：www.chysoft.net/showinfo.asp?id=87