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:[email protected]+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:[email protected]+1 AS uniq, id FROM (SELECT @t:=0) AS derived, thetable ORDER BY uniq;
SELECT @t:[email protected]+1 AS uniq, id, COUNT(id)
(SELECT @t:=0) AS derived1,
(SELECT id FROM thetable ORDER BY id) AS derived2
GROUP BY id
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)