Monyog Custom Counters – overcoming a problem.

by Feb 20, 2012

In this previous Blog I described an example on how to build a Custom Object in MONyog. But there is one problem you may encounter and that is, that when querying Information_Schema and Performance_Schema, it can sometimes be difficult or impossible to be sure to have a unique column (or a unique set of columns)  in the result set as neither of those generally guarantee uniqueness with a(ny) subset of columns. And a unique column (or a unique set of columns) is required for a MONyog Custom SQL Object.

A common way to add a unique row-identifer to a result set is like this (note the use of  "assignment operator" (":=") as different from "comparison operator" ("=") ):

SET @t = 0;
SELECT @t:=@t+1 AS uniq, id FROM thetable ORDER BY uniq;

.. but using this is not possible with MONyog. Only a single SQL-query can define a MONyog Custom Object (currently – we may add an option for multiple queries later though). But there is a simple workaround: a SUBQUERY in the FROM-clause can be used to reduce the above two queries to a single query:

SELECT @t:=@t+1 AS uniq, id FROM (SELECT @t:=0) AS derived, thetable ORDER BY uniq;

Actually you can add one more subquery SELECTING from `mytable`and use an ORDER BY here to ensure that the lowest value of `id` (the column you are retrieving in this simple example) will also have the lowest value of the unique identifer you created. Doing so may ease building the javascript-part of a Custom Counter as there will be less conditions to test for and sorting can be avoided (data returned are simply 'better prepared for scripting'). Such an example (with an AGGREGATE and a GROUP BY and a little formatting added as well) could be like this:

SELECT @t:=@t+1 AS uniq, id, COUNT(id)
(SELECT @t:=0) AS derived1,
(SELECT id FROM thetable ORDER BY id) AS derived2
ORDER BY uniq;

MySQL documentation has a reservation though. It says "As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed." But this reservation  seems not to apply if the assignment is a "SUBQUERY in the FROM-clause".  It is a still a separate statement as far a I can understand – and also I have not been able to provoke an unexpected behaviour with the construction no matter what SQL syntax I tried (JOINs, GROUP BY .. HAVING etc.).

(And I did not invent this myself.  The tip appears as a user comment in the MySQL documentation)