Grafana8: SQL data sources and worldmap panel issues

[bontchev]

What happened:

My Grafana installation updated to version 8.0 and my visualizations broke.

What you expected to happen:

I expected the visualizations to continue working

How to reproduce it (as minimally and precisely as possible):

I have asked about the problem on the support forum. Got no useful replies; just one person said they had the same problem.

The forum post contains the actual query I am using. Note that it uses the $__time(column) macro. This is supposed to translate to UNIX_TIMESTAMP(column) as time_sec. In other words, the corresponding column ought to contain Unix epoch times. And, indeed, this is what MySQL returns, when the query is issued manually to it.

However, if I change the Worldmap visualization to a Table visualization, what I get in this column is human-readable timestamps. This probably confuses the Worldmap plug-in and it refuses to work.

The same problem occurs with the Pie Chart visualization - but I managed to fix it there by switching from time series to table and omitting the timestamp column entirely. Unfortunately, the Worldmap visualization requires time series, so I cannot use the same trick there.

Please, folks, this needs to be fixed ASAP. Currently all our visualizations are down, because they all contain world maps.

Anything else we need to know?:

Environment:

• Grafana version: 8.0.1
• Data source type & version: MySQL
• OS Grafana is installed on: Ubuntu Linux 16.04
• User OS & Browser: Windows 7, Opera
• Grafana plugins: Worldmap, Pie Chart
• Others:

[torkelo]

This might need an update to worldmap panel

[bontchev]

If I am right and the problem is indeed that Grafana 8 converts epoch times into human-readable ones, the Worldmap panel is probably by far not the only one that would need updating. Just about every panel that uses time series would be broken. For instance, the Pie Chart panel no longer works with time series. I managed to circumvent the problem by configuring it to use table instead (which the Worldmap cannot do) - but the fact remains that Grafana 8 has broken it too.

There are probably several others; I'm just using these two. Somebody ought to look at all panels and see which ones use time series as input data format.

[torkelo]

The the problem is that we updated the SQL data sources to use our new data structure, we will review and investigate if any changes to Grafana itself is also needed to fix this

[bontchev]

I see, thanks. Would be nice if this could be fixed promptly; the boss just yelled at me that she has to send a report and can't get meaningful images from the visualization. She's pressing me to install an old version, turn off automatic updates - anything that would get things working again. Hopefully we could avoid that, if the problem is fixed quickly.

[torkelo]

It could take a few days, not sure yet

[marefr]

@bontchev sounds like you might want to downgrade temporarily until this has been resolved.

[marefr]

@torkelo let us know if you need help/support with anything related to this.

[hugohaggmark]

@bontchev I'm looking into this and I think I need a clarification. You write in the description The forum post contains the actual query I am using. . Is the statement I added below the statement you're referring too?

That statement doesn't seem to include any geo information instead I guess that is the statement used in the PieChart, right? Could you please share the SQL statement you're using and share the dashboard or worldmap panel (there are quite a lot of settings) so we can more easily reproduce. Thank you

EDIT: I'm new to the WorldMap Panel so I guess if you use countries_3letter setting this would work. Sorry for the confusion, still could be useful to see the settings you're using for WordMap panel. Thank you 🙏

SELECT
  $__time(timestamp),
  country_iso_code AS metric,
  COUNT(country_iso_code) AS value
FROM
  connections,
  geolocation
WHERE
  $__timeFilter(timestamp) AND
  connections.ip = geolocation.ip
GROUP BY metric

[grafanabot]

Thanks for creating this issue! We think it's missing some basic information.

Follow the issue template and add additional information that will help us replicate the problem.
For data visualization issues:

• Query results from the inspect drawer (data tab & query inspector)
• Panel settings can be extracted in the panel inspect drawer JSON tab

For dashboard related issues:

• Dashboard JSON can be found in the dashboard settings JSON model view

For authentication, provisioning and alerting issues, Grafana server logs are useful.

Happy graphing!

[bontchev]

@hugohaggmark, yes, this is the query. I cannot use a 3-letter country code - my database contains only 2-letter country codes. It doesn't contain coordinates or geohashes, either (and, anyway, I want the data grouped by country; not by exact location), so I can't use Worldmap's ability to use tables instead of time series. Not only this, but the 3-letter country code setting also requires the data to be time series - which is currently bugged for the MySQL data source - so it wouldn't work anyway.

Not sure what settings you need exactly but here is the complete JSON source of the panel:

{
  "id": 23763571993,
  "gridPos": {
    "h": 15,
    "w": 16,
    "x": 0,
    "y": 0
  },
  "type": "grafana-worldmap-panel",
  "title": "Attacks against the IPP honeypot of the National Laboratory of Computer Virology at Bulgarian Academy of Sciences",
  "datasource": "IPPHoney-MySQL",
  "thresholds": "50,500",
  "maxDataPoints": 1,
  "circleMaxSize": 30,
  "circleMinSize": "3",
  "colors": [
    "rgb(0, 255, 0)",
    "rgb(0, 0, 255)",
    "rgb(255, 0, 0)"
  ],
  "decimals": 0,
  "esMetric": "Count",
  "hideEmpty": false,
  "hideZero": false,
  "initialZoom": "2",
  "locationData": "countries",
  "mapCenter": "Europe",
  "mapCenterLatitude": 46,
  "mapCenterLongitude": 14,
  "mouseWheelZoom": false,
  "showLegend": true,
  "stickyLabels": false,
  "tableQueryOptions": {
    "geohashField": "geohash",
    "latitudeField": "latitude",
    "longitudeField": "longitude",
    "metricField": "metric",
    "queryType": "geohash"
  },
  "targets": [
    {
      "format": "time_series",
      "group": [],
      "metricColumn": "none",
      "rawQuery": true,
      "rawSql": "SELECT\r\n  $__time(timestamp),\r\n  country_iso_code AS metric,\r\n  COUNT(country_iso_code) AS value\r\nFROM\r\n  connections,\r\n  geolocation\r\nWHERE\r\n  $__timeFilter(timestamp) AND\r\n  connections.ip = geolocation.ip AND\r\n  IF (\"$Attacks\" = 'IPP Only', request = 'POST', 1)\r\nGROUP BY metric",
      "refId": "A",
      "select": [
        [
          {
            "params": [
              "id"
            ],
            "type": "column"
          }
        ]
      ],
      "table": "auth",
      "timeColumn": "timestamp",
      "timeColumnType": "timestamp",
      "where": [
        {
          "name": "$__timeFilter",
          "params": [],
          "type": "macro"
        }
      ]
    }
  ],
  "timeFrom": null,
  "timeShift": null,
  "unitPlural": "",
  "unitSingle": "",
  "valueName": "total"
}

[bontchev]

Also, in order to better understand what that query is doing you probably need to know the structure of the database:

CREATE TABLE IF NOT EXISTS `connections` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `timestamp` DATETIME DEFAULT NULL,
  `ip` VARCHAR(15) DEFAULT NULL,
  `remote_port` INT(11) DEFAULT NULL,
  `request` VARCHAR(6) DEFAULT NULL,
  `url` INT(4) DEFAULT NULL,
  `operation` INT(4) DEFAULT NULL,
  `file` INT(4) DEFAULT NULL,
  `query` INT(4) DEFAULT NULL,
  `user_agent` INT(4) DEFAULT NULL,
  `local_host` VARCHAR(15) DEFAULT NULL,
  `local_port` INT(11) DEFAULT NULL,
  `sensor` INT(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `time_idx` (`timestamp`),
  KEY `ip_idx` (`ip`),
  KEY `ip2_idx` (`timestamp`, `ip`)
);

CREATE TABLE IF NOT EXISTS `urls` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `path` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `operations` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `op_name` VARCHAR(63) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `files` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `filesize` INT(11) DEFAULT NULL,
  `filename` VARCHAR(255),
  `hash` VARCHAR(66),
  PRIMARY KEY (`id`),
  UNIQUE (`hash`)
);

CREATE TABLE IF NOT EXISTS `queries` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `query` JSON,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `user_agents` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_agent` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `sensors` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE (`name`)
);

CREATE TABLE IF NOT EXISTS `geolocation` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `ip` VARCHAR(15) DEFAULT NULL,
  `country_name` VARCHAR(45) DEFAULT '',
  `country_iso_code` VARCHAR(2) DEFAULT '',
  `city_name` VARCHAR(128) DEFAULT '',
  `org` VARCHAR(128) DEFAULT '',
  `org_asn` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE(`ip`)
);

[marefr]

Figured I could use a transformation as a temporary workaround but that unfortunately only works for one country code together with a Labels to fields transformation. Here's the query I used

select CURRENT_TIMESTAMP() as time, 'US' as countrycode, 10 as value

But if I use this one together with Labels to fields transformation it fails with TypeError: Cannot read property 'length' of undefined:

select CURRENT_TIMESTAMP() as time, 'US' as countrycode, 10 as value union all
select CURRENT_TIMESTAMP() as time, 'FR' as countrycode, 5 as value

If we at least would have some transformation to convert from wide frame to non-wide there would at least exist workarounds for those panels, worldmap included, not supporting this.

[hugohaggmark]

@bontchev thank you for all the details, I've been busy with other high prio things but I intend to start on this later today or first thing tomorrow. Thank you for your patience!

[bontchev]

Folks, what exactly is the problem with fixing this issue? It's kinda pressing for us, since our whole visualization is broken because of it!

It's been nearly a month already. I've seen it moved to milestone 8.0.3, 8.0.4, 8.0.5, and now to 8.0.6. What is the hold up? Is there anything I can do to help?

[mckn]

Sorry for the bad response @bontchev. We have had some trouble finding a good way to solve this issue. I have created a PR that will add a transformation that will stretch data frames in the new format to the old format.

This will resolve the issues for the old panels not supporting the new wide format properly.

[bontchev]

@mckn, PLEASE RE-OPEN THE ISSUE. The problem is NOT resolved.

I just installed version 8.0.6 and the Worldmap plugin is still broken! I still get the error

failed to convert long to wide series when converting from dataframe: long series must be sorted ascending by time to be converted

The pull request mentions something about "transforms"? Do I need to create one, in order to make the panel work? How do I do that? It's not clear at all to me from the documentation. Can't you guys just make this stuff work as it used to?!

I even tried reverting to an old version of Grafana, but the visualization is still broken, because I've been messing with the panels, trying to make them work in the new version - and several of them apparently no longer work with the old one!

Goddammit, version 8.x of Grafana has been a real disaster for us! Can't you do something? Revert the code that is causing the problems? Go through each one of the plugins and modify them to use the new format? Anything?

If I ever get this crap to work as it used to, I'm freezing Grafana and am not updating it EVER!

[mckn]

@bontchev I'm currently adding some documentation on how to use this transformation. But have a look here:

https://grafana.com/docs/grafana/next/panels/transformations/types-options/#prepare-time-series

and here:

https://grafana.com/docs/grafana/next/panels/transformations/apply-transformations/

[mckn]

And we are super sorry that we introduced this change in such a bad way. We will do our best to make sure it doesn't happen again.

The issue is that the datasource you are using is returning wide data frames instead of long data frames. The panel you are using is expecting long data frames and that is why it stops working. If you want to roll back your Grafana version you also need to rollback the datasource plugins to a version that isn't returning wide data frames.

To get more details about the data frame format please have a look here:
https://grafana.com/docs/grafana/next/developers/plugins/data-frames/

[bontchev]

@mckn, I've read these documents on transforms. When I said that it is not clear to me from the documentation what transform to create and how exactly, in order to make this crap work, I fucking meant it!

The issue is that the datasource you are using is returning wide data frames instead of long data frames. The panel you are using is expecting long data frames and that is why it stops working. If you want to roll back your Grafana version you also need to rollback the datasource plugins to a version that isn't returning wide data frames.

Wrong. When I reverted to an older version of Grafana, I didn't touch any data sources or plugins - yet the Worldmap worked. It's some of the other panels that were broken - like Pie Chart and a couple of Singlestats - and that was because I've been messing with them, trying (and succeeding) to make them work in Grafana 8.x. Apparently, they've stopped working in Grafana 7.x, as a result.

Fuck this shit, I'm out of patience.

I managed to find a backup copy of the original panel sources. I reverted to them (after having reverted to an old version of Grafana) and now everything is as it was before - I mean, working. I have also frozen the package and I will NEVER, EVER upgrade to a newer version of this crap ever again.

Goodbye.

[torkelo]

@peterschristoph did you add the "Prepare time series" transform and select "multi frame"

[peterschristoph]

@mckn

Please ignore my before comment, i delete it. I found the solution.

Can you please update your worldmap plugin site - Your information

"https://grafana.com/docs/grafana/next/panels/transformations/types-options/#prepare-time-series
and here:
https://grafana.com/docs/grafana/next/panels/transformations/apply-transformations/"

is not to be found there and has cost me several hours of searching. Also the changelog is outdated.

[peterschristoph]

@peterschristoph did you add the "Prepare time series" transform and select "multi frame"

That was the solution, which unfortunately can only be found here in this thread. Or I have overlooked it elsewhere.