Upgrading Debian 9 (Stretch) to Debian 10 (Buster) did not provide a smooth Cacti upgrade from 0.8.8h to 1.2.2 in my experience. Debian automatically upgraded my packages, but was unable to handle the database upgrade when I visited the web interface after the upgrade.
First, I was presented with an error during the database upgrade about a missing table. The exact error message was:
Error Locating Profiles
Then, I was presented with an error during the schema upgrade from 1.1.31 to 1.1.34. The exact error message was:
ALTER IGNORE TABLE version DROP PRIMARY KEY [Fail]
I describe how I successfully upgraded and overcame these errors.
Note: During the OS upgrade, Debian asked if I wanted to keep my existing Cacti config files or use the new package files. I backed up my Cacti config files and then allowed Debian to overwrite my config files with new config files, just in case the new files contained parameters I’d need. Afterwards, I manually copied the MySQL database password from my old files to my new files. Here’s how I backed up the files:
cp /etc/cacti/debian.php /etc/cacti/debian.php.bak cp /etc/cacti/spine.conf /etc/cacti/spine.conf.bak
Backup Your Cacti Database
Create a full MySQL database backup, just in case things go wrong.
mysqldump cacti > mysql-cacti-full.sql
If things go terrible, you can drop all of the tables in your cacti database and then restore the full backup with this command:
mysql cacti < mysql-cacti-full.sql
Import Cacti 1.2.2 Database Tables
Before we continue with the Cacti automated web-based database upgrade, we need to resolve an issue that is preventing Cacti from even attempting an upgrade. I did not record the exact message, but Cacti was complaining about a missing table.
I imported the default database for Cacti 1.2.2 over our existing Cacti database. This created tables that did not exist in 0.8.8h without affecting our existing Cacti tables.
mysql -f cacti < /usr/share/doc/cacti/cacti.sql
I used the force option (-f) to ignore errors, since the default database file attempted to create a lot of tables and records that already exist.
After this, I was able to refresh the Cacti web interface and move on to the next step of the upgrade process.
Resolve Cacti 1.1.34 Database Version Table Upgrade Error
The Cacti web-based upgrade successfully upgraded my database version from 0.8.8h through multiple versions all the way up to 1.0.0 and beyond. It installed multiple upgrades beyond 1.0.0 until it ran into an error upgrading from 1.1.31 to 1.1.34.
The upgrade script failed on the very last command of the 1.1.34 upgrades, after it had dropped the PRIMARY KEY for the version table and was attempting to create a new PRIMARY KEY for the version table. It failed to create a primary key, because the table contained duplicate entries.
When I browsed to the Cacti version table, I found the following two duplicate entries. MySQL won’t be able to create a unique PRIMARY KEY if the table has duplicate entries!
I had to TRUNCATE the version table to remove the two duplicate records, since there isn’t an easier way to remove records from an unindexed table.
TRUNCATE TABLE version;
I then manually created the PRIMARY KEY:
ALTER TABLE `version` ADD PRIMARY KEY(`cacti`);
At this point the table has been upgraded to the 1.1.34 schema, since this was the only change that failed when upgrading from the 1.1.31 schema, so I proceeded to INSERT the 1.1.34 version number into the record table:
INSERT INTO `version` (`cacti`) VALUES ('1.1.34');
At this point, I had resolved the error that the Cacti upgrade scripts encountered and expected the web interface to be able to proceed.
Clearing Cacti Upgrade Status
Unfortunately, refreshing the Cacti web interface just kept showing the same error status without attempting to proceed, so I had to figure out how to clear the Cacti upgrade status.
Cacti Upgrade Cache File (for reference only – don’t need to delete this file)
The upgrade error page mentioned Cacti was using a temporary upgrade cache file named “/tmp/cduYuWx7Q”. Check your output to see what your temporary cache file is named.
The file was NOT in my Cacti server’s /tmp directory so I searched my filesystem for the file.
find /tmp | grep cduYuWx7Q
It turns out systemd was instructing Apache to store the file under /tmp in a subfolder:
I removed (renamed) that file and refreshed the Cacti upgrade web page hoping to reset the upgrade status, but the upgrade page still displayed the same errors!
Cacti Upgrade Database Settings
It was not obvious (to me) where Cacti was storing the upgrade status but I DID find and successfully reset the upgrade status! The status was stored in several records in the Cacti “settings” database table. I updated the following records and when I refreshed the Cacti web interface, the Cacti upgrade web page was fooled into believing that I was upgrading from Cacti 1.1.34 to Cacti 1.2.2. Yay!
UPDATE `settings` SET `value` = "" WHERE `name` = "install_cache_db"; UPDATE `settings` SET `value` = "" WHERE `name` = "install_error"; UPDATE `settings` SET `value` = "1.1.34" WHERE `name` = "install_version";
After updating these 3 records and refreshing the Cacti web interface, the Cacti upgrade started over as if I had just installed Cacti 1.2.2 over Cacti 1.1.34. SUCCESS!
I was prompted to acknowledge the License, select the Theme, and confirm the upgrade again. Then the Cacti upgrade page displayed a successful upgrade message along with all of the schema upgrade steps between Cacti 1.1.34 and Cacti 1.2.2.
Cacti Time Zone Configuration
During the Cacti upgrade process, I was prompted to populate time zone data and give the Cacti MySQL user access to the table. In case you didn’t make note of this during the upgrade, here’s the setting you run to populate time zone data into the MyQL database:
mysql_tzinfo_to_sql /usr/share/zoneinfo | sudo mysql mysql
Afterwards, I gave my “cacti” user access to read the data and then reload the privilege table.
GRANT SELECT ON `mysql`.`time_zone_name` TO 'cacti'@'localhost'; FLUSH PRIVILEGES;
This cleared up the warning I was receiving regarding time zones.
Here are some other tips to help you through the upgrade process…
Cacti File Permissions
The Cacti upgrade process prompted me to adjust several file permissions. The example was misleading.
Since the Apache2 server on Debian 10 runs as user “www-data”, I ran the following commands so that the Cacti upgrade process could write to these directories:
chown -R www-data /usr/share/cacti/site/resource/snmp_queries/ chown -R www-data /usr/share/cacti/site/resource/script_server/ chown -R www-data /usr/share/cacti/site/resource/script_queries/ chown -R www-data /usr/share/cacti/site/scripts/
After the upgrade process was complete, I ran the following commands to restore the file ownership to their original “root” user permissions:
chown -R root.root /usr/share/cacti/site/resource/snmp_queries/ chown -R root.root /usr/share/cacti/site/resource/script_server/ chown -R root.root /usr/share/cacti/site/resource/script_queries/ chown -R root.root /usr/share/cacti/site/scripts/
The Cacti upgrade page only shows the second set of commands when telling you to change permissions.
Cacti PHP Configuration
The Cacti upgrade process prompted me to fine-tune a few PHP settings.
I created two new files for my custom Cacti settings for PHP CLI and Web environments:
vi /etc/php/7.3/cli/conf.d/99-cacti.ini vi /etc/php/7.3/apache2/conf.d/99-cacti.ini
Then added this content to BOTH files. Adjust as appropriate for your environment.
memory_limit = 400M max_execution_time = 60 date.timezone = UTC
Then restarted Apache web server:
Cacti MySQL Configuration
The Cacti upgrade process prompted me to fine-tune a few MySQL settings.
I created a new file for my custom Cacti settings:
Then added the following content to the file. Adjust as appropriate for your environment.
[mysqld] max_heap_table_size = 32M tmp_table_size = 32M join_buffer_size = 64M innodb_buffer_pool_size = 256M innodb_flush_log_at_timeout = 2 innodb_read_io_threads = 16 innodb_write_io_threads = 8 innodb_buffer_pool_instances = 2
Then restarted MySQL server:
Thanks for Visiting!
Hopefully this helps you get back up and running right away, so you don’t end up with 5+ hour gaps in your Cacti graphs like I did. If this helps you complete your Cacti upgrade from Debian 9 to Debian 10, can you please post a short thanks/reply below so I know this this helped someone out? 🙂