Missing user_auto_row_cache table

[failsafe218]

Hey everyone,

I sort of inherited this server and while going through the logs earlier I noticed a bunch of these errors:
"CMDPHP ERROR: A DB Exec Failed!, Error: Table 'cacti.user_aut_row_cace' doesn't exist"


So I checked the database and it was not kidding.

Code: Select all

mysql> SHOW TABLES;
+-------------------------------------+
| Tables_in_cacti                     |
+-------------------------------------+
| aggregate_graph_templates           |
| aggregate_graph_templates_graph     |
| aggregate_graph_templates_item      |
| aggregate_graphs                    |
| aggregate_graphs_graph_item         |
| aggregate_graphs_items              |
| automation_devices                  |
| automation_graph_rule_items         |
| automation_graph_rules              |
| automation_ips                      |
| automation_match_rule_items         |
| automation_networks                 |
| automation_processes                |
| automation_snmp                     |
| automation_snmp_items               |
| automation_templates                |
| automation_tree_rule_items          |
| automation_tree_rules               |
| cdef                                |
| cdef_items                          |
| color_template_items                |
| color_templates                     |
| colors                              |
| data_debug                          |
| data_input                          |
| data_input_data                     |
| data_input_fields                   |
| data_local                          |
| data_source_profiles                |
| data_source_profiles_cf             |
| data_source_profiles_rra            |
| data_source_purge_action            |
| data_source_purge_temp              |
| data_source_stats_daily             |
| data_source_stats_hourly            |
| data_source_stats_hourly_cache      |
| data_source_stats_hourly_last       |
| data_source_stats_monthly           |
| data_source_stats_weekly            |
| data_source_stats_yearly            |
| data_template                       |
| data_template_data                  |
| data_template_rrd                   |
| external_links                      |
| graph_local                         |
| graph_template_input                |
| graph_template_input_defs           |
| graph_templates                     |
| graph_templates_gprint              |
| graph_templates_graph               |
| graph_templates_item                |
| graph_tree                          |
| graph_tree_items                    |
| host                                |
| host_graph                          |
| host_snmp_cache                     |
| host_snmp_query                     |
| host_template                       |
| host_template_graph                 |
| host_template_snmp_query            |
| plugin_config                       |
| plugin_db_changes                   |
| plugin_hooks                        |
| plugin_intropage_dashboard          |
| plugin_intropage_panel_dashboard    |
| plugin_intropage_panel_data         |
| plugin_intropage_panel_definition   |
| plugin_intropage_trends             |
| plugin_intropage_user_auth          |
| plugin_monitor_dashboards           |
| plugin_monitor_notify_history       |
| plugin_monitor_reboot_history       |
| plugin_monitor_uptime               |
| plugin_notification_lists           |
| plugin_realms                       |
| plugin_thold_contacts               |
| plugin_thold_daemon_data            |
| plugin_thold_host_failed            |
| plugin_thold_host_template          |
| plugin_thold_log                    |
| plugin_thold_template_contact       |
| plugin_thold_threshold_contact      |
| poller                              |
| poller_command                      |
| poller_data_template_field_mappings |
| poller_item                         |
| poller_output                       |
| poller_output_boost                 |
| poller_output_boost_processes       |
| poller_output_realtime              |
| poller_reindex                      |
| poller_resource_cache               |
| poller_time                         |
| processes                           |
| reports                             |
| reports_items                       |
| sessions                            |
| settings                            |
| settings_tree                       |
| settings_user                       |
| settings_user_group                 |
| sites                               |
| snmp_query                          |
| snmp_query_graph                    |
| snmp_query_graph_rrd                |
| snmp_query_graph_rrd_sv             |
| snmp_query_graph_sv                 |
| snmpagent_cache                     |
| snmpagent_cache_notifications       |
| snmpagent_cache_textual_conventions |
| snmpagent_managers                  |
| snmpagent_managers_notifications    |
| snmpagent_mibs                      |
| snmpagent_notifications_log         |
| thold_data                          |
| thold_template                      |
| user_auth                           |
| user_auth_cache                     |
| user_auth_group                     |
| user_auth_group_members             |
| user_auth_group_perms               |
| user_auth_group_realm               |
| user_auth_perms                     |
| user_auth_realm                     |
| user_domains                        |
| user_domains_ldap                   |
| user_log                            |
| vdef                                |
| vdef_items                          |
| version                             |
+-------------------------------------+
130 rows in set (0.00 sec)

mysql>

Is there anyway that I can rerun the initial sql script "mysql -u root cacti < /var/www/html/cacti/cacti.sql" and tell it to only fix the one table or is the script smart enough to fix what is there and not overwrite my current database contents?

I see that here is a section in there for this table

Code: Select all

-- Table structure for table `user_auth_row_cache`
--

CREATE TABLE user_auth_row_cache (
  `user_id` mediumint(8) NOT NULL default '0',
  `class` varchar(20) NOT NULL default '',
  `hash` varchar(32) NOT NULL default '0',
  `total_rows` int(10) unsigned NOT NULL default '0',
  `time` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`,`class`,`hash`)
) ENGINE=InnoDB ROW_FORMAT=Dynamic;

Im running on 20.04 LTS and running version 1.2.25.

Thanks!

[macan]

cd /your/cacti/installation/cli
php upgrade_database.php
or
php upgrade_database.php --forcever=1.2.25

[failsafe218]

cd /your/cacti/installation/cli
php upgrade_database.php
or
php upgrade_database.php --forcever=1.2.25

Thank you for your reply.

I tried running both and got the following:

Code: Select all

root@switchbackup:/usr/share/cacti/cli# php upgrade_database.php --forcever=1.2.25
NOTE: Repairing Tables for Local Database
Your Cacti is already up to date (v1.2.25 vs v1.2.25)
root@switchbackup:/usr/share/cacti/cli#

I confirmed that the table is still missing and I am still getting the same errors in the web GUI.

Attached is the error I am seeing in the logs.

To preface why I started looking at the logs I was trying to modify some reports I get and when I try to add another item I get an error that site_id does not exist even though it does, if I try to modify an existing one I get the same error. Not sure if when I did an update awhile back maybe it corrupted something.
nectar -> Reporting -> report edit -> items

[macan]

Try to create table manually:
CREATE TABLE IF NOT EXISTS `user_auth_row_cache` (
`user_id` mediumint(8) NOT NULL DEFAULT 0,
`class` varchar(20) NOT NULL DEFAULT '',
`hash` varchar(32) NOT NULL DEFAULT '0',
`total_rows` int(10) unsigned NOT NULL DEFAULT 0,
`time` timestamp NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`user_id`,`class`,`hash`))
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC


Try to run second script from cli:
php audit_database.php

[failsafe218]

Try to create table manually:
CREATE TABLE IF NOT EXISTS `user_auth_row_cache` (
`user_id` mediumint(8) NOT NULL DEFAULT 0,
`class` varchar(20) NOT NULL DEFAULT '',
`hash` varchar(32) NOT NULL DEFAULT '0',
`total_rows` int(10) unsigned NOT NULL DEFAULT 0,
`time` timestamp NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`user_id`,`class`,`hash`))
ENGINE=InnoDB
ROW_FORMAT=DYNAMIC


Try to run second script from cli:
php audit_database.php

That worked! I am no longer getting those errors in the clog tab.

Just so I understand, you took the table creation section from the intial cacti.sql script and just had me create that one table manually?

Thanks again!

[macan]

it seems that you have any older database schema or wrong updated DB.

[failsafe218]

I have one more odd one.

When I try to add an item to report I get site_id column doesn't exist. I confirmed in the table the column is missing.

Code: Select all

mysql> SHOW COLUMNS FROM reports_items;
+-------------------+-------------------+------+-----+---------+----------------+
| Field             | Type              | Null | Key | Default | Extra          |
+-------------------+-------------------+------+-----+---------+----------------+
| id                | int unsigned      | NO   | PRI | NULL    | auto_increment |
| report_id         | int unsigned      | NO   | MUL | 0       |                |
| item_type         | tinyint unsigned  | NO   |     | 1       |                |
| tree_id           | int unsigned      | NO   |     | 0       |                |
| branch_id         | int unsigned      | NO   |     | 0       |                |
| tree_cascade      | char(2)           | NO   |     |         |                |
| graph_name_regexp | varchar(128)      | NO   |     |         |                |
| host_template_id  | int unsigned      | NO   |     | 0       |                |
| host_id           | int unsigned      | NO   |     | 0       |                |
| graph_template_id | int unsigned      | NO   |     | 0       |                |
| local_graph_id    | int unsigned      | NO   |     | 0       |                |
| timespan          | int unsigned      | NO   |     | 0       |                |
| align             | tinyint unsigned  | NO   |     | 1       |                |
| item_text         | mediumtext        | NO   |     | NULL    |                |
| font_size         | smallint unsigned | NO   |     | 10      |                |
| sequence          | smallint unsigned | NO   |     | 0       |                |
+-------------------+-------------------+------+-----+---------+----------------+
16 rows in set (0.01 sec)

mysql> 

Can I do something similar and modify the reports_items table and add that one column
`site_id` int(11) NOT NULL DEFAULT '-1',

[failsafe218]

I have one more odd one.

When I try to add an item to report I get site_id column doesn't exist. I confirmed in the table the column is missing.

Code: Select all

mysql> SHOW COLUMNS FROM reports_items;
+-------------------+-------------------+------+-----+---------+----------------+
| Field             | Type              | Null | Key | Default | Extra          |
+-------------------+-------------------+------+-----+---------+----------------+
| id                | int unsigned      | NO   | PRI | NULL    | auto_increment |
| report_id         | int unsigned      | NO   | MUL | 0       |                |
| item_type         | tinyint unsigned  | NO   |     | 1       |                |
| tree_id           | int unsigned      | NO   |     | 0       |                |
| branch_id         | int unsigned      | NO   |     | 0       |                |
| tree_cascade      | char(2)           | NO   |     |         |                |
| graph_name_regexp | varchar(128)      | NO   |     |         |                |
| host_template_id  | int unsigned      | NO   |     | 0       |                |
| host_id           | int unsigned      | NO   |     | 0       |                |
| graph_template_id | int unsigned      | NO   |     | 0       |                |
| local_graph_id    | int unsigned      | NO   |     | 0       |                |
| timespan          | int unsigned      | NO   |     | 0       |                |
| align             | tinyint unsigned  | NO   |     | 1       |                |
| item_text         | mediumtext        | NO   |     | NULL    |                |
| font_size         | smallint unsigned | NO   |     | 10      |                |
| sequence          | smallint unsigned | NO   |     | 0       |                |
+-------------------+-------------------+------+-----+---------+----------------+
16 rows in set (0.01 sec)

mysql> 

Can I do something similar and modify the reports_items table and add that one column
`site_id` int(11) NOT NULL DEFAULT '-1',

I ran with it and its working now.

Thanks again for all your help!