LibreNMS Error Running ./validate.php – Database: incorrect column…

LibreNMS error while running ./validate.php

Recently I started getting the following error while running ./validate.php.

$ sudo -u librenms ./validate.php
====================================
Component | Version
--------- | -------
LibreNMS  | 21.8.0-41-g0a76ca4
DB Schema | 2021_08_26_093522_config_value_to_medium_text (217)
PHP       | 7.4.20
Python    | 3.6.8
MySQL     | 10.5.10-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' ;

Reading online it sounds like some of the recent changes are causing the issue. Looks fairly easy to resolve though.

First we’ll need to get a MySQL prompt. We’ll do that by running

mysql -u librenms -p librenms

It’ll ask use for the librenms user’s mysql password.

Once we have the MySQL prompt we can just copy and paste the commands in.

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' ;

Type ‘quit’ to exit MySQL and lets run the validate script again.

sudo -u librenms ./validate.php

Everything should check out OK.

Delete anonymous MySQL user

Log into mysql

mysql -u root -p

List users

select User,Host from mysql.user;

Should return something like the following

MariaDB [mysql]> select User,Host from user;
+----------+-----------------------+
| User | Host |
+----------+-----------------------+
| root | 127.0.0.1 |
| librenms | localhost |
| | localhost.localdomain |
+----------+-----------------------+
3 rows in set (0.00 sec)
MariaDB [mysql]>

Delete anonymous user

Note that there are two single quotes ‘ before the @ sign, not a double quote “

drop user ''@'localhost.localdomain';

Troubleshoot MySQL Performance

Login to MySQL

mysql -u root -p

Show Active processes

SHOW PROCESSLIST;

or to list all the processes use “FULL”

SHOW FULL PROCESSLIST;

Run MySQL optimizer

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl ./mysqltuner.pl

MySQL tuner should give some recommendations on settings to tweak.

Setting up MySQL for FreeRadius

Install Mysql

yum install mariadb mariadb-client mariadb-server freeradius-mysql

Setup MySQL database.

mysql -u root -p
CREATE DATABASE radius;
GRANT ALL ON radius.* TO radius@localhost IDENTIFIED BY "RadiusPassword";
exit

Import the schema.sql file into the db.

mysql -u radius -p radius < /etc/raddb/mods-config/sql/main/mysql/schema.sql

Should be good to go.