MySQL Bugs: #38612: Agent logging critical messages about SELECTs returning more than one row

Bug #38612	Agent logging critical messages about SELECTs returning more than one row
Submitted:	6 Aug 2008 21:22	Modified:	23 Oct 2008 16:42
Reporter:	Mark Leith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Advisors/Rules	Severity:	S2 (Serious)
Version:	2.0.0.7017	OS:	Any
Assigned to:	Andy Bang	CPU Architecture:	Any

Description:
The agent is logging messages such as the following at the critical level:

2008-08-06 16:33:59: (critical) job_collect_mysql.c:716: <SELECT CONCAT('\'',user,'\'@\'',host,'\'') as user FROM mysql.user WHERE password=''> is returning more than one row on 'agent', we ignore the rest

How to repeat:
o Make sure the monitored server has more than one user with no password
o Start up agent/server
o Enable security rules
o Observe the agent log file

Suggested fix:
These should use GROUP_CONCAT on the CONCAT(), to get a list of all user, host combinations, to then surface to the advisor as a list.

This involved a fix to both the custom data collections in items-mysql-monitor.xml:

    Pushed up to revision 1065

And to the rules:

    Pushed up to revision 227

In other words, you need to update the agent as well as get the new rules.

Patch applied in versions => 2.0.0.7076.

Note that information can be lost if the result returned from GROUP_CONCAT is greater than group_concat_max_len, but there's nothing we can do about that.

Take the accounts without password rule.  Suppose there are 5 user accounts that don't have passwords.  You should now see all of them listed.  But suppose you have 100 user accounts like this.  The string created by GROUP_CONCAT will likely be larger than group_concat_max_len, so the list will be truncated somewhere.  In that case you can either increase group_concat_max_len, or you can fix the first bunch, run the rule again, and see some more.

The point is that we can now handle a reasonable number, but we can't handle a potentially infinite number.

Verified fixed in bld 2.0.0.7077.