MySQL Bugs: #69099: MySQL 5.6 I_S.innodb_metrics configuration seems inconsistent with P

Bug #69099	MySQL 5.6 I_S.innodb_metrics configuration seems inconsistent with P_S
Submitted:	30 Apr 2013 8:31	Modified:	1 May 2013 14:48
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.6.11	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I notice that I_S.innodb_metrics and P_S setup configuration is completely different.

P_S.setup_consumers and setup_instruments uses an UPDATE statement to adjust the configuration, whereas http://dev.mysql.com/doc/refman/5.6/en/innodb-metrics-table.html indicates the use of:

set global innodb_monitor_enable = XXXX;
set global innodb_monitor_reset_all = XXXX;
set global innodb_monitor_reset = "XXXX";
set global innodb_monitor_disable = all;

etc.

So completely different for something that appears to the DBA as similar data.
It's also not clear how these settings can be initialised on startup, if we went to configure things differently from the default (disabled?) settings.

How to repeat:
Look at the 5.6 manual and this will be clear.

Suggested fix:
* Please make configuration of these 2 setups consistent.
* Please make it straightforward to configure initial startup values if they are different from the defaults.
* The current initial configuration of P_S setup_consumers and setup_instruments is far from intuitive.

Initially I thought I could configure the table this way, but see that's not possible:

root@myserver [information_schema]> update innodb_metrics set status = 'ENABLED' where name like 'purge%';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

and given my grants this was obviously not the problem:

root@myserver [information_schema]> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Thank you very much for the feature request.

This table is a bit strange : using the information_schema to access the performance_schema.

Note that the information_schema is read only by design so you can only modify the information using variables (or DDL such as create table) :

"
Although you can select INFORMATION_SCHEMA as the default database with a USE statement, you can only read the contents of tables, not perform INSERT, UPDATE, or DELETE operations on them
"

http://dev.mysql.com/doc/refman/5.6/en/information-schema.html

To summarize what needs to be done in 5.7 :

- remove the innodb_monitor* variables
- move the information_schema.innodb_metrics to the performance_schema.