When editing a data source template, inconsistent results can be seen due to database query

[eriksejr]

Describe the bug
Some SQL queries can return inconsistent results depending on the order in which rows were added to the database and/or database version.

To Reproduce
Steps to reproduce the behavior:

1. Have a large installation of cacti 1.1.37 - several hundred templates with 3k-4k data sources running on FreeBSD 10/MySQL 5.6
2. Export the database with mysqldump
3. Import the database on a Linux host with MySQL 5.7 and Cacti 1.2.9. Perform the database upgrade from the CLI
4. Start cacti. Multiple datasource templates will be broken in the GUI throwing the following errors in cacti.log if one is clicked:

2020/03/12 18:39:32 - ERROR PHP NOTICE: Undefined index: data_template_id in file: /usr/share/cacti/site/data_templates.php  on line: 607
2020/03/12 18:39:32 - CMDPHP PHP ERROR NOTICE Backtrace:  (/data_templates.php[67]:template_edit(), /data_templates.php[607]:CactiErrorHandler())
2020/03/12 18:39:32 - ERROR PHP NOTICE: Undefined index: data_template_id in file: /usr/share/cacti/site/data_templates.php  on line: 607
2020/03/12 18:39:32 - CMDPHP PHP ERROR NOTICE Backtrace:  (/data_templates.php[67]:template_edit(), /data_templates.php[607]:CactiErrorHandler())
2020/03/12 18:39:32 - ERROR PHP NOTICE: Undefined index: data_template_id in file: /usr/share/cacti/site/data_templates.php  on line: 607
2020/03/12 18:39:32 - CMDPHP PHP ERROR NOTICE Backtrace:  (/data_templates.php[67]:template_edit(), /data_templates.php[607]:CactiErrorHandler())
2020/03/12 18:39:32 - ERROR PHP NOTICE: Undefined index: data_template_id in file: /usr/share/cacti/site/data_templates.php  on line: 607
2020/03/12 18:39:32 - CMDPHP PHP ERROR NOTICE Backtrace:  (/data_templates.php[67]:template_edit(), /data_templates.php[607]:CactiErrorHandler())
2020/03/12 18:39:32 - ERROR PHP NOTICE: Undefined index: data_template_id in file: /usr/share/cacti/site/data_templates.php  on line: 607
2020/03/12 18:39:32 - CMDPHP PHP ERROR NOTICE Backtrace:  (/data_templates.php[67]:template_edit(), /data_templates.php[607]:CactiErrorHandler())
2020/03/12 18:39:32 - ERROR PHP NOTICE: Undefined index: data_sources in file: /usr/share/cacti/site/data_templates.php  on line: 661
2020/03/12 18:39:32 - CMDPHP PHP ERROR NOTICE Backtrace:  (/data_templates.php[67]:template_edit(), /data_templates.php[661]:CactiErrorHandler())
2020/03/12 18:39:32 - ERROR PHP NOTICE: Undefined index: data_sources in file: /usr/share/cacti/site/data_templates.php  on line: 661
2020/03/12 18:39:32 - CMDPHP PHP ERROR NOTICE Backtrace:  (/data_templates.php[67]:template_edit(), /data_templates.php[661]:CactiErrorHandler())
2020/03/12 18:39:32 - ERROR PHP NOTICE: Undefined index: data_input_id in file: /usr/share/cacti/site/data_templates.php  on line: 739
2020/03/12 18:39:32 - CMDPHP PHP ERROR NOTICE Backtrace:  (/data_templates.php[67]:template_edit(), /data_templates.php[739]:CactiErrorHandler())
2020/03/12 18:39:32 - ERROR PHP NOTICE: Undefined index: data_input_id in file: /usr/share/cacti/site/data_templates.php  on line: 744
2020/03/12 18:39:32 - CMDPHP PHP ERROR NOTICE Backtrace:  (/data_templates.php[67]:template_edit(), /data_templates.php[744]:CactiErrorHandler())

Expected behavior
Templates which displayed complete information without errors prior to the upgrade and database dump/import will continue to appear in the GUI with complete information and without errors.

In the screenshot below the template is missing its name, data input method and has the wrong data source profile.

Screenshots

Desktop (please complete the following information):

• OS: Pre-Upgrade FreeBSD 10.X, Post-Upgrade Ubuntu 18.04
• Browser Firefox & Chrome
• Version 68 & 80

Additional context
This problem appears to be caused by this SQL query (similar ones may have this issue as well):

mysql> SELECT dtd.*, SUM(CASE WHEN dl.data_template_id = '41' THEN 1 ELSE 0 END) AS data_sources FROM data_template_data AS dtd LEFT JOIN data_local AS dl ON dl.id=dtd.local_data_id WHERE dtd.data_template_id = '41' HAVING dtd.local_data_id=0;
Empty set (0.07 sec)

Where this query should return the template information for template ID 41, it returns an empty set on the post-upgrade database, but the expected data on the pre-upgrade database:

mysql> SELECT dtd.*, SUM(CASE WHEN dl.data_template_id = '41' THEN 1 ELSE 0 END) AS data_sources FROM data_template_data AS dtd LEFT JOIN data_local AS dl ON dl.id=dtd.local_data_id WHERE dtd.data_template_id = '41' HAVING dtd.local_data_id=0;
+----+-----------------------------+---------------+------------------+---------------+--------+------------------------------+------------+------------------+----------+--------+------------+----------+--------------------------+------------------------+--------------+
| id | local_data_template_data_id | local_data_id | data_template_id | data_input_id | t_name | name                         | name_cache | data_source_path | t_active | active | t_rrd_step | rrd_step | t_data_source_profile_id | data_source_profile_id | data_sources |
+----+-----------------------------+---------------+------------------+---------------+--------+------------------------------+------------+------------------+----------+--------+------------+----------+--------------------------+------------------------+--------------+
| 41 |                           0 |             0 |               41 |             2 | on     | |host_description| - Traffic |            | NULL             |          | on     |            |      300 |                          |                      2 |         1304 |
+----+-----------------------------+---------------+------------------+---------------+--------+------------------------------+------------+------------------+----------+--------+------------+----------+--------------------------+------------------------+--------------+
1 row in set (0.04 sec)

The reason for this is that the query can only return one row due to the SUM(CASE WHEN dl.data_template_id = '41' THEN 1 ELSE 0 END) AS data_sources portion of the query. Without any type of forced ORDERING this will cause the query to return the first chronologically inserted row in the database which matches. Under normal circumstances this will always be the template and the query will work. However, if the database is changed in such a way where the rows could be inserted in a different chronological order - such as during an dump/import operation - this query can break and return nothing.

For example, in the pre-upgrade database the slightly-modified version of this query returns:

mysql> SELECT dtd.* FROM data_template_data AS dtd LEFT JOIN data_local AS dl ON dl.id=dtd.local_data_id WHERE dtd.data_template_id = '41' LIMIT 1;
+----+-----------------------------+---------------+------------------+---------------+--------+------------------------------+------------+------------------+----------+--------+------------+----------+--------------------------+------------------------+--------------+
| id | local_data_template_data_id | local_data_id | data_template_id | data_input_id | t_name | name                         | name_cache | data_source_path | t_active | active | t_rrd_step | rrd_step | t_data_source_profile_id | data_source_profile_id | data_sources |
+----+-----------------------------+---------------+------------------+---------------+--------+------------------------------+------------+------------------+----------+--------+------------+----------+--------------------------+------------------------+--------------+
| 41 |                           0 |             0 |               41 |             2 | on     | |host_description| - Traffic |            | NULL             |          | on     |            |      300 |                          |                      2 |         1304 |
+----+-----------------------------+---------------+------------------+---------------+--------+------------------------------+------------+------------------+----------+--------+------------+----------+--------------------------+------------------------+--------------+
1 row in set (0.04 sec)

This is expected, and when the HAVING filter is applied to this result it will match and the row is returned.

In the post-upgrade/import database the same query returns a different row:

mysql> SELECT dtd.* FROM data_template_data AS dtd LEFT JOIN data_local AS dl ON dl.id=dtd.local_data_id WHERE dtd.data_template_id = '41' LIMIT 1;                    +-------+-----------------------------+---------------+------------------+---------------+--------+------------------------------------------------+-------------------------------------------------------+---------------------------------------------------------------------+----------+--------+------------+----------+--------------------------+------------------------+
| id    | local_data_template_data_id | local_data_id | data_template_id | data_input_id | t_name | name                                           | name_cache                                            | data_source_path                                                    | t_active | active | t_rrd_step | rrd_step | t_data_source_profile_id | data_source_profile_id |
+-------+-----------------------------+---------------+------------------+---------------+--------+------------------------------------------------+-------------------------------------------------------+---------------------------------------------------------------------+----------+--------+------------+----------+--------------------------+------------------------+
| 11665 |                          41 |         11528 |               41 |             2 | NULL   | |host_description| - Traffic - |query_ifDescr| | Resolute Bay PowerBeam02 SCIN -> CSP - Traffic - eth0 | <path_rra>/resolute_bay_powerbeam02_scin_-_csp_traffic_in_11528.rrd | NULL     | on     | NULL       |      300 |                          |                      3 |
+-------+-----------------------------+---------------+------------------+---------------+--------+------------------------------------------------+-------------------------------------------------------+---------------------------------------------------------------------+----------+--------+------------+----------+--------------------------+------------------------+
1 row in set (0.00 sec)

This is a data source - not a template. When the HAVING filter is applied this returns an empty set and the template is broken.

[TheWitness]

Have you considered a pull request? Good catch by the way!

[eriksejr]

Thanks for the response. I have not considered a pull request because all my attempts thus far to compose a working equivalent SQL query have failed. My SQL abilities may not be good enough to come up with a graceful solution to this problem on my own, so I was hoping for some help with that. Even if you try to add some ordering to the query it seems to break the SUM() portion of the query which tries to sum up the data sources using the template.

I've also tried variations of the query using SUM(IF(dl.data_template_id = '41', 1, 0)) instead but I run into the same problem. I can't seem to make it work without reverting to two queries.

[eriksejr]

I commissioned some SQL expertise from within my company here and we came up with this as a possible alternative:

mysql> SELECT dtd.*, data_sources FROM (SELECT COUNT(*) AS data_sources FROM data_local AS dl LEFT JOIN data_template_data AS idtd ON dl.id = idtd.local_data_id WHERE idtd.data_template_id=41) AS ds JOIN (SELECT * FROM data_template_data WHERE data_template_id=41 AND local_data_id=0) AS dtd;
+----+-----------------------------+---------------+------------------+---------------+--------+------------------------------+------------+------------------+----------+--------+------------+----------+--------------------------+------------------------+--------------+
| id | local_data_template_data_id | local_data_id | data_template_id | data_input_id | t_name | name                         | name_cache | data_source_path | t_active | active | t_rrd_step | rrd_step | t_data_source_profile_id | data_source_profile_id | data_sources |
+----+-----------------------------+---------------+------------------+---------------+--------+------------------------------+------------+------------------+----------+--------+------------+----------+--------------------------+------------------------+--------------+
| 41 |                           0 |             0 |               41 |             2 | on     | |host_description| - Traffic |            | NULL             |          | on     |            |      300 |                          |                      3 |         4628 |
+----+-----------------------------+---------------+------------------+---------------+--------+------------------------------+------------+------------------+----------+--------+------------+----------+--------------------------+------------------------+--------------+
1 row in set (0.04 sec)

[TheWitness]

I just made some comments. We need the fix against the 1.2.x branch. Otherwise, looks good and no negative performance impact.