Posted on by morgo

Proposal to remove InnoDB Monitor tables

Before there was information_schema and performance_schema, there were InnoDB Monitor tables.
What the monitor tables are, is a way of communicating directly to the InnoDB storage engine by creating a table of a special name. Additional debugging information is then written to MySQL’s error log. For example:

CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;
/* view to the error log */
DROP TABLE innodb_monitor;

There’s a number of problems with this approach:

  1. By using a magic table name, there is no immediate feedback for typos or spelling errors in monitor table names. The user has to wait to see if the error log is written to, and then debug if not.
  2. The monitors will continually write to the error log as long as the magic table still exists. This can reduce the error log readability.
  3. Parsing and using the error log is non-trivial for automated tooling. It also requires local filesystem access, and the log-file path can be changed by configuration.
  4. It is difficult to apply privileges to the creation of magic tables, other than those required to CREATE or DROP tables. This is not specifically a security risk (local file system access is still required to view the log file), but semantically it makes more sense for these to require either the PROCESS or SUPER privilege.
  5. information_schema is the SQL Standard for meta-data access and should be used wherever possible.

Deprecation Plan

The deprecation plan proposed for MySQL 5.7 is:

  • innodb_monitor. To be replaced with SET GLOBAL innodb_monitor=ON|OFF.
  • innodb_lock_monitor. To be replaced with SET GLOBAL innodb_lock_monitor=ON|OFF.
  • innodb_tablespace_monitor. To be removed. information_schema will become the recommended alternative.
  • innodb_table_monitor. To be removed. information_schema will become the recommended alternative.
  • innodb_mem_validate. To be removed. This depends on UNIV_MEM_DEBUG, which is not normally enabled even in debug builds.

So in all cases the magic table name (point #1 above) is removed, but in some cases the monitors remain to offer compatibility where the corresponding information_schema functionality may differ dramatically in meta-data available.

Conclusion

As with our other upcoming changes planned, we are seeing community feedback as to how this change will affect users.
Do you use the InnoDB Monitors?
Will the deprecation plan affect you negatively?
Please leave a comment, or get in touch with me. Thanks!