"Unable to convert count value to int" errors when using histograms

[blitzkopf]

I'm submitting a ...

• bug report
• feature request

What is the current behavior?

When reading histogram data from V$EVENT_HISTOGRAM_MICRO view on a reasonably busy DB the exporter reports errors:

ts=2023-04-19T16:38:08.326Z caller=main.go:375 level=error msg="Unable to convert count value to int" metric=data metricHelp="Histogram - sum total of all values in the data field." value=2.53221949e+08
...
ts=2023-04-19T16:38:08.347Z caller=main.go:375 level=error msg="Unable to convert count value to int" metric=data metricHelp="Histogram - sum total of all values in the data field." value=1.077715588e+09
ts=2023-04-19T16:38:08.351Z caller=main.go:375 level=error msg="Unable to convert count value to int" metric=data metricHelp="Histogram - sum total of all values in the data field." value=6.780668e+06

It looks like the oci8.go Oracle driver decides to treat big numbers as floats (or even as a String ) in scientific notation. And the int converter can not deal with that.

If the current behavior is a bug, please provide the steps to reproduce and if possible a minimal demo of the problem

Added this config to custom-metrics.toml, connection to Oracle must connect to CDB :

[[metric]]
context = "event_histogram"
request = '''
with rowgen as (
SELECT 
	case when rownum = 24 then 4294967295 
		else power(2,rownum -1 )
	end le 
FROM dual
CONNECT BY LEVEL <= 24
), cross as (
	select se.con_id,se.event,total_waits, se.time_waited_micro,
		le wait_time_micro, 
			sum(coalesce(eh.wait_count,0)) over (partition by se.con_id,se.event order by r.le)   wait_count 
	from ( v$system_event se
		cross join rowgen r) 
		left join V$EVENT_HISTOGRAM_micro eh 
			on eh.event=se.event 
			and eh.con_id = se.con_id  
			and eh.wait_time_micro = r.le
	--where se.event='rdbms ipc message'
)
select sys_context('userenv','CDB_NAME') CDB_NAME,nvl(c.name,'*') pdb_name,con_id,
    event, 
     le_1 ,
     le_2 ,
     le_4,
     le_8,
     le_16,
     le_32,
     le_64,
     le_128,
     le_256,
     le_512,
     le_1024,
     le_2048, 
     le_4096,
		 le_8192,
			le_16384,
			le_32768,
			le_65536,
			le_131072,
			le_262144,
			le_524288,
			 le_1048576,
			le_2097152,
			le_4194304,
		 le_4294967295 as "COUNT", 
     TIME_WAITED_MICRO as "DATA" 
from cross
pivot ( 
    sum(wait_count) 
    for  wait_time_micro in ( 
        1 as le_1 ,
        2 as le_2 ,
        4 as le_4,
        8 as le_8,
        16 as le_16,
        32 as le_32,
        64 as le_64,
        128 as le_128,
        256 as le_256,
        512 as le_512,
        1024 as le_1024,
        2048 as le_2048,
        4096 as le_4096,
        8192 as le_8192,
        16384 as le_16384,
        32768 as le_32768,
        65536 as le_65536,
        131072 as le_131072,
        262144 as le_262144,
        524288 as le_524288,
        1048576 as le_1048576,
        2097152 as le_2097152,
        4194304 as le_4194304,
        4294967295 as le_4294967295
    )
)
left join V$containers c Using(con_id) 
'''
labels = ["cdb_name","pdb_name","event"]
metricsdesc = { data = "Histogram - sum total of all values in the data field." }
metricstype = { data = "histogram", count = "counter" }
metricsbuckets = { data = { le_1 = "1", le_2 = "2", le_4 = "4", le_8 = "8", le_16  = "16",    le_32  = "32", le_64  = "64", le_128  = "128", le_256  = "256", le_512  = "512", le_1024  = "1024",   le_2048  = "2048" , le_4096  = "4096" ,le_8192 = "8192",le_16384 = "16384",le_32768 = "32768",le_65536 = "65536",le_131072 = "131072",le_262144 = "262144",le_524288 = "524288", le_1048576= "1048576", le_2097152 = "2097152", le_4194304 = "4194304"  } }

What is the expected behavior?

The exporter should return the results of the Query as Histogram to Prometheus.

What is the motivation / use case for changing the behavior?

I would like to visualize histogram information in grafana from Oracle Events like this:

Please tell us about your environment:

Current git version built locally as 0.4.4-ora21.8.linux-amd64

[blitzkopf]

I did manage to get this working using Go-Ora instead of go-oci8, but I did not want to send a create a PR without discussing the matter first, maybe there are other issues with Go-ora that I'm not aware of.
I commited the changes to my fork at https://github.com/blitzkopf/oracledb_exporter , they are quite simple.

[Yannig]

Hi @blitzkopf,

Thanks for your feedback. I was already considering to switch to this driver to avoid the driver installation from Oracle.

It seems that @schmikei is already working on this topic in this PR: #312

Guess we can have a look at how you have done the modification but as far as I can tell, it seems to be quite straighforward :)

[Yannig]

@blitzkopf the swith have been made by @schmikei (thanks again !)

I think you should give a try to the last version.

Yannig

[blitzkopf]

I'm unable to run with any custom-metric.toml, I think it's this issue: #321

ts=2023-05-10T12:40:08.780Z caller=collector.go:352 level=info /home/vt368/prome/custom-metrics.toml="has been changed. Reloading metrics..."
panic: assignment to entry in nil map

goroutine 24 [running]:
github.com/observiq/oracledb_exporter/collector.(*Exporter).checkIfMetricsChanged(0xc0002b2000)
        /home/vt368/repos/clone/oracledb_exporter/collector/collector.go:353 +0x3e5
github.com/observiq/oracledb_exporter/collector.(*Exporter).scrape(0xc0002b2000, 0xc0001823c0)
        /home/vt368/repos/clone/oracledb_exporter/collector/collector.go:268 +0x585
github.com/observiq/oracledb_exporter/collector.(*Exporter).Collect(0xc0002b2000, 0x0?)
        /home/vt368/repos/clone/oracledb_exporter/collector/collector.go:180 +0xf4
github.com/observiq/oracledb_exporter/collector.(*Exporter).Describe(0x0?, 0xc0001b0a80)
        /home/vt368/repos/clone/oracledb_exporter/collector/collector.go:158 +0xc7
github.com/prometheus/client_golang/prometheus.(*Registry).Register.func1()
        /home/vt368/go/pkg/mod/github.com/prometheus/client_golang@v1.14.0/prometheus/registry.go:280 +0x2b
created by github.com/prometheus/client_golang/prometheus.(*Registry).Register
        /home/vt368/go/pkg/mod/github.com/prometheus/client_golang@v1.14.0/prometheus/registry.go:279 +0x19b

[shaxiaozz]

I also encountered this type of problem "Unable to convert count value to float" when using custom indicators.

After many attempts, I found a solution. Below I will briefly describe the custom indicator I want to implement:

Count the number of rows in the formtable_main_1342 table：
At first I executed SQL in the sqlplus window: select count(*) from formtable_main_1342; It was successful

So my custom toml configuration file looks like this

[[metric]]
context = "formtable_main_1342"
request = "select count(*) from formtable_main_1342"
metricsdesc = {count="formtable_main_1342 table count."}

I applied the custom configuration file and started the container, but found an error message and could not get the data of the custom indicator. The reason was: "Unable to convert count value to float"

Therefore, I am very confused. The data returned by sqlplus sql execution is integer type, not floating point data.

After testing, I found that the title in the sql query needs to be consistent with the definition in metricsdesc. Since the title of my sql query is COUNT()*, and the definition in my metricsdesc is count=, I need to use the as instruction to change the title of the sql output to COUNT

Therefore, the correct configuration should be as follows:

[[metric]]
context = "formtable_main_1342"
request = "select count(*) as count from formtable_main_1342"
metricsdesc = {count="formtable_main_1342 table count."}

Since my custom indicator is completed, I hope it will be helpful to you~