Report database schema issues here

We have released an update to validate.php which will tell you if your dbSchema is wrong - we expect most people will be hit by this. Validate will provide you the queries to run to fix the issue but we’d like people to report those recommendations here first / as well so we can be sure the code is 100%.

Just as an FYI, your posts will be deleted once we’ve used the information and you’ve confirmed everything is ok - please don’t repost unless you have further issues.

Hello,

./validate.php reports:

====================================

Component Version
LibreNMS 1.52-51-gc2b09b8
DB Schema 2019_05_30_225937_device_groups_rewrite (135)
PHP 7.1.29
MySQL 5.5.60-MariaDB
RRDTool 1.4.8
SNMP NET-SNMP 5.7.2

====================================

[OK] Composer Version: 1.8.6
[OK] Dependencies up-to-date.
[OK] Database connection successful
[WARN] Your database schema has extra migrations (2019_05_30_225937_device_groups_rewrite). If you just switched to the stable release from the daily release, your database is in between releases and this will be resolved with the next release.
[FAIL] Database: incorrect column (device_groups/desc)
[FAIL] Database: missing column (device_groups/params)
[FAIL] Database: extra column (device_groups/type)
[FAIL] Database: extra column (device_groups/rules)
[FAIL] We have detected that your database schema may be wrong, please report the following to us on Discord (https://t.libren.ms/discord) or the community site (https://t.libren.ms/5gscd):
[FIX]:
Run the following SQL statements to fix.
SQL Statements:
ALTER TABLE device_groups CHANGE desc desc varchar(255) NOT NULL DEFAULT ‘’ ;
ALTER TABLE device_groups ADD params text NULL AFTER pattern;
ALTER TABLE device_groups DROP type;
ALTER TABLE device_groups DROP rules;

This happened after I ran ./daily.sh manually today (because I changed the purge-value for syslog).

Prior to daily.sh I already ran a validate.php which reported no issues:

====================================

Component Version
LibreNMS 1.52-50-gcfc51d5
DB Schema 2019_05_30_225937_device_groups_rewrite (135)
PHP 7.1.29
MySQL 5.5.60-MariaDB
RRDTool 1.4.8
SNMP NET-SNMP 5.7.2

====================================

[OK] Composer Version: 1.8.6
[OK] Dependencies up-to-date.
[OK] Database connection successful
[OK] Database schema correct

Do I have to run the SQL statements to fix the table?

Thank you!
-awaum

Hello , I have the save error . Do i have to run the Sql Statements ?

Thank you

[[email protected] librenms]# ./validate.php

Component Version
LibreNMS 1.52-53-gd8931e1
DB Schema 2019_05_30_225937_device_groups_rewrite (135)
PHP 7.2.17
MySQL 5.5.60-MariaDB
RRDTool 1.4.8
SNMP NET-SNMP 5.7.2

====================================

[OK] Composer Version: 1.8.6
[OK] Dependencies up-to-date.
[OK] Database connection successful
[WARN] Your database schema has extra migrations (2019_05_30_225937_device_groups_rewrite). If you just switched to the stable release from the daily release, your database is in between releases and this will be resolved with the next release.
[FAIL] Database: incorrect column (device_groups/desc)
[FAIL] Database: missing column (device_groups/params)
[FAIL] Database: extra column (device_groups/type)
[FAIL] Database: extra column (device_groups/rules)
[FAIL] We have detected that your database schema may be wrong, please report the following to us on Discord (https://t.libren.ms/discord) or the community site (https://t.libren.ms/5gscd):
[FIX]:
Run the following SQL statements to fix.
SQL Statements:
ALTER TABLE device_groups CHANGE desc desc varchar(255) NOT NULL DEFAULT ‘’ ;
ALTER TABLE device_groups ADD params text NULL AFTER pattern;
ALTER TABLE device_groups DROP type;
ALTER TABLE device_groups DROP rules;

====================================

Component Version
LibreNMS 1.53
DB Schema 2019_05_30_225937_device_groups_rewrite (135)
PHP 7.2.19-0ubuntu0.18.04.1
MySQL 10.1.40-MariaDB-0ubuntu0.18.04.1
RRDTool 1.7.0
SNMP NET-SNMP 5.7.3

====================================

[OK] Composer Version: 1.8.6
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] Database: incorrect column (device_groups/desc)
[FAIL] Database: missing column (device_groups/type)
[FAIL] Database: missing column (device_groups/rules)
[FAIL] Database: extra column (device_groups/params)
[FAIL] Database: missing table (mpls_lsps)
[FAIL] Database: missing table (mpls_lsp_paths)
[FAIL] We have detected that your database schema may be wrong, please report the following to us on Discord (https://t.libren.ms/discord) or the community site (https://t.libren.ms/5gscd):
[FIX]:
Run the following SQL statements to fix.
SQL Statements:
ALTER TABLE device_groups CHANGE desc desc varchar(255) NULL DEFAULT ‘’ ;
ALTER TABLE device_groups ADD type varchar(16) NOT NULL DEFAULT ‘dynamic’ AFTER desc;
ALTER TABLE device_groups ADD rules text NULL AFTER type;
ALTER TABLE device_groups DROP params;
CREATE TABLE mpls_lsps (lsp_id int(10) unsigned NOT NULL auto_increment, vrf_oid int(10) unsigned NOT NULL , lsp_oid int(10) unsigned NOT NULL , device_id int(10) unsigned NOT NULL , mplsLspRowStatus enum(‘active’,‘notInService’,‘notReady’,‘createAndGo’,‘createAndWait’,‘destroy’) NOT NULL , mplsLspLastChange bigint(20) NULL , mplsLspName varchar(64) NOT NULL , mplsLspAdminState enum(‘noop’,‘inService’,‘outOfService’) NOT NULL , mplsLspOperState enum(‘unknown’,‘inService’,‘outOfService’,‘transition’) NOT NULL , mplsLspFromAddr varchar(32) NOT NULL , mplsLspToAddr varchar(32) NOT NULL , mplsLspType enum(‘unknown’,‘dynamic’,‘static’,‘bypassOnly’,‘p2mpLsp’,‘p2mpAuto’,‘mplsTp’,‘meshP2p’,‘oneHopP2p’,‘srTe’,‘meshP2pSrTe’,‘oneHopP2pSrTe’) NOT NULL , mplsLspFastReroute enum(‘true’,‘false’) NOT NULL , mplsLspAge bigint(20) NULL , mplsLspTimeUp bigint(20) NULL , mplsLspTimeDown bigint(20) NULL , mplsLspPrimaryTimeUp bigint(20) NULL , mplsLspTransitions int(10) unsigned NULL , mplsLspLastTransition bigint(20) NULL , mplsLspConfiguredPaths int(10) unsigned NULL , mplsLspStandbyPaths int(10) unsigned NULL , mplsLspOperationalPaths int(10) unsigned NULL , PRIMARY KEY (lsp_id), INDEX device_id (device_id));
CREATE TABLE mpls_lsp_paths (lsp_path_id int(10) unsigned NOT NULL auto_increment, lsp_id int(10) unsigned NOT NULL , path_oid int(10) unsigned NOT NULL , device_id int(10) unsigned NOT NULL , mplsLspPathRowStatus enum(‘active’,‘notInService’,‘notReady’,‘createAndGo’,‘createAndWait’,‘destroy’) NOT NULL , mplsLspPathLastChange bigint(20) NOT NULL , mplsLspPathType enum(‘other’,‘primary’,‘standby’,‘secondary’) NOT NULL , mplsLspPathBandwidth int(10) unsigned NOT NULL , mplsLspPathOperBandwidth int(10) unsigned NOT NULL , mplsLspPathAdminState enum(‘noop’,‘inService’,‘outOfService’) NOT NULL , mplsLspPathOperState enum(‘unknown’,‘inService’,‘outOfService’,‘transition’) NOT NULL , mplsLspPathState enum(‘unknown’,‘active’,‘inactive’) NOT NULL , mplsLspPathFailCode varchar(64) NOT NULL , mplsLspPathFailNodeAddr varchar(32) NOT NULL , mplsLspPathMetric int(10) unsigned NOT NULL , mplsLspPathOperMetric int(10) unsigned NOT NULL , mplsLspPathTimeUp bigint(20) NULL , mplsLspPathTimeDown bigint(20) NULL , mplsLspPathTransitionCount int(10) unsigned NULL , PRIMARY KEY (lsp_path_id), INDEX device_id (device_id));
[FAIL] Discovery has not completed in the last 24 hours.
[FIX]:
Check the cron job to make sure it is running and using discovery-wrapper.py

Component Version
LibreNMS 1.53.1
DB Schema 2019_07_03_132417_create_mpls_saps_table (397)
PHP 7.3.6-1+0~20190531112735.39+stretch~1.gbp6131b7
MySQL 10.1.38-MariaDB-0+deb9u1
RRDTool 1.6.0
SNMP NET-SNMP 5.7.3

====================================

[OK] Composer Version: 1.8.6
[OK] Dependencies up-to-date.
[OK] Database connection successful
[WARN] Your database schema has extra migrations (2019_06_30_190400_create_mpls_sdps_table, 2019_06_30_190401_create_mpls_sdp_binds_table, 2019_06_30_190402_create_mpls_services_table, 2019_07_03_132417_create_mpls_saps_table). If you just switched to the stable release from the daily release, your database is in between releases and this will be resolved with the next release.
[FAIL] Database: extra table (mpls_saps)
[FAIL] Database: extra table (mpls_sdps)
[FAIL] Database: extra table (mpls_sdp_binds)
[FAIL] Database: extra table (mpls_services)
[FAIL] We have detected that your database schema may be wrong, please report the following to us on Discord (https://t.libren.ms/discord) or the community site (https://t.libren.ms/5gscd):
[FIX]:
Run the following SQL statements to fix.
SQL Statements:
DROP TABLE mpls_saps;
DROP TABLE mpls_sdps;
DROP TABLE mpls_sdp_binds;
DROP TABLE mpls_services;

[email protected]:/opt/librenms# ./validate.php

Component Version
LibreNMS 1.54-8-gc6e719b9b
DB Schema 2019_07_03_132417_create_mpls_saps_table (139)
PHP 7.2.19-0ubuntu0.18.04.1
MySQL 5.7.24-log
RRDTool 1.7.0
SNMP NET-SNMP 5.7.3

====================================

[OK] Composer Version: 1.8.6
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] You have lower_case_table_names set to 1 or true in mysql config.
[FIX]:
Set lower_case_table_names=0 in your mysql config file in the [mysqld] section.
[FAIL] Time between this server and the mysql database is off
Mysql time 2019-08-01 17:49:48
PHP time 2019-08-01 19:49:48

[FAIL] Database: missing table (bgpPeers)
[FAIL] Database: missing table (bgpPeers_cbgp)
[FAIL] Database: missing table (ciscoASA)
[FAIL] Database: missing table (dbSchema)
[FAIL] Database: missing table (entityState)
[FAIL] Database: missing table (entPhysical)
[FAIL] Database: missing table (entPhysical_state)
[FAIL] Database: missing table (hrDevice)
[FAIL] Database: missing table (juniAtmVp)
[FAIL] Database: extra table (bgppeers)
[FAIL] Database: extra table (bgppeers_cbgp)
[FAIL] Database: extra table (ciscoasa)
[FAIL] Database: extra table (dbschema)
[FAIL] Database: extra table (entitystate)
[FAIL] Database: extra table (entphysical)
[FAIL] Database: extra table (entphysical_state)
[FAIL] Database: extra table (hrdevice)
[FAIL] Database: extra table (juniatmvp)
[FAIL] We have detected that your database schema may be wrong, please report the following to us on Discord (https://t.libren.ms/discord) or the community site (https://t.libren.ms/5gscd):
[FIX]:
Run the following SQL statements to fix.
SQL Statements:
CREATE TABLE bgpPeers (bgpPeer_id int(10) unsigned NOT NULL auto_increment, device_id int(10) unsigned NOT NULL , vrf_id int(10) unsigned NULL , astext varchar(255) NOT NULL , bgpPeerIdentifier text NOT NULL , bgpPeerRemoteAs bigint(20) NOT NULL , bgpPeerState text NOT NULL , bgpPeerAdminStatus text NOT NULL , bgpLocalAddr text NOT NULL , bgpPeerRemoteAddr text NOT NULL , bgpPeerDescr varchar(255) NOT NULL DEFAULT ‘’ , bgpPeerInUpdates int(11) NOT NULL , bgpPeerOutUpdates int(11) NOT NULL , bgpPeerInTotalMessages int(11) NOT NULL , bgpPeerOutTotalMessages int(11) NOT NULL , bgpPeerFsmEstablishedTime int(11) NOT NULL , bgpPeerInUpdateElapsedTime int(11) NOT NULL , context_name varchar(128) NULL , PRIMARY KEY (bgpPeer_id), INDEX device_id (device_id,context_name));
CREATE TABLE bgpPeers_cbgp (device_id int(10) unsigned NOT NULL , bgpPeerIdentifier varchar(64) NOT NULL , afi varchar(16) NOT NULL , safi varchar(16) NOT NULL , AcceptedPrefixes int(11) NOT NULL , DeniedPrefixes int(11) NOT NULL , PrefixAdminLimit int(11) NOT NULL , PrefixThreshold int(11) NOT NULL , PrefixClearThreshold int(11) NOT NULL , AdvertisedPrefixes int(11) NOT NULL , SuppressedPrefixes int(11) NOT NULL , WithdrawnPrefixes int(11) NOT NULL , AcceptedPrefixes_delta int(11) NOT NULL , AcceptedPrefixes_prev int(11) NOT NULL , DeniedPrefixes_delta int(11) NOT NULL , DeniedPrefixes_prev int(11) NOT NULL , AdvertisedPrefixes_delta int(11) NOT NULL , AdvertisedPrefixes_prev int(11) NOT NULL , SuppressedPrefixes_delta int(11) NOT NULL , SuppressedPrefixes_prev int(11) NOT NULL , WithdrawnPrefixes_delta int(11) NOT NULL , WithdrawnPrefixes_prev int(11) NOT NULL , context_name varchar(128) NULL , UNIQUE unique_index (device_id,bgpPeerIdentifier,afi,safi), INDEX device_id (device_id,bgpPeerIdentifier,context_name));
CREATE TABLE ciscoASA (ciscoASA_id int(10) unsigned NOT NULL auto_increment, device_id int(10) unsigned NOT NULL , oid varchar(255) NOT NULL , data bigint(20) NOT NULL , high_alert bigint(20) NOT NULL , low_alert bigint(20) NOT NULL , disabled tinyint(4) NOT NULL DEFAULT ‘0’ , PRIMARY KEY (ciscoASA_id), INDEX device_id (device_id));
CREATE TABLE dbSchema (version int(11) NOT NULL DEFAULT ‘0’ , PRIMARY KEY (version));
CREATE TABLE entityState (entity_state_id int(10) unsigned NOT NULL auto_increment, device_id int(10) unsigned NULL , entPhysical_id int(10) unsigned NULL , entStateLastChanged datetime NULL , entStateAdmin int(11) NULL , entStateOper int(11) NULL , entStateUsage int(11) NULL , entStateAlarm text NULL , entStateStandby int(11) NULL , PRIMARY KEY (entity_state_id), INDEX entityState_device_id_index (device_id));
CREATE TABLE entPhysical (entPhysical_id int(10) unsigned NOT NULL auto_increment, device_id int(10) unsigned NOT NULL , entPhysicalIndex int(11) NOT NULL , entPhysicalDescr text NOT NULL , entPhysicalClass text NOT NULL , entPhysicalName text NOT NULL , entPhysicalHardwareRev varchar(64) NULL , entPhysicalFirmwareRev varchar(64) NULL , entPhysicalSoftwareRev varchar(64) NULL , entPhysicalAlias varchar(32) NULL , entPhysicalAssetID varchar(32) NULL , entPhysicalIsFRU varchar(8) NULL , entPhysicalModelName text NOT NULL , entPhysicalVendorType text NULL , entPhysicalSerialNum text NOT NULL , entPhysicalContainedIn int(11) NOT NULL , entPhysicalParentRelPos int(11) NOT NULL , entPhysicalMfgName text NOT NULL , ifIndex int(11) NULL , PRIMARY KEY (entPhysical_id), INDEX device_id (device_id));
CREATE TABLE entPhysical_state (device_id int(10) unsigned NOT NULL , entPhysicalIndex varchar(64) NOT NULL , subindex varchar(64) NULL , group varchar(64) NOT NULL , key varchar(64) NOT NULL , value varchar(255) NOT NULL , INDEX device_id_index (device_id,entPhysicalIndex));
CREATE TABLE hrDevice (hrDevice_id int(10) unsigned NOT NULL auto_increment, device_id int(10) unsigned NOT NULL , hrDeviceIndex int(11) NOT NULL , hrDeviceDescr text NOT NULL , hrDeviceType text NOT NULL , hrDeviceErrors int(11) NOT NULL DEFAULT ‘0’ , hrDeviceStatus text NOT NULL , hrProcessorLoad tinyint(4) NULL , PRIMARY KEY (hrDevice_id), INDEX device_id (device_id));
CREATE TABLE juniAtmVp (juniAtmVp_id int(10) unsigned NOT NULL , port_id int(10) unsigned NOT NULL , vp_id int(10) unsigned NOT NULL , vp_descr varchar(32) NOT NULL , INDEX port_id (port_id));
DROP TABLE bgppeers;
DROP TABLE bgppeers_cbgp;
DROP TABLE ciscoasa;
DROP TABLE dbschema;
DROP TABLE entitystate;
DROP TABLE entphysical;
and 3 more…
[WARN] You have not added any devices yet.
[FIX]:
You can add a device in the webui or with ./addhost.php
[WARN] IPv6 is disabled on your server, you will not be able to add IPv6 devices.
[WARN] IPv6 is disabled on your server, you will not be able to add IPv6 devices.
[FAIL] We have found some files that are owned by a different user than librenms, this will stop you updating automatically and / or rrd files being updated causing graphs to fail.
[FIX]:
sudo chown -R librenms:librenms /media/data/opt/librenms
sudo setfacl -d -m g::rwx /media/data/opt/librenms/rrd /media/data/opt/librenms/logs /media/data/opt/librenms/bootstrap/cache/ /media/data/opt/librenms/storage/
sudo chmod -R ug=rwX /media/data/opt/librenms/rrd /media/data/opt/librenms/logs /media/data/opt/librenms/bootstrap/cache/ /media/data/opt/librenms/storage/
Files:
/media/data/opt/librenms/config.php