Database: incorrect column error

Help
1

Hi All

I’m not entirely sure when this error first appeared as I only noticed it by chance, but everything seems to be working as it should. What could cause this and how do I fix it?

librenms@librenms:~$ ./daily.sh
Updating to latest codebase OK
Updating Composer packages OK
Updating SQL-Schema OK
Updating submodules OK
Cleaning up DB OK
Fetching notifications OK
Caching PeeringDB data OK
librenms@librenms:~$ ./validate.php

Component Version
LibreNMS 1.43-38-g9f0afa2
DB Schema 265
PHP 7.0.30-0ubuntu0.16.04.1
MySQL 10.0.36-MariaDB-0ubuntu0.16.04.1
RRDTool 1.5.5
SNMP NET-SNMP 5.7.3

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

[OK] Composer Version: 1.7.2
[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 IRC 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 users CHANGE created_at created_at timestamp NOT NULL DEFAULT ‘1970-01-02 00:00:01’ ;

2

Why happened? Dont know
How to fix? The validate output tell you.

Run those commands in mysql:

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’ ;
3

Hi Doc

My appologies, my SQL knowledge is non existant so assumed those were related to the error and not the fix :man_facepalming:

How do I access the database to run those commands?

Thanks,
R

4

No problem :slight_smile:

For running those commands:

mysql -u librenmsuser -p librenms (You can take the credentials from config.php or .env files)

Then enter the mysql password:

Then execute the commands I’ve told you.

5

Hi Doc

Not sure if I should feel stupid or worried, but no matter what I do I can’t log into mysql :see_no_evil:
I have tried all the passwords and both librenms and root as user (most of my configs are still default, Ubuntu VM) but keep getting “Access denied” no matter what.

Any advice?

6

No idea.

Just to be sure:

mysql -u user -p database so, if you have default user and database, will be:
mysql -u librenms -p librenms
Then you will be prompted for the password, enter it using copy&paste from the config.php or .env file.

Also Im guessing you are running mysql locally and not in other server, right?

7

That is correct and yes, it’s running locally.

Very confusing business this, the WebUI works fine still so whatever is pulling the data from the database is still doing so :man_shrugging:

8

Just FYI, never figured what the issue was but I was running a pre-configured VM at the time so I fixed it by installing my own setup from scratch :wink: