Yum update on DB server, promptly caused table issues on validate.php

Help
1

Patched up an environment and promptly broke things… :face_with_raised_eyebrow:
Looking for a few quick wins to get this thing happy again.
Should this resolve as simply as running the referenced SQL statements?

[root@librenms librenms]# ./validate.php

Component Version
LibreNMS 1.48.1-31-gcf17016
DB Schema 2019_01_16_195644_add_vrf_id_and_bgpLocalAs (131)
PHP 7.2.14
MySQL 5.5.60-MariaDB
RRDTool 1.4.8
SNMP NET-SNMP 5.7.2

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

[OK] Composer Version: 1.8.4
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] Database: incorrect column (notifications/datetime)
[FAIL] Database: missing index (syslog/device_id-timestamp)
[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 syslog ADD INDEX device_id-timestamp (device_id,timestamp);
ALTER TABLE users CHANGE created_at created_at timestamp NOT NULL DEFAULT ‘1970-01-02 00:00:01’ ;
[WARN] Some devices have not been polled in the last 5 minutes. You may have performance issues.

2

Easy to fix, just run the ALTER SQL statements

3

Thanks @murrant
Proceeding with these:
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’ ;
ALTER TABLE syslog ADD INDEX device_id-timestamp (device_id,timestamp);

Fired off the first two, no issues and it cleared the warning, waiting on the “ADD INDEX” now. We’ll see.

4

meh…there’s always something…

MariaDB [librenms]> ALTER TABLE syslog ADD INDEX device_id-timestamp (device_id,timestamp);
ERROR 1114 (HY000): The table ‘syslog’ is full
MariaDB [librenms]>

Any suggestions?
I pulled the syslog retention back from 180 days to 90 days, running a daily.sh to see if it helps prior to running the SQL statement again. It’s been hanging out on “cleaning up db” for an hour or so now.

5

After pulling the syslog retention back and the daily.sh, the latest run of the SQL statement failed just the same. Any ideas?

6

A quick google search: https://dev.mysql.com/doc/refman/8.0/en/full-table.html

7

If it isn’t disk space, you may have to truncate the syslog table to recover. Then alter the DB.