Bug #71640 Somewhat misleading deprecation warning for innodb_lock_monitor
Submitted: 9 Feb 2014 17:52 Modified: 24 Jul 2014 20:13
Reporter: Valeriy Kravchuk Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6.16 OS:Any
Assigned to: CPU Architecture:Any
Tags: innodb_lock_monitor

[9 Feb 2014 17:52] Valeriy Kravchuk
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...
[24 Jul 2014 20:13] Sveta Smirnova
Thank you for the report.

Verified as described.