Closed jkw117 closed 3 years ago
The system failing is a new one because it's become a protected word where in the past it was not. The reason we alter first is because they table may exist, in a later upgrade, we check if the table was even present because the automation sub system used to be a separate plugin so may not exist anyway and is done this way by design.
I am pretty sure that if you search through past issues, the 'system' keyword restriction has actually be raised already.
I am running into this exact same issue with the failed altering of the cdef table when trying to upgrade from 0.8.8.f to 1.2.10 on Ubuntu 20.04. The mysql-server version installed by the standard Cacti 1.2.10 apt package for that distro is mysql-server-8.0.21-0ubuntu0.20.04.4, which is what was put on my system.
I dug through past issues and could find no reference to the 'system' keyword restriction.
Is there a recommended workaround for this? No matter what I try to enclose the reserved name 'system' in inside of a manual mysql session, I cannot get that cdef alter to work. I ended up manually insuring that the cdef table "system" fields are all changed from 0 to 1, by mysqldumping, hand-editing, and reimporting the cdef table.
However when I surf back to myserver.company.com/cacti I am forever redirected back to /cacti/install/install.php?data={"Step":98,"Eula":"1"} even in new incognito sessions.
Please advise what to try from here. Should I try to downgrade the Mysql-server running on my Ubuntu 20.04 server or pivot to Mariadb? Will I need to hold on for a fixed version of Cacti that avoids the now-reserved keyword of "system" as a field name?
Thanks, Kevin
I also did a manual upgrade to the latest released version of cacti 1.2.14 and still fails, but with less notes on what's wrong, it literally just takes me back to the license agreement page. (any suggestion on where I can turn up debugging/find the logs?) Also I've made the change in the mysql and it would still flag it as failed.. luckily I have a few snapshots in vmware so going back to the older version/undoing things isn't hard.
Grab the 'updates' folder from the 1.2.x branch. There was a recent change to the 1_0_0.php file for newer MariaDB's.
cd /var/www/html/cacti/install/updates/
What worked for me based on TheWitness' advice:
cd /root git clone https://github.com/Cacti/cacti.git cd cacti git checkout 1.2.x cp /root/cacti/install/upgrades/1* /usr/share/cacti/site/install/upgrades/ cp /root/cacti/install/functions.php /usr/share/cacti/site/install/
rm /usr/share/cacti/site/install/upgrades/1_2_1?.php
mysql cacti < cacti-0.8.8b.sql
cd /usr/share/cacti php -q cli/upgrade_database.php
Upgrading from v0.8.8h Upgrading from v0.8.8h (DB 0.8.8h) to v1.0.0 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++-+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++Upgrading from v1.0.0 (DB 1.0.0) to v1.0.4 -Upgrading from v1.0.4 (DB 1.0.4) to v1.0.5 ++++++++++++++++Upgrading from v1.0.5 (DB 1.0.5) to v1.1.2 ++++++++++++Upgrading from v1.1.2 (DB 1.1.2) to v1.1.4 ++++++++++-++--++--------++---++++++Upgrading from v1.1.4 (DB 1.1.4) to v1.1.6 ++++++++++-++-++++++++++-+++++Upgrading from v1.1.6 (DB 1.1.6) to v1.1.7 +Upgrading from v1.1.7 (DB 1.1.7) to v1.1.8 +Upgrading from v1.1.8 (DB 1.1.8) to v1.1.11 +++Upgrading from v1.1.11 (DB 1.1.11) to v1.1.14 +Upgrading from v1.1.14 (DB 1.1.14) to v1.1.17 +Upgrading from v1.1.17 (DB 1.1.17) to v1.1.20 ++++++++++++++++++Upgrading from v1.1.20 (DB 1.1.20) to v1.1.26 ++++++++-Upgrading from v1.1.26 (DB 1.1.26) to v1.1.28 ++++++-++Upgrading from v1.1.28 (DB 1.1.28) to v1.1.31 +++++Upgrading from v1.1.31 (DB 1.1.31) to v1.1.34 ++++-+++++++++++++++++++-+-++-+-++-+++-+++++++++-+-+++++-++-++Upgrading from v1.1.34 (DB 1.1.34) to v1.1.35 +++Upgrading from v1.1.35 (DB 1.1.35) to v1.1.36 ++Upgrading from v1.1.36 (DB 1.1.36) to v1.1.37 +Upgrading from v1.1.37 (DB 1.1.37) to v1.2.0 +++++++++++++++++++++++++++++++++++++++++++++Upgrading from v1.2.0 (DB 1.2.0) to v1.2.1 ++Upgrading from v1.2.1 (DB 1.2.1) to v1.2.2 ++Upgrading from v1.2.2 (DB 1.2.2) to v1.2.3 ++++Upgrading from v1.2.3 (DB 1.2.3) to v1.2.5 +++Upgrading from v1.2.5 (DB 1.2.5) to v1.2.7
Success! Thanks for the help! Kevin
Describe the bug
Upgrading from v0.8.8f (DB 1.2.10 (DB: 0.8.8f)) to v1.0.0 - database migrated from old server. Two SQL DB commands failing.
The following fails:
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' | [Fail]
I question two things, one why is it trying to alter the table when further down it is creating the table (on the condition that it is failing), I did try and drop the table and refresh the page it still shows the alter table as failing. I check mysql and it has recreated the table.
At one point " UPDATE cdef SET system=1 WHERE name LIKE '_%' " which fails is used to update the cdef table. the word system seems to be a protected word in mysql-server 8.0.21-0ubuntu0.20.04.4 this being said when I run the command in mysql with system in apostrophe's it is able to run the command successfully.
To Reproduce
Steps to reproduce the behavior:
Output for cdef mysql commands:
mysql> UPDATE cdef SET 'system'='1' WHERE name LIKE '_%'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''system'='1' WHERE name LIKE '_%'' at line 1 mysql> UPDATE cdef SET
system
=1 WHERE name LIKE '_%'; Query OK, 4 rows affected (0.01 sec) Rows matched: 4 Changed: 4 Warnings: 0Desktop (please complete the following information)
OS: windows 10
Browser Chrome
Version 85.0.4183.102
user_auth_group
(id
int(10) unsigned NOT NULL auto_increment,name
varchar(20) NOT NULL,description
varchar(255) NOT NULL default '',graph_settings
varchar(2) DEFAULT NULL,login_opts
tinyint(1) NOT NULL DEFAULT '1',show_tree
varchar(2) DEFAULT 'on',show_list
varchar(2) DEFAULT 'on',show_preview
varchar(2) NOT NULL DEFAULT 'on',policy_graphs
tinyint(1) unsigned NOT NULL DEFAULT '1',policy_trees
tinyint(1) unsigned NOT NULL DEFAULT '1',policy_hosts
tinyint(1) unsigned NOT NULL DEFAULT '1',policy_graph_templates
tinyint(1) unsigned NOT NULL DEFAULT '1',enabled
char(2) NOT NULL DEFAULT 'on', PRIMARY KEY (id
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Table that Contains User Groups';user_auth_group_perms
(group_id
mediumint(8) unsigned NOT NULL DEFAULT '0',item_id
mediumint(8) unsigned NOT NULL DEFAULT '0',type
tinyint(2) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (group_id
,item_id
,type
), KEYgroup_id
(group_id
,type
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Table that Contains User Group Permissions';user_auth_group_realm
(group_id
int(10) unsigned NOT NULL,realm_id
int(10) unsigned NOT NULL, PRIMARY KEY (group_id
,realm_id
), KEYgroup_id
(group_id
), KEYrealm_id
(realm_id
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Table that Contains User Group Realm Permissions';user_auth_group_members
(group_id
int(10) unsigned NOT NULL,user_id
int(10) unsigned NOT NULL, PRIMARY KEY (group_id
,user_id
), KEYgroup_id
(group_id
), KEYrealm_id
(user_id
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Table that Contains User Group Members';settings_user_group
(group_id
smallint(8) unsigned NOT NULL DEFAULT '0',name
varchar(50) NOT NULL DEFAULT '',value
varchar(2048) NOT NULL DEFAULT '', PRIMARY KEY (group_id
,name
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Stores the Default User Group Graph Settings';data_source_stats_daily
(local_data_id
mediumint(8) unsigned NOT NULL,rrd_name
varchar(19) NOT NULL,average
DOUBLE DEFAULT NULL,peak
DOUBLE DEFAULT NULL, PRIMARY KEY (local_data_id
,rrd_name
) ) ENGINE=InnoDB ROW_FORMAT=Dynamic;data_source_stats_hourly
(local_data_id
mediumint(8) unsigned NOT NULL,rrd_name
varchar(19) NOT NULL,average
DOUBLE DEFAULT NULL,peak
DOUBLE DEFAULT NULL, PRIMARY KEY (local_data_id
,rrd_name
) ) ENGINE=InnoDB ROW_FORMAT=Dynamic;data_source_stats_hourly_cache
(local_data_id
mediumint(8) unsigned NOT NULL,rrd_name
varchar(19) NOT NULL,time
timestamp NOT NULL default '0000-00-00 00:00:00',value
DOUBLE DEFAULT NULL, PRIMARY KEY (local_data_id
,time
,rrd_name
), KEYtime
USING BTREE (time
) ) ENGINE=MEMORY;data_source_stats_hourly_last
(local_data_id
mediumint(8) unsigned NOT NULL,rrd_name
varchar(19) NOT NULL,value
DOUBLE DEFAULT NULL,calculated
DOUBLE DEFAULT NULL, PRIMARY KEY (local_data_id
,rrd_name
) ) ENGINE=MEMORY;data_source_stats_hourly_last
ADDcalculated
data_source_stats_monthly
(local_data_id
mediumint(8) unsigned NOT NULL,rrd_name
varchar(19) NOT NULL,average
DOUBLE DEFAULT NULL,peak
DOUBLE DEFAULT NULL, PRIMARY KEY (local_data_id
,rrd_name
) ) ENGINE=InnoDB ROW_FORMAT=Dynamic;data_source_stats_weekly
(local_data_id
mediumint(8) unsigned NOT NULL,rrd_name
varchar(19) NOT NULL,average
DOUBLE DEFAULT NULL,peak
DOUBLE DEFAULT NULL, PRIMARY KEY (local_data_id
,rrd_name
) ) ENGINE=InnoDB ROW_FORMAT=Dynamic;data_source_stats_yearly
(local_data_id
mediumint(8) unsigned NOT NULL,rrd_name
varchar(19) NOT NULL,average
DOUBLE DEFAULT NULL,peak
DOUBLE DEFAULT NULL, PRIMARY KEY (local_data_id
,rrd_name
) ) ENGINE=InnoDB ROW_FORMAT=Dynamic;poller_output_boost
(local_data_id
mediumint(8) unsigned NOT NULL default '0',rrd_name
varchar(19) NOT NULL default '',time
timestamp NOT NULL default '0000-00-00 00:00:00',output
varchar(512) NOT NULL, PRIMARY KEY USING BTREE (local_data_id
,rrd_name
,time
) ) ENGINE=InnoDB ROW_FORMAT=Dynamic;poller_output_boost_processes
(sock_int_value
bigint(20) unsigned NOT NULL auto_increment,status
varchar(255) default NULL, PRIMARY KEY (sock_int_value
)) ENGINE=MEMORY;user_domains
(domain_id
int(10) unsigned NOT NULL auto_increment,domain_name
varchar(20) NOT NULL,type
int(10) UNSIGNED NOT NULL DEFAULT '0',enabled
char(2) NOT NULL DEFAULT 'on',defdomain
tinyint(3) NOT NULL DEFAULT '0',user_id
int(10) unsigned NOT NULL default '0', PRIMARY KEY (domain_id
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Table to Hold Login Domains';user_domains_ldap
(domain_id
int(10) unsigned NOT NULL,server
varchar(128) NOT NULL,port
int(10) unsigned NOT NULL,port_ssl
int(10) unsigned NOT NULL,proto_version
tinyint(3) unsigned NOT NULL,encryption
tinyint(3) unsigned NOT NULL,referrals
tinyint(3) unsigned NOT NULL,mode
tinyint(3) unsigned NOT NULL,dn
varchar(128) NOT NULL,group_require
char(2) NOT NULL,group_dn
varchar(128) NOT NULL,group_attrib
varchar(128) NOT NULL,group_member_type
tinyint(3) unsigned NOT NULL,search_base
varchar(128) NOT NULL,search_filter
varchar(128) NOT NULL,specific_dn
varchar(128) NOT NULL,specific_password
varchar(128) NOT NULL, PRIMARY KEY (domain_id
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Table to Hold Login Domains for LDAP';snmpagent_cache
(oid
varchar(191) NOT NULL,name
varchar(191) NOT NULL,mib
varchar(191) NOT NULL,type
varchar(255) NOT NULL DEFAULT '',otype
varchar(255) NOT NULL DEFAULT '',kind
varchar(255) NOT NULL DEFAULT '',max-access
varchar(255) NOT NULL DEFAULT 'not-accessible',value
varchar(255) NOT NULL DEFAULT '',description
varchar(5000) NOT NULL DEFAULT '', PRIMARY KEY (oid
), KEYname
(name
), KEYmib
(mib
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='SNMP MIB CACHE';snmpagent_mibs
(id
int(8) NOT NULL AUTO_INCREMENT,name
varchar(32) NOT NULL DEFAULT '',file
varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (id
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Registered MIB files';snmpagent_cache_notifications
(name
varchar(191) NOT NULL,mib
varchar(255) NOT NULL,attribute
varchar(255) NOT NULL,sequence_id
smallint(6) NOT NULL, KEYname
(name
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Notifcations and related attributes';snmpagent_cache_textual_conventions
(name
varchar(191) NOT NULL,mib
varchar(191) NOT NULL,type
varchar(255) NOT NULL DEFAULT '',description
varchar(5000) NOT NULL DEFAULT '', KEYname
(name
), KEYmib
(mib
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Textual conventions';snmpagent_managers
(id
int(8) NOT NULL AUTO_INCREMENT,hostname
varchar(100) NOT NULL,description
varchar(255) NOT NULL,disabled
char(2) DEFAULT NULL,max_log_size
tinyint(1) NOT NULL,snmp_version
varchar(255) NOT NULL,snmp_community
varchar(255) NOT NULL,snmp_username
varchar(255) NOT NULL,snmp_auth_password
varchar(255) NOT NULL,snmp_auth_protocol
varchar(255) NOT NULL,snmp_priv_password
varchar(255) NOT NULL,snmp_priv_protocol
varchar(255) NOT NULL,snmp_engine_id
varchar(64) NOT NULL DEFAULT '',snmp_port
varchar(255) NOT NULL,snmp_message_type
tinyint(1) NOT NULL,notes
text, PRIMARY KEY (id
), KEYhostname
(hostname
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='snmp notification receivers';snmpagent_managers_notifications
(manager_id
int(8) NOT NULL,notification
varchar(190) NOT NULL,mib
varchar(191) NOT NULL, KEYmib
(mib
), KEYmanager_id
(manager_id
), KEYmanager_id2
(manager_id
,notification
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='snmp notifications to receivers';snmpagent_notifications_log
(id
int(12) NOT NULL AUTO_INCREMENT,time
int(24) NOT NULL,severity
tinyint(1) NOT NULL,manager_id
int(8) NOT NULL,notification
varchar(190) NOT NULL,mib
varchar(191) NOT NULL,varbinds
varchar(5000) NOT NULL, PRIMARY KEY (id
), KEYtime
(time
), KEYseverity
(severity
), KEYmanager_id
(manager_id
), KEYmanager_id2
(manager_id
,notification
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='logs snmp notifications to receivers';data_source_purge_temp
(id
integer UNSIGNED auto_increment,name_cache
varchar(255) NOT NULL default '',local_data_id
mediumint(8) unsigned NOT NULL default '0',name
varchar(128) NOT NULL default '',size
integer UNSIGNED NOT NULL default '0',last_mod
TIMESTAMP NOT NULL default '0000-00-00 00:00:00',in_cacti
tinyint NOT NULL default '0',data_template_id
mediumint(8) unsigned NOT NULL default '0', PRIMARY KEY (id
), UNIQUE KEY name (name
), KEY local_data_id (local_data_id
), KEY in_cacti (in_cacti
), KEY data_template_id (data_template_id
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='RRD Cleaner File Repository';data_source_purge_action
(id
integer UNSIGNED auto_increment,name
varchar(128) NOT NULL default '',local_data_id
mediumint(8) unsigned NOT NULL default '0',action
tinyint(2) NOT NULL default 0, PRIMARY KEY (id
), UNIQUE KEY name (name
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='RRD Cleaner File Actions';graph_tree
ADDenabled
graph_tree
ADDlocked
graph_tree
ADDlocked_date
graph_tree
ADDlast_modified
graph_tree
ADDuser_id
graph_tree
ADDmodified_by
graph_tree_items
ADDparent
graph_tree_items
ADDposition
graph_tree_items
ADD INDEX parent(parent)graph_tree_items
ADD INDEX parent(parent)user_auth_cache
user_auth_cache
(id
int(10) unsigned NOT NULL AUTO_INCREMENT,user_id
int(10) unsigned NOT NULL DEFAULT '0',hostname
varchar(64) NOT NULL DEFAULT '',last_update
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,token
varchar(191) NOT NULL DEFAULT '', PRIMARY KEY (id
), UNIQUE KEYtokenkey
(token
), KEYhostname
(hostname
), KEYuser_id
(user_id
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Caches Remember Me Details'user_auth
ADDlastchange
user_auth
ADDlastlogin
user_auth
ADDpassword_history
user_auth
ADDlocked
user_auth
ADDfailed_attempts
user_auth
ADDlastfail
graph_tree_items
DROPorder_key
user_auth_realm
VALUES (19,1);user_auth_realm
VALUES (22,1);user_auth
ADDemail_address
user_auth
ADDpassword_change
poller_output_realtime
time
), KEY poller_id(poller_id(191))) ENGINE=InnoDB ROW_FORMAT=Dynamicpoller_output_rt
reports
(id
mediumint(8) unsigned NOT NULL AUTO_INCREMENT,user_id
mediumint(8) unsigned NOT NULL DEFAULT '0',name
varchar(100) NOT NULL DEFAULT '',cformat
char(2) NOT NULL DEFAULT '',format_file
varchar(255) NOT NULL DEFAULT '',font_size
smallint(2) unsigned NOT NULL DEFAULT '0',alignment
smallint(2) unsigned NOT NULL DEFAULT '0',graph_linked
char(2) NOT NULL DEFAULT '',intrvl
smallint(2) unsigned NOT NULL DEFAULT '0',count
smallint(2) unsigned NOT NULL DEFAULT '0',offset
int(12) unsigned NOT NULL DEFAULT '0',mailtime
bigint(20) unsigned NOT NULL DEFAULT '0',subject
varchar(64) NOT NULL DEFAULT '',from_name
varchar(40) NOT NULL,from_email
text NOT NULL,email
text NOT NULL,bcc
text NOT NULL,attachment_type
smallint(2) unsigned NOT NULL DEFAULT '1',graph_height
smallint(2) unsigned NOT NULL DEFAULT '0',graph_width
smallint(2) unsigned NOT NULL DEFAULT '0',graph_columns
smallint(2) unsigned NOT NULL DEFAULT '0',thumbnails
char(2) NOT NULL DEFAULT '',lastsent
bigint(20) unsigned NOT NULL DEFAULT '0',enabled
char(2) DEFAULT '', PRIMARY KEY (id
), KEYmailtime
(mailtime
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Cacri Reporting Reports'reports_items
(id
int(10) unsigned NOT NULL AUTO_INCREMENT,report_id
int(10) unsigned NOT NULL DEFAULT '0',item_type
tinyint(1) unsigned NOT NULL DEFAULT '1',tree_id
int(10) unsigned NOT NULL DEFAULT '0',branch_id
int(10) unsigned NOT NULL DEFAULT '0',tree_cascade
char(2) NOT NULL DEFAULT '',graph_name_regexp
varchar(128) NOT NULL DEFAULT '',host_template_id
int(10) unsigned NOT NULL DEFAULT '0',host_id
int(10) unsigned NOT NULL DEFAULT '0',graph_template_id
int(10) unsigned NOT NULL DEFAULT '0',local_graph_id
int(10) unsigned NOT NULL DEFAULT '0',timespan
int(10) unsigned NOT NULL DEFAULT '0',align
tinyint(1) unsigned NOT NULL DEFAULT '1',item_text
text NOT NULL,font_size
smallint(2) unsigned NOT NULL DEFAULT '10',sequence
smallint(5) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (id
), KEYreport_id
(report_id
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Cacti Reporting Items'host
ADDsnmp_sysDescr
host
ADDsnmp_sysObjectID
host
ADDsnmp_sysUpTimeInstance
host
ADDsnmp_sysContact
host
ADDsnmp_sysName
host
ADDsnmp_sysLocation
host
ADDpolling_time
plugin_autom8_match_rule_items
(id
,rule_id
,rule_type
,sequence
,operation
,field
,operator
,pattern
) VALUES (1, 1, 1, 1, 0, 'h.description', 14, ''), (2, 1, 1, 2, 1, 'h.snmp_version', 12, '2'), (3, 1, 3, 1, 0, 'ht.name', 1, 'Linux'), (4, 2, 1, 1, 0, 'ht.name', 1, 'Linux'), (5, 2, 1, 2, 1, 'h.snmp_version', 12, '2'), (6, 2, 3, 1, 0, 'ht.name', 1, 'SNMP'), (7, 2, 3, 2, 1, 'gt.name', 1, 'Traffic')plugin_autom8_graph_rules
(id
,name
,snmp_query_id
,graph_type_id
,enabled
) VALUES (1, 'Traffic 64 bit Server', 1, 14, ''), (2, 'Traffic 64 bit Server Linux', 1, 14, ''), (3, 'Disk Space', 8, 18, '')plugin_autom8_graph_rule_items
(id
,rule_id
,sequence
,operation
,field
,operator
,pattern
) VALUES (1, 1, 1, 0, 'ifOperStatus', 7, 'Up'), (2, 1, 2, 1, 'ifIP', 16, ''), (3, 1, 3, 1, 'ifHwAddr', 16, ''), (4, 2, 1, 0, 'ifOperStatus', 7, 'Up'), (5, 2, 2, 1, 'ifIP', 16, ''), (6, 2, 3, 1, 'ifHwAddr', 16, '')plugin_autom8_tree_rules
(id
,name
,tree_id
,tree_item_id
,leaf_type
,host_grouping_type
,rra_id
,enabled
) VALUES (1, 'New Device', 1, 0, 3, 1, 0, ''), (2, 'New Graph', 1, 0, 2, 0, 1, '')plugin_autom8_tree_rule_items
(id
,rule_id
,sequence
,field
,rra_id
,sort_type
,propagate_changes
,search_pattern
,replace_pattern
) VALUES (1, 1, 1, 'ht.name', 0, 1, '', '^(.)\sLinux\s(.)$', '${1}\n${2}'), (2, 1, 2, 'h.hostname', 0, 1, '', '^(\w)\s(\w)\s(\w).$', '${1}\n${2}\n${3}'), (3, 2, 1, '0', 0, 2, 'on', 'Traffic', ''), (4, 2, 2, 'gtg.title_cache', 0, 1, '', '^(.)\s-\sTraffic -\s(.*)$', '${1}\n${2}');plugin_autom8_graph_rules
TOautomation_graph_rules
plugin_autom8_graph_rule_items
TOautomation_graph_rule_items
plugin_autom8_match_rule_items
TOautomation_match_rule_items
plugin_autom8_tree_rules
TOautomation_tree_rules
plugin_autom8_tree_rule_items
TOautomation_tree_rule_items
plugin_discover_hosts
TOautomation_devices
automation_devices
DROPhash
plugin_discover_template
TOautomation_templates
automation_templates
ADDavailability_method
automation_templates
ADDsysName
automation_templates
ADDsysOid
automation_templates
ADDsequence
automation_templates
DROPtree
automation_templates
DROPsnmp_version
automation_devices
(id
bigint(20) unsigned NOT NULL AUTO_INCREMENT,network_id
int(10) unsigned NOT NULL DEFAULT '0',hostname
varchar(100) NOT NULL DEFAULT '',ip
varchar(17) NOT NULL DEFAULT '',community
varchar(100) NOT NULL DEFAULT '',snmp_version
tinyint(1) unsigned NOT NULL DEFAULT '1',snmp_port
int(10) unsigned NOT NULL DEFAULT '161',snmp_username
varchar(50) DEFAULT NULL,snmp_password
varchar(50) DEFAULT NULL,snmp_auth_protocol
char(5) DEFAULT '',snmp_priv_passphrase
varchar(200) DEFAULT '',snmp_priv_protocol
char(6) DEFAULT '',snmp_context
varchar(64) DEFAULT '',snmp_engine_id
varchar(30) DEFAULT '',sysName
varchar(100) NOT NULL DEFAULT '',sysLocation
varchar(255) NOT NULL DEFAULT '',sysContact
varchar(255) NOT NULL DEFAULT '',sysDescr
varchar(255) NOT NULL DEFAULT '',sysUptime
int(32) NOT NULL DEFAULT '0',os
varchar(64) NOT NULL DEFAULT '',snmp
tinyint(4) NOT NULL DEFAULT '0',known
tinyint(4) NOT NULL DEFAULT '0',up
tinyint(4) NOT NULL DEFAULT '0',time
int(11) NOT NULL DEFAULT '0', PRIMARY KEY (id
), UNIQUE KEYip
(ip
), KEYhostname
(hostname
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Table of Discovered Devices'automation_ips
(ip_address
varchar(20) NOT NULL DEFAULT '',hostname
varchar(250) DEFAULT NULL,network_id
int(10) unsigned DEFAULT NULL,pid
int(10) unsigned DEFAULT NULL,status
int(10) unsigned DEFAULT NULL,thread
int(10) unsigned DEFAULT NULL, PRIMARY KEY (ip_address
), KEYpid
(pid
)) ENGINE=MEMORY COMMENT='List of discoverable ip addresses used for scanning'automation_networks
(id
int(10) unsigned NOT NULL AUTO_INCREMENT,poller_id
int(10) unsigned DEFAULT '0',name
varchar(128) NOT NULL DEFAULT '' COMMENT 'The name for this network',subnet_range
varchar(255) NOT NULL DEFAULT '' COMMENT 'Defined subnet ranges for discovery',dns_servers
varchar(128) NOT NULL DEFAULT '' COMMENT 'DNS Servers to use for name resolution',enabled
char(2) DEFAULT '',snmp_id
int(10) unsigned DEFAULT NULL,enable_netbios
char(2) DEFAULT '',add_to_cacti
char(2) DEFAULT '',total_ips
int(10) unsigned DEFAULT '0',up_hosts
int(10) unsigned NOT NULL DEFAULT '0',snmp_hosts
int(10) unsigned NOT NULL DEFAULT '0',ping_method
int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'The ping method (ICMP:TCP:UDP)',ping_port
int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'For TCP:UDP the port to ping',ping_timeout
int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'The ping timeout in seconds',ping_retries
int(10) unsigned DEFAULT '0',sched_type
int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Schedule type: manual or automatic',threads
int(10) unsigned DEFAULT '1',run_limit
int(10) unsigned NULL DEFAULT '0' COMMENT 'The maximum runtime for the discovery',start_at
varchar(20) DEFAULT NULL,next_start
timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',recur_every
int(10) unsigned DEFAULT '1',day_of_week
varchar(45) DEFAULT NULL COMMENT 'The days of week to run in crontab format',month
varchar(45) DEFAULT NULL COMMENT 'The months to run in crontab format',day_of_month
varchar(45) DEFAULT NULL COMMENT 'The days of month to run in crontab format',monthly_week
varchar(45) DEFAULT NULL,monthly_day
varchar(45) DEFAULT NULL,last_runtime
double NOT NULL DEFAULT '0' COMMENT 'The last runtime for discovery',last_started
timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'The time the discovery last started',last_status
varchar(128) NOT NULL DEFAULT '' COMMENT 'The last exit message if any',rerun_data_queries
char(2) DEFAULT NULL COMMENT 'Rerun data queries or not for existing hosts', PRIMARY KEY (id
), KEYpoller_id
(poller_id
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Stores scanning subnet definitions'automation_processes
(pid
int(8) unsigned NOT NULL,poller_id
int(10) unsigned DEFAULT '0',network_id
int(10) unsigned NOT NULL DEFAULT '0',task
varchar(20) NULL DEFAULT '',status
varchar(20) DEFAULT NULL,command
varchar(20) DEFAULT NULL,up_hosts
int(10) unsigned DEFAULT '0',snmp_hosts
int(10) unsigned DEFAULT '0',heartbeat
timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (pid
,network_id
)) ENGINE=MEMORY COMMENT='Table tracking active poller processes'automation_snmp
(id
int(10) unsigned NOT NULL AUTO_INCREMENT,name
varchar(100) NOT NULL DEFAULT '', PRIMARY KEY (id
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Group of SNMP Option Sets'automation_snmp_items
(id
int(10) unsigned NOT NULL AUTO_INCREMENT,snmp_id
int(10) unsigned NOT NULL DEFAULT '0',sequence
int(10) unsigned NOT NULL DEFAULT '0',snmp_version
varchar(100) NOT NULL DEFAULT '',snmp_readstring
varchar(100) NOT NULL,snmp_port
int(10) NOT NULL DEFAULT '161',snmp_timeout
int(10) unsigned NOT NULL DEFAULT '500',snmp_retries
tinyint(11) unsigned NOT NULL DEFAULT '3',max_oids
int(12) unsigned DEFAULT '10',snmp_username
varchar(50) DEFAULT NULL,snmp_password
varchar(50) DEFAULT NULL,snmp_auth_protocol
char(5) DEFAULT '',snmp_priv_passphrase
varchar(200) DEFAULT '',snmp_priv_protocol
char(6) DEFAULT '',snmp_context
varchar(64) DEFAULT '', PRIMARY KEY (id
,snmp_id
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Set of SNMP Options'automation_templates
(id
int(8) NOT NULL AUTO_INCREMENT,host_template
int(8) NOT NULL DEFAULT '0',availability_method
int(10) unsigned DEFAULT '2',sysDescr
varchar(255) NULL DEFAULT '',sysName
varchar(255) NULL DEFAULT '',sysOid
varchar(60) NULL DEFAULT '',sequence
int(10) unsigned DEFAULT '0', PRIMARY KEY (id
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Templates of SNMP Sys variables used for automation'colors
ADD UNIQUE INDEX hex(hex)colors
ADD UNIQUE INDEX hex(hex)colors
ADDname
colors
ADDread_only
settings_graphs
TOsettings_user
user_auth
ADDreset_perms
graph_templates_item
ADDvdef_id
graph_templates_item
ADDline_width
graph_templates_item
ADDdashes
graph_templates_item
ADDdash_offset
graph_templates_item
ADDshift
graph_templates_item
ADDtextalign
graph_templates_graph
ADDt_alt_y_grid
graph_templates_graph
ADDalt_y_grid
graph_templates_graph
ADDt_right_axis
graph_templates_graph
ADDright_axis
graph_templates_graph
ADDt_right_axis_label
graph_templates_graph
ADDright_axis_label
graph_templates_graph
ADDt_right_axis_format
graph_templates_graph
ADDright_axis_format
graph_templates_graph
ADDt_right_axis_formatter
graph_templates_graph
ADDright_axis_formatter
graph_templates_graph
ADDt_left_axis_formatter
graph_templates_graph
ADDleft_axis_formatter
graph_templates_graph
ADDt_no_gridfit
graph_templates_graph
ADDno_gridfit
graph_templates_graph
ADDt_unit_length
graph_templates_graph
ADDunit_length
graph_templates_graph
ADDt_tab_width
graph_templates_graph
ADDtab_width
graph_templates_graph
ADDt_dynamic_labels
graph_templates_graph
ADDdynamic_labels
graph_templates_graph
ADDt_force_rules_legend
graph_templates_graph
ADDforce_rules_legend
graph_templates_graph
ADDt_legend_position
graph_templates_graph
ADDlegend_position
graph_templates_graph
ADDt_legend_direction
graph_templates_graph
ADDlegend_direction
vdef
VALUES (1, 'e06ed529238448773038601afb3cf278', 'Maximum');vdef
VALUES (2, 'e4872dda82092393d6459c831a50dc3b', 'Minimum');vdef
VALUES (3, '5ce1061a46bb62f36840c80412d2e629', 'Average');vdef
VALUES (4, '06bd3cbe802da6a0745ea5ba93af554a', 'Last (Current)');vdef
VALUES (5, '631c1b9086f3979d6dcf5c7a6946f104', 'First');vdef
VALUES (6, '6b5335843630b66f858ce6b7c61fc493', 'Total: Current Data Source');vdef
VALUES (7, 'c80d12b0f030af3574da68b28826cd39', '95th Percentage: Current Data Source');vdef_items
VALUES (1, '88d33bf9271ac2bdf490cf1784a342c1', 1, 1, 4, 'CURRENT_DATA_SOURCE');vdef_items
VALUES (2, 'a307afab0c9b1779580039e3f7c4f6e5', 1, 2, 1, '1');vdef_items
VALUES (3, '0945a96068bb57c80bfbd726cf1afa02', 2, 1, 4, 'CURRENT_DATA_SOURCE');vdef_items
VALUES (4, '95a8df2eac60a89e8a8ca3ea3d019c44', 2, 2, 1, '2');vdef_items
VALUES (5, 'cc2e1c47ec0b4f02eb13708cf6dac585', 3, 1, 4, 'CURRENT_DATA_SOURCE');vdef_items
VALUES (6, 'a2fd796335b87d9ba54af6a855689507', 3, 2, 1, '3');vdef_items
VALUES (7, 'a1d7974ee6018083a2053e0d0f7cb901', 4, 1, 4, 'CURRENT_DATA_SOURCE');vdef_items
VALUES (8, '26fccba1c215439616bc1b83637ae7f3', 4, 2, 1, '5');vdef_items
VALUES (9, 'a8993b265f4c5398f4a47c44b5b37a07', 5, 1, 4, 'CURRENT_DATA_SOURCE');vdef_items
VALUES (10, '5a380d469d611719057c3695ce1e4eee', 5, 2, 1, '6');vdef_items
VALUES (11, '65cfe546b17175fad41fcca98c057feb', 6, 1, 4, 'CURRENT_DATA_SOURCE');vdef_items
VALUES (12, 'f330b5633c3517d7c62762cef091cc9e', 6, 2, 1, '7');vdef_items
VALUES (13, 'f1bf2ecf54ca0565cf39c9c3f7e5394b', 7, 1, 4, 'CURRENT_DATA_SOURCE');vdef_items
VALUES (14, '11a26f18feba3919be3af426670cba95', 7, 2, 6, '95');vdef_items
VALUES (15, 'e7ae90275bc1efada07c19ca3472d9db', 7, 3, 1, '8');data_template_data
ADDt_data_source_profile_id
data_template_data
ADDdata_source_profile_id
data_source_profiles
(id
mediumint(8) unsigned NOT NULL AUTO_INCREMENT,hash
varchar(32) NOT NULL DEFAULT '',name
varchar(255) NOT NULL DEFAULT '',step
int(10) unsigned NOT NULL DEFAULT '300',heartbeat
int(10) unsigned NOT NULL DEFAULT '600',x_files_factor
double DEFAULT '0.5',default
char(2) DEFAULT '', PRIMARY KEY (id
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Stores Data Source Profiles'data_source_profiles_cf
(data_source_profile_id
mediumint(8) unsigned NOT NULL DEFAULT '0',consolidation_function_id
smallint(5) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (data_source_profile_id
,consolidation_function_id
), KEYdata_source_profile_id
(data_source_profile_id
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Maps the Data Source Profile Consolidation Functions'data_source_profiles_rra
(id
mediumint(8) unsigned NOT NULL AUTO_INCREMENT,data_source_profile_id
mediumint(8) unsigned not null default '0',name
varchar(255) NOT NULL DEFAULT '',steps
int(10) unsigned DEFAULT '1',rows
int(10) unsigned NOT NULL DEFAULT '700', PRIMARY KEY (id
), KEYdata_source_profile_id
(data_source_profile_id
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Stores RRA Definitions for Data Source Profiles'data_source_profile_id
,name
,steps
,rows
) SELECT '4' ASdata_source_profile_id
,name
,steps
,rows
FROMrra
WHEREid
=1data_source_profile_id
,name
,steps
,rows
) SELECT '4' ASdata_source_profile_id
,name
,steps
,rows
FROMrra
WHEREid
=2data_source_profile_id
,name
,steps
,rows
) SELECT '4' ASdata_source_profile_id
,name
,steps
,rows
FROMrra
WHEREid
=3data_source_profile_id
,name
,steps
,rows
) SELECT '4' ASdata_source_profile_id
,name
,steps
,rows
FROMrra
WHEREid
=4data_source_profile_id
,name
,steps
,rows
) SELECT '5' ASdata_source_profile_id
,name
,steps
,rows
FROMrra
WHEREid
=1data_source_profile_id
,name
,steps
,rows
) SELECT '5' ASdata_source_profile_id
,name
,steps
,rows
FROMrra
WHEREid
=2data_source_profile_id
,name
,steps
,rows
) SELECT '5' ASdata_source_profile_id
,name
,steps
,rows
FROMrra
WHEREid
=3data_source_profile_id
,name
,steps
,rows
) SELECT '5' ASdata_source_profile_id
,name
,steps
,rows
FROMrra
WHEREid
=4data_source_profile_id
,name
,steps
,rows
) SELECT '6' ASdata_source_profile_id
,name
,steps
,rows
FROMrra
WHEREid
=1data_source_profile_id
,name
,steps
,rows
) SELECT '6' ASdata_source_profile_id
,name
,steps
,rows
FROMrra
WHEREid
=2data_source_profile_id
,name
,steps
,rows
) SELECT '6' ASdata_source_profile_id
,name
,steps
,rows
FROMrra
WHEREid
=3data_source_profile_id
,name
,steps
,rows
) SELECT '6' ASdata_source_profile_id
,name
,steps
,rows
FROMrra
WHEREid
=4data_source_profile_id
,name
,steps
,rows
) SELECT '6' ASdata_source_profile_id
,name
,steps
,rows
FROMrra
WHEREid
=5rra
rra_cf
data_template_data_rra
data_template_data
DROPt_rra_id
automation_tree_rule_items
DROPrra_id
automation_tree_rules
DROPrra_id
graph_tree_items
DROPrra_id
aggregate_graph_templates_graph
ADDt_alt_y_grid
aggregate_graph_templates_graph
ADDalt_y_grid
aggregate_graph_templates_graph
ADDt_right_axis
aggregate_graph_templates_graph
ADDright_axis
aggregate_graph_templates_graph
ADDt_right_axis_label
aggregate_graph_templates_graph
ADDright_axis_label
aggregate_graph_templates_graph
ADDt_right_axis_format
aggregate_graph_templates_graph
ADDright_axis_format
aggregate_graph_templates_graph
ADDt_right_axis_formatter
aggregate_graph_templates_graph
ADDright_axis_formatter
aggregate_graph_templates_graph
ADDt_left_axis_formatter
aggregate_graph_templates_graph
ADDleft_axis_formatter
aggregate_graph_templates_graph
ADDt_no_gridfit
aggregate_graph_templates_graph
ADDno_gridfit
aggregate_graph_templates_graph
ADDt_unit_length
aggregate_graph_templates_graph
ADDunit_length
aggregate_graph_templates_graph
ADDt_tab_width
aggregate_graph_templates_graph
ADDtab_width
aggregate_graph_templates_graph
ADDt_dynamic_labels
aggregate_graph_templates_graph
ADDdynamic_labels
aggregate_graph_templates_graph
ADDt_force_rules_legend
aggregate_graph_templates_graph
ADDforce_rules_legend
aggregate_graph_templates_graph
ADDt_legend_position
aggregate_graph_templates_graph
ADDlegend_position
aggregate_graph_templates_graph
ADDt_legend_direction
aggregate_graph_templates_graph
ADDlegend_direction
cdef
ADDsystem
data_local
ADD INDEX data_template_id(data_template_id)data_local
ADD INDEX snmp_query_id(snmp_query_id)host
ADDpoller_id
host
ADD INDEX poller_id(poller_id)host
ADD INDEX poller_id(poller_id)external_links
(id
int(11) NOT NULL AUTO_INCREMENT,sortorder
int(11) NOT NULL DEFAULT '0',enabled
char(2) DEFAULT 'on',contentfile
varchar(255) NOT NULL DEFAULT '',title
varchar(20) NOT NULL DEFAULT '',style
varchar(10) NOT NULL DEFAULT '',extendedstyle
varchar(50) NOT NULL DEFAULT '', PRIMARY KEY (id
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Stores External Link Information'sites
(id
int(10) unsigned NOT NULL AUTO_INCREMENT,name
varchar(100) NOT NULL DEFAULT '',address1
varchar(100) DEFAULT '',address2
varchar(100) DEFAULT '',city
varchar(50) DEFAULT '',state
varchar(20) DEFAULT NULL,postal_code
varchar(20) DEFAULT '',country
varchar(30) DEFAULT '',timezone
varchar(40) DEFAULT '',latitude
decimal(13,10) NOT NULL DEFAULT '0.0000000000',longitude
decimal(13,10) NOT NULL DEFAULT '0.0000000000',alternate_id
varchar(30) DEFAULT '',notes
varchar(1024), PRIMARY KEY (id
), KEYname
(name
), KEYcity
(city
), KEYstate
(state
), KEYpostal_code
(postal_code
), KEYcountry
(country
), KEYalternate_id
(alternate_id
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Contains information about customer sites';host
ADDsite_id
host
ADD INDEX site_id(site_id)host
ADD INDEX site_id(site_id)poller
poller
(id
smallint(5) unsigned NOT NULL AUTO_INCREMENT,disabled
char(2) DEFAULT '',name
varchar(30) DEFAULT NULL,notes
varchar(1024) DEFAULT '',status
int(10) unsigned NOT NULL DEFAULT '0',hostname
varchar(250) NOT NULL DEFAULT '',dbdefault
varchar(20) NOT NULL DEFAULT 'cacti',dbhost
varchar(64) NOT NULL DEFAULT '',dbuser
varchar(20) NOT NULL DEFAULT '',dbpass
varchar(64) NOT NULL DEFAULT '',dbport
int(10) unsigned DEFAULT '3306',dbssl
char(3) DEFAULT '',total_time
double DEFAULT '0',snmp
mediumint(8) unsigned DEFAULT '0',script
mediumint(8) unsigned DEFAULT '0',server
mediumint(8) unsigned DEFAULT '0',last_update
timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',last_status
timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (id
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Pollers for Cacti'host
ADDsnmp_engine_id
poller_item
ADDsnmp_engine_id
automation_snmp_items
ADDsnmp_engine_id
host
ADDlast_updated
host
ADD INDEX last_updated(last_updated)host
ADD INDEX last_updated(last_updated)host_snmp_cache
ADDlast_updated
host_snmp_cache
ADD INDEX last_updated(last_updated)host_snmp_cache
ADD INDEX last_updated(last_updated)poller_item
ADDlast_updated
poller_item
ADD INDEX last_updated(last_updated)poller_item
ADD INDEX last_updated(last_updated)poller_command
ADDlast_updated
poller_command
ADD INDEX last_updated(last_updated)poller_command
ADD INDEX last_updated(last_updated)graph_local
ADDsnmp_query_graph_id
graph_local
ADD INDEX snmp_query_graph_id(snmp_query_graph_id)graph_local
ADD INDEX snmp_query_graph_id(snmp_query_graph_id)graph_tree
ADDsequence
poller_data_template_field_mappings
(data_template_id
int(10) unsigned NOT NULL DEFAULT '0',data_name
varchar(40) NOT NULL DEFAULT '',data_source_names
varchar(125) NOT NULL DEFAULT '',last_updated
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (data_template_id
,data_name
,data_source_names
)) ENGINE=InnoDB ROW_FORMAT=Dynamic COMMENT='Tracks mapping of Data Templates to their Data Source Names'data_local
ADD INDEX snmp_index(snmp_index(191))data_local
ADD INDEX snmp_index(snmp_index(191))graph_local
ADD INDEX snmp_index(snmp_index(191))graph_local
ADD INDEX snmp_index(snmp_index(191))