Possible to select multi metrics for MySQL or other database sources

1

My table looks like this

CREATE TABLE `dev_defect_density` (
  `data_date` date NOT NULL COMMENT,
  `org_id` varchar(20) NOT NULL COMMENT,
  `org_name` varchar(60) NOT NULL COMMENT,
  `version_id` varchar(20) NOT NULL,
  `version_name` varchar(20) NOT NULL,
  `sys_id` varchar(20) NOT NULL COMMENT,
  `sys_name` varchar(60) NOT NULL COMMENT,
  `dev_defect_density` tinyint(5) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT,
  PRIMARY KEY (`data_date`,`sys_id`,`sys_name`,`org_id`,`org_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I use this query in Grafana

SELECT
  data_date AS "time",
  org_name as org_name,
  sys_name AS metric,
  sum(dev_defect_density) AS value
FROM dev_defect_density
WHERE
  $__timeFilter(data_date)
GROUP BY time, org_name, metric
ORDER BY time

But got error Value column must have numeric datatype, column: org_name type: string value:xxx

Seems graphite support multiple metrics, but I also want to use it in tranditional data sources, is that possible?

2

Hi!

The problem is that you are selecting org_name. The MySQL data source will presume that any selected column that isn’t named either time or metric is an additional metric value column, and since org_name is selected but is a varchar rather than a numeric value, it causes problems.

Try this instead

SELECT
  data_date AS "time",
  sys_name AS metric,
  sum(dev_defect_density) AS value
  avg(dev_defect_density) AS avg
FROM dev_defect_density
WHERE
  $__timeFilter(data_date)
GROUP BY time, org_name, metric
ORDER BY time

I added in an average value as well to show how to add in several metric values into the same query.

3

Thanks,
But the select result only one metric, I need org_name, sys_name in the result list as two metrics

4

Is this more in line with what you need?

SELECT
  data_date AS "time",
  CONCAT(org_name, "-", sys_name) AS metric,
  sum(dev_defect_density) AS value
FROM dev_defect_density
WHERE
  $__timeFilter(data_date)
GROUP BY time, org_name, sys_name
ORDER BY time
5

But still one combined metric column, not two metrics
Seem grafana not support multi metric for mysql?

6

I don’t think I understand entirely what you are asking for. Could you take a screenshot of what you get and explaib what isn’t there?

7

I want to use query like this:

SELECT
  data_date AS "time",
  org_name AS metric1,
  sys_pro_name as metric2,
  sum(dev_defect_density) AS value
FROM dev_defect_density
GROUP BY time, org_name, org_name, sys_pro_name
ORDER BY time

And then use my custome plugin to draw a graphics like this:

image

8

I’m afraid I don’t know how to help you. The query I posted previously would give you one metric per combination of org_name and sys_name (as long as there is something for the time period).

9

Hi!
In one query getting 2 metrics is not possible, But you can add query in same graph so you can have 2 metrics, one from each.