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.

Hi,

Just recently started getting this error - and it asks to have the details posted here. I re-ran daily.sh (and then validate.php), but no change. And tried those statements in SQL - no joy there either.

====================================
Component | Version
--------- | -------
LibreNMS  | 1.59-76-g398d5fe2b
DB Schema | 2019_12_28_180000_add_overwrite_ip_to_devices (156)
PHP       | 7.3.11-0ubuntu0.19.10.2
MySQL     | 8.0.19-0ubuntu0.19.10.3
RRDTool   | 1.7.1
SNMP      | NET-SNMP 5.7.3
====================================

[OK]    Composer Version: 1.9.2
[OK]    Dependencies up-to-date.
[OK]    Database connection successful
[FAIL]  Database: incorrect column (devices/device_id)
[FAIL]  Database: incorrect column (devices/port)
[FAIL]  Database: incorrect column (devices/timeout)
[FAIL]  Database: incorrect column (devices/retries)
[FAIL]  Database: incorrect column (devices/bgpLocalAs)
[FAIL]  Database: incorrect column (devices/location_id)
[FAIL]  Database: incorrect column (devices/uptime)
[FAIL]  Database: incorrect column (devices/agent_uptime)
[FAIL]  Database: incorrect column (devices/poller_group)
[FAIL]  Database: incorrect column (devices/port_association_mode)
[FAIL]  Database: incorrect column (devices/max_depth)
[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 `devices` CHANGE `device_id` `device_id` int(10) unsigned NOT NULL auto_increment;
         ALTER TABLE `devices` CHANGE `port` `port` smallint(5) unsigned NOT NULL DEFAULT '161' ;
         ALTER TABLE `devices` CHANGE `timeout` `timeout` int(11) NULL ;
         ALTER TABLE `devices` CHANGE `retries` `retries` int(11) NULL ;
         ALTER TABLE `devices` CHANGE `bgpLocalAs` `bgpLocalAs` int(10) unsigned NULL ;
         ALTER TABLE `devices` CHANGE `location_id` `location_id` int(10) unsigned NULL ;
         ALTER TABLE `devices` CHANGE `uptime` `uptime` bigint(20) NULL ;
         ALTER TABLE `devices` CHANGE `agent_uptime` `agent_uptime` int(10) unsigned NOT NULL DEFAULT '0' ;
         ALTER TABLE `devices` CHANGE `poller_group` `poller_group` int(11) NOT NULL DEFAULT '0' ;
         ALTER TABLE `devices` CHANGE `port_association_mode` `port_association_mode` int(11) NOT NULL DEFAULT '1' ;
         ALTER TABLE `devices` CHANGE `max_depth` `max_depth` int(11) NOT NULL DEFAULT '0' ;

Thanks!

Run those SQL queries.

Sorry, I may not have been very clear above - I did try those queries. Got some warnings, but no joy … still have the same failure.

Thoughts?

Thanks!

what are the errors?

Here is one, executing the first noted mysql command,

mysql> ALTER TABLE `devices` CHANGE `device_id` `device_id` int(10) unsigned NOT NULL auto_increment;
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+------------------------------------------------------------------------------+
| Level   | Code | Message                                                                      |
+---------+------+------------------------------------------------------------------------------+
| Warning | 1681 | Integer display width is deprecated and will be removed in a future release. |
+---------+------+------------------------------------------------------------------------------+
1 row in set (0.01 sec)

And no rows affected, so it makes sense that it doesn’t fix the issue. Thoughts?

Thanks!

That is just a warning and can be ignored, post the output of validate again as that error should no longer show.

Hi,

daily.sh ran again last night, as did validate - results below, but no change.

Status ...
====================================
Component | Version
--------- | -------
LibreNMS  | 1.60-7-gc380921d4
DB Schema | 2020_02_05_093457_add_inserted_to_devices (157)
PHP       | 7.3.11-0ubuntu0.19.10.2
MySQL     | 8.0.19-0ubuntu0.19.10.3
RRDTool   | 1.7.1
SNMP      | NET-SNMP 5.7.3
====================================

[OK]    Composer Version: 1.9.3
[OK]    Dependencies up-to-date.
[OK]    Database connection successful
[FAIL]  Database: incorrect column (devices/device_id)
[FAIL]  Database: incorrect column (devices/port)
[FAIL]  Database: incorrect column (devices/timeout)
[FAIL]  Database: incorrect column (devices/retries)
[FAIL]  Database: incorrect column (devices/bgpLocalAs)
[FAIL]  Database: incorrect column (devices/location_id)
[FAIL]  Database: incorrect column (devices/uptime)
[FAIL]  Database: incorrect column (devices/agent_uptime)
[FAIL]  Database: incorrect column (devices/poller_group)
[FAIL]  Database: incorrect column (devices/port_association_mode)
[FAIL]  Database: incorrect column (devices/max_depth)
[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 `devices` CHANGE `device_id` `device_id` int(10) unsigned NOT NULL auto_increment;
	 ALTER TABLE `devices` CHANGE `port` `port` smallint(5) unsigned NOT NULL DEFAULT '161' ;
	 ALTER TABLE `devices` CHANGE `timeout` `timeout` int(11) NULL ;
	 ALTER TABLE `devices` CHANGE `retries` `retries` int(11) NULL ;
	 ALTER TABLE `devices` CHANGE `bgpLocalAs` `bgpLocalAs` int(10) unsigned NULL ;
	 ALTER TABLE `devices` CHANGE `location_id` `location_id` int(10) unsigned NULL ;
	 ALTER TABLE `devices` CHANGE `uptime` `uptime` bigint(20) NULL ;
	 ALTER TABLE `devices` CHANGE `agent_uptime` `agent_uptime` int(10) unsigned NOT NULL DEFAULT '0' ;
	 ALTER TABLE `devices` CHANGE `poller_group` `poller_group` int(11) NOT NULL DEFAULT '0' ;
	 ALTER TABLE `devices` CHANGE `port_association_mode` `port_association_mode` int(11) NOT NULL DEFAULT '1' ;
	 ALTER TABLE `devices` CHANGE `max_depth` `max_depth` int(11) NOT NULL DEFAULT '0' ;

Thanks!

====================================
Component | Version
--------- | -------
LibreNMS | 1.60-48-g6a897b5c4
DB Schema | 2020_02_10_223323_create_alert_location_map_table (159)
PHP | 7.4.2
MySQL | 8.0.19
RRDTool | 1.6.0
SNMP | NET-SNMP 5.7.3
====================================

[OK] Composer Version: 1.9.3
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] Database: incorrect column (alert_location_map/id)
[FAIL] Database: incorrect column (alert_location_map/rule_id)
[FAIL] Database: incorrect column (alert_location_map/location_id)
[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 alert_location_map CHANGE id id int(10) unsigned NOT NULL auto_increment;
ALTER TABLE alert_location_map CHANGE rule_id rule_id int(10) unsigned NOT NULL ;
ALTER TABLE alert_location_map CHANGE location_id location_id int(10) unsigned NOT NULL ;

I get the above when running ./validate.php.

When running these queries, they complete with error but the errors remain in ./validate.php output.

Thank you

Hi,

FYI, still seeing this in the latest releases - and running the recommended commands seems to show,
Query OK, 0 rows affected,

Thoughts?

Thanks!

I’m running into the following:
====================================
Component | Version
--------- | -------
LibreNMS | 1.61
DB Schema | 2020_02_10_223323_create_alert_location_map_table (159)
PHP | 7.2.24
MySQL | 5.5.64-MariaDB
RRDTool | 1.4.8
SNMP | NET-SNMP 5.7.2
====================================

[OK]    Composer Version: 1.9.3
[OK]    Dependencies up-to-date.
[OK]    Database connection successful
[WARN]  Some tables are not using the recommended InnoDB engine, this may cause you issues.
	Tables:
	 alert_group_map
	 alert_transport_groups
	 alert_transport_map
	 alert_transports
	 application_metrics
	 bill_port_counters
	 device_group_device
	 entityState
	 mefinfo
	 migrations
	 poller_cluster
	 ports_fdb
	 ports_nac
	 transport_group_transport
	 wireless_sensors
[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;

Running the database scripts throws no errors, but running the validate script again afterward comes back with the same error.

MariaDB [librenms]> 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;
Query OK, 247 rows affected (0.00 sec)
Records: 247  Duplicates: 0  Warnings: 0

MariaDB [librenms]> ALTER TABLE `device_group_device` ADD CONSTRAINT `device_group_device_device_id_foreign` FOREIGN KEY (`device_id`)  REFERENCES `devices` (`device_id`) ON DELETE CASCADE;
Query OK, 247 rows affected (0.00 sec)
Records: 247  Duplicates: 0  Warnings: 0

MariaDB [librenms]> ALTER TABLE `wireless_sensors` ADD CONSTRAINT `wireless_sensors_device_id_foreign` FOREIGN KEY (`device_id`)  REFERENCES `devices` (`device_id`) ON DELETE CASCADE;
Query OK, 131 rows affected (0.00 sec)
Records: 131  Duplicates: 0  Warnings: 0

MariaDB [librenms]> 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;
Query OK, 247 rows affected (0.00 sec)
Records: 247  Duplicates: 0  Warnings: 0

MariaDB [librenms]> \q 

Any suggestions on how to proceed?

Sorry, just checking back in on this one, as I get the same warning each day (i.e. daily.sh). Any suggestions on how to correct this? I have tried the noted SQL commands, but no joy.

Thanks!

Actually, digging a bit deeper, I’m not sure that this is in the database. I say that because (an example, checked a couple of these),

Run the following SQL statements to fix.
ALTER TABLE `alert_location_map` CHANGE `id` `id` int(10) unsigned NOT NULL auto_increment;

But I checked,

mysql> describe alert_location_map;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int unsigned | NO   | PRI | NULL    | auto_increment |
| rule_id     | int unsigned | NO   | MUL | NULL    |                |
| location_id | int unsigned | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

So the field id is actually correct, no? Perhaps this is in the schema or script somewhere?

Thanks!