Epilogue

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.