MySQL Bugs: #45755: Custom collections do not override standard collections

Bug #45755	Custom collections do not override standard collections
Submitted:	25 Jun 2009 15:27	Modified:	21 Jul 2009 10:22
Reporter:	Mark Leith	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Agent	Severity:	S3 (Non-critical)
Version:	2.1.0.1018	OS:	Any
Assigned to:	Kay Roepke	CPU Architecture:	Any

Description:
If you define a custom collection file, and specify collections which are the same as the defaults but with some extensions for ignoring users, for example, the custom collection does not get picked up and override the default collection. 

This is useful for customizing rules but allowing the default collection file to be left alone for upgrade purposes. 

How to repeat:
o Create share/mysql-proxy/items/custom-items.xml
o Add the following to it:

<?xml version="1.0" encoding="utf-8"?>
<classes>
        <class>
                <namespace>mysql</namespace>
                <classname>global_privileges</classname>
                <query><![CDATA[SELECT GROUP_CONCAT('\\\\\n* ', '\'',user,'\'@\'',host,'\'' ORDER BY user, host) as user FROM mysql.user WHERE user !='root' AND NOT (user='dumpuser' AND host='localhost') AND (Drop_priv='Y' OR Alter_priv = 'Y' OR Delete_priv='Y' OR Update_priv='Y' OR Insert_priv='Y' OR Lock_tables_priv='Y')]]></query>
        </class>
</classes>

o On the monitored instance:

GRANT ALL ON *.* TO dumpuser@localhost;

o Modify the agent-item-files:

agent-item-files = share/mysql-proxy/items/quan.lua,share/mysql-proxy/items/items-mysql-monitor.xml,share/mysql-proxy/items/agent-allocation-stats.lua,share/mysql-proxy/items/custom-items.xml

o Restart the agent
o Enable the "Account Has Global Privileges" rule
o Check a debug log for the agent - you can see the standard collection being run, but not the custom one

Suggested fix:
o Override standard collections with custom collections in custom files properly

Doesn't show in the debug log running a second time either:

2009-06-25 16:19:22: (message) network-io.c:247: encoding = 0.01 ms, request = 11.89 ms (incl. response 0.06 ms)
2009-06-25 16:19:22: (debug) scheduler.c.512: scheduling list_known_data_items for (null)
2009-06-25 16:19:22: (debug) job (task 9223372036854775807 (list_known_data_items)) executed only once
2009-06-25 16:19:22: (critical) agent_mysqld.c:641: successfully connected to database at 127.0.0.1:13306 as user service_manager (with password: YES)
2009-06-25 16:19:22: (debug) network-socket.c:857: is-local src: 127.0.0.1(:51148) =? ...
2009-06-25 16:19:22: (debug) network-socket.c:863: is-local dst: 127.0.0.1(:13306)
2009-06-25 16:19:22: (debug) agent_mysqld.c:179: [mysql] processlist-thread-ids: 6907 =? 6909
2009-06-25 16:19:22: (debug) agent_mysqld.c:179: [mysql] processlist-thread-ids: 6908 =? 6909
2009-06-25 16:19:22: (debug) agent_mysqld.c:213: [mysql] 127.0.0.1:51148 =? ...
2009-06-25 16:19:22: (debug) agent_mysqld.c:219: [mysql] 127.0.0.1:51148
2009-06-25 16:19:22: (message) agent_mysqld.c:318: [mysql] mysqld is local and directly connected
2009-06-25 16:19:22: (message) mysql-query (mysql): SHOW DATABASES LIKE 'test'
2009-06-25 16:19:22: (message) mysql-query (mysql): SELECT count(*) AS repl_slaves FROM mysql.user WHERE user !='root' AND Repl_slave_priv='Y'
2009-06-25 16:19:22: (message) mysql-query (mysql): SELECT GROUP_CONCAT('\\\\\n* ', '\'',user,'\'@\'',host,'\'' ORDER BY user, host) AS user_spec FROM mysql.user WHERE user != 'root' AND (Select_priv = 'Y' OR Insert_priv = 'Y' OR Update_priv = 'Y' OR Delete_priv = 'Y' OR Create_priv = 'Y' OR Drop_priv = 'Y' OR Index_priv = 'Y' OR Alter_priv = 'Y')
2009-06-25 16:19:22: (message) mysql-query (mysql): SELECT GROUP_CONCAT('\\\\\n* ', '\'',user,'\'@\'',host,'\'' ORDER BY user, host) as user FROM mysql.user WHERE password=''
2009-06-25 16:19:22: (message) mysql-query (mysql): SELECT COUNT(*) AS root_account FROM mysql.user WHERE user='root'
2009-06-25 16:19:22: (message) mysql-query (mysql): SHOW /*!50000 GLOBAL */ VARIABLES
2009-06-25 16:19:22: (message) mysql-query (mysql): SELECT GROUP_CONCAT('\\\\\n* ', '\'',d.user,'\'@\'',d.host,'\' on DB ', d.db ORDER BY d.user, d.host, d.db) AS user, GROUP_CONCAT('\\\\\n* ', d.db ORDER BY d.db) AS db_name FROM mysql.db d LEFT JOIN information_schema.schemata s ON d.db=s.schema_name WHERE s.schema_name IS NULL ORDER BY d.user, d.db;
2009-06-25 16:19:22: (message) mysql-query (mysql): SELECT GROUP_CONCAT('\\\\\n* ', '\'',user,'\'@\'',host,'\'' ORDER BY user, host) as user FROM mysql.user WHERE user !='root' AND (File_priv='Y' OR Process_priv='Y' OR Shutdown_priv='Y' OR Grant_priv='Y')
2009-06-25 16:19:22: (message) mysql-query (mysql): SELECT COUNT(*) as remote_login FROM mysql.user WHERE user='root' AND host <> 'localhost' AND host <> '127.0.0.1'
2009-06-25 16:19:22: (message) mysql-query (mysql): SHOW /*!40000 FULL */ PROCESSLIST
2009-06-25 16:19:22: (message) mysql-query (mysql): SHOW MASTER LOGS
2009-06-25 16:19:22: (message) job_collect_mysql.c:1075: <SHOW MASTER LOGS> failed, skipping all data-items for that scope: You are not using binary logging (1381)
2009-06-25 16:19:22: (message) mysql-query (mysql): SELECT GROUP_CONCAT('\\\\\n* ', '\'',user,'\'@\'',host,'\'' ORDER BY user, host) as user FROM mysql.user WHERE LENGTH(password) > 1 AND LENGTH(password) < 41;
2009-06-25 16:19:22: (message) mysql-query (mysql): SELECT GROUP_CONCAT('\\\\\n* ', '\'',user,'\'@\'',host,'\'' ORDER BY user, host) as user FROM mysql.user WHERE user !='root' AND (Drop_priv='Y' OR Alter_priv='Y')
2009-06-25 16:19:22: (message) mysql-query (mysql): SELECT GROUP_CONCAT('\\\\\n* ', '\'',user,'\'' ORDER BY user) as user FROM mysql.user WHERE host = '%'
2009-06-25 16:19:22: (message) mysql-query (mysql): SHOW /*!50000 GLOBAL */ STATUS
2009-06-25 16:19:22: (message) mysql-query (mysql): SELECT COUNT(*) AS no_password FROM mysql.user WHERE user='root' AND password=''
2009-06-25 16:19:22: (message) mysql-query (mysql): SELECT GROUP_CONCAT('\\\\\n* ', routine_schema, '.', routine_name, '()' ORDER BY routine_schema, routine_name) AS routine FROM information_schema.routines WHERE UPPER(routine_definition) LIKE '%SELECT *%'
2009-06-25 16:19:22: (message) mysql-query (mysql): SELECT COUNT(*) AS num_long_running, @@long_query_time AS long_running_time FROM information_schema.processlist WHERE time > @@long_query_time AND state = 'Locked'
2009-06-25 16:19:22: (message) mysql-query (mysql): 
SELECT DISTINCT GROUP_CONCAT('\\\\\n* ', s.table_schema, '.', s.table_name, '.', s.index_name) AS table_list
FROM information_schema.statistics s
JOIN information_schema.tables t
  ON (s.table_schema = t.table_schema
  AND s.table_name = t.table_name)
WHERE s.table_schema != 'mysql'
  AND t.engine = 'MyISAM'
  AND s.cardinality IS NULL
2009-06-25 16:19:22: (message) mysql-query (mysql): SELECT COUNT(*) AS user_count FROM mysql.user WHERE user=''
2009-06-25 16:19:22: (message) mysql-query (mysql): SHOW /*!50000 ENGINE */ INNODB STATUS
2009-06-25 16:19:22: (message) mysql-query (mysql): SELECT COUNT(*) AS num_long_running, @@long_query_time AS long_running_time FROM information_schema.processlist WHERE time > @@long_query_time AND command != 'Sleep'
2009-06-25 16:19:22: (message) mysql-query (mysql): SELECT GROUP_CONCAT('\\\\\n* ', '\'',user,'\'@\'',host,'\'' ORDER BY user, host) as user FROM mysql.user WHERE user !='root' AND (Drop_priv='Y' OR Alter_priv = 'Y' OR Delete_priv='Y' OR Update_priv='Y' OR Insert_priv='Y' OR Lock_tables_priv='Y')
2009-06-25 16:19:22: (message) mysql-query (mysql): SELECT COUNT(*) AS num_locked FROM information_schema.processlist WHERE state = 'Locked'
2009-06-25 16:19:22: (message) mysql-query (mysql): SHOW MASTER STATUS
2009-06-25 16:19:22: (message) mysql-query (mysql): SELECT GROUP_CONCAT('\\\\\n* ', '\'',user,'\'@\'',host,'\'' ORDER BY user, host) AS user_spec FROM mysql.user WHERE user != 'root' AND Grant_priv = 'Y'
2009-06-25 16:19:22: (message) mysql-query (mysql): SHOW MASTER LOGS
2009-06-25 16:19:22: (message) job_collect_mysql.c:936: <SHOW MASTER LOGS> failed, skipping all data-items for that scope: You are not using binary logging (1381)
2009-06-25 16:19:22: (message) mysql-query (mysql): SHOW DATABASES
2009-06-25 16:19:22: (message) mysql-query (mysql): SHOW SLAVE STATUS
2009-06-25 16:19:22: (message) mysql-query (mysql): SELECT GROUP_CONCAT('\\\\\n* ', '\'',user,'\'@\'',host,'\'' ORDER BY user, host) AS user_spec FROM mysql.user WHERE user != 'root' AND (Create_user_priv = 'Y' OR File_priv = 'Y' OR Lock_tables_priv = 'Y' OR Reload_priv = 'Y' OR Shutdown_priv = 'Y' OR Super_priv = 'Y')
2009-06-25 16:19:22: (message) mysql-query (mysql): 
SELECT GROUP_CONCAT('\\\\\n* ', t.table_schema, '.', t.table_name) AS table_list
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints c
  ON (t.table_schema = c.table_schema
  AND t.table_name = c.table_name
  AND c.constraint_type IN ('PRIMARY KEY','UNIQUE'))
WHERE t.table_schema NOT IN ('mysql','information_schema')
  AND t.engine NOT IN ('ARCHIVE','FEDERATED')
  AND c.table_name IS NULL
2009-06-25 16:19:24: (message) job_collect_mysql.c:1997: patching UUID of proxy's backend: mysql (127.0.0.1:13306)
2009-06-25 16:19:25: (message) network-io.c:954: found list_known_data_items ... uncorking
2009-06-25 16:19:25: (message) --> sending heartbeat (209598 bytes) (shutdown = 0)
2009-06-25 16:19:25: (message) network-io.c:994: backlog: 1 (corked: 0)

Gary Whizin writes: 
Support requests this make initial 2.1 GA

Kay Roepke writes: 
When reading in the xml items files, their order was reversed, leading to unexpected effects when overriding existing data collections.

Keith Russell writes: 
Patch applied in version => 2.1.0.1076.

Diego Medina writes: 
Verified fixed on 2.1.0.1076

An entry was added to the 2.1.0 changelog:

Custom collections did not override standard collections.

If a custom collection file was defined, and collections specified which were the same as the defaults but with some extensions, the custom collection did not appear to be processed and so did not override the default collection.