Database schema may be wrong

Florian_Gellrich · 21 January 2019 12:12

Since running last daily.sh I get following error after running validate.sh:

[librenms@fh-vm-noc05 ~]$ /opt/librenms/daily.sh
Updating to latest codebase OK
Updating Composer packages OK
Updating SQL-Schema OK
Updating submodules OK
Cleaning up DB OK
Fetching notifications OK
Caching PeeringDB data OK
[librenms@fh-vm-noc05 ~]$ /opt/librenms/validate.php

Component	Version
LibreNMS	1.47-103-gea63321
DB Schema	1000
PHP	7.2.14
MySQL	5.5.62
RRDTool	1.4.7
SNMP	NET-SNMP 5.5

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

[OK] Composer Version: 1.8.0
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] Database: missing constraint (device_group_device/device_group_device_device_group_id_foreign)
[FAIL] Database: missing constraint (device_group_device/device_group_device_device_id_foreign)
[FAIL] Database: missing constraint (wireless_sensors/wireless_sensors_device_id_foreign)
[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_group_device ADD CONSTRAINT device_group_device_device_group_id_foreign FOREIGN KEY (device_group_id) REFERENCES device_groups (id) ON DELETE CASCADE;
ALTER TABLE device_group_device ADD CONSTRAINT device_group_device_device_id_foreign FOREIGN KEY (device_id) REFERENCES devices (device_id) ON DELETE CASCADE;
ALTER TABLE wireless_sensors ADD CONSTRAINT wireless_sensors_device_id_foreign FOREIGN KEY (device_id) REFERENCES devices (device_id) ON DELETE CASCADE;

But running the adviced SQL statements doesn’t solve the problem. it stays the same …
Any idea?

murrant · 21 January 2019 12:52

Possibly should be some statements to drop the existing indexes…

murrant · 21 January 2019 12:53

You could have data that violates the constraint too. You need to post the sql error.

Florian_Gellrich · 21 January 2019 13:01

Is the mysql installation to old? Or is there another problem?
Can I reimport the last sql schmea (1000.sql)?

190121 13:57:15 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
190121 13:57:15 [ERROR] Native table ‘performance_schema’.‘events_waits_current’ has the wrong structure
190121 13:57:15 [ERROR] Native table ‘performance_schema’.‘events_waits_history’ has the wrong structure
190121 13:57:15 [ERROR] Native table ‘performance_schema’.‘events_waits_history_long’ has the wrong structure
190121 13:57:15 [ERROR] Native table ‘performance_schema’.‘setup_consumers’ has the wrong structure
190121 13:57:15 [ERROR] Native table ‘performance_schema’.‘setup_instruments’ has the wrong structure
190121 13:57:15 [ERROR] Native table ‘performance_schema’.‘setup_timers’ has the wrong structure
190121 13:57:15 [ERROR] Native table ‘performance_schema’.‘performance_timers’ has the wrong structure
190121 13:57:15 [ERROR] Native table ‘performance_schema’.‘threads’ has the wrong structure
190121 13:57:15 [ERROR] Native table ‘performance_schema’.‘events_waits_summary_by_thread_by_event_name’ has the wrong structure
190121 13:57:15 [ERROR] Native table ‘performance_schema’.‘events_waits_summary_by_instance’ has the wrong structure
190121 13:57:15 [ERROR] Native table ‘performance_schema’.‘events_waits_summary_global_by_event_name’ has the wrong structure
190121 13:57:15 [ERROR] Native table ‘performance_schema’.‘file_summary_by_event_name’ has the wrong structure
190121 13:57:15 [ERROR] Native table ‘performance_schema’.‘file_summary_by_instance’ has the wrong structure
190121 13:57:15 [ERROR] Native table ‘performance_schema’.‘mutex_instances’ has the wrong structure
190121 13:57:15 [ERROR] Native table ‘performance_schema’.‘rwlock_instances’ has the wrong structure
190121 13:57:15 [ERROR] Native table ‘performance_schema’.‘cond_instances’ has the wrong structure
190121 13:57:15 [ERROR] Native table ‘performance_schema’.‘file_instances’ has the wrong structure

Florian_Gellrich · 23 January 2019 07:29

DB Schema changed with last daily update, but the error stays the same.
SQL statements didn’t fix the problem:

[root@fh-vm-noc05 librenms]$ ./validate.php ====================================

Component	Version
LibreNMS	1.47-101-gd6b1024
DB Schema	2019_01_16_195644_add_vrf_id_and_bgpLocalAs (131)
PHP	7.2.14
MySQL	5.5.62
RRDTool	1.4.7
SNMP	NET-SNMP 5.5

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

[OK] Composer Version: 1.8.0
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] Database: missing constraint (device_group_device/device_group_device_device_group_id_foreign)
[FAIL] Database: missing constraint (device_group_device/device_group_device_device_id_foreign)
[FAIL] Database: missing constraint (wireless_sensors/wireless_sensors_device_id_foreign)
[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_group_device ADD CONSTRAINT device_group_device_device_group_id_foreign FOREIGN KEY (device_group_id) REFERENCES device_groups (id) ON DELETE CASCADE;
ALTER TABLE device_group_device ADD CONSTRAINT device_group_device_device_id_foreign FOREIGN KEY (device_id) REFERENCES devices (device_id) ON DELETE CASCADE;
ALTER TABLE wireless_sensors ADD CONSTRAINT wireless_sensors_device_id_foreign FOREIGN KEY (device_id) REFERENCES devices (device_id) ON DELETE CASCADE;

murrant · 24 January 2019 06:21

You should run mysql_upgrade

Run show create table device_group_device; for each of these tables and post the output.

Florian_Gellrich · 29 January 2019 07:22

I did the mysql_upgrade, seems to run fine.
This is the output of the two affected tables from the ./validate.php script:

mysql> show create table device_group_device;
±--------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±--------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| device_group_device | CREATE TABLE device_group_device (
device_group_id int(10) unsigned NOT NULL,
device_id int(10) unsigned NOT NULL,
PRIMARY KEY (device_group_id,device_id),
KEY device_group_device_device_group_id_index (device_group_id),
KEY device_group_device_device_id_index (device_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
±--------------------±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table wireless_sensors;
±-----------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±-----------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| wireless_sensors | CREATE TABLE wireless_sensors (
sensor_id int(10) unsigned NOT NULL AUTO_INCREMENT,
sensor_deleted tinyint(1) NOT NULL DEFAULT ‘0’,
sensor_class varchar(64) COLLATE utf8_unicode_ci NOT NULL,
device_id int(10) unsigned NOT NULL DEFAULT ‘0’,
sensor_index varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
sensor_type varchar(255) COLLATE utf8_unicode_ci NOT NULL,
sensor_descr varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
sensor_divisor int(11) NOT NULL DEFAULT ‘1’,
sensor_multiplier int(11) NOT NULL DEFAULT ‘1’,
sensor_aggregator varchar(16) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘sum’,
sensor_current double DEFAULT NULL,
sensor_prev double DEFAULT NULL,
sensor_limit double DEFAULT NULL,
sensor_limit_warn double DEFAULT NULL,
sensor_limit_low double DEFAULT NULL,
sensor_limit_low_warn double DEFAULT NULL,
sensor_alert tinyint(1) NOT NULL DEFAULT ‘1’,
sensor_custom enum(‘No’,‘Yes’) COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘No’,
entPhysicalIndex varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
entPhysicalIndex_measured varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
lastupdate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
sensor_oids text COLLATE utf8_unicode_ci NOT NULL,
access_point_id int(10) unsigned DEFAULT NULL,
PRIMARY KEY (sensor_id),
KEY sensor_class (sensor_class),
KEY sensor_host (device_id),
KEY sensor_type (sensor_type)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
±-----------------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Florian_Gellrich · 1 February 2019 08:53

Hey Murrant,
do you have any suggestion for my DB Problem?
I made fresh install on Ubunut and migrated my data. But the error stays. So It’s definitly something which is going wrong in the DB. Is there a way to reimport SQL schema? Which ist the last one?
Or do you have any other idea? THX for helping

murrant · 1 February 2019 13:30

Possibly you have some data in your database and blocking the foreign keys.

Florian_Gellrich · 6 February 2019 17:27

Problem solved:
mysql was running in “myisam” mode. This doesn’t accept constraints.
Switching / migrating to innodb mode solved the problem

murrant · 6 February 2019 17:39

We could add a check for this in validate. How can you detect what is being used?

Florian_Gellrich · 8 February 2019 08:33

Check for Engine=MyISAM and replace this in all affected tables with Engine=InnoDB

dump affected DB
sed -e ‘s/MyISAM/InnoDB/’ < librenms_dump_origin_myisam.sql > librenms_dump_adapted_innodb.sql
import adapted DB

more info:
–> https://dev.mysql.com/doc/refman/8.0/en/converting-tables-to-innodb.html

Example:

origin:

DROP TABLE IF EXISTS device_group_device;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE device_group_device (
device_group_id int(10) unsigned NOT NULL,
device_id int(10) unsigned NOT NULL,
PRIMARY KEY (device_group_id,device_id),
KEY device_group_device_device_group_id_index (device_group_id),
KEY device_group_device_device_id_index (device_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/!40101 SET character_set_client = @saved_cs_client */;

adapted:

DROP TABLE IF EXISTS device_group_device;
/*!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE device_group_device (
device_group_id int(10) unsigned NOT NULL,
device_id int(10) unsigned NOT NULL,
PRIMARY KEY (device_group_id,device_id),
KEY device_group_device_device_group_id_index (device_group_id),
KEY device_group_device_device_id_index (device_id),
CONSTRAINT device_group_device_device_group_id_foreign FOREIGN KEY (device_group_id) REFERENCES device_groups (id) ON DELETE CASCADE,
CONSTRAINT device_group_device_device_id_foreign FOREIGN KEY (device_id) REFERENCES devices (device_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/!40101 SET character_set_client = @saved_cs_client */;

Database schema may be wrong

[librenms@fh-vm-noc05 ~]$ /opt/librenms/daily.sh Updating to latest codebase OK Updating Composer packages OK Updating SQL-Schema OK Updating submodules OK Cleaning up DB OK Fetching notifications OK Caching PeeringDB data OK [librenms@fh-vm-noc05 ~]$ /opt/librenms/validate.php

Example:

origin:

adapted:

[librenms@fh-vm-noc05 ~]$ /opt/librenms/daily.sh
Updating to latest codebase OK
Updating Composer packages OK
Updating SQL-Schema OK
Updating submodules OK
Cleaning up DB OK
Fetching notifications OK
Caching PeeringDB data OK
[librenms@fh-vm-noc05 ~]$ /opt/librenms/validate.php