MSQL issue with ova librenms-centos-7.6-x86_64.ova

Help
1

hello support,

i’ve implemented the new ova Librenms-centos-7.6, and i’ve change the timezone for corresponding to my country (Europe/Paris) after that i run ./validate.php and all the time i’ve this issue

DatabaseFailure

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

i’ve try this command in mariadb but unfortunately it doen’t work at all please help me

2

Please post ./validate.php

3

[root@Host-001 librenms]# ./validate.php

Component Version
LibreNMS 1.61
DB Schema 2020_02_10_223323_create_alert_location_map_table (159)
PHP 7.2.22
MySQL 5.5.64-MariaDB
RRDTool 1.4.8
SNMP NET-SNMP 5.7.2

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

[OK] Composer Version: 1.10.1
[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:
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’ ;

4

Someone can help me with my issue

5

Do you know how I can we reproduce this ?

From the information you gave, I can assume that it’s a fresh install, you updated /etc/timezone and /etc/php/…php.ini ?

What else ?

6

hello _dGs, exactaly i’ve modify the time zone with following command :

timedatectl set-timezone

also modify the file php.ini

after that i relaunch the ./validate.php after that the result mention that i have swedish mysql or something like that and i modify for utf 8, unfortunately i get it the following result :

[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):

i’ve try this two sql statement fix but it doesn’t work at all

7

Ok so I have downloaded the same ova and did as librenms:

  • sudo timedatectl set-timezone Europe/Paris
  • sudo vi /etc/php.ini (date.timezone = Europe/Paris)
  • sudo reboot now
  • ./validate.php
====================================
Component | Version
--------- | -------
LibreNMS  | 1.56
DB Schema | 2019_09_05_153524_create_notifications_attribs_index (141)
PHP       | 7.2.22
MySQL     | 5.5.64-MariaDB
RRDTool   | 1.4.8
SNMP      | NET-SNMP 5.7.2
====================================

[OK]    Composer Version: 1.10.1
[OK]    Dependencies up-to-date.
[OK]    Database connection successful
[FAIL]  MySQL Database collation is wrong: latin1 latin1_swedish_ci
	[FIX]: 
	Check https://t.libren.ms/-zdwk for info on how to fix.
[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:
	 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' ;
[FAIL]  The poller (localhost.localdomain) has not completed within the last 5 minutes, check the cron job.
[FAIL]  Discovery has not completed in the last 24 hours.
	[FIX]: 
	Check the cron job to make sure it is running and using discovery-wrapper.py
  • echo 'ALTER DATABASE librenms CHARACTER SET utf8 COLLATE utf8_unicode_ci;' | mysql -p -u librenms librenms

First FAIL fixed.

  • mysql -u librenms librenms -p
  • copy/paste the 2 ALTER TABLE
MariaDB [librenms]> ALTER TABLE `notifications` CHANGE `datetime` `datetime` timestamp NOT NULL DEFAULT '1970-01-02 00:00:00' ;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [librenms]>  ALTER TABLE `users` CHANGE `created_at` `created_at` timestamp NOT NULL DEFAULT '1970-01-02 00:00:01' ;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Before : SHOW FULL COLUMNS FROM users;
| created_at | timestamp | NULL | NO | | 1970-01-02 01:00:01 | | select,insert,update,references | |

AFTER : SHOW FULL COLUMNS FROM users;
| created_at | timestamp | NULL | NO | | 1970-01-02 00:00:01 | | select,insert,update,references | |

  • ./validate.php (everything OK except poller and discovery but that normal)
  • ./daily.sh
  • ./validate.php , still OK and updated
8

i’ve done what you gave me, thanks a lot it’s worked

9

I’m glad that it now works for you ! you welcome :slight_smile: