Report database schema issues here

laf · 5 April 2017 07:29

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.

arrmo · 1 February 2020 21:11

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!

laf · 4 February 2020 11:10

Run those SQL queries.

arrmo · 4 February 2020 13:41

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!

Kevin_Krumm · 4 February 2020 14:59

what are the errors?

arrmo · 4 February 2020 15:28

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!

laf · 6 February 2020 09:12

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

arrmo · 6 February 2020 12:46

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!

JustWayne · 18 February 2020 18:38

====================================
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 (LibreNMS) or the community site (Report database schema issues here - LibreNMS Community):
[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

arrmo · 24 February 2020 14:29

Hi,

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

Thoughts?

Thanks!

Gord · 3 March 2020 20:33

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?

arrmo · 8 March 2020 12:02

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!

arrmo · 18 March 2020 23:42

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!

arrmo · 3 April 2020 00:58

FYI, the list seems to be growing

	[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 ;
	 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' ;
	 ALTER TABLE `device_graphs` CHANGE `device_id` `device_id` int(10) unsigned NOT NULL ;
	  and 43 more...

Thoughts? Thanks!

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

Slight update,

I admit, likely me doing something dumb here (my usual ), but this list keeps growing … “57 more” now. I have tried to enter these commands, manually, copy and paste, no joy. Just not sure what is out of sync here.

Validate runs every night, so I keep getting poked about it … daily

Thanks!

netphil · 14 April 2020 15:52

Apparently I can’t delete posts. This was resolved by running some directory ownership suggested commands as well as github-remove and daily.sh

Hello,

Yesterday we rain the daily.sh script and apparently around that time all graphing stopped working. This installation has been running for four months without issue. I also deleted a user which had admin privileges (but was added after the server was initially set up). Today when I ran validate I get this:

[FAIL] Database: incorrect column (notifications/datetime)
[FAIL] Database: incorrect column (users/created_at)
[FAIL] We have detected that your database schema may be wrong

I can try running the suggested SQL statements but I don’t know how my schema has been wrong all this time?

Component	Version
LibreNMS	1.62-64-g212c9aad9
DB Schema	2020_03_25_165300_add_column_to_ports (162)
PHP	7.2.24-0ubuntu0.18.04.3
MySQL	10.1.44-MariaDB-0ubuntu0.18.04.1
RRDTool	1.7.0
SNMP	NET-SNMP 5.7.3

Any help would be greatly appreciated.

-Phil

arrmo · 26 April 2020 11:23

Hi,

I seem to be getting all sorts of errors with validate.php - and the list keeps growing? It says to report here, not sure how to fix it … but I have tried the recommended commands, they don’t help . Any suggestions much appreciated!

The growing list (too long to report here), Untitled - LibreNMS

Thanks!

sriccio · 29 April 2020 18:32

Hello,

So I have the same problem since we updated our mysql server to 8.0.19.

From 8.0.17 they added a deprecation message for integer with display:
“Integer display width is deprecated and will be removed in a future release.”

From 8.0.19 the integer witdh is not displayed anymore.

And i think validate.php is now confused about each integer columns as it wants to see “INT (xx)” but the server is reporting only “INT”.

for example:
accesspoint_id int unsigned NOT NULL AUTO_INCREMENT,`

was previously displayed as:
accesspoint_id int(11) unsigned NOT NULL AUTO_INCREMENT,

Therefore validate.php fails…

validate.php should take this into account or every installations with mysql 8.0.19+ will wrongly reports problems with db schema.

Kind regards

Source:
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-19.html (search for Display width specification)

Victor_Romero · 13 May 2020 00:54

Hi Guys

I’m having trouble updating and would like your help.

[librenms@az-libreweb librenms]$ ./validate.php

Component	Version
LibreNMS	1.63
DB Schema	2020_04_19_010532_eventlog_sensor_reference_cleanup (423)
PHP	7.2.24
MySQL	5.5.64-MariaDB
RRDTool	1.6.0
SNMP	NET-SNMP 5.7.2

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

[OK] Composer Version: 1.10.6
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] Database: incorrect column (route/created_at)
[FAIL] Database: incorrect column (route/updated_at)
[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 route CHANGE created_at created_at timestamp NULL ;
ALTER TABLE route CHANGE updated_at updated_at timestamp NULL ;

MariaDB [librenms]> ALTER TABLE “route” CHANGE “created_at” “created_at” timesta mp NULL ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘“ro ute” CHANGE “created_at” “created_at” timestamp NULL’ at line 1
MariaDB [librenms]> ALTER TABLE “route” CHANGE “updated_at” “updated_at” timesta mp NULL ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘“ro ute” CHANGE “updated_at” “updated_at” timestamp NULL’ at line 1
MariaDB [librenms]> select created_at from route;
ERROR 1054 (42S22): Unknown column ‘created_at’ in ‘field list’
MariaDB [librenms]> select updated_at from route;
ERROR 1054 (42S22): Unknown column ‘updated_at’ in ‘field list’
MariaDB [librenms]>

[librenms@az-libreweb librenms]$ ./daily.sh
Updating to latest release OK
Updating Composer packages OK
Updating SQL-Schema OK
Updating submodules OK
Cleaning up DB OK
Fetching notifications OK
Caching PeeringDB data OK

Max_Schmidt · 28 May 2020 16:35

Reporting another:

##############################################################################
librenms@librenms:/opt/librenms# uname -a
Linux librenms 4.15.0-101-generic #102-Ubuntu SMP Mon May 11 10:07:26 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
librenms@librenms:/opt/librenms# ./validate.php

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

Component	Version
LibreNMS	1.63-155-gb2d6540ff
DB Schema	2020_04_19_010532_eventlog_sensor_reference_cleanup (165)
PHP	7.2.24-0ubuntu0.18.04.6
Python	3.6.9
MySQL	10.1.44-MariaDB-0ubuntu0.18.04.1
RRDTool	1.7.0
SNMP	NET-SNMP 5.7.3

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

[OK] Composer Version: 1.6.3
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] Database: incorrect column (notifications/datetime)
[FAIL] Database: incorrect column (users/created_at)
[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 notifications CHANGE datetime datetime timestamp NOT NULL DEFAULT ‘1970-01-02 00:00:00’ ;
ALTER TABLE users CHANGE created_at created_at timestamp NOT NULL DEFAULT ‘1970-01-02 00:00:01’ ;

MariaDB [librenms]> ALTER TABLE notifications CHANGE datetime datetime timestamp NOT NULL DEFAULT ‘1970-01-02 00:00:00’ ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [librenms]> ALTER TABLE users CHANGE created_at created_at timestamp NOT NULL DEFAULT ‘1970-01-02 00:00:01’ ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
##############################################################################

A few other side notes about the fresh install I just did, that may help with something:

In the web interface, the notifications cannot be marked as read. Clicking on the “eye icons” to mark the message as read, the icon dims, but nothing happens.
validate.php takes a long time to run, compared to our test install. Test takes less than 10 seconds, while the new production install (this install) took 264 seconds.

Carl_Boberg · 2 June 2020 10:35

./daily.sh runs fine but validate complains
====================================
Component | Version
--------- | -------
LibreNMS | 1.64.1-2-g90d4ef7
DB Schema | 2020_04_19_010532_eventlog_sensor_reference_cleanup (164)
PHP | 7.4.6
Python | 3.6.8
MySQL | 5.5.65-MariaDB
RRDTool | 1.4.8
SNMP | NET-SNMP 5.7.2
====================================

[OK]    Composer Version: 1.10.6
[OK]    Dependencies up-to-date.
[OK]    Database connection successful
[FAIL]  Your database is out of date!
	[FIX]: 
	./lnms migrate

Running ./lnms migrate
**************************************
* Application In Production! *
**************************************
Do you really wish to run this command? (yes/no) [no]:
> yes
Migrating: 2020_04_06_001048_the_great_index_rename
In Connection.php line 669:

  SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'deleted'; check that column/key exists (SQL: ALTER TABLE access_points DROP INDEX deleted, ADD INDEX access_points_deleted_index(deleted);)  
                                                                                                                                                                                                            
In PDOStatement.php line 129:                                                                                                             
  SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'deleted'; check that column/key exists  

In PDOStatement.php line 127:
  SQLSTATE[42000]: Syntax error or access violation: 1091 Can't DROP 'deleted'; check that column/key exists