Hi,
I can’t seem to find a list of supported software, specifically databases, is there one?
The reason I’m asking is I’m having two problems since upgrading to MySQL 8.0.15 (running latest LibreNMS master) which seems to be related to changes made in MySQL. I did however see there was a change in LibreNMS 1.48 related to MySQL 8 (https://github.com/librenms/librenms/pull/9668) so it seems that there has been work to support it.
Problem 1)
This problem is with dashboards and widgets, LibreNMS is not able to load or save them in the database due to reserved keyword ‘row’ that was introduced in MySQL 8.0.2.
Log output:
[2019-03-03 22:53:27] production.ERROR: SQLSTATE[42000]: Syntax error or access violation: 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 ',size_x,size_y,refresh FROM users_widgets
LEFT JOIN widgets ON widgets’ at line 1 (SQL: SELECT user_widget_id,users_widgets.widget_id,title,widget,col,row,size_x,size_y,refresh FROM users_widgets
LEFT JOIN widgets ON widgets.widget_id=users_widgets.widget_id WHERE dashboard_id=2) (SQL: SELECT user_widget_id,users_widgets.widget_id,title,widget,col,row,size_x,size_y,refresh FROM users_widgets
Testing a little gives this:
mysql> SELECT col,row,size_x,size_y,refresh FROM users_widgets;
ERROR 1064 (42000): 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 ',size_x,size_y,refresh FROM users_widgets’ at line 1
mysql> SELECT col,row,size_x,size_y,refresh FROM users_widgets;
±----±----±-------±-------±--------+
| col | row | size_x | size_y | refresh |
±----±----±-------±-------±--------+
| 13 | 1 | 8 | 4 | 60 |
±----±----±-------±-------±--------+
1 row in set (0.00 sec)
According to https://dev.mysql.com/doc/refman/8.0/en/keywords.html ROW became reserved in 8.0.2.
Problem 2)
I havent noticed any real world impact from this error except for the validate script; it complains about all timestamp columns.
-bash-4.1$ ./validate.php
| Component | Version |
|---|---|
| LibreNMS | 1.49-2-g7f104d4 |
| DB Schema | 2019_02_10_220000_add_dates_to_fdb (132) |
| PHP | 7.3.2 |
| MySQL | 8.0.15 |
| RRDTool | 1.3.8 |
| SNMP | NET-SNMP 5.5 |
====================================
[OK] Composer Version: 1.8.4
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] Database: incorrect column (alerts/timestamp)
[FAIL] Database: incorrect column (alert_log/time_logged)
[FAIL] Database: incorrect column (applications/timestamp)
[FAIL] Database: incorrect column (authlog/datetime)
[FAIL] Database: incorrect column (bill_history/updated)
[FAIL] Database: incorrect column (bill_port_counters/timestamp)
[FAIL] Database: incorrect column (component_statuslog/timestamp)
[FAIL] Database: incorrect column (devices_attribs/updated)
[FAIL] Database: incorrect column (device_mibs/last_modified)
[FAIL] Database: incorrect column (device_oids/last_modified)
[FAIL] Database: incorrect column (mibdefs/last_modified)
[FAIL] Database: incorrect column (ports_adsl/port_adsl_updated)
[FAIL] Database: incorrect column (proxmox/last_seen)
[FAIL] Database: incorrect column (proxmox_ports/last_seen)
[FAIL] Database: incorrect column (sensors/lastupdate)
[FAIL] Database: incorrect column (state_translations/state_lastupdated)
[FAIL] Database: incorrect column (syslog/timestamp)
[FAIL] Database: incorrect column (users/updated_at)
[FAIL] Database: incorrect column (wireless_sensors/lastupdate)
[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 alerts CHANGE timestamp timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
ALTER TABLE alert_log CHANGE time_logged time_logged timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ;
ALTER TABLE applications CHANGE timestamp timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
ALTER TABLE authlog CHANGE datetime datetime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ;
ALTER TABLE bill_history CHANGE updated updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ;
ALTER TABLE bill_port_counters CHANGE timestamp timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ;
ALTER TABLE component_statuslog CHANGE timestamp timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ;
ALTER TABLE devices_attribs CHANGE updated updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
ALTER TABLE device_mibs CHANGE last_modified last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
ALTER TABLE device_oids CHANGE last_modified last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
ALTER TABLE mibdefs CHANGE last_modified last_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
ALTER TABLE ports_adsl CHANGE port_adsl_updated port_adsl_updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ;
ALTER TABLE proxmox CHANGE last_seen last_seen timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ;
ALTER TABLE proxmox_ports CHANGE last_seen last_seen timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ;
ALTER TABLE sensors CHANGE lastupdate lastupdate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP;
and 5 more…
Looking at the just the first table everything looks fine.
mysql> show create table alerts;
| alerts | CREATE TABLE alerts (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
device_id int(10) unsigned NOT NULL,
rule_id int(10) unsigned NOT NULL,
state int(11) NOT NULL,
alerted int(11) NOT NULL,
open int(11) NOT NULL,
note text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
info text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY unique_alert (device_id,rule_id),
KEY rule_id (rule_id),
KEY device_id (device_id)
) ENGINE=InnoDB AUTO_INCREMENT=267 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
Running the suggested queries does nothing to change the vlaidate output and I’ve tried several different DEFAULTs and variations. My guess is that something is returned differently in MySQL 8 that doesn’t match up with the validate check.
Thanks,
-David
