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 namename varchar(255) NOT NULL ;
ALTER TABLE api_tokens CHANGE token_hashtoken_hash varchar(255) NULL ;
ALTER TABLE cache CHANGE keykey varchar(255) NOT NULL ;
ALTER TABLE cache_locks CHANGE keykey varchar(255) NOT NULL ;
ALTER TABLE config CHANGE config_nameconfig_name varchar(255) NOT NULL ;
ALTER TABLE device_groups CHANGE namename varchar(255) NOT NULL DEFAULT ‘’ ;
ALTER TABLE locations CHANGE locationlocation varchar(255) NOT NULL ;
ALTER TABLE munin_plugins CHANGE mplug_typemplug_type varchar(255) NOT NULL ;
ALTER TABLE packages CHANGE versionversion varchar(255) NOT NULL ;
ALTER TABLE pollers CHANGE poller_namepoller_name varchar(255) NOT NULL ;
ALTER TABLE poller_cluster CHANGE node_idnode_id varchar(255) NOT NULL ;
ALTER TABLE proxmox CHANGE clustercluster varchar(255) NOT NULL ;
ALTER TABLE users CHANGE usernameusername varchar(255) NOT NULL ;
ALTER TABLE widgets CHANGE widgetwidget 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?
Remove the MySQL-Community RPM that contains MySQL 5.6:
$ yum remove mysql-community-release
Install the MySQL 5.7 RPM:
$ rpm -ivh /tmp/mysql57-community-release-el7.rpm
Update the MySQL Repository to 5.7:
$ yum update mysql
Let MySQL know that it received an upgrade:
sudo systemctl start mysqld
sudo systemctl status mysqld
mysql_upgrade
Restart MySQL after upgrade
sudo systemctl restart mysqld
sudo systemctl status mysqld
mysql
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)
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]:
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
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.
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.
[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 ?
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