logging - Get Full MySQL Query String on Insert or Update

Question

Need help with MySQL as it's not really my forte. So any help is appreciated.

I have issues on my site where UPDATE or INSERT were done with missing values. This caused some issues on other functions on the site, but I am not able to find where the UPDATE or INSERT were done in any of the classes.

Is there any way, maybe a MySQL trigger, that I could add to these tables that would allow me to store the original or full query of the UPDATE or INSERT. I have tried logging but that applies to the whole database and it takes up too much diskspace.

Thanks in advance for any replies.

PS: At the moment, the PHP classes are a bit messy as we're still in the development stage, so adding exceptions to the updates or inserts functions will take too much time. So please focus the answer to the question. Thanks again.

Answer 1

You can get the current SQL query as a string with the following statement:

SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID()

So what you have to do is to create a TRIGGER which runs on insert and/or update operations on your table which should (i) get the current sql statement and (ii) insert it into another table, like so:

DELIMITER |

CREATE TRIGGER log_queries_insert BEFORE INSERT ON `your_table`
FOR EACH ROW
BEGIN
    DECLARE original_query VARCHAR(1024);
    SET original_query = (SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID());
    INSERT INTO `app_sql_debug_log`(`query`) VALUES (original_query);
END;
|
DELIMITER ;

You will have to create two triggers - one for updates and one for inserts. The trigger inserts the new query as a string in the app_sql_debug_log table in the query column.