Column "name" must have numeric datatype ?

[smargo171]

Hello, thanks for improving the worldmap panel!

I am reading data from a PostgreSQL table and get coordinates , metric and label name. This works in the "official" panel. However in NG panel I get an error message.

Query:

SELECT
    now() AS time,
    node.nodelabel AS name,
    assets.longitude AS longitude ,
    assets.latitude AS latitude,
    count(alarms.nodeid)::varchar(255) AS metric
FROM
    node
LEFT JOIN alarms
on alarms.nodeid = node.nodeid
LEFT JOIN assets
on node.nodeid = assets.nodeid
group by node.nodelabel, assets.longitude, assets.latitude

Error message:

Value column must have numeric datatype, column: name type: string value: mytestnode

Why "name" should have numeric datatype? What am I doing wrong? Thanks.

[amotl]

Dear Sergei,

thanks for writing in. We are aware the large refactoring of the code base might have had some impact for different data sources. We would like to look into the specific issue you are observing.

Is it possible for you to provide information about your environment or even support us by giving us some details about how to ramp up an environment you are running? Database schema creation files and some data extracts we could put into a PostgreSQL database would be good. Along the lines, complementing the database dump with a dump of your dashboard JSON would make everything perfect.

With kind regards, Andreas.

[smargo171]

Dear Andreas,

Thanks for looking at it.

I am using openNMS db. Pls see attached.

My test data and jsons:

OpenNMS_3_Tables.zip Worldmap_Originall_and_NG.zip

there are 2 json files: one is for the "original" panel and it works fine, another is for NG which does not work.

[amotl]

Dear Sergei,

thanks for providing this data to us. While the second archive file is perfect to give us an idea about the current configuration of the worldmap panel, we don't know exactly what to do with the CSV files contained inside the first archive file.

It would be perfect if that would be some kind of SQL we could replay into a PostgreSQL database in order to get us bootstrapped without doing a full OpenNMS installation. Having this at hand will help tremendously when ramping up the development environment appropriately.

Thanks in advance and with kind regards, Andreas.

[smargo171]

CSVs are the export of the tables which are used in the query.

SQL which works in the "original" plugin and produces errors in NG:

SELECT
    now() AS time,
    concat(node.nodelabel,' ',alarms.ipaddr) AS name,
    assets.longitude AS longitude ,
    assets.latitude AS latitude,
    count(alarms.nodeid) AS metric
FROM
    node
LEFT JOIN alarms
on alarms.nodeid = node.nodeid
LEFT JOIN assets
on node.nodeid = assets.nodeid
group by node.nodelabel, assets.longitude, assets.latitude, alarms.ipaddr

Thanks!

[amotl]

Thanks!

CSVs are the export of the tables which are used in the query.

Would it be possible to dump them in SQL format in order to make it easier for me to replay the data into a PostgreSQL database? Saying that, I'm not only talking about the data (DML), appropriate schema files (DDL) à la CREATE TABLE ... would be cool. You can also point me to a place inside the OpenNMS open source repository where they might be living.

Thanks again!

-- https://www.geeksforgeeks.org/sql-ddl-dml-tcl-dcl/

[amotl]

I see that you are using

count(alarms.nodeid) AS metric

in the dashboard target metric for the vanilla worldmap panel while there is

count(alarms.nodeid)::varchar(255) AS metric

for the dashboard containing Worldmap NG.

While not obvious from the error message the query is giving, might this guy actually be the culprit?

[smargo171]

I see that you are using

If I don't do it, I have an error message: "Column metric must be of type UNKNOWN, TEXT, VARCHAR, CHAR. metric column name: metric type: INT8 but datatype is int64"

Something seems to wrong in NG with the data types, "metric" is expected to be text and "name" is expected to be numeric...

[smargo171]

create tables sql:

create_tables_sql.zip

[amotl]

Something seems to wrong in NG with the data types, "metric" is expected to be text and "name" is expected to be numeric...

It looks like that, I hear you. Something seems to be fishy there. Thanks for your patience. I will try to have a look into the details tonight so I want to use the daytime for getting all resources together to optimize the investigations to come.

create tables sql

Thanks a bunch for the DDL. Will I be able to load the CSV dumps from OpenNMS_3_Tables.zip 1:1 into tables created from this DDL by using some slightly magic psql command?

[smargo171]

Thanks a lot for looking at it!

Import should be possible with COPY table_name FROM ‘/path_to_csv_file.csv’ WITH FORMAT csv;

( https://tableplus.io/blog/2018/04/postgresql-import-csv-file-to-a-table.html )

[amotl]

Dear Sergei,

we just wanted to get back to you in order to apologize that we haven't been able to catch some time to look into this yet. Please bear with us.

We hope we will be able to get into this in the course of this week, otherwise we will be very happy if you could ping us again if you don't hear back from us.

Thanks already and with kind regards, Andreas.

[amotl]

Dear @smargo171,

I am just revisiting this, sorry for the delay again. I am sorry that reproducing the database on my machine was not possible so easily.

The SQL schema files you provided through create_tables_sql.zip unfortunately bring in some foreign key definitions. When editing them, the CSV files to not match the schema.

Are you able to do something about it?

With kind regards, Andreas.

[Alexandre-Silva]

Hello @smargo171 and @amotl,

Now, this may be rather stupid and a completely different problem, but I've been struggling with what appears to be exact same problem as @smargo171 but I think I fixed it. After rechecking everything, it seems that I had the "Format As" option selected to "Time Series" instead of "Table" and once changed the panel started working as expected.

Just a suggestion for something to check and try.

Good luck, Alexandre