MySQL Bugs: #41608: Agent runs custom SQL data collection queries even though rule is not scheduled

Bug #41608	Agent runs custom SQL data collection queries even though rule is not scheduled
Submitted:	18 Dec 2008 22:56	Modified:	9 Jan 2015 10:06
Reporter:	Andy Bang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Agent	Severity:	S2 (Serious)
Version:	2.0.1.7125	OS:	Any
Assigned to:	Jan Kneschke	CPU Architecture:	Any
Tags:	mem_20_backlog

Description:
See Bug Bug #41555 - trigger_schema query from items-mysql-monitor.xml taking a long time to execute.

How to repeat:
1) Include something like the following in your items-mysql-monitor.xml file:

<class>
	<namespace>mysql</namespace>
	<classname>trigger_with_select_star</classname>
	<query><![CDATA[SELECT GROUP_CONCAT('\\\\\n* ', trigger_schema, '.', trigger_name ORDER BY trigger_schema, trigger_name) AS trigger_defn FROM information_schema.triggers WHERE UPPER(action_statement) LIKE '%SELECT *%']]></query>
</class>

2) Have lots of databases and trigger files.

3) Start the agent and watch it take a long time to get going even though you haven't scheduled a rule that uses that data collection item.

Suggested fix:
Don't run the SQL as part of the inventory discovery process.  Use some other method to determine if it can be run and what it returns, so the user can decide if any rules that use it are worth it.

Bug council proposal:

1) put return type back in the xml
2) specify whether a counter or not
3) run an explain instead of running the stmt (if get error, then can't run it)

The problem is that accessing information_schema.triggers can be VERY expensive if you have lots of databases and tables.  The workaround is to comment out the following lines in the items-mysql-monitor.xml file (or remove them):

	<class>
		<namespace>mysql</namespace>
		<classname>trigger_with_select_star</classname>
		<query><![CDATA[SELECT GROUP_CONCAT('\\\\\n* ', trigger_schema, '.', trigger_name ORDER BY trigger_schema, trigger_name) AS trigger_defn FROM information_schema.triggers WHERE UPPER(action_statement) LIKE '%SELECT *%']]></query>
	</class>