Your Cacti Server v1.2.5 has been installed/updated with errors

[Fiber-Optics]

Hello, Cacti forum

I used the yum upgrade and it updated my cacti install. After following the install instructions this error happened. I did some searching and found similar issues but no luck on getting it resolved. I am running CentOS 7 with SE Linux at the moment disabled. Any help on this issue is greatly appreciated as I don't have access to my graphs over the past couple days. I wish the installer didn't allow it to upgrade because it should have warned before if it has issues that need to get resolved first. My database version mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1

I followed a few examples from this post https://github.com/Cacti/cacti/issues/2824

MariaDB [(none)]> use cacti
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [cacti]> ALTER TABLE poller_output_realtime ADD PRIMARY KEY (local_data_id, rrd_name, time, poller_id);
ERROR 1068 (42000): Multiple primary key defined
MariaDB [cacti]> ALTER TABLE poller_output_realtime DROP PRIMARY KEY;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [cacti]> ALTER TABLE poller_output_realtime DROP PRIMARY KEY;
ERROR 1091 (42000): Can't DROP 'PRIMARY'; check that column/key exists
MariaDB [cacti]> ALTER TABLE poller_output_realtime ADD PRIMARY KEY (local_data_id, rrd_name, time, poller_id);
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
MariaDB [cacti]> SET @@global.innodb_large_prefix = 1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [cacti]> ALTER TABLE poller_output_realtime ADD PRIMARY KEY (local_data_id, rrd_name, time, poller_id);
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
MariaDB [cacti]> ALTER TABLE poller_output_realtime DROP PRIMARY KEY;
ERROR 1091 (42000): Can't DROP 'PRIMARY'; check that column/key exists

------

Error
Your Cacti Server v1.2.5 has been installed/updated with errors

Database Upgrade - Version 1.2.5
The following table lists the status of each upgrade performed on the database

UPDATE graph_local AS gl INNER JOIN graph_templates_item AS gti ON gti.local_graph_id = gl.id INNER JOIN data_template_rrd AS dtr ON gti.task_item_id = dtr.id INNER JOIN data_local AS dl ON dl.id = dtr.local_data_id SET gl.snmp_query_id = dl.snmp_query_id, gl.snmp_index = dl.snmp_index WHERE gl.graph_template_id IN (SELECT graph_template_id FROM snmp_query_graph) AND gl.snmp_query_id = 0 [Success]

UPDATE graph_local AS gl INNER JOIN ( SELECT DISTINCT local_graph_id, task_item_id FROM graph_templates_item ) AS gti ON gl.id = gti.local_graph_id INNER JOIN data_template_rrd AS dtr ON gti.task_item_id = dtr.id INNER JOIN data_template_data AS dtd ON dtr.local_data_id = dtd.local_data_id INNER JOIN data_input_fields AS dif ON dif.data_input_id = dtd.data_input_id INNER JOIN data_input_data AS did ON did.data_template_data_id = dtd.id AND did.data_input_field_id = dif.id INNER JOIN snmp_query_graph_rrd AS sqgr ON sqgr.snmp_query_graph_id = did.value SET gl.snmp_query_graph_id = did.value WHERE input_output = 'in' AND type_code = 'output_type' AND gl.graph_template_id IN (SELECT graph_template_id FROM snmp_query_graph) [Success]

ALTER TABLE poller_output_realtime DROP PRIMARY KEY, ADD PRIMARY KEY (local_data_id, rrd_name, time, poller_id) ----Fail

[netniV]

Your problem is with the mysql settings:
https://github.com/Cacti/cacti/issues/2836

[Fiber-Optics]

Hi, netniV

I read the post but can you make a few suggestions on what part of that article directly involves my cacti setup. And what commands or file locations that need to be updated?

Thanks for your help much appreciated!

[Fiber-Optics]

more /etc/my.cnf
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
max_heap_table_size=512M
max_allowed_packet=16777216
tmp_table_size=128M
join_buffer_size=128M
innodb_file_per_table=ON
innodb_buffer_pool_size=1024M
innodb_doublewrite=OFF
innodb_additional_mem_pool_size=128M
innodb_additional_mem_pool_size=2
innodb_file_format=Barracuda
innodb_file_per_table=1
innodb_large_prefix=1

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d


----

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 73
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use cacti
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [cacti]> show variables like "%innodb_file%";
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| innodb_file_format | Barracuda |
| innodb_file_format_check | ON |
| innodb_file_format_max | Barracuda |
| innodb_file_per_table | ON |
+--------------------------+-----------+
4 rows in set (0.00 sec)

[Fiber-Optics]

Well I was able to get the install to finish after following random instructions over the web. So this was the process and really was hoping it was a quick fix but when your not a MySQL / MariaDB expert but understand cacti overall it helps to have the guts to just redo a bunch of stuff. I was like what's the worst case scenario?? Just have to redo my entire cacti graphs I did that before after my 10 year cacti server finally took a nose dive so it can't be any worse than that. Hopefully some of these instructions help someone else.

I followed those instructions to update the MariaDB

https://linuxhostsupport.com/blog/how-t ... -centos-7/

vi /etc/my.cnf.d/server.cnf

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
max_heap_table_size=512M
#max_allowed_packet=16777216
tmp_table_size=128M
join_buffer_size=128M
innodb_file_per_table=ON
innodb_buffer_pool_size=1024M
innodb_doublewrite=OFF
#innodb_additional_mem_pool_size=128M
#innodb_additional_mem_pool_size=2
innodb_buffer_pool_instances=9
innodb_file_format=Barracuda
innodb_file_per_table=1
innodb_large_prefix=1
innodb_io_capacity=5000
innodb_flush_log_at_timeout=3
innodb_read_io_threads=32
innodb_write_io_threads=16

And after all that I then went and did all these steps manually

MariaDB [(none)]> use cacti
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [cacti]> DELETE FROM settings WHERE name LIKE 'install_%';
MariaDB [cacti]> show variables like "%innodb_file%";
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| innodb_file_format | Barracuda |
| innodb_file_format_check | ON |
| innodb_file_format_max | Barracuda |
| innodb_file_per_table | ON |
+--------------------------+-----------+
4 rows in set (0.00 sec)

MariaDB [cacti]> alter table poller_output_realtime ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [cacti]> SELECT * FROM information_schema.INNODB_SYS_TABLES WHERE name='cacti/poller_output_realtime'\G
*************************** 1. row ***************************
TABLE_ID: 384
NAME: cacti/poller_output_realtime
FLAG: 33
N_COLS: 8
SPACE: 271
FILE_FORMAT: Barracuda
ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
1 row in set (0.00 sec)

MariaDB [(none)]> use cacti
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [cacti]> show variables like "%innodb_file%";
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| innodb_file_format | Barracuda |
| innodb_file_format_check | ON |
| innodb_file_format_max | Barracuda |
| innodb_file_per_table | ON |
+--------------------------+-----------+
4 rows in set (0.00 sec)

MariaDB [cacti]> alter table poller_output_realtime ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [cacti]> SELECT * FROM information_schema.INNODB_SYS_TABLES WHERE name='cacti/poller_output_realtime'\G
*************************** 1. row ***************************
TABLE_ID: 384
NAME: cacti/poller_output_realtime
FLAG: 33
N_COLS: 8
SPACE: 271
FILE_FORMAT: Barracuda
ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
SPACE_TYPE: Single
1 row in set (0.00 sec)

MariaDB [cacti]> SET @@global.innodb_large_prefix = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [cacti]> ALTER TABLE poller_output_realtime DROP PRIMARY KEY;
ERROR 1091 (42000): Can't DROP INDEX `PRIMARY`; check that it exists
MariaDB [cacti]> ALTER TABLE poller_output_realtime ADD PRIMARY KEY (local_data_id, rrd_name, time, poller_id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

Next was to follow the reinstall procedure on cacti the link I followed

--https://www.fosslinux.com/7527/how-to-i ... ntos-7.htm

[root@server my.cnf.d]# cd /opt/
[root@server opt]# ls
database-dump.sql logdistro

[root@server opt]# mysql -u root -p cacti < database-dump.sql
Enter password:

MariaDB [(none)]> use cacti
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

MariaDB [cacti]> CREATE USER 'cactiuser'@'localhost' IDENTIFIED BY 'PUTPASSWORDHERE';
Query OK, 0 rows affected (0.00 sec)

MariaDB [cacti]> grant all privileges on cacti.* to cactiuser@localhost ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [cacti]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [cacti]> Bye

[root@server opt]# mysql -u root -p
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 10.2.26-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> GRANT SELECT ON mysql.time_zone_name TO cactiuser@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> Bye

-Then went back to restart the installer and after some fingers crossed it went smoothly. Luckily none of my current graphs were deleted because I upgraded my MariaDB to 10.2

Pre-installation Checks
Location checks

PHP - Recommendations (web)

PHP - Recommendations (cli)

PHP - Module Support (Required)

PHP - Module Support (Optional)

MySQL - TimeZone Support

MySQL - Settings

Installation Type
Upgrade
Upgrade from 1.2.4 to 1.2.5
WARNING:
In the event of issues, It is highly recommended that you clear your browser cache, closing then reopening your browser (not just the tab Cacti is on) and retrying, before raising an issue with The Cacti Group
On rare occasions, we have had reports from users who experience some minor issues due to changes in the code. These issues are caused by the browser retaining pre-upgrade code and whilst we have taken steps to minimise the chances of this, it may still occur. If you need instructions on how to clear your browser cache, https://www.refreshyourcache.com/ is a good starting point.
If after clearing your cache and restarting your browser, you still experience issues, please raise the issue with us and we will try to identify the cause of it.

Directory Permission Checks
Please ensure the directory permissions below are correct before proceeding. During the install, these directories need to be owned by the Web Server user. These permission changes are required to allow the Installer to install Device Template packages which include XML and script files that will be placed in these directories. If you choose not to install the packages, there is an 'install_package.php' cli script that can be used from the command line after the install is complete.
These directories will be required to stay read writable after the install so that the Cacti remote synchronization process can update them as the Main Cacti Web Site changes
NOTE:
If you are installing packages, once the packages are installed, you should change the scripts directory back to read only as this presents some exposure to the web site.
Potential permission issues

Required Writable at Install Time Only

Required Writable after Install Complete


Server Collation
Your server collation appears to be UTF8 compliant
Database Collation
Your database default collation appears to be UTF8 compliant
Table Setup
All your tables appear to be UTF8 compliant

Complete
Your Cacti Server v1.2.5 has been installed/updated. You may now start using the software.
Database Upgrade - Version 1.2.5

Process Log

[netniV]

The important part was adding innodb_large_prefix=1 I believe, which allows for longer keys I believe. I mean the name wouldn't give that away but hey...

There are now steps taken at the installation wizard to point this out to you.