Database out of date - validate.php - lnms update

hello,
i’m facing to an issue after follow the instruction of validate.php to perform ./lnms migrate:

According to the last ./validate.php that say do ./lnms to upgrade your Mariadb.
i met some error on ./lnms migrate about varchar(255) to loon so i modify vachar for the table/row and lnms pass finally but now when i do ./validate i have other error message according to the DB schema i think:

-bash-4.2$ ./validate.php

Component Version
LibreNMS 21.3.0-57-g6616727
DB Schema 2021_02_09_122930_migrate_to_utf8mb4 (205)
PHP 7.4.16
Python 3.6.8
MySQL 5.5.68-MariaDB
RRDTool 1.4.8
SNMP NET-SNMP 5.7.2

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

[OK] Composer Version: 2.0.11
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] MariaDB version 10.2.2 is the minimum supported version as of March, 2021. We recommend you update MariaDB to a supported version 10.5 suggested). Failure to update MariaDB will eventually cause issues.
[FAIL] Database: incorrect column (alert_rules/name)
[FAIL] Database: incorrect column (api_tokens/token_hash)
[FAIL] Database: incorrect column (cache/key)
[FAIL] Database: incorrect column (cache_locks/key)
[FAIL] Database: incorrect column (config/config_name)
[FAIL] Database: incorrect column (device_groups/name)
[FAIL] Database: incorrect column (locations/location)
[FAIL] Database: incorrect column (munin_plugins/mplug_type)
[FAIL] Database: incorrect column (packages/version)
[FAIL] Database: incorrect column (pollers/poller_name)
[FAIL] Database: incorrect column (poller_cluster/node_id)
[FAIL] Database: incorrect column (proxmox/cluster)
[FAIL] Database: incorrect column (users/username)
[FAIL] Database: incorrect column (widgets/widget)
[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 alert_rules CHANGE name name varchar(255) NOT NULL ;
ALTER TABLE api_tokens CHANGE token_hash token_hash varchar(255) NULL ;
ALTER TABLE cache CHANGE key key varchar(255) NOT NULL ;
ALTER TABLE cache_locks CHANGE key key varchar(255) NOT NULL ;
ALTER TABLE config CHANGE config_name config_name varchar(255) NOT NULL ;
ALTER TABLE device_groups CHANGE name name varchar(255) NOT NULL DEFAULT ‘’ ;
ALTER TABLE locations CHANGE location location varchar(255) NOT NULL ;
ALTER TABLE munin_plugins CHANGE mplug_type mplug_type varchar(255) NOT NULL ;
ALTER TABLE packages CHANGE version version varchar(255) NOT NULL ;
ALTER TABLE pollers CHANGE poller_name poller_name varchar(255) NOT NULL ;
ALTER TABLE poller_cluster CHANGE node_id node_id varchar(255) NOT NULL ;
ALTER TABLE proxmox CHANGE cluster cluster varchar(255) NOT NULL ;
ALTER TABLE users CHANGE username username varchar(255) NOT NULL ;
ALTER TABLE widgets CHANGE widget widget varchar(255) NOT NULL ;

if i try to modify the Table like recommanded upper that failed because of :ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

do you have an idea to solve that because i turn around?

Update your MySQL version

From release notes
The minimum supported MySQL / MariaDB version is now 5.7.7 / 10.2.2

hi,
thank you for your reply, do you have a tutorial link to share to update MySQL version?
thank you

because in fact i think ./lnms migrate do the sql upgrate and migrate the db. i’m wrong so

i think i find a link to do the mysql upgrade:

  1. Create a backup of the original MySQL data:
    mysqldump -v -u root -p --all-databases > /tmp/backups/mysql56_all_databases_dump.date +%F.sql
  2. Stop the mysql database and backup db files:
    $ systemctl stop mysqld $ cp /var/lib/mysql /var/lib/mysql56.backup
  3. Download the MySQL 5.7 RPM:
    wget http://repo.mysql.com/mysql57-community-release-el7.rpm -P /tmp/
  4. Remove the MySQL-Community RPM that contains MySQL 5.6:
    $ yum remove mysql-community-release
  5. Install the MySQL 5.7 RPM:
    $ rpm -ivh /tmp/mysql57-community-release-el7.rpm
  6. Update the MySQL Repository to 5.7:
    $ yum update mysql
  7. Let MySQL know that it received an upgrade:

sudo systemctl start mysqld

sudo systemctl status mysqld

mysql_upgrade

  1. Restart MySQL after upgrade

sudo systemctl restart mysqld

sudo systemctl status mysqld

mysql

  1. Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 4
    Server version: 5.7.22 MySQL Community Server (GPL)
    mysql> show databases;
    ±-------------------+
    | Database |
    ±-------------------+
    | information_schema |
    | mysql |
    | performance_schema |
    | sys |
    ±-------------------+
    4 rows in set (0.00 sec)

Is that the way to do it ?

thank you

Depends on your distro. I was able to run apt update && apt upgrade on my debian box

my distro is a Centos 7

hi @Wolfraider, I upgraded MariaDB but am still getting SQL state errors saying the table ‘port-groups’ already exists, how do I fix this? Please advise.

$ ./validate.php

Component Version
LibreNMS 21.3.0-82-gedb36fd69
DB Schema 2021_03_11_003713_rename_printer_columns (204)
PHP 7.4.16
Python 3.6.9
MySQL 10.4.18-MariaDB-1:10.4.18+maria~bionic
RRDTool 1.7.0
SNMP NET-SNMP 5.7.3

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

[OK] Composer Version: 2.0.12
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] Your database is out of date!
[FIX]:
./lnms migrate
[WARN] IPv6 is disabled on your server, you will not be able to add IPv6 devices.
$
$
$ ./lnms migrate


  • Application In Production!     *
    

Do you really wish to run this command? (yes/no) [no]:

yes

Migrating: 2020_12_14_091314_create_port_groups_table

In Connection.php line 678:

SQLSTATE[42S01]: Base table or view already exists: 1050 Table ‘port_groups’ already exists (S
QL: create table port_groups (id int unsigned not null auto_increment primary key, name
varchar(255) not null, desc varchar(255) null) default character set utf8mb4 collate ‘utf8mb
4_unicode_ci’)

In Exception.php line 18:

SQLSTATE[42S01]: Base table or view already exists: 1050 Table ‘port_groups’ already exists

In PDOStatement.php line 112:

SQLSTATE[42S01]: Base table or view already exists: 1050 Table ‘port_groups’ already exists

Update: Fixed this by deleting the existing tables in MySQL (I suggest backing them up first with mysqldump) and then running ./lnms migrate.

This worked for me as well. Upgraded mariadb and attempted the ‘./lnms migrate’ and got he port_groups errors. Verified that table was empty, dropped the table, and ran the migrate again and it succeeded. Thanks for this.

are you on Centos or Ubuntu? To upgrade Mariadb on Centos not a simple yum update is enought, right?
is my procedure upper is the good way to upgrade mariadb/mysql?

https://mariadb.com they have a good guide for migrating 5.5 to 10.
You need to get the right mariadb repos set up, there’s a handy script or two there that makes it easier.
Upgraded mine to 10.5 today.
I ran validate and the output gave me some sql commands I had to run on my mariadb librenms table.

1 Like

thank you very much i will take a look.

Hi @Anthony_Anyoti I got the exact same problem. Do you ahve a step by step instruction for your solution ?

@Usaia_Tawakevou

  • 1st step is to have a supported mariadb version. ./validate.php will tell you if you have it or not.
  • 2nd step, as your LibreNMS was (most probably) updated while mariadb was not, is to delete the “port_groups” table. You need to open a mysql connection to your libreNMS db and type:
    DROP table port_groups;
  • 3rd step: run lnms migrate. This step might take long depending on your DB size.
  • 4th step: run ./daily.sh again.
  • 5th step: run ./validate.php again. Should not come with any error.
2 Likes

Yes Ive got the automatic update on.

Component Version
LibreNMS 21.4.0
DB Schema 2020_12_14_091314_create_port_group_port_table (202)
PHP 7.4.13
Python 3.8.6
MySQL 10.1.47-MariaDB-0ubuntu0.18.04.1
RRDTool 1.7.0
SNMP NET-SNMP 5.7.3

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

[OK] Composer Version: 2.0.12
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] MariaDB version 10.2.2 is the minimum supported version as of March, 2021. We recommend you update MariaDB to a supported version 10.5 suggested). Failure to update MariaDB will eventually cause issues.
[FAIL] Your database is out of date!
[FIX]:
./lnms migrate
[FAIL] You need to run this script as librenms or root

Sorry Im no good with database, how do I open mysql connection to my libreNMS db ?

mysql -u root or librenms -p
enter the db password for the user
use database librenms;
enter the command to DROP the table ports_group

Got this error now when I run the lnms migrate


  • Application In Production!     *
    

Do you really wish to run this command? (yes/no) [no]:

yes

Migrating: 2020_12_14_091314_create_port_groups_table

In Connection.php line 678:

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter table port_groups add unique port_groups_name_unique(name))

In Exception.php line 18:

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

In PDOStatement.php line 112:

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

Fixed now. Upgrade my Maria DB to 10.5 then drop that table and migrate

This is how I fixed my CentOS 7 installs (use at your own risk).

systemctl stop mariadb
systemctl stop httpd
rpm -e --nodeps "mariadb-*"
rpm -e --nodeps "mariadb-libs"
rpm -e --nodeps "mariadb-server"
yum -y install wget
wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
echo "7a24f5580421fd353dc22c5439001bdaec86c54ed911c80e5482f62921125ac8 mariadb_repo_setup"     | sha256sum -c -
chmod +x mariadb_repo_setup
sudo ./mariadb_repo_setup    --mariadb-server-version="mariadb-10.5"
yum -y install mariadb mariadb-libs mariadb-server
mv /etc/my.cnf.rpmsave /etc/my.cnf
systemctl start mariadb
systemctl start httpd
systemctl enable mariadb

mariadb
use librenms
drop table port_groups;
quit

su librenms
cd
./lnms migrate
./validate.php
7 Likes