Finally I'd like to repeat methods which we discussed. Unfortunately there are several problems left. I will be glad to know your opinion about what else to descuss. I will be waiting your notes at sveta_dot_smirnova_at_oracle_dot_com or sveta_at_js-client_dot_com
List of methods.
Method #1: use output operator to output query in exactly same way in which RDBMS gets it.
Method #2: use general query log if you need to find which exact query causes wrong behavior of your application.
Method #3: after you found a query which causes the problem tune it using command line client and analyze result.
Method #4: try to modify SQL in such a way what you get correct result. Use search engines to find a workaround.
Method #5: use EXPLAIN EXTENDED for finding how optimized (and executing) SQL query.
Method #6: convert DML queries to corresponding SELECT to examine which rows will be modified.
Method #7: repeat your scenario backward step-by-step until you found the problem query.
Method #8: always check result of the query! Use means of your connector or interface of interactive client.
Method #9: tune your application in such a way so it will write queries to log files itself.
Method #10: use MySQL Proxy or any other proxy.
Method #11: use query SHOW PROCESSLIST to see list of queries which run at the same time.
Method #12: use table INFORMATION_SCHEMA.PROCESSLIST if you need list of simultaneous queries ordered by one of parameters.
Mehtod #13: use query SHOW ENGINE INNODB STATUS to get information about transactions.
Method #14: use general query log if output of SHOW ENGINE INNODB STATUS contains only part of information about problem transaction.
Method #15: check value of max_allowed_packet and size of data and SQL queries if MySQL server returns syntax error for correct query.
Method #16: check value of wait_timeout and other timeouts if you get error "MySQL server has gone away"
Method #17: check value of connect_timeout in case of error "Lost connection to MySQL server at 'reading authorization packet'"
Method #18: always use error log
Mehtod #19: use general query log if error log does not contain enough information about server crash.
Method #20: always check if you have enough RAM for allocated buffers.
Method #21: set realistic value of max_connections based on your operating system resources.
Method #22: use monitoring tools of your operating system to find which application use enourmous amount of resources which lead to crashes of MySQL server.
Mehtod #23: Use option log_warnings=2 to examine if you have rejected connections.
Method #24: check problematic queries using MySQL server running with option --no-defaults and compare result.
Method #25: if something unexpected happens check error log in first time.
Method #26: turn InnoDB Monitor to on to have information about all InnoDB transactions in the error log file.
Method #27: use slow query log to find all slow queries.
Method #28: use MySQL Sandbox for fast and convinient testing of your application using several versions of MySQL server.
Method #29: use part of data when work with queries which return wrong results from tables which contains a lot of data.
Author 2010 Sveta Smirnova
COPYRIGHT © 2010 S.Smirnova and S. Lasunov