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 16:37
Reporter:	Mark Leith
Status:	Verified
Category:	Monitoring: Agent	Severity:	S3 (Non-critical)
Version:	2.0.0.7111	OS:	Any
Assigned to:	Kay Roepke	Target Version:	2.0 maint release
Tags:	mem_20_maint
Triage:	Needs Triage: D3 (Medium)

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).