[MySQL] MySQL database transactions in the production environment are always RUNNING, and mysqlrunning

Source: Internet
Author: User

[MySQL] MySQL database transactions in the production environment are always RUNNING, and mysqlrunning

Preface:

The operations staff reported that a ticket was submitted and the page was not returned.


1. At first, I suspected that the application server or database was under too high pressure. I checked the load on the application server and the database immediately. It seems to be OK. It is very low and should not be a db performance problem.


2. Check whether the table is locked. Check that two transactions have been RUNNING and are not finished .,

Mysql> select * from INNODB_TRX \ G;
* *************************** 1. row ***************************
Trx_id: 28573155
Trx_state: RUNNING
Trx_started: 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: 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. Use trx_mysql_thread_id: 1662332 to query information_schema.processlist to find the SQL thread of the client request for transaction execution.


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. Use the SQL thread to find the application IP address and port 10.2xx.3.xx: 23452.
 
Go to the application server and use netstat to search for port 23452 and find the PID of the running application project.
[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
Why does 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 = 256 m-XX: MaxPermSize = 512 m-XX: NewSize = 1536 m-XX: MaxNewSize = 3072 m-Djava. endorsed. dirs =/usr/local/application/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/apps/apache-tomcat-6.0.37_7000/temp org. apache. catalina. startup. bootstrap start
Root 21039 21007 0 00:00:00 pts/2 grep 12059
[Root @ lb-lan-2 ~] # Cd/usr/local/app/apache-tomcat-6.0.37_7000/webapps/
[Root @ lb-lan-2 webapps] # ll
Total usage 68292
Drwxrwxr-x. 6 tomcat 4096 21:33 shopAdminWeb
-Rw-r --. 1 tomcat 39126457 21:33 shopAdminWeb. war
Drwxrwxr-x. 4 tomcat 4096 August 5 21:33 shop_service
-Rw-r --. 1 tomcat 30792513 August 5 21:33 shop_service.war
[Root @ lb-lan-2 webapps] #



5. Find the project and check the error message of the tomcat application, as shown below:
--- Check the select ID, SHOP_NAME, clerk, 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, U PDATED_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
16:23:57, 746 [http-7000-6: ERROR] com. plocc. framework. exception. handler. impl. LogHandlerImpl-system exception or unhandled exception, need to be handled by maintenance personnel! Exception information: Database exception!
Com. plocc. framework. exception. SystemException: the database is abnormal!
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. Now it is obvious that the transaction has not been committed. check all the java code with the developer and find two places. In Exception e, I forgot to write rollback.
} Catch (SQLException e ){
// TODO Auto-generated catch block
E. printStackTrace ();
// Rollback is missing.

}


Conclusion: when writing database transactions, developers should remember to forget the rollback when handling exceptions.

 
Can not connect to MYSQL server

1. The database service may not be started.
2. The specified database connection username or password may be incorrect.
3. The database connection statement may be incorrectly written.
4. The specified database may not exist.
5. No SQL package or driver may be introduced

How to migrate data in the old Environment After configuring the new environment? Mysql database

1. If it is a windows environment and mysql data is still running (the original machine), you only need to turn mysql into a service,
A. Modify the my. ini file. The configuration must be consistent with the actual directory (especially the data directory), and place the my. ini file in the specified directory.
B. Enter the bin directory of mysql and run
Mysqld-nt -- install mysql -- defaults-file = (actual location of the my. ini file) \ my. ini to turn mysql into a service
C. Start the mysql Service

2. for different machines and versions, use the mysqldump command to export the entire database and restore it.
Reference: www.chysoft.net/showinfo.asp? Id = 87

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.