OK. The issue is with the 2020_04_06_001048_the_great_index_rename.php
I checked few of the indexes and they were renamed long before this migration. The PHP is checking only for ‘wireless_sensors_sensor_type_index’ in wireless_sensors table. If it’s there it just glances over.
I had to manually execute all queries just to be sure …
Any suggestions how to move past 2020_04_06_001048_the_great_index_rename ?
[edit]
Ok, got impatient so I tried it like you seem too have done.
Ran all the queries in 2020_04_06_001048_the_great_index_rename. Then got a few errors from validate and ran the suggested SQL. Now it looks ok as far as I can tell
I’ve updated my setup with the required python3 dependency, so it has been a while without updates.
Now when running validate.php I’m getting this warning.
I haven’t run the recommended SQL fix statements.
====================================
Component | Version
--------- | -------
LibreNMS | 1.68-60-ge56f17e
DB Schema | 2020_10_03_1000_add_primary_key_transport_group_transport (186)
PHP | 7.2.19
Python | 3.6.8
MySQL | 5.5.60-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
[FAIL] Database: extra index (devices/sysName)
[FAIL] Database: missing index (entityState/entitystate_device_id_index)
[FAIL] Database: extra index (entityState/entityState_device_id_index)
[FAIL] Database: extra index (mefinfo/mefID)
[FAIL] Database: extra index (munin_plugins/UNIQUE)
[FAIL] Database: extra index (ports/device_ifIndex)
[FAIL] Database: extra index (ports_vlans/unique)
[FAIL] Database: extra index (syslog/device_id-timestamp)
[FAIL] Database: extra index (tnmsneinfo/neID)
[FAIL] Database: extra index (vminfo/vmwVmVMID)
[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` DROP INDEX `sysName`;
ALTER TABLE `entityState` DROP INDEX `entityState_device_id_index`;
ALTER TABLE `entityState` ADD INDEX `entitystate_device_id_index` (`device_id`);
ALTER TABLE `mefinfo` DROP INDEX `mefID`;
ALTER TABLE `munin_plugins` DROP INDEX `UNIQUE`;
ALTER TABLE `ports` DROP INDEX `device_ifIndex`;
ALTER TABLE `ports_vlans` DROP INDEX `unique`;
ALTER TABLE `syslog` DROP INDEX `device_id-timestamp`;
ALTER TABLE `tnmsneinfo` DROP INDEX `neID`;
ALTER TABLE `vminfo` DROP INDEX `vmwVmVMID`;
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 datetimedatetime timestamp NOT NULL DEFAULT ‘1970-01-02 00:00:00’ ;
ALTER TABLE users CHANGE created_atcreated_at timestamp NOT NULL DEFAULT ‘1970-01-02 00:00:01’ ;
[OK] Composer Version: 2.1.6
[OK] Dependencies up-to-date.
[OK] Database connection successful
[WARN] Your database schema has extra migrations (2020_06_24_155119_drop_ports_if_high_speed, 2020_09_18_230114_create_service_templates_device_group_table, 2020_09_18_230114_create_service_templates_device_table, 2020_09_18_230114_create_service_templates_table, 2020_09_18_230114_extend_services_table_for_service_templates_table, 2020_09_19_230114_add_foreign_keys_to_service_templates_device_group_table, 2020_09_19_230114_add_foreign_keys_to_service_templates_device_table, 2020_12_14_091314_create_port_group_port_table, 2020_12_14_091314_create_port_groups_table, 2021_02_08_224355_fix_invalid_dates, 2021_02_09_084318_remove_perf_times). 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] MySQL Database collation is wrong: utf8mb4 utf8mb4_unicode_ci
[FIX]:
Check Post DB Collation/CharacterSet update fixes - Announcements - LibreNMS Community for info on how to fix.
[FAIL] MySQL tables collation is wrong:
[FIX]:
Check Post DB Collation/CharacterSet update fixes - #2 by laf - Announcements - LibreNMS Community for info on how to fix.
Tables:
ciscoASA utf8mb4 utf8mb4_unicode_ci
alert_device_map utf8mb4 utf8mb4_unicode_ci
mpls_services utf8mb4 utf8mb4_unicode_ci
alert_location_map utf8mb4 utf8mb4_unicode_ci
graph_types utf8mb4 utf8mb4_unicode_ci
pdb_ix utf8mb4 utf8mb4_unicode_ci
access_points utf8mb4 utf8mb4_unicode_ci
ports_statistics utf8mb4 utf8mb4_unicode_ci
packages utf8mb4 utf8mb4_unicode_ci
devices_attribs utf8mb4 utf8mb4_unicode_ci
component_statuslog utf8mb4 utf8mb4_unicode_ci
users utf8mb4 utf8mb4_unicode_ci
state_indexes utf8mb4 utf8mb4_unicode_ci
syslog utf8mb4 utf8mb4_unicode_ci
ipv6_networks utf8mb4 utf8mb4_unicode_ci
and 120 more…
[FAIL] MySQL column collation is wrong:
[FIX]:
Check Post DB Collation/CharacterSet update fixes - Announcements - LibreNMS Community for info on how to fix.
Columns:
ciscoASA oid utf8mb4 utf8mb4_unicode_ci
mpls_services svcRowStatus utf8mb4 utf8mb4_unicode_ci
mpls_services svcType utf8mb4 utf8mb4_unicode_ci
mpls_services svcAdminStatus utf8mb4 utf8mb4_unicode_ci
mpls_services svcOperStatus utf8mb4 utf8mb4_unicode_ci
mpls_services svcDescription utf8mb4 utf8mb4_unicode_ci
mpls_services svcTlsMacLearning utf8mb4 utf8mb4_unicode_ci
mpls_services svcTlsStpAdminStatus utf8mb4 utf8mb4_unicode_ci
mpls_services svcTlsStpOperStatus utf8mb4 utf8mb4_unicode_ci
graph_types graph_type utf8mb4 utf8mb4_unicode_ci
graph_types graph_subtype utf8mb4 utf8mb4_unicode_ci
graph_types graph_section utf8mb4 utf8mb4_unicode_ci
graph_types graph_descr utf8mb4 utf8mb4_unicode_ci
pdb_ix name utf8mb4 utf8mb4_unicode_ci
perf_times type utf8mb4 utf8mb4_unicode_ci
and 2 more…
[FAIL] Database: missing column (ports/ifHighSpeed)
[FAIL] Database: missing column (ports/ifHighSpeed_prev)
[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 ports ADD ifHighSpeed int NULL AFTER ifPromiscuousMode;
ALTER TABLE ports ADD ifHighSpeed_prev int NULL AFTER ifHighSpeed;
[WARN] IPv6 is disabled on your server, you will not be able to add IPv6 devices.
[WARN] Your install is over 24 hours out of date, last update: Wed, 02 Dec 2020 14:39:06 +0000
[FIX]:
Make sure your daily.sh cron is running and run ./daily.sh by hand to see if there are any errors.
[WARN] Your local git branch is not master, this will prevent automatic updates.
[FIX]:
You can switch back to master with git checkout master
[WARN] Your local git contains modified files, this could prevent automatic updates.
[FIX]:
You can fix this with ./scripts/github-remove
Modified Files:
html/images/librenms_logo_blue.svg
html/images/librenms_logo_dark.svg
html/images/librenms_logo_light.svg
html/images/librenms_logo_mono.svg
rrd/.gitignore
librenms@dc5up-vlibrenms01:~$ mysql -p -u librenms librenms < /opt/librenms/sql-schema/171.sql
Enter password:
ERROR 1146 (42S02) at line 2: Table ‘librenms.alert_map’ doesn’t exist
librenms@dc5up-vlibrenms01:~$ mysql -p -u librenms librenms < /opt/librenms/sql-schema/171.sql
Enter password:
ERROR 1146 (42S02) at line 2: Table ‘librenms.alert_map’ doesn’t exist
librenms@dc5up-vlibrenms01:~$
librenms@dc5up-vlibrenms01:~$ ./lnms migrate
Application In Production! *
Do you really wish to run this command? (yes/no) [no]:
[OK] Composer Version: 2.1.6
[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:
SET TIME_ZONE=’+00:00’;
ALTER TABLE notifications CHANGE datetimedatetime timestamp NOT NULL DEFAULT ‘1970-01-02 00:00:00’ ;
ALTER TABLE users CHANGE created_atcreated_at timestamp NOT NULL DEFAULT ‘1970-01-02 00:00:01’ ;
Sent here by an error reported by validate.php - haven’t had any errors with this previously.
====================================
Component | Version
--------- | -------
LibreNMS | 21.8.0-26-g4f50c3c05
DB Schema | 2021_08_26_093522_config_value_to_medium_text (217)
PHP | 7.4.3
Python | 3.8.10
MySQL | 10.3.31-MariaDB-0ubuntu0.20.04.1
RRDTool | 1.7.2
SNMP | NET-SNMP 5.8
====================================
[OK] Composer Version: 2.1.6
[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:
SET TIME_ZONE='+00:00';
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' ;
Running the commands suggested has fixed the error even though it says 0 rows were affected…
[OK] Composer Version: 2.1.6
[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:
SET TIME_ZONE=’+00:00’;
ALTER TABLE notifications CHANGE datetimedatetime timestamp NOT NULL DEFAULT ‘1970-01-02 00:00:00’ ;
ALTER TABLE users CHANGE created_atcreated_at timestamp NOT NULL DEFAULT ‘1970-01-02 00:00:01’ ;
Hello. I’ve run validate.php and I find the errors below. I’ve tried running all the sql statements and still see the same errors. Any help would be great. Thanks!
[OK] Composer Version: 2.1.6
[OK] Dependencies up-to-date.
[FAIL] APP_KEY does not match key used to encrypt data. APP_KEY must be the same on all nodes.
[FIX]:
If you rotated APP_KEY, run lnms key:rotate to resolve.
[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:
SET TIME_ZONE=’+00:00’;
ALTER TABLE notifications CHANGE datetimedatetime timestamp NOT NULL DEFAULT ‘1970-01-02 00:00:00’ ;
ALTER TABLE users CHANGE created_atcreated_at timestamp NOT NULL DEFAULT ‘1970-01-02 00:00:01’ ;
Same as @Mitch_Toler above, except a newer MariaDB and older everything else. LibreNMS Version 21.8.0-41-g0a76ca4
[blentz@hrhnms01 ~]$ sudo su - librenms
Last login: Thu Sep 9 12:52:15 EDT 2021
[librenms@hrhnms01 ~]$ ./validate.php
====================================
Component | Version
--------- | -------
LibreNMS | 21.8.0-41-g0a76ca4
DB Schema | 2021_08_26_093522_config_value_to_medium_text (217)
PHP | 7.3.27
Python | 3.6.8
MySQL | 10.5.9-MariaDB
RRDTool | 1.4.8
SNMP | NET-SNMP 5.7.2
====================================
[OK] Composer Version: 2.1.6
[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:
SET TIME_ZONE='+00:00';
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' ;
Same issue here. Completely fresh install on a fresh Debian 10 LXC container.
====================================
Component | Version
--------- | -------
LibreNMS | 21.8.0-61-gd9b54469f
DB Schema | 2021_25_01_0129_isis_adjacencies_nullable (217)
PHP | 7.3.29-1~deb10u1
Python | 3.7.3
MySQL | 10.3.29-MariaDB-0+deb10u1
RRDTool | 1.7.1
SNMP | NET-SNMP 5.7.3
====================================
[OK] Composer Version: 2.1.8
[OK] Dependencies up-to-date.
[WARN] You have no devices.
[FIX]:
Consider adding a device such as localhost: /addhost
[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:
SET TIME_ZONE='+00:00';
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' ;
Received the message below. I ran the suggested SQL statements; ./validate.php returns no warning after SQL statements.
librenms@librenms:~$ ./validate.php
Component
Version
LibreNMS
21.9.0
DB Schema
2021_25_01_0129_isis_adjacencies_nullable (217)
PHP
7.4.22
Python
3.8.10
MySQL
10.5.12-MariaDB-1:10.5.12+maria~bionic
RRDTool
1.7.2
SNMP
NET-SNMP 5.8
====================================
[OK] Composer Version: 2.1.8
[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:
SET TIME_ZONE=’+00:00’;
ALTER TABLE notifications CHANGE datetimedatetime timestamp NOT NULL DEFAULT ‘1970-01-02 00:00:00’ ;
ALTER TABLE users CHANGE created_atcreated_at timestamp NOT NULL DEFAULT ‘1970-01-02 00:00:01’ ;
LibreNMS is running on Ubuntu 20.04.
librenms@librenms:~$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 20.04.3 LTS
Release: 20.04
Codename: focal
Hello Everyone, here some other Database Schema errors:
====================================
Component
Version
LibreNMS
21.9.0
DB Schema
2021_25_01_0129_isis_adjacencies_nullable (217)
PHP
7.3.29-1~deb10u1
Python
3.7.3
MySQL
10.3.29-MariaDB-0+deb10u1
RRDTool
1.7.1
SNMP
NET-SNMP 5.7.3
====================================
[OK] Composer Version: 2.1.8
[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 (LibreNMS) or the community site (Report database schema issues here - LibreNMS Community):
[FIX]:
Run the following SQL statements to fix.
SQL Statements:
SET TIME_ZONE=‘+00:00’;
ALTER TABLE notifications CHANGE datetimedatetime timestamp NOT NULL DEFAULT ‘1970-01-02 00:00:00’ ;
ALTER TABLE users CHANGE created_atcreated_at timestamp NOT NULL DEFAULT ‘1970-01-02 00:00:01’ ;
So the same schema errors have apparently been happening for like 18months.
I’m getting the same errors a ton of repeats down-thread.
ALTER TABLE notifications CHANGE datetimedatetime timestamp NOT NULL DEFAULT ‘1970-01-02 00:00:00’ ;
ALTER TABLE users CHANGE created_atcreated_at timestamp NOT NULL DEFAULT ‘1970-01-02 00:00:01’ ;
Running the suggested ^^ mysql statements doesn’t resolve the validation errors. (They still exist.)
Can someone please address this?!
I’m fine if they don’t mean anything, but I would like to ensure that somewhere down the road things don’t simply blow up because of it.
To be clear, new install on Ubuntu 20.04 following the wiki example.
Other details
Version 21.9.0-21-g7857dab98 - Fri Sep 24 2021 14:17:07 GMT-0700
Database Schema 2021_25_01_0129_isis_adjacencies_nullable (217)
Web Server nginx/1.18.0
PHP 7.4.3
Python 3.8.10
MySQL 10.3.31-MariaDB-0ubuntu0.20.04.1
Laravel 8.49.2
RRDtool 1.7.2
If your database password has any special chars like @ ^ £, change it to alphanumeric instead. Then update the new password in the .env file in home directory. Then run the SQL statements afterwards. Got this fix from r/librenms
===========================================
Component | Version
--------- | -------
LibreNMS | 24.12.0 (2024-12-18T00:55:13+01:00)
DB Schema | 2024_11_22_135845_alert_log_refactor_indexes (314)
PHP | 8.4.1
Python | 3.11.2
Database | MariaDB 10.11.6-MariaDB-0+deb12u1
RRDTool | 1.7.2
SNMP | 5.9.3
===========================================
[OK] Composer Version: 2.8.4
[OK] Dependencies up-to-date.
Checking database: WARN
[OK] Database connection successful
[WARN] Your database schema has extra migrations (2020_05_15_180000_add_alert_transport_timerange, 2020_06_30_170000_create_transport_device_map_table, 2020_06_30_170000_create_transport_group_map_table, 2020_06_30_170000_create_transport_location_map_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.
[OK] SQL Server meets minimum requirements
[OK] lower_case_table_names is enabled
[OK] MySQL engine is optimal
[OK] MySQL and PHP time match