Data Templates is empty after new cacti installation

[alexf]

Hi All,

After installing a new Cacti (0.8.8f) under Ubuntu 16.04 LTS, I found that there is NO entry in the "Data Templates"
However, the title of the "Data Templates" shows "Showing Rows 1 to 30 of 41 [1,2] ", it tells that the "data templates" is not empty.
In addition, the "Graph template" and "Host template" are displayed normally.

Executing the cacti.sql for several times to re-populate the initial database gives the same results.

Then, I found that there is a SQL error (Error: 1055) in the in the cacti.log.

05/18/2016 06:12:24 PM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1055', SQL:"SELECT data_template.id, data_template.name, data_input.name AS data_input_method, data_template_data.active AS active FROM (data_template,data_template_data) LEFT JOIN data_input ON (data_template_data.data_input_id = data_input.id) WHERE data_template.id=data_template_data.data_template_id AND data_template_data.local_data_id=0 GROUP BY data_template.id ORDER BY name ASC LIMIT 0,30"

Any hints to fix the problem? Thanks!

Regards,

Alex

[alexf]

Dear All,

I have fixed the problem and found the root cause.

I use the same cacti.sql script to create the cacti DB in other mysql server.
The SQL work properly. Then, I execute the SAME SQL in the Ubuntu 16.04 mysql server Ver 5.7.12.

The output shows the error is caused by the "incompatible with sql_mode=only_full_group_by"

The fix is to change the sql_mode to remove "only_full_group_by".

Solution
1) Get the original sql_mode in your server
mysql> select @@sql_mode;
------------------
| @@sql_mode |
------------------
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


2) REMOVE the "ONLY_FULL_GROUP_BY, NO_ZERO_IN_DATE,NO_ZERO_DATE" in /etc/mysql/mysql.conf.d/mysqld.cnf
the NO_ZERO_IN_DATE and NO_ZERO_DATE cause other error in the cacti operation. So, they will also be removed

sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION


3) Restart the mysql server
# service mysql restart


Below please find the SQL that cause the error with the error code


"mysql> SELECT data_template.id, data_template.name, data_input.name AS data_input_method, data_template_data.active AS active FROM (data_template,data_template_data) LEFT JOIN data_input ON (data_template_data.data_input_id = data_input.id) WHERE data_template.id=data_template_data.data_template_id AND data_template_data.local_data_id=0 GROUP BY data_template.id ORDER BY name ASC LIMIT 30,30;

ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cacti.data_input.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"



Regards,

Alex

[kbonner]

+1

Had the same problem while migrating Cacti to a new host. This ended up being the correct fix

Thanks!
-Kevin