MySQL Bugs: #41987: Having too many users with strong privileges breaks events

Bug #41987	Having too many users with strong privileges breaks events
Submitted:	9 Jan 2009 4:31	Modified:	2 Mar 2009 15:13
Reporter:	Diego Medina	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Server	Severity:	S1 (Critical)
Version:	2.0.2.7128	OS:	Any
Assigned to:	MC Brown	CPU Architecture:	Any

Description:
if you have about 1000 users with full privileges and the value of group_concat_max_len = 100001, the size of the data that the agent sends to the service manager it too large and causes errors on the service manager and the event

"Security -> Account has Strong MySQL privileges"

does not trigger.

How to repeat:
1- Install and start the service manager
2- Run this bash file to create 999 users with strong privileges

#!/bin/bash
i="0"
while [ $i -lt 1000 ]
do
mysql -h127.0.0.1 -P14040 -uroot -pmsandbox -e "grant all on *.* to 'user_$i';"
echo "Created user user_$i"
i=$[$i+1]
done

=============================
2-a run from the mysql command client

mysql> SET global group_concat_max_len = 100001;

3- Install and start the agent
4- Schedule the rule ""Security -> Account has Strong MySQL privileges"" for interval 1 minute to see quick entries on the logs
5- Monitor the apache-tomcat/logs/mysql-monitor.log  file and you will see a message like 

" Could not execute JDBC batch update"
"Data truncation: Data too long for column 'value' at row 2"
among others

(I will attatch the log file later)

Suggested fix:
Handle large results from the different group_concat() statements the agent runs

error log

Attachment: error.txt (text/plain), 32.72 KiB.

This situation affects all these rules:

2009-01-08 23:37:07,995 ERROR [em-worker-pool-thread-4:com.mysql.advisor] 184b1362-f457-4780-93ea-8427efa42725 account_has_broad_host_specifier.name: ("\\
2009-01-08 23:37:08,023 ERROR [em-worker-pool-thread-2:com.mysql.advisor] 6623d93a-aeac-4b41-beed-06ba2080ac4a account_has_global_privileges.name: "\\
2009-01-08 23:37:08,180 ERROR [em-worker-pool-thread-7:com.mysql.advisor] 474b702d-4b4d-4881-8ea5-ab3aa29f802c account_has_strong_mysql_privileges.name: "\\
2009-01-08 23:37:08,229 ERROR [em-worker-pool-thread-3:com.mysql.advisor] e59634f3-086d-4a35-81b8-3b9f446467cd server_has_accounts_without_password.name: "\\

Verified fixed on 2.0.5.7144

An entry was added to the 2.0.5 changelog:

Having too many users with strong privileges generated Service Manager errors and events failed to be triggered.

If there were approximately 1000 users with full privileges and the value of group_concat_max_len was set to 100001, the size of the data that the agent sent to the Service Manager was too large and caused Service Manager errors. Also, the Security event “Account has Strong MySQL privileges” did not trigger.