MySQL Bugs: #41798: Incomplete error message for SELECT denied to mysql.inventory

Bug #41798	Incomplete error message for SELECT denied to mysql.inventory
Submitted:	30 Dec 2008 15:37	Modified:	11 Jan 2011 17:17
Reporter:	Mark Leith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Agent	Severity:	S3 (Non-critical)
Version:	2.0.0.7111	OS:	Any
Assigned to:	Mark Leith	CPU Architecture:	Any
Tags:	mem_20_maint

Description:
If the host:port / socket configuration variables within the agent-instance.ini file do not have an exact string match with the value for proxy-backend-addresses, then the proxy tries to SELECT uuid FROM mysql.inventory; on the target instance, so that it can get the instance's uuid for Query Analysis. 

This requires changes to application user permissions:

2008-12-26 14:51:40: (critical) (share/mysql-proxy/quan.lua:711) [proxy] please add SELECT permissions for this user on mysql.inventory to enable the QUAN feature, got SELECT command denied to user 'user'@'host' for table 'inventory'

The message is not clear why this is required though (i.e some people could use an IP address for the proxy backends, and a host variable for the agent instance configuration. 

How to repeat:
o Create a proxy-backend-addresses variable with IP (such as 127.0.0.1) in mysql-monitor-agent.ini
o Create a host variable in the instances/mysql/agent-instance.ini file using the host name of the system
o Create an application user with grants on a specific database only:
  CREATE DATABASE foo;
  GRANT ALL ON foo.* TO 'app'@'%';
o Connect your app user through the QUAN port
o Check the agent logs

Suggested fix:
Describe the issue in more depth in the logs, if no string match can be found matching the proxy backend and the monitored instances when reading the configs, then describe how to set up both in unison. Also explain the GRANT that would need to be run for each user connecting, and why it is required.

The error-msg will be replaced by a link to the manual that describes the problem a lot better.

GRANTing SELECT on mysql.inventory DOES NOT WORK as there are no table level grants on the MYSQL database. The help description should be clear about the grants that are actually required.

The error message is a solution to the problem the proxy has, but it does not actually describe the problem which is that the connection information used by the proxy to connect to the backend doesn't correspond with the connection information stored in etc/instances/XXXX/agent-instance.ini.

That fact the proxy can't find the required information may imply the administrator has not configured the proxy correctly.

In this specific case the proxy backend had been configured with an ip-address while agent-instance.ini had been configured with a hostname. IMO the proxy should have been able to figure out that the configuration matched.

In any case if the details don't match the agent SHOULD probably report this in the log file, so the administrator understands the problem, or the measures s[he] should take if the configuration is actually correct.

Two things to fix here:

(1) Better message, like Simon's comment, just say things are misconfigured.

(2) Do a more robust match on hosts between agent and proxy (compare hostnames, and IPs).

Mark Leith writes: 
Patch pushed to 2.3:

1979 Mark Leith	2010-11-15
     Bug#41798 / EM-306 - Incomplete error message for SELECT denied to mysql.inventory
     - Further clarify the error message

   modified:
     lib/quan.lua

Andy Bang writes: 
In build 2.3.1.2039.

Closed with no doc changelog entry.