Report database schema issues here


#1

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.

The following are safe to apply if they are printed out by validate.php:

ALTER TABLE `dbSchema` CHANGE `version` `version` int(11) NOT NULL DEFAULT '0';
ALTER TABLE `dbSchema` ADD PRIMARY KEY (`version`)
ALTER TABLE users ADD remember_token varchar(100) NULL AFTER realname;
ALTER TABLE users ADD updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER remember_token;
DROP TABLE port_in_measurements;
DROP TABLE port_out_measurements;

DB problem
MariaDB error when trying to correct schema
Wrong services status
xDP network map doesn't work anymore
MySQL Error: Unknown column 'mempools.mempool_desc' in 'where clause'
Component - Roadmap
Mysql error - null in ports_stp table
#426

I was able to clear all but one. I enabled the default update schedule and then ran the daily.sh. I was able to run the validate.php and work through all the SQL errors but one, it still lingers and I can not find any info as to what I might be doing wrong or why it’s not working-

./validate.php

Component Version
LibreNMS 1.43-141-g7c45cd5
DB Schema 268
PHP 7.2.10
MySQL 5.5.60-MariaDB
RRDTool 1.4.8
SNMP NET-SNMP 5.7.2

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

[OK] Composer Version: 1.7.2
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] Database: incorrect index (users/username)
[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 users DROP INDEX username, UNIQUE username (auth_type,username);

And when I try to execute the SQL command I get the following:

MariaDB [(none)]> use librenms
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [librenms]> ALTER TABLE users DROP INDEX username, UNIQUE username (auth_type,username);
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 ‘UNIQUE username (auth_type,username)’ at line 1

Any help would be appreciated.


#427

Hi there!

We are seeing this:

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

Component Version
LibreNMS 1.43-146-g4fa1926
DB Schema 268
PHP 7.0.32-0ubuntu0.16.04.1
MySQL 5.7.23-0ubuntu0.16.04.1
RRDTool 1.5.5
SNMP NET-SNMP 5.7.3

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

[OK] Composer Version: 1.7.2
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] Database: missing index (config/uniqueindex_configname)
[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 config ADD UNIQUE uniqueindex_configname (config_name);

But when we run that we get the following:

Error
SQL query: ALTER TABLE config ADD UNIQUE uniqueindex_configname (config_name)

MySQL said: #1062 - Duplicate entry ‘alert.default_mail’ for key ‘uniqueindex_configname’

Any advice much appreciated! :slight_smile:


#428

Hi,
in reaction to the 1.44 pre-anouncement I ran the validate script, and it reported my db scheme as being wrong. Here are the suggestes SQL-Statements:

ALTER TABLE `access_points` CHANGE `channel` `channel` tinyint(4) unsigned NOT NULL DEFAULT '0' ;
ALTER TABLE `alerts` DROP `info`;
ALTER TABLE `bgpPeers` DROP `bgpPeerDescr`;
ALTER TABLE `bill_data` CHANGE `delta` `delta` bigint(11) NOT NULL ;
ALTER TABLE `bill_data` CHANGE `in_delta` `in_delta` bigint(11) NOT NULL ;
ALTER TABLE `bill_data` CHANGE `out_delta` `out_delta` bigint(11) NOT NULL ;
ALTER TABLE `component` CHANGE `id` `id` int(11) unsigned NOT NULL auto_increment;
ALTER TABLE `component` CHANGE `device_id` `device_id` int(11) unsigned NOT NULL ;
ALTER TABLE `component_prefs` CHANGE `id` `id` int(11) unsigned NOT NULL auto_increment;
ALTER TABLE `component_prefs` CHANGE `component` `component` int(11) unsigned NOT NULL ;
ALTER TABLE `component_statuslog` CHANGE `id` `id` int(11) unsigned NOT NULL auto_increment;
ALTER TABLE `component_statuslog` CHANGE `component_id` `component_id` int(11) unsigned NOT NULL ;
ALTER TABLE `dashboards` CHANGE `access` `access` int(1) NOT NULL DEFAULT '0' ;
ALTER TABLE `devices` CHANGE `device_id` `device_id` int(11) unsigned NOT NULL auto_increment;
ALTER TABLE `devices` CHANGE `status` `status` tinyint(4) NOT NULL DEFAULT '0' ;
ALTER TABLE `devices` CHANGE `ignore` `ignore` tinyint(4) NOT NULL DEFAULT '0' ;
ALTER TABLE `devices` CHANGE `last_ping_timetaken` `last_ping_timetaken` double(5,2) NULL ;
ALTER TABLE `devices_perms` ADD `access_level` int(4) NOT NULL DEFAULT '0' AFTER `device_id`;
ALTER TABLE `device_groups` CHANGE `id` `id` int(11) unsigned NOT NULL auto_increment;
ALTER TABLE `device_perf` CHANGE `id` `id` int(11) NOT NULL auto_increment;
ALTER TABLE `device_perf` CHANGE `xmt` `xmt` float NOT NULL ;
ALTER TABLE `device_perf` CHANGE `rcv` `rcv` float NOT NULL ;
ALTER TABLE `device_perf` CHANGE `loss` `loss` float NOT NULL ;
ALTER TABLE `device_perf` CHANGE `min` `min` float NOT NULL ;
ALTER TABLE `device_perf` CHANGE `max` `max` float NOT NULL ;
ALTER TABLE `device_perf` CHANGE `avg` `avg` float NOT NULL ;
ALTER TABLE `device_perf` ADD INDEX `id` (`id`);
ALTER TABLE `device_perf` DROP INDEX `PRIMARY`;
ALTER TABLE `device_relationships` CHANGE `parent_device_id` `parent_device_id` int(11) unsigned NOT NULL DEFAULT '0' ;
ALTER TABLE `device_relationships` CHANGE `child_device_id` `child_device_id` int(11) unsigned NOT NULL ;
ALTER TABLE `eventlog` CHANGE `severity` `severity` int(1) NULL DEFAULT '2' ;
ALTER TABLE `ipv4_addresses` ADD INDEX `interface_id_2` (`port_id`);
ALTER TABLE `ipv6_addresses` ADD INDEX `interface_id_2` (`port_id`);
ALTER TABLE `links` CHANGE `active` `active` tinyint(4) NOT NULL DEFAULT '1' ;
ALTER TABLE `locations` CHANGE `lat` `lat` float(10,6) NOT NULL ;
ALTER TABLE `locations` CHANGE `lng` `lng` float(10,6) NOT NULL ;
ALTER TABLE `locations` ADD INDEX `id` (`id`);
ALTER TABLE `locations` DROP INDEX `PRIMARY`;
ALTER TABLE `mefinfo` CHANGE `mefID` `mefID` int(32) NOT NULL ;
ALTER TABLE `mefinfo` CHANGE `mefMTU` `mefMTU` int(16) NOT NULL DEFAULT '1500' ;
ALTER TABLE `mempools` CHANGE `mempool_used` `mempool_used` bigint(16) NOT NULL ;
ALTER TABLE `mempools` CHANGE `mempool_free` `mempool_free` bigint(16) NOT NULL ;
ALTER TABLE `mempools` CHANGE `mempool_total` `mempool_total` bigint(16) NOT NULL ;
ALTER TABLE `mempools` CHANGE `mempool_largestfree` `mempool_largestfree` bigint(16) NULL ;
ALTER TABLE `mempools` CHANGE `mempool_lowestfree` `mempool_lowestfree` bigint(16) NULL ;
ALTER TABLE `munin_plugins` CHANGE `mplug_total` `mplug_total` binary(1) NOT NULL DEFAULT '0' ;
ALTER TABLE `munin_plugins` CHANGE `mplug_graph` `mplug_graph` binary(1) NOT NULL DEFAULT '1' ;
ALTER TABLE `netscaler_vservers` CHANGE `vsvr_port` `vsvr_port` int(8) NOT NULL ;
ALTER TABLE `pdb_ix_peers` CHANGE `remote_asn` `remote_asn` varchar(16) NOT NULL ;
ALTER TABLE `pollers` DROP INDEX `PRIMARY`, PRIMARY KEY (`poller_name`);
ALTER TABLE `pollers` ADD INDEX `id` (`id`);
ALTER TABLE `pollers` DROP INDEX `poller_name`;
ALTER TABLE `poller_cluster` DROP INDEX `PRIMARY`, PRIMARY KEY (`node_id`);
ALTER TABLE `poller_cluster` ADD UNIQUE `id` (`id`);
ALTER TABLE `poller_cluster` DROP INDEX `poller_cluster_node_id_unique`;
ALTER TABLE `poller_cluster_stats` CHANGE `parent_poller` `parent_poller` varchar(255) NOT NULL DEFAULT '' ;
ALTER TABLE `poller_cluster_stats` CHANGE `poller_type` `poller_type` varchar(255) NOT NULL DEFAULT '' ;
ALTER TABLE `poller_cluster_stats` CHANGE `depth` `depth` int(11) unsigned NOT NULL ;
ALTER TABLE `poller_cluster_stats` CHANGE `devices` `devices` int(11) unsigned NOT NULL ;
ALTER TABLE `poller_cluster_stats` CHANGE `workers` `workers` int(11) unsigned NOT NULL ;
ALTER TABLE `poller_cluster_stats` CHANGE `frequency` `frequency` int(11) unsigned NOT NULL ;
ALTER TABLE `poller_cluster_stats` DROP INDEX `PRIMARY`, PRIMARY KEY (`parent_poller`,`poller_type`);
ALTER TABLE `poller_cluster_stats` ADD UNIQUE `id` (`id`);
ALTER TABLE `poller_cluster_stats` DROP INDEX `parent_poller_poller_type`;
ALTER TABLE `ports_fdb` CHANGE `port_id` `port_id` int(11) unsigned NOT NULL ;
ALTER TABLE `ports_fdb` CHANGE `vlan_id` `vlan_id` int(11) unsigned NOT NULL ;
ALTER TABLE `ports_fdb` CHANGE `device_id` `device_id` int(11) unsigned NOT NULL ;
ALTER TABLE `ports_perms` ADD `access_level` int(11) NOT NULL AFTER `port_id`;
ALTER TABLE `ports_vlans` CHANGE `priority` `priority` bigint(32) NOT NULL ;
ALTER TABLE `ports_vlans` CHANGE `untagged` `untagged` tinyint(4) NOT NULL DEFAULT '0' ;
ALTER TABLE `processes` CHANGE `pid` `pid` int(255) NOT NULL ;
ALTER TABLE `processes` CHANGE `vsz` `vsz` int(255) NOT NULL ;
ALTER TABLE `processes` CHANGE `rss` `rss` int(255) NOT NULL ;
ALTER TABLE `processors` ADD INDEX `device_id_2` (`device_id`);
ALTER TABLE `route` CHANGE `ipRouteDest` `ipRouteDest` varchar(256) NOT NULL ;
ALTER TABLE `route` CHANGE `ipRouteNextHop` `ipRouteNextHop` varchar(256) NOT NULL ;
ALTER TABLE `sensors` CHANGE `device_id` `device_id` int(11) unsigned NOT NULL DEFAULT '0' ;
ALTER TABLE `sensors` CHANGE `sensor_current` `sensor_current` float NULL ;
ALTER TABLE `sensors` CHANGE `sensor_limit` `sensor_limit` float NULL ;
ALTER TABLE `sensors` CHANGE `sensor_limit_warn` `sensor_limit_warn` float NULL ;
ALTER TABLE `sensors` CHANGE `sensor_limit_low` `sensor_limit_low` float NULL ;
ALTER TABLE `sensors` CHANGE `sensor_limit_low_warn` `sensor_limit_low_warn` float NULL ;
ALTER TABLE `sensors` CHANGE `sensor_prev` `sensor_prev` float NULL ;
ALTER TABLE `state_translations` CHANGE `state_value` `state_value` smallint(5) NOT NULL DEFAULT '0' ;
ALTER TABLE `storage` ADD INDEX `device_id_2` (`device_id`);
ALTER TABLE `tnmsneinfo` CHANGE `neID` `neID` int(32) NOT NULL ;
ALTER TABLE `ucd_diskio` ADD INDEX `device_id_2` (`device_id`);
ALTER TABLE `users` CHANGE `can_modify_passwd` `can_modify_passwd` tinyint(4) NOT NULL DEFAULT '1' ;
ALTER TABLE `users` DROP `auth_type`;
ALTER TABLE `users` DROP `auth_id`;
ALTER TABLE `users` DROP INDEX `username`, UNIQUE `username` (`username`);
ALTER TABLE `users_prefs` CHANGE `user_id` `user_id` int(16) NOT NULL ;
ALTER TABLE `users_prefs` ADD UNIQUE `user_id.pref` (`user_id`,`pref`);
ALTER TABLE `users_prefs` DROP INDEX `users_prefs_user_id_pref_unique`;
ALTER TABLE `wireless_sensors` CHANGE `device_id` `device_id` int(11) unsigned NOT NULL DEFAULT '0' ;
ALTER TABLE `wireless_sensors` CHANGE `sensor_current` `sensor_current` float NULL ;
ALTER TABLE `wireless_sensors` CHANGE `sensor_prev` `sensor_prev` float NULL ;
ALTER TABLE `wireless_sensors` CHANGE `sensor_limit` `sensor_limit` float NULL ;
ALTER TABLE `wireless_sensors` CHANGE `sensor_limit_warn` `sensor_limit_warn` float NULL ;
ALTER TABLE `wireless_sensors` CHANGE `sensor_limit_low` `sensor_limit_low` float NULL ;
ALTER TABLE `wireless_sensors` CHANGE `sensor_limit_low_warn` `sensor_limit_low_warn` float NULL ;

#429

Hey Guys,

Have somewhat successfully upgraded to 1.44, I don’t seem to be able to resolve this one SQL schema change though.

Any help would be appreciated.

Cheers

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

Component Version
LibreNMS 1.44
DB Schema 268
PHP 7.2.10-0ubuntu0.18.04.1
MySQL 10.1.34-MariaDB-0ubuntu0.18.04.1
RRDTool 1.7.0
SNMP NET-SNMP 5.7.3

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

[OK] Composer Version: 1.7.2
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] Database: incorrect index (users/username)
[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 users DROP INDEX username, UNIQUE username (auth_type,username);
[email protected]:/opt/librenms# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 318
Server version: 10.1.34-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

Copyright © 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]> use librenms
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [librenms]> ALTER TABLE users DROP INDEX username, UNIQUE username (auth_type,username);
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 ‘UNIQUE username (auth_type,username)’ at line 1


#430

Is it OK, that after upgrade there are so many DB errors. It takes a lot of time and I can’t even run all at once, because there are listed only first 15.

====================================
Component | Version
--------- | -------
LibreNMS  | 1.44
DB Schema | 268
PHP       | 7.2.10-1+0~20181001133118.7+stretch~1.gbpb6e829
MySQL     | 5.7.22-22
RRDTool   | 1.6.0
SNMP      | NET-SNMP 5.7.3
====================================

[OK]    Composer Version: 1.7.2
[OK]    Dependencies up-to-date.
[OK]    Database connection successful
[FAIL]  Database: incorrect column (access_points/channel)
[FAIL]  Database: missing column (bgpPeers/bgpPeerDescr)
[FAIL]  Database: incorrect column (bill_data/delta)
[FAIL]  Database: incorrect column (bill_data/in_delta)
[FAIL]  Database: incorrect column (bill_data/out_delta)
[FAIL]  Database: incorrect column (component/id)
[FAIL]  Database: incorrect column (component/device_id)
[FAIL]  Database: incorrect column (component_prefs/id)
[FAIL]  Database: incorrect column (component_prefs/component)
[FAIL]  Database: incorrect column (component_statuslog/id)
[FAIL]  Database: incorrect column (component_statuslog/component_id)
[FAIL]  Database: incorrect column (dashboards/access)
[FAIL]  Database: incorrect column (devices/device_id)
[FAIL]  Database: incorrect column (devices/status)
[FAIL]  Database: incorrect column (devices/ignore)
[FAIL]  Database: incorrect column (devices/last_ping_timetaken)
[FAIL]  Database: extra column (devices_perms/access_level)
[FAIL]  Database: incorrect column (device_groups/id)
[FAIL]  Database: incorrect column (device_perf/id)
[FAIL]  Database: incorrect column (device_perf/xmt)
[FAIL]  Database: incorrect column (device_perf/rcv)
[FAIL]  Database: incorrect column (device_perf/loss)
[FAIL]  Database: incorrect column (device_perf/min)
[FAIL]  Database: incorrect column (device_perf/max)
[FAIL]  Database: incorrect column (device_perf/avg)
[FAIL]  Database: missing index (device_perf/PRIMARY)
[FAIL]  Database: extra index (device_perf/id)
[FAIL]  Database: incorrect column (device_relationships/parent_device_id)
[FAIL]  Database: incorrect column (device_relationships/child_device_id)
[FAIL]  Database: incorrect column (eventlog/severity)
[FAIL]  Database: extra index (ipv4_addresses/interface_id_2)
[FAIL]  Database: extra index (ipv6_addresses/interface_id_2)
[FAIL]  Database: incorrect column (links/active)
[FAIL]  Database: incorrect column (locations/lat)
[FAIL]  Database: incorrect column (locations/lng)
[FAIL]  Database: missing index (locations/PRIMARY)
[FAIL]  Database: extra index (locations/id)
[FAIL]  Database: incorrect column (mefinfo/mefID)
[FAIL]  Database: incorrect column (mefinfo/mefMTU)
[FAIL]  Database: incorrect column (mempools/mempool_used)
[FAIL]  Database: incorrect column (mempools/mempool_free)
[FAIL]  Database: incorrect column (mempools/mempool_total)
[FAIL]  Database: incorrect column (mempools/mempool_largestfree)
[FAIL]  Database: incorrect column (mempools/mempool_lowestfree)
[FAIL]  Database: incorrect column (munin_plugins/mplug_total)
[FAIL]  Database: incorrect column (munin_plugins/mplug_graph)
[FAIL]  Database: incorrect column (netscaler_vservers/vsvr_port)
[FAIL]  Database: incorrect column (pdb_ix_peers/remote_asn)
[FAIL]  Database: incorrect index (pollers/PRIMARY)
[FAIL]  Database: missing index (pollers/poller_name)
[FAIL]  Database: extra index (pollers/id)
[FAIL]  Database: incorrect index (poller_cluster/PRIMARY)
[FAIL]  Database: missing index (poller_cluster/poller_cluster_node_id_unique)
[FAIL]  Database: extra index (poller_cluster/id)
[FAIL]  Database: incorrect column (poller_cluster_stats/parent_poller)
[FAIL]  Database: incorrect column (poller_cluster_stats/poller_type)
[FAIL]  Database: incorrect column (poller_cluster_stats/depth)
[FAIL]  Database: incorrect column (poller_cluster_stats/devices)
[FAIL]  Database: incorrect column (poller_cluster_stats/workers)
[FAIL]  Database: incorrect column (poller_cluster_stats/frequency)
[FAIL]  Database: incorrect index (poller_cluster_stats/PRIMARY)
[FAIL]  Database: missing index (poller_cluster_stats/parent_poller_poller_type)
[FAIL]  Database: extra index (poller_cluster_stats/id)
[FAIL]  Database: incorrect column (ports_fdb/port_id)
[FAIL]  Database: incorrect column (ports_fdb/vlan_id)
[FAIL]  Database: incorrect column (ports_fdb/device_id)
[FAIL]  Database: extra column (ports_perms/access_level)
[FAIL]  Database: incorrect column (ports_vlans/priority)
[FAIL]  Database: incorrect column (ports_vlans/untagged)
[FAIL]  Database: incorrect column (processes/pid)
[FAIL]  Database: incorrect column (processes/vsz)
[FAIL]  Database: incorrect column (processes/rss)
[FAIL]  Database: extra index (processors/device_id_2)
[FAIL]  Database: incorrect column (route/ipRouteDest)
[FAIL]  Database: incorrect column (route/ipRouteNextHop)
[FAIL]  Database: incorrect column (sensors/device_id)
[FAIL]  Database: incorrect column (sensors/sensor_current)
[FAIL]  Database: incorrect column (sensors/sensor_limit)
[FAIL]  Database: incorrect column (sensors/sensor_limit_warn)
[FAIL]  Database: incorrect column (sensors/sensor_limit_low)
[FAIL]  Database: incorrect column (sensors/sensor_limit_low_warn)
[FAIL]  Database: incorrect column (sensors/sensor_prev)
[FAIL]  Database: incorrect column (state_translations/state_value)
[FAIL]  Database: extra index (storage/device_id_2)
[FAIL]  Database: incorrect column (tnmsneinfo/neID)
[FAIL]  Database: extra index (ucd_diskio/device_id_2)
[FAIL]  Database: missing column (users/auth_type)
[FAIL]  Database: missing column (users/auth_id)
[FAIL]  Database: incorrect column (users/can_modify_passwd)
[FAIL]  Database: incorrect index (users/username)
[FAIL]  Database: incorrect column (users_prefs/user_id)
[FAIL]  Database: missing index (users_prefs/users_prefs_user_id_pref_unique)
[FAIL]  Database: extra index (users_prefs/user_id.pref)
[FAIL]  Database: incorrect column (wireless_sensors/device_id)
[FAIL]  Database: incorrect column (wireless_sensors/sensor_current)
[FAIL]  Database: incorrect column (wireless_sensors/sensor_prev)
[FAIL]  Database: incorrect column (wireless_sensors/sensor_limit)
[FAIL]  Database: incorrect column (wireless_sensors/sensor_limit_warn)
[FAIL]  Database: incorrect column (wireless_sensors/sensor_limit_low)
[FAIL]  Database: incorrect column (wireless_sensors/sensor_limit_low_warn)
[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 `access_points` CHANGE `channel` `channel` tinyint(3) unsigned NOT NULL DEFAULT '0' ;
	 ALTER TABLE `bgpPeers` ADD `bgpPeerDescr` varchar(255) NOT NULL DEFAULT ''  AFTER `bgpPeerRemoteAddr`;
	 ALTER TABLE `bill_data` CHANGE `delta` `delta` bigint(20) NOT NULL ;
	 ALTER TABLE `bill_data` CHANGE `in_delta` `in_delta` bigint(20) NOT NULL ;
	 ALTER TABLE `bill_data` CHANGE `out_delta` `out_delta` bigint(20) NOT NULL ;
	 ALTER TABLE `component` CHANGE `id` `id` int(10) unsigned NOT NULL auto_increment;
	 ALTER TABLE `component` CHANGE `device_id` `device_id` int(10) unsigned NOT NULL ;
	 ALTER TABLE `component_prefs` CHANGE `id` `id` int(10) unsigned NOT NULL auto_increment;
	 ALTER TABLE `component_prefs` CHANGE `component` `component` int(10) unsigned NOT NULL ;
	 ALTER TABLE `component_statuslog` CHANGE `id` `id` int(10) unsigned NOT NULL auto_increment;
	 ALTER TABLE `component_statuslog` CHANGE `component_id` `component_id` int(10) unsigned NOT NULL ;
	 ALTER TABLE `dashboards` CHANGE `access` `access` tinyint(1) NOT NULL DEFAULT '0' ;
	 ALTER TABLE `devices` CHANGE `device_id` `device_id` int(10) unsigned NOT NULL auto_increment;
	 ALTER TABLE `devices` CHANGE `status` `status` tinyint(1) NOT NULL DEFAULT '0' ;
	 ALTER TABLE `devices` CHANGE `ignore` `ignore` tinyint(1) NOT NULL DEFAULT '0' ;
	  and 85 more...

#431

At some point of running queries I got this error:

ALTER TABLE `pollers` DROP INDEX `PRIMARY`, PRIMARY KEY (`id`)
> 1064 - 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 'PRIMARY KEY (`id`)' at line 1
> Time: 0.005s

UPDATE:
Full list of wrong queries:

	 ALTER TABLE `pollers` DROP INDEX `PRIMARY`, PRIMARY KEY (`id`);
	 ALTER TABLE `pollers` DROP INDEX `id`;
	 ALTER TABLE `poller_cluster` DROP INDEX `PRIMARY`, PRIMARY KEY (`id`);
	 ALTER TABLE `poller_cluster` DROP INDEX `id`;
	 ALTER TABLE `poller_cluster_stats` DROP INDEX `PRIMARY`, PRIMARY KEY (`id`);
	 ALTER TABLE `poller_cluster_stats` DROP INDEX `id`;
	 ALTER TABLE `users` DROP INDEX `username`, UNIQUE `username` (`auth_type`,`username`);

#432

For anyone still having issues with this, I found a fix in discord.

Simply run the below SQL query instead, validate again and then run any more SQL queries it suggests.

ALTER TABLE users DROP INDEX username;


#435

[OK] Composer Version: 1.7.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’ ;


#436

@AmmeonSupport Run those mysql queries.


#437

Hi, thanks for the response,
I ran them but I get repeating issues usually with the storage subdirectory.
I have 2 librenms installs and I get these issues with both.
running the commands fixes it but they return

Component Version
LibreNMS 1.45-34-gd3f5dc6
DB Schema 270
PHP 7.2.11
MySQL 5.5.60-MariaDB
RRDTool 1.4.8
SNMP NET-SNMP 5.7.2

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

[OK] Composer Version: 1.7.3
[OK] Dependencies up-to-date.
[OK] Database connection successful
[OK] Database schema correct
[FAIL] The poller (localhost.localdomain) has not completed within the last 5 minutes, check the cron job.
[FAIL] Some folders have incorrect file permissions, this may cause issues.
[FIX]:
sudo chown -R librenms:librenms /opt/librenms
sudo setfacl -d -m g::rwx /opt/librenms/rrd /opt/librenms/logs /opt/librenms/bootstrap/cache/ /opt/librenms/storage/
sudo chmod -R ug=rwX /opt/librenms/rrd /opt/librenms/logs /opt/librenms/bootstrap/cache/ /opt/librenms/storage/
Files:
/opt/librenms/storage/framework/views/7ca752eb64b1fdd7418e1a5d75ddd8be


#438

That’s not something to discuss in this thread.


#439

Installed a fresh copy of CentOS VM. Changed the timezone for system & php to Asia/Kolkata.
Validate now gives these errors which were not there before change in timezone.

[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’ ;