database_upgrade.php failed with host_snmp_cache with truncated varchar(191)

[nb1dev]

Hello,

I'm trying to upgrade an old database from cacti 0.8.8h to the last version 1.2.25 (from the 1.2.x branch from git) on a new server (with MariaDB 10.5 and PHP 7.4).

I have followed theses steps from https://docs.cacti.net/Upgrading-Cacti.md :
- backup old database and files from the old server
- copy files on the new server and extract 1.2.x zip branch over the old files
- changes permissions
- import backup.sql on the new server (having changed cacti install path with sed before import)
- run the upgrade wizard

It worked fine for the first upgrade but loop to the "Upgrade from v1.0.4 to v1.0.5".
So I tried to execute the upgrade from cli :

Code: Select all

# php -q cli/upgrade_database.php --forcever=1.0.4
NOTE: Repairing Tables for Local Database
Upgrading from v1.0.4
Upgrading from v1.0.4 (DB 1.0.4) to v1.0.5
x++++++++++++
################################
[Fail]       -   DB Exec Failed!, Error 1062: Duplicate entry '105-19-redactedredactedredacted-32.47.48.49.59.60..................' for key 'PRIMARY'
+------------------------------+
ALTER TABLE host_snmp_cache MODIFY COLUMN snmp_index varchar(191) NOT NULL default ""
################################

Then I tried the SQL command to see the result from MariaDB :
ERROR 1265 (01000): Data truncated for column 'snmp_index' at row 7014

From my understanding the script is trying to reduce the column snmp_index from varchar(255) to varchar(191) to be compliant with some standard ?
But in my case, I have a lot of value that exceed this maximum, so it created duplicate entry on this colomn ?

Code: Select all

MariaDB [cacti]> select * from host_snmp_cache where CHAR_LENGTH(snmp_index) >191;
3240 row in set (0,328 sec)

What can I do to be able to continue the database upgrade process ?

Thank you for your help

[Osiris]

Need more info honestly.. Show the create table output for this table. What is MySQL/MariaDB Version.

[nb1dev]

Hi Osiris,

I'm using MariaDB 10.5.19 and here the result of the create table :

Code: Select all

MariaDB [cacti]> show create table host_snmp_cache;
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table           | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| host_snmp_cache | CREATE TABLE `host_snmp_cache` (
  `host_id` mediumint(8) unsigned NOT NULL DEFAULT 0,
  `snmp_query_id` mediumint(8) unsigned NOT NULL DEFAULT 0,
  `field_name` varchar(50) NOT NULL DEFAULT '',
  `field_value` varchar(512) DEFAULT NULL,
  `snmp_index` varchar(255) NOT NULL DEFAULT '',
  `oid` mediumtext NOT NULL,
  `present` tinyint(4) NOT NULL DEFAULT 1,
  `last_updated` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`host_id`,`snmp_query_id`,`field_name`,`snmp_index`),
  KEY `host_id` (`host_id`,`field_name`),
  KEY `snmp_index` (`snmp_index`),
  KEY `field_name` (`field_name`),
  KEY `field_value` (`field_value`),
  KEY `snmp_query_id` (`snmp_query_id`),
  KEY `present` (`present`),
  KEY `last_updated` (`last_updated`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,000 sec)

[Osiris]

The table is in a good place. I would check to see if innodb_large_prefix is set and if not, set it and restart MariaDB.

Then upgrade MariaDB as there is a major bug in your release. Lastly, since the table is already correct, goto install/updates/ and edit the update file and comment out that line. Then rerun the upgrade script.