Which tables were affected during single query run by triggers cascade

Question

According to MySQL Performance Blog, the new Percona Servers, announced yesterday (May 6), both include the open source version of the MySQL Audit Plugin.

The task I want to accomplish is: log the tables affected by cascade trigger execution during one single update query run. E. g. when UPDATE MY_TABLE … is executed, the triggers {BEFORE,AFTER}_UPDATE may update other tables, on which there might be their own triggers, etc.

Currently I use the domestic solution; inside all triggers I put smth like:

IF (
        SELECT count(*) 
        FROM  `information_schema`.`ROUTINES` 
        WHERE  specific_name = 'my_own_log' 
          AND  routine_schema = 'my_schema'
) > 0 THEN
    CALL my_own_log ('FOO_TRIGGER', 'Hi, I’m to update MY_TABLE') ;
END IF ;

In production I don’t have the my_own_log procedure defined and since the information_schema table is well-optimized, I don’t yield any performance penalties.

The question is if I could switch to enterprise solution (aforementioned audit plugin) to harvest an information about which tables were affected by cascade trigger execution. JFYI: the only similar question I have found here is not supplied with an applicable answer.

Thanks for any suggestions.

Answer 1

Plugin auditing is designed to register outside interactions with the server, being used to track invasion and other related activities, not interactions of the server with itself (like triggers and procedures).

These internal activities will not generate actions on any audit plugin by design. From the dev blog:

---

http://dev.mysql.com/doc/refman/5.6/en/audit-log-plugin-logging-control.html

The MySQL server calls the audit log plugin to write an element whenever an auditable event occurs, such as when it completes execution of an SQL statement received from a client. Typically the first element written after server startup has the server description and startup options. Elements following that one represent events such as client connect and disconnect events, executed SQL statements, and so forth. Only top-level statements are logged, not statements within stored programs such as triggers or stored procedures. Contents of files referenced by statements such as LOAD DATA INFILE are not logged.

---

For now, you are better with your homegrown solution. You could try to improve its performance so you can turn it on in the production environment.