Description:
In MySQL 5.6 whenever one tries to create innodb_lock_monitor table to get detailed information about InnoDB, including locks set, dumped to the error log from time to time (good old troubleshooting habit), she gets this warning:
mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 131
Message: Using the table name innodb_lock_monitor to enable diagnostic output is
deprecated and may be removed in future releases. Use INFORMATION_SCHEMA or PER
FORMANCE_SCHEMA tables or SET GLOBAL innodb_status_output=ON.
1 row in set (0.00 sec)
I consider this text somewhat misleading, as INFORMATION_SCHEMA.INNODB_LOCKS table contains information only about lock waits or blocking locks:
"The INNODB_LOCKS table contains information about each lock that an InnoDB transaction has requested but not yet acquired, and each lock that a transaction holds that is blocking another transaction."
(see https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-table.html)
instead of all locks we get from the monitor, no matter blocking or not.
Moreover, PERFORMAMCE_SCHEMA does NOT provide any useful details about specific row or table locks at all (at best we can track lock waits, correct me if I am wrong).
So, the only valid suggestion in that warning is about setting innodb_status_output to ON (probably one has to set innodb_status_output_locks to ON also actually). These server variables are NOT documented at the moment (see bug #71639 on that), but they do work and give desired effect.
How to repeat:
create table innodb_lock_monitor(c1 int) engine=InnoDB;
show warnings\G
Now, read the text and try to find out how these suggestions help to get information about ALL locks set by active transactions...
drop table innodb_lock_monitor;
Suggested fix:
Change warning text to mention innodb_status_output_locks and NOT mention PERFORMANCE_SCHEMA - it has no way to provide the same information as innodb_lock_monitor did, just by its own design...