UPPER CASE / lower case SMP index data source creati problem

[Wohali]

Hi there,

I earlier posted to another thread but think this warrants its own thread.

While using Cacti to monitor interface data on a host that has both a TUNL0 and a tunl0 interface, I noticed that sometimes Cacti will select the wrong interface.

I'm creating the graph by using the New Graph tab, where the interfaces are listed. I check the box for the TUNL0 interface (the one that I want, item #52 in the SNMP index), select "Total In/Out Bytes with Total Bandwidth," click Create, and wait. Sometimes Cacti will incorrectly select the tunl0 (lowercase, also item #4 in the SNMP index) interface and create a data source on it instead. Going back to try and force TUNL0 to be created as a data source doesn't work. I've also checked the specification for the data source, and it is using ifDescr, "TUNL0" (correctly in capitals).

A check of the data_local table shows that the data source is pointing to the wrong index number -- 4 instead of 52. I can't figure out if cacti is doing a case insensitive match on the index name, but it doesn't look like it.

I can't isolate the code responsible for the bug, but I am not done looking yet, either. Has anyone else seen this issue, or have a resolution for the problem?

[Wohali]

Hi there,

After much logfile and code analysis, I found the gotcha. MySQL does not by default perform case sensitive string matches. As the query from cacti looks like:

SELECT host_snmp_cache.snmp_index
FROM host_snmp_cache
WHERE host_snmp_cache.field_name = 'ifDescr'
AND host_snmp_cache.field_value = 'TUNL0'
AND host_snmp_cache.host_id = '2'
AND host_snmp_cache.snmp_query_id = '1'

...the host_snmp_cache.field_value field must be declared as BINARY. As I had a reasonably fresh installation, I just dropped the tables, edited the cacti.sql file to include the BINARY declaration for field_value, and reloaded. Worked like a charm.

As long as cacti is tied directly to MySQL, this might be a good thing to roll into the default cacti.sql.gz file.

Hope this helps others!