What tables were affected during a single query triggered by a cascade of triggers

Question

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

The task that I want to accomplish is as follows: register the tables affected by the cascading trigger during one update run. E. g. when UPDATE MY_TABLE … is executed, the {BEFORE,AFTER}_UPDATE can update other tables that may have their own triggers, etc.

I am currently using an internal 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 ; 

During the production process, my_own_log procedure is not defined for my_own_log , and since the information_schema table is well optimized, I do not give any performance penalties.

The question is whether it is possible to switch to a corporate solution (the aforementioned audit plugin) to collect information about which tables were affected by the cascading trigger. JFYI: the only similar question I found here does not contain a corresponding answer.

Thanks for any suggestions.

Answer 1

The plugin audit is designed to record external interactions with the server, which is used to track intrusion and other related activities, and not the server’s interaction with itself (for example, triggers and procedures).

These internal actions will not generate actions on any design audit plugin. 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 record the item whenever an audit event occurs, for example, when it completes the execution of the SQL statement received from the client. Typically, the first item recorded after the server starts has a server description and startup parameters. The elements following this represent events such as client connect and disconnect events, execution of SQL statements, etc. Only top-level instructions are written, not instructions in stored programs, such as triggers or stored procedures. The contents of files referenced by operators such as LOAD DATA INFILE are not logged.

---

While you are better with your homegrown solution. You can try to improve your performance so that you can enable it in a production environment.