Bug #41608 Agent runs custom SQL data collection queries even though rule is not scheduled

• Submitted: 18 Dec 2008 23:56
• Reporter: Andy Bang
• Status: Verified
• Category: Monitoring: Agent
• Severity: S2 (Serious)
• Version: 2.0.1.7125
• OS: Any
• Assigned to: Jan Kneschke
• Target Version: 2.1
• Tags: mem_20_backlog

[18 Dec 2008 23:56] Andy Bang

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.

[27 Feb 18:47] Gary Whizin

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)

[27 Feb 18:51] Andy Bang

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>