Missing data in DB, no error or warning raised

Telegraf
,
1

Hello Influxworld,
I’m running telegraf-1.17.3 and influxdb-1.8.4 on RH7.9 and I’ve written a custom plugin that reads some data from an Oracle database using simple queries and stores them into Influxdb or, at least, it writes all of them but those with a specific value.

[root@ticks02 ~]# telegraf --config /etc/telegraf/telegraf.d/oracle.conf  --test|grep DWH_ExaDBInfo
2021-11-03T10:35:10Z I! Starting Telegraf 1.17.3
> DWH_ExaDBInfo,db="DBSCSL1",host=ticks02 host_name="exa71v1.vlhll.loc",inst_id=1i,startup_time="2021-10-21 03:49:25",status="OPEN",version="11.2.0.4.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBSCSL1",host=ticks02 host_name="exa72v1.vlhll.loc",inst_id=2i,startup_time="2021-10-21 05:54:49",status="OPEN",version="11.2.0.4.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBSCSX1",host=ticks02 host_name="exa71v1.vlhll.loc",inst_id=1i,startup_time="2021-10-21 06:08:03",status="OPEN",version="12.1.0.2.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBSCSX1",host=ticks02 host_name="exa72v1.vlhll.loc",inst_id=2i,startup_time="2021-10-21 05:54:53",status="OPEN",version="12.1.0.2.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBAMDA1",host=ticks02 host_name="exa71v1.vlhll.loc",inst_id=1i,startup_time="2021-10-21 03:49:28",status="OPEN",version="12.1.0.2.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBAMDA1",host=ticks02 host_name="exa72v1.vlhll.loc",inst_id=2i,startup_time="2021-10-21 05:54:53",status="OPEN",version="12.1.0.2.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBIC1",host=ticks02 host_name="exa71v1.vlhll.loc",inst_id=1i,startup_time="2021-10-21 03:49:25",status="OPEN",version="11.2.0.4.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBIC1",host=ticks02 host_name="exa72v1.vlhll.loc",inst_id=2i,startup_time="2021-10-21 05:54:49",status="OPEN",version="11.2.0.4.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBWH2",host=ticks02 host_name="exa71v1.vlhll.loc",inst_id=1i,startup_time="2021-10-21 03:49:25",status="OPEN",version="11.2.0.4.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBWH2",host=ticks02 host_name="exa72v1.vlhll.loc",inst_id=2i,startup_time="2021-10-21 05:54:49",status="OPEN",version="11.2.0.4.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBAI1",host=ticks02 host_name="exa71v1.vlhll.loc",inst_id=1i,startup_time="2021-10-21 03:49:25",status="OPEN",version="11.2.0.4.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBAI1",host=ticks02 host_name="exa72v1.vlhll.loc",inst_id=2i,startup_time="2021-10-21 05:54:49",status="OPEN",version="11.2.0.4.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBSS02",host=ticks02 host_name="exa71v1.vlhll.loc",inst_id=1i,startup_time="2021-10-21 03:49:25",status="OPEN",version="11.2.0.4.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBSS02",host=ticks02 host_name="exa72v1.vlhll.loc",inst_id=2i,startup_time="2021-10-21 05:54:49",status="OPEN",version="11.2.0.4.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBBP01",host=ticks02 host_name="exa71v1.vlhll.loc",inst_id=1i,startup_time="2021-10-21 03:49:25",status="OPEN",version="11.2.0.4.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBBP01",host=ticks02 host_name="exa72v1.vlhll.loc",inst_id=2i,startup_time="2021-10-21 05:54:49",status="OPEN",version="11.2.0.4.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBCB1",host=ticks02 host_name="exa71v1.vlhll.loc",inst_id=1i,startup_time="2021-10-21 03:49:28",status="OPEN",version="12.1.0.2.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBCB1",host=ticks02 host_name="exa72v1.vlhll.loc",inst_id=2i,startup_time="2021-10-21 05:54:53",status="OPEN",version="12.1.0.2.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBDWH1",host=ticks02 host_name="exa71v1.vlhll.loc",inst_id=1i,startup_time="2021-10-21 03:49:25",status="OPEN",version="11.2.0.4.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBDWH1",host=ticks02 host_name="exa72v1.vlhll.loc",inst_id=2i,startup_time="2021-10-21 05:54:49",status="OPEN",version="11.2.0.4.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBAXIT1",host=ticks02 host_name="exa71v1.vlhll.loc",inst_id=1i,startup_time="2021-10-21 03:49:25",status="OPEN",version="11.2.0.4.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBAXIT1",host=ticks02 host_name="exa72v1.vlhll.loc",inst_id=2i,startup_time="2021-10-21 05:54:49",status="OPEN",version="11.2.0.4.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBCOMM1",host=ticks02 host_name="exa71v1.vlhll.loc",inst_id=1i,startup_time="2021-10-21 03:49:25",status="OPEN",version="11.2.0.4.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBCOMM1",host=ticks02 host_name="exa72v1.vlhll.loc",inst_id=2i,startup_time="2021-10-21 05:54:49",status="OPEN",version="11.2.0.4.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBZ1",host=ticks02 host_name="exa71v1.vlhll.loc",inst_id=1i,startup_time="2021-10-21 03:49:28",status="OPEN",version="12.1.0.2.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBZ1",host=ticks02 host_name="exa72v1.vlhll.loc",inst_id=2i,startup_time="2021-10-21 05:54:53",status="OPEN",version="12.1.0.2.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBDS01",host=ticks02 host_name="exa71v1.vlhll.loc",inst_id=1i,startup_time="2021-10-21 03:49:25",status="OPEN",version="11.2.0.4.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBDS01",host=ticks02 host_name="exa72v1.vlhll.loc",inst_id=2i,startup_time="2021-10-21 05:54:49",status="OPEN",version="11.2.0.4.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBTXX1",host=ticks02 host_name="exa71v1.vlhll.loc",inst_id=1i,startup_time="2021-10-21 03:49:28",status="OPEN",version="12.1.0.2.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBTXX1",host=ticks02 host_name="exa72v1.vlhll.loc",inst_id=2i,startup_time="2021-10-21 05:54:53",status="OPEN",version="12.1.0.2.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBOEM1",host=ticks02 host_name="upx6dbadm01.vlhll.loc",inst_id=1i,startup_time="2021-10-28 11:50:31",status="OPEN",version="12.1.0.2.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBOEM1",host=ticks02 host_name="upx6dbadm02.vlhll.loc",inst_id=2i,startup_time="2021-10-28 12:12:45",status="OPEN",version="12.1.0.2.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBTC1",host=ticks02 host_name="exa71v1.vlhll.loc",inst_id=1i,startup_time="2021-10-21 03:49:25",status="OPEN",version="11.2.0.4.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBTC1",host=ticks02 host_name="exa72v1.vlhll.loc",inst_id=2i,startup_time="2021-10-21 05:54:49",status="OPEN",version="11.2.0.4.0" 1635935711000000000

> select * from DWH_ExaDBInfo where inst_id=1 and time > now()-7d;
>

If with inst_id=2 it shows thousands of values.

Nothing is shown in the logs.

Have you ever seen a similar behaviour? Do you have any recommendation?

Kind regards,
Gab

2

Hi,

When you say nothing is shown in the logs, which logs are you referring to?

What is your expected behavior?
What is the actual behavior you are seeing?

Thanks!

3

I would also suggest reading up on how InfluxDB Line protocol works. You can get a brief intro on my blog post here.

Let’s take the first two lines as examples:

> DWH_ExaDBInfo,db="DBSCSL1",host=ticks02 host_name="exa71v1.vlhll.loc",inst_id=1i,startup_time="2021-10-21 03:49:25",status="OPEN",version="11.2.0.4.0" 1635935711000000000
> DWH_ExaDBInfo,db="DBSCSL1",host=ticks02 host_name="exa72v1.vlhll.loc",inst_id=2i,startup_time="2021-10-21 05:54:49",status="OPEN",version="11.2.0.4.0" 1635935711000000000

Here is the breakdown of values:

  • The measurement name is the very first value: DWH_ExaDBInfo
  • The next values are the tags: db="DBSCSL1",host=ticks02, these values are indexed
  • The rest of the values are the fields, until the final value, which is the timestamp
  • These rows have identical timestamps

A point is uniquely identified by the measurement name, tag set, and timestamp. Therefore, these two lines are considered identical! This means InfluxDB is going to take the union of the old field set and the new field set, where any ties go to the new field set.

You probably want more tags in your data to distinguish between different instances and/or hostnames.

Edit: I modified this back to two lines as the db name was in fact different.

4

Hello jpowers,
I had already read the official documentation, but I might had misunderstood something: now it’s clearer. I would have included a second comma separated fieldKey in the example in the documentation to make it clearer to new users.

[root@ticks02 ~]# telegraf --config /etc/telegraf/telegraf.d/oracle.conf  --test|grep DWH_ExaDBInfo
2021-11-03T14:33:43Z I! Starting Telegraf 1.17.3
> DWH_ExaDBInfo,db="DBSCSL1",host=ticks02,inst_id=1 host_name="exa71v1.vlhll.loc",startup_time="2021-10-21 03:49:25",status="OPEN",version="11.2.0.4.0" 1635950024000000000
> DWH_ExaDBInfo,db="DBSCSL1",host=ticks02,inst_id=2 host_name="exa72v1.vlhll.loc",startup_time="2021-10-21 05:54:49",status="OPEN",version="11.2.0.4.0" 1635950024000000000
> DWH_ExaDBInfo,db="DBSCSX1",host=ticks02,inst_id=1 host_name="exa71v1.vlhll.loc",startup_time="2021-10-21 06:08:03",status="OPEN",version="12.1.0.2.0" 1635950024000000000
> DWH_ExaDBInfo,db="DBSCSX1",host=ticks02,inst_id=2 host_name="exa72v1.vlhll.loc",startup_time="2021-10-21 05:54:53",status="OPEN",version="12.1.0.2.0" 1635950024000000000
> DWH_ExaDBInfo,db="DBAMDA1",host=ticks02,inst_id=1 host_name="exa71v1.vlhll.loc",startup_time="2021-10-21 03:49:28",status="OPEN",version="12.1.0.2.0" 1635950024000000000
> DWH_ExaDBInfo,db="DBAMDA1",host=ticks02,inst_id=2 host_name="exa72v1.vlhll.loc",startup_time="2021-10-21 05:54:53",status="OPEN",version="12.1.0.2.0" 1635950024000000000
[...]

I’ve included the inst_id in the tag and now the data are stored as expected.

Thank you very much and kind regards,
Gab

1 Like