"Alternate methods for finding problem query.": chapter 5 of "Methods for searching errors in SQL application" just published
By Sveta Smirnova-Oracle on Jun 23, 2010
Translation of chapter 5 which describes alternate methods for searching problem query just published. It starts like:
Chapter 5. Alternate methods for finding problem query.
I already wrote about using of general query log requires resources.
Part of the problem can be solved if use new feature of MySQL 5.1:
online logging which allows to turn general query log to on or to off
without stopping the MySQL server.
Unfortunately this doesn't always work: you can have old
version of MySQL server which has not such a feature, general query log
can contain very large amount of information, so you can just miss the
problem in thousands of correct queries, you can have some other own
But what to do if you can not use general query log?
One of the methods is write log files using your application.
Add a code which will write queries which your application sends to
MySQL to the log file.
With this method you can tune how and what to write to the log
file. Would be good if you will write return value and error messages
in the same log file.
Method #9: tune your application in such a way so it will write queries to log files itself.
Yet another method is using proxy which will get queries and write them to the file.
One of most preffered variants is MySQL Proxy, because this is
scriptable proxy made for working with MySQL server. It uses MySQL
client-server protocol. You can write programs for MySQL Proxy using
Lua programming language.
Below you can see example how to write general query log using MySQL Proxy:
Rest of the chapter is here