The main queries enable you to retrieve data from the monitored server.
When defining queries, the following restrictions apply:
The query must be defined within a
<![CDATA]>container. For example:
<![CDATA[SELECT X FROM Y AS FOO]]>. Do not enter any characters between CDATA and the following [, nor between the [ and the start of the query. The same rule applies to the closing ]].
Only SELECT statements are possible. It is not possible to use INSERT, UPDATE, DELETE, and so on.
It is not possible to define more than one query per class.
The agent must have sufficient rights to run the query.
Do not define queries which take longer to run than the schedule defined on the advisor. For example, if the query takes 2 minutes to run, but the advisor-defined schedule requires the query to run every 1 minute, no results are returned. To avoid this, test your query thoroughly on the monitored server. If the custom data collection is deployed on multiple agents, it must be tested on each monitored server and the schedule modified accordingly.
The query can return only one row, except if the result type
CLASS_TYPE_1STCOL_ATTRIBUTESis used. See Section 18.104.22.168, “Returning Multiple Rows” for more information.
For each value retrieved from the server, you must assign a name. That is, you must use the following format, where NAME is the name applied to the data collection:
SELECT X AS NAME FROM Y
The items are displayed in the Data Item
drop-down menu on the Variable Assignment
frame of the new Advisor page. They take the following format:
namespace:classname:name. For example,
The examples used in this section are taken from the default advisors delivered with your MySQL Enterprise Monitor installation.
The following example is used by the Server Has Anonymous Accounts advisor:
<class> <namespace>mysql</namespace> <classname>anonymous_user</classname> <query><![CDATA[SELECT COUNT(*) AS user_count FROM mysql.user WHERE user='']]></query> </class>
In this advisor, the variable
is mapped to the Data Item
mysql:anonymous_user:user_count defined in