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.

Show Down Devices From SSH – LibreNMS

Fortunately, it is super easy to get a list of down devices in LibreNMS. All we need to do is a dump of the devices from MySQL.

You can use the following command to print which devices are down. Should be the same devices that show down on your dashboard.

mysql -u librenms -p librenms -e 'use librenms ; select hostname,sysName,status from devices where status=0'

Tip: You can change 0 to 1 to see all up devices.

Show down devices via SSH in LibreNMS

LibreNMS dump devices in MySQL database

All the devices are stored in the librenms datebase devices table.

You can dump all of them with the following command from a shell prompt.

sudo mysql -u librenms  -p librenms -e "select hostname,sysName from devices"

Example output

[jim@localhost ~]$ sudo mysql -p librenms -e "select hostname,sysName from devices"
 Enter password:
 +----------------------------------------+--------------------------------+
 | hostname                               | sysName                        |
 +----------------------------------------+--------------------------------+
 | 192.168.1.1                            | linksys                        |
 | 192.168.1.9                            | wifi-unifi                     |
 +----------------------------------------+--------------------------------+

Reset WordPress admin password in MySQL

Log into MySQL from command line

mysql -u root -p

Select the correct database

USE wordpress_db;

Print current users

SELECT * FROM wp_users;

Should get something similar to the following

mysql> SELECT * FROM wp_users
-> ;
+----+------------+------------------------------------+---------------+---------------------------+----------+---------------------+------------------------------------+-------------+--------------+
| ID | user_login | user_pass | user_nicename | user_email | user_url | user_registered | user_activation_key | user_status | display_name |
+----+------------+------------------------------------+---------------+---------------------------+----------+---------------------+------------------------------------+-------------+--------------+
| 1 | admin | 5f4dcc3b5aa765d61d8327deb882cf99 | admin | bob@incredigeek.com | | 2018-08-09 10:10:42 | | 0 | admin |
| 2 | bob | 210805fb52a13251f4bedc7e725e575a | bob | bob@incredigeek.com | | 2019-11-01 11:31:23 | | 0 | bob smith |
+----+------------+------------------------------------+---------------+---------------------------+----------+---------------------+------------------------------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql>

To update the password use

UPDATE wp_users SET user_pass = MD5('NewPass') WHERE ID=1;

The MD5 hashes the NewPass and adds it to the database. You can print the users again to verify the hash changed.

You should now be able to login using the new password you configure.

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.

Recovering LibreNMS from crashed XenServer VM

Had a LibreNMS instance crash, or the VM crashed, not bootable anymore.  Was able to boot it up on a CentOS iso with rescue mode, which gave me access to the files.  So the idea is to manually copy off the LibreNMS files and LibreNMS database and import them to a new LibreNMS instance

Issues

  • Need to access the the system files
  • MySQL doesn’t start in a chroot environment, so no way to do a mysqldump —  (Has to do with systemv or something)

Steps

  1. Create new LibreNMS VM with a new instance of LibreNMS installed
  2. Gain access to the crashed system
  3. Copy over LibreNMS MySQL databases to new LibreNMS instance
  4. Copy over LibreNMS files “/opt/librenms” to new LibreNMS instance
  5. Clean up.  Set users on directories, check SELinux etc.

1. Installing New LibreNMS VM

  • Guide for Ubuntu/Debian distro’s here
  • Guide for for Fedora/CentOS/RedHat here

2. Gaining access to crashed VM

There can be a couple of ways to gain access to a crashed XenServer VM.  One of the easiest ways is to boot up in recovery mode and go through the installers rescue mode.  Guide to boot up XenServer VM in recovery mode here.

The installer rescue mode should detect the OS and mount everything.  If not you should be able to mount the root partition manually.

Once booted up, you’ll need to enable network access if your going to use sftp or scp to copy files.  There are a few different ways to do this

  • Run “dhclient” to pull an address via DHCP
  • Set a static IP address
    • Guide for Ubuntu/Debian distro’s here
    • Guide for fedora/CentOS/RedHat here

3.Copy LibreNMS Mysql Database

Backup the LibreNMS MySQL database directory

tar czvf librenms_mysql.tgz /var/lib/mysql

Use scp or sftp to copy it to the new LibreNMS instance

scp librenms_mysql.tgz user@new_LibreNMS_ip

Now on the new LibreNMS instance we need to run the following few commands

systemctl stop mariadb
rm -rf /var/lib/mysql/*
tar xzvf librenms_mysql.tgz -C /
chown -R mysql:mysql /var/lib/mysql

4. Copy LibreNMS directory

Backup the LibreNMS directory

tar czvf librenms.tgz /opt/librenms

Use scp or sftp to copy it to the new LibreNMS instance

scp librenms.tgz user@new_LibreNMS_ip

Now on the new LibreNMS instance we need to run the following few commands

rm -rf /opt/librenms/*
tar xzvf librenms.tgz -C /
chown -R librenms:librenms /opt/librenms

5. Clean up

Disable SELinux if you have not already.  Guide here

Restart apache, and start mysql.  If your on Ubuntu, the services are named apache2 and mysql

systemctl restart httpd
systemctl restart mariadb

That should get it working, if not try a reboot.

Special notes

The whole MySQL directory needs to be copied, there are innodb files that will keep MySQL from starting if they are not copied.

There is some good info here
https://stackoverflow.com/questions/1795176/how-to-change-mysql-data-directory