31.2.2.2 Main Queries

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_ATTRIBUTES is used. See Section 31.2.3.2, “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, mysql:status:open_files_limit.

Note

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 %user_count% is mapped to the Data Item mysql:anonymous_user:user_count defined in the query.