Issue to import DB from 0.8.8f to 1.2.21

[jackouille]

Hi,

I know it's a big jump but it was a forgotten server...

[root@cacti1 cli]# php upgrade_database.php
NOTE: Repairing Tables for Local Database
Upgrading from v0.8.8f
Upgrading from v0.8.8f (DB 0.8.8f) to v1.0.0
++++++++++++++++++++++++++++++++++++-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++-++++++++++++++++++++++++++--x-+++++--+++++++++++++-+++++-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++----++-++++++++++++++++++++++++++++--+++-++++-+++++++++++++++++-+-+-+-++++++-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++-
################################
[Fail] - DB Exec Failed!, Error 1060: Duplicate column name 'id'
+------------------------------+
ALTER TABLE automation_devices ADD COLUMN id BIGINT unsigned auto_increment FIRST, ADD COLUMN network_id INT unsigned NOT NULL default '0' AFTER id, ADD COLUMN snmp_port int(10) unsigned NOT NULL DEFAULT '161' AFTER snmp_version, ADD COLUMN snmp_engine_id varchar(30) DEFAULT '' AFTER snmp_context, DROP PRIMARY KEY, ADD PRIMARY KEY(id), ADD UNIQUE INDEX ip(ip); ADD INDEX network_id(network_id), COMMENT='Table of Discovered Devices'
################################

How can I solve it ?

[TheWitness]

See what columns exist and which are missing and change the ALTER statement to match it.

[TheWitness]

The audit_database.php script should also help you fix this. It has a repair option.

[TheWitness]

Code: Select all

[root@vmhost3 cli]# php -q audit_database.php --help
Cacti Database Audit Utility, Version 1.2.22 (DB: 1.2.22), Copyright (C) 2004-2022 The Cacti Group

usage: audit_database.php --report | --repair [ --upgrade ]

Cacti utility for auditing and correcting your Cacti database.  This utility can
will scan your Cacti database and report any problems in the schema that it finds.

Options:
    --report  - Report on any issues found in the audit of the database
    --repair  - Repair any issues found during the audit of the database
    --upgrade - Upgrade the Cacti database before running

Developer Options:
    --create  - Initialize or Re-initialize the Audit Schema tables.
    --load    - Take a pristine Cacti install and create Audit Schema and file.
    --alters  - Print out all the alter commands vs. executing for debugging.

[jackouille]

PHP Warning: Undefined variable $1 in /var/lib/cacti/cli/audit_database.php on line 114
FATAL: Failed to find Audit Schema
Scanning Table: 'aggregate_graph_templates' - Completed
Scanning Table: 'aggregate_graph_templates_graph' - Completed
Scanning Table: 'aggregate_graph_templates_item' - Completed
Scanning Table: 'aggregate_graphs' - Completed
Scanning Table: 'aggregate_graphs_graph_item' - Completed
Scanning Table: 'aggregate_graphs_items' - Completed
Scanning Table: 'automation_devices' - Completed
Scanning Table: 'automation_graph_rule_items' - Completed
Scanning Table: 'automation_graph_rules' - Completed
Scanning Table: 'automation_ips' - Completed
Scanning Table: 'automation_match_rule_items' - Completed
Scanning Table: 'automation_networks' - Completed
Scanning Table: 'automation_processes' - Completed
Scanning Table: 'automation_snmp' - Completed
Scanning Table: 'automation_snmp_items' - Completed
Scanning Table: 'automation_templates' - Completed
Scanning Table: 'automation_tree_rule_items' - Completed
Scanning Table: 'automation_tree_rules' - Completed
Scanning Table: 'cdef' - Completed
Scanning Table: 'cdef_items' - Completed
Scanning Table: 'color_template_items' - Completed
Scanning Table: 'color_templates' - Completed
Scanning Table: 'colors' - Completed
Scanning Table: 'data_debug' - Completed
Scanning Table: 'data_input' - Completed
Scanning Table: 'data_input_data' - Completed
Scanning Table: 'data_input_fields' - Completed
Scanning Table: 'data_local' - Completed
Scanning Table: 'data_source_profiles' - Completed
Scanning Table: 'data_source_profiles_cf' - Completed
Scanning Table: 'data_source_profiles_rra' - Completed
Scanning Table: 'data_source_purge_action' - Completed
Scanning Table: 'data_source_purge_temp' - Completed
Scanning Table: 'data_source_stats_daily' - Completed
Scanning Table: 'data_source_stats_hourly' - Completed
Scanning Table: 'data_source_stats_hourly_cache' - Completed
Scanning Table: 'data_source_stats_hourly_last' - Completed
Scanning Table: 'data_source_stats_monthly' - Completed
Scanning Table: 'data_source_stats_weekly' - Completed
Scanning Table: 'data_source_stats_yearly' - Completed
Scanning Table: 'data_template' - Completed
Scanning Table: 'data_template_data' - Completed
Scanning Table: 'data_template_rrd' - Completed
Scanning Table: 'external_links' - Completed
Scanning Table: 'graph_local' - Completed
Scanning Table: 'graph_template_input' - Completed
Scanning Table: 'graph_template_input_defs' - Completed
Scanning Table: 'graph_templates' - Completed
Scanning Table: 'graph_templates_gprint' - Completed
Scanning Table: 'graph_templates_graph' - Completed
Scanning Table: 'graph_templates_item' - Completed
Scanning Table: 'graph_tree' - Completed
Scanning Table: 'graph_tree_items' - Completed
Scanning Table: 'host' - Completed
Scanning Table: 'host_graph' - Completed
Scanning Table: 'host_snmp_cache' - Completed
Scanning Table: 'host_snmp_query' - Completed
Scanning Table: 'host_template' - Completed
Scanning Table: 'host_template_graph' - Completed
Scanning Table: 'host_template_snmp_query' - Completed
Scanning Table: 'modems' - Completed
Scanning Table: 'plugin_config' - Completed
Scanning Table: 'plugin_db_changes' - Completed
Scanning Table: 'plugin_discover_hosts' - Completed
Scanning Table: 'plugin_discover_template' - Completed
Scanning Table: 'plugin_fix64bit' - Completed
Scanning Table: 'plugin_hooks' - Completed
Scanning Table: 'plugin_notification_lists' - Completed
Scanning Table: 'plugin_realms' - Completed
Scanning Table: 'plugin_rrdclean' - Completed
Scanning Table: 'plugin_rrdclean_action' - Completed
Scanning Table: 'plugin_thold_contacts' - Completed
Scanning Table: 'plugin_thold_host_failed' - Completed
Scanning Table: 'plugin_thold_log' - Completed
Scanning Table: 'plugin_thold_template_contact' - Completed
Scanning Table: 'plugin_thold_threshold_contact' - Completed
Scanning Table: 'poller' - Completed
Scanning Table: 'poller_command' - Completed
Scanning Table: 'poller_data_template_field_mappings' - Completed
Scanning Table: 'poller_item' - Completed
Scanning Table: 'poller_output' - Completed
Scanning Table: 'poller_output_boost' - Completed
Scanning Table: 'poller_output_boost_local_data_ids' - Completed
Scanning Table: 'poller_output_boost_processes' - Completed
Scanning Table: 'poller_output_realtime' - Completed
Scanning Table: 'poller_reindex' - Completed
Scanning Table: 'poller_resource_cache' - Completed
Scanning Table: 'poller_time' - Completed
Scanning Table: 'processes' - Completed
Scanning Table: 'reports' - Completed
Scanning Table: 'reports_items' - Completed
Scanning Table: 'sessions' - Completed
Scanning Table: 'settings' - Completed
Scanning Table: 'settings_graphs' - Completed
Scanning Table: 'settings_tree' - Completed
Scanning Table: 'settings_user' - Completed
Scanning Table: 'settings_user_group' - Completed
Scanning Table: 'sites' - Completed
Scanning Table: 'snmp_query' - Completed
Scanning Table: 'snmp_query_graph' - Completed
Scanning Table: 'snmp_query_graph_rrd' - Completed
Scanning Table: 'snmp_query_graph_rrd_sv' - Completed
Scanning Table: 'snmp_query_graph_sv' - Completed
Scanning Table: 'snmpagent_cache' - Completed
Scanning Table: 'snmpagent_cache_notifications' - Completed
Scanning Table: 'snmpagent_cache_textual_conventions' - Completed
Scanning Table: 'snmpagent_managers' - Completed
Scanning Table: 'snmpagent_managers_notifications' - Completed
Scanning Table: 'snmpagent_mibs' - Completed
Scanning Table: 'snmpagent_notifications_log' - Completed
Scanning Table: 'syslog' - Completed
Scanning Table: 'syslog_alert' - Completed
Scanning Table: 'syslog_facilities' - Completed
Scanning Table: 'syslog_host_facilities' - Completed
Scanning Table: 'syslog_hosts' - Completed
Scanning Table: 'syslog_incoming' - Completed
Scanning Table: 'syslog_logs' - Completed
Scanning Table: 'syslog_priorities' - Completed
Scanning Table: 'syslog_remove' - Completed
Scanning Table: 'syslog_removed' - Completed
Scanning Table: 'syslog_reports' - Completed
Scanning Table: 'syslog_statistics' - Completed
Scanning Table: 'table_columns' - Completed
Scanning Table: 'table_indexes' - Completed
Scanning Table: 'thold_data' - Completed
Scanning Table: 'thold_template' - Completed
Scanning Table: 'user_auth' - Completed
Scanning Table: 'user_auth_cache' - Completed
Scanning Table: 'user_auth_group' - Completed
Scanning Table: 'user_auth_group_members' - Completed
Scanning Table: 'user_auth_group_perms' - Completed
Scanning Table: 'user_auth_group_realm' - Completed
Scanning Table: 'user_auth_perms' - Completed
Scanning Table: 'user_auth_realm' - Completed
Scanning Table: 'user_domains' - Completed
Scanning Table: 'user_domains_ldap' - Completed
Scanning Table: 'user_log' - Completed
Scanning Table: 'vdef' - Completed
Scanning Table: 'vdef_items' - Completed
Scanning Table: 'version' - Completed
---------------------------------------------------------------------------------------------
Repair Completed! No changes performed.
[root@cacti1 cli]#

And still have the issue, here are the logs from the upgrade:
2022-07-31 19:27:23 - INSTALL: always: Installation was started at 2022-07-31 17:27:20, completed at 2022-07-31 17:27:23
2022-07-31 19:27:23 - INSTALL: always: WARNING: One or more upgrades failed to install correctly
2022-07-31 19:27:23 - INSTALL: always: Finished UPGRADE Process for v1.2.21
2022-07-31 19:27:21 - INSTALL: always: Upgrading from v0.8.8h (DB 1.2.21 (DB: 0.8.8h)) to v1.0.0
2022-07-31 19:27:21 - INSTALL: always: NOTE: Using temporary file for db cache: /tmp/cduP6Gp1z
2022-07-31 19:27:21 - INSTALL: always: Switched from to /tmp/cduP6Gp1z
2022-07-31 19:27:21 - INSTALL: always: No tables where found or selected for conversion
2022-07-31 19:27:21 - INSTALL: always: Starting UPGRADE Process for v1.2.21
2022-07-31 19:27:20 - INSTALL: always: Setting PHP Option memory_limit = -1
2022-07-31 19:27:20 - INSTALL: always: Setting PHP Option max_execution_time = 0
2022-07-31 19:27:20 - INSTALL: Checking arguments
2022-07-31 19:27:20 - INSTALL: always: Spawning background process: /usr/bin/php '/usr/share/cacti/install/background.php' 1659288440.5576

Then it automatically restart the process, looping...

[TheWitness]

Oh, good catch. Do this:

On that line, change $$upgrade to $upgrade, then try again.

[TheWitness]

I've also upstreamed the fix.

[jackouille]

Which file and line ?

[TheWitness]

audit_database.php

[jackouille]

Ok changed but still facing the issue:

Cacti Server v1.2.21 - Installation Wizard
Installing Cacti Server v1.2.21

Your Cacti Server is now installing

Refresh in ProgressLast updated: 19:46:33

3 %

2022-07-31 19:46:33 - INSTALL: always: Upgrading from v0.8.8h (DB 1.2.21 (DB: 0.8.8h)) to v1.0.0
2022-07-31 19:46:33 - INSTALL: always: NOTE: Using temporary file for db cache: /tmp/cduZjSoz1
2022-07-31 19:46:33 - INSTALL: always: Switched from to /tmp/cduZjSoz1
2022-07-31 19:46:33 - INSTALL: always: No tables where found or selected for conversion
2022-07-31 19:46:33 - INSTALL: always: Starting UPGRADE Process for v1.2.21
2022-07-31 19:46:33 - INSTALL: always: Setting PHP Option memory_limit = -1
2022-07-31 19:46:33 - INSTALL: always: Setting PHP Option max_execution_time = 0
2022-07-31 19:46:33 - INSTALL: Checking arguments
2022-07-31 19:46:32 - INSTALL: always: Spawning background process: /usr/bin/php '/usr/share/cacti/install/background.php' 1659289592.9577
2022-07-31 19:46:01 - INSTALL: always: Installation was started at 2022-07-31 17:45:58, completed at 2022-07-31 17:46:01
2022-07-31 19:46:01 - INSTALL: always: WARNING: One or more upgrades failed to install correctly
2022-07-31 19:46:01 - INSTALL: always: Finished UPGRADE Process for v1.2.21
2022-07-31 19:45:59 - INSTALL: always: Upgrading from v0.8.8h (DB 1.2.21 (DB: 0.8.8h)) to v1.0.0
2022-07-31 19:45:59 - INSTALL: always: NOTE: Using temporary file for db cache: /tmp/cdu3tTwLK
2022-07-31 19:45:59 - INSTALL: always: Switched from to /tmp/cdu3tTwLK
2022-07-31 19:45:59 - INSTALL: always: No tables where found or selected for conversion
2022-07-31 19:45:59 - INSTALL: always: Starting UPGRADE Process for v1.2.21
2022-07-31 19:45:58 - INSTALL: always: Setting PHP Option memory_limit = -1
2022-07-31 19:45:58 - INSTALL: always: Setting PHP Option max_execution_time = 0
2022-07-31 19:45:58 - INSTALL: Checking arguments
2022-07-31 19:45:58 - INSTALL: always: Spawning background process: /usr/bin/php '/usr/share/cacti/install/background.php' 1659289558.7359

[TheWitness]

Run the upgrade cli and force the version

Code: Select all

php -q upgrade_database.php --forcever=0.8.8f

[jackouille]

[root@cacti1 cli]# php -q upgrade_database.php --forcever=0.8.8f
NOTE: Repairing Tables for Local Database
Upgrading from v0.8.8f
Upgrading from v0.8.8f (DB 0.8.8h) to v1.0.0
++++++++++++++++++++++++++++++++++++-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++-++++++++++++++++++++++++++--x-+++++--+++++++++++++-+++++-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++----++-++++++++++++++++++++++++++++--+++-++++-+++++++++++++++++-+-+-+-++++++-++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++-
################################
[Fail] - DB Exec Failed!, Error 1060: Duplicate column name 'id'
+------------------------------+
ALTER TABLE automation_devices ADD COLUMN id BIGINT unsigned auto_increment FIRST, ADD COLUMN network_id INT unsigned NOT NULL default '0' AFTER id, ADD COLUMN snmp_port int(10) unsigned NOT NULL DEFAULT '161' AFTER snmp_version, ADD COLUMN snmp_engine_id varchar(30) DEFAULT '' AFTER snmp_context, DROP PRIMARY KEY, ADD PRIMARY KEY(id), ADD UNIQUE INDEX ip(ip); ADD INDEX network_id(network_id), COMMENT='Table of Discovered Devices'
################################

[TheWitness]

This is a known issue with upgrading from the Web. There are some cases where it gets stuck sadly.

[TheWitness]

Run the audit_database.php with the --report option first, see if there are any warnings. Post them.

[TheWitness]

Also,

Code: Select all

SHOW CREATE TABLE automation_devices;

In MySQL.