Database out of date - validate.php - lnms update

Help
15

@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.
3 Likes
16

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 ?

17

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

18

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

19

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

20

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
6 Likes
21

Yep. That was the step 1 : Upgrade to a supported MariaDB version as required.
Bye

2 Likes
22 
librenms@librenms:~$ /opt/librenms/lnms migrate --pretend
**************************************
*     Application In Production!     *
**************************************

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

CreatePortGroupsTable: 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 'utf8mb4_unicode_ci'
CreatePortGroupsTable: alter table `port_groups` add unique `port_groups_name_unique`(`name`)
MigrateToUtf8mb4: ALTER SCHEMA `librenms` DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
MigrateToUtf8mb4: select `TABLE_NAME` from `information_schema`.`tables` where `table_schema` = ?
MigrateToUtf8mb4: select * from `information_schema`.`columns` where `table_schema` = ? and `CHARACTER_SET_NAME` is not null and `COLLATION_NAME` is not null and (`CHARACTER_SET_NAME` != ? or `COLLATION_NAME` != ?)
RenameTonerTable: rename table `toner` to `printer_supplies`

In SchemaException.php line 86:

  There is no column with name 'toner_id' on table 'printer_supplies'.


librenms@librenms:~$ ./validate.php
====================================
Component | Version
--------- | -------
LibreNMS  | 21.4.0
DB Schema | 2020_12_14_091314_create_port_group_port_table (202)
PHP       | 7.3.27-9+ubuntu18.04.1+deb.sury.org+1
Python    | 3.6.9
MySQL     | 10.2.37-MariaDB-1:10.2.37+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
23

Thanks for your solution :slight_smile: Ran into the same issues

24

thanks also… perhaps this needs to be a pinned topic if not already and documented in some way

2 Likes
25

i will try when i have time.
thanks!

26

I got the same issue but after I upgraded to 10.5.9-MariaDB and run ./lnms migrate I’m getting this

SQLSTATE[HY000]: General error: 1206 The total number of locks exceeds the lock table size (SQL: ALTER TABLE syslog CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;)

Update:
when I added the innodb_buffer_pool_size = 4G in /etc/my.cnf.d/server.cnf and run ./lnms migrate again im getting this
SQLSTATE[HY000] [2002] Connection refused (SQL: ALTER TABLE syslog CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;)

update2:FIXED

Got it fixed with increasing the storage of the server, because I realized the free space might have not been enough to copy and alter the table. I also decreased the buffer size to 2gb and added wait time in the config /etc/my.cnf.d/server.cnf
wait_timeout = 20000
interactive_timeout = 21600
innodb_buffer_pool_size = 2048M
and then I ran the ALTER table command manually
ALTER TABLE syslog CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

everything was working after that.

27

I’m getting the same exception, was you able to clear yours up?

28

Thanks @ [Dan_Baker] My Problem is Solved

29

thanks i follow your tutorial and finally at the end after apply recommanded change in librenms db that work fine

30

it seems since the upgrade of mariadb an lnms migrate the buffer/cache fill up more than before i’m at 24Gb buffer/cache for 32Gb of Ram, swap is not solicited and the Ram use 8Gb now.
someone notice this? sometime nginx page show timeout and in cli connected by ssh on librenms by moment the command respond very slowly and no cpu or ram overload. strange…

31

Same here with the ‘toner_id’ has anyone seen a posted fix yet? Been down about a week now.

32

Hello @John_Roberts

Please post your ./validate.php to check if you have the same issue. Do you have a supported SQL version or not ?

Bye

33

Thank you for the reply, here is what we have sir. Any assistance would be greatly apricated.

bash-4.2$ ./validate.php

Component Version
LibreNMS 21.4.0
DB Schema 2020_12_14_091314_create_port_group_port_table (202)
PHP 7.3.27
Python 3.6.8
MySQL 10.5.9-MariaDB
RRDTool 1.7.1
SNMP NET-SNMP 5.7.2

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

[OK] Composer Version: 2.0.13
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] Your database is out of date!
[FIX]:
./lnms migrate

34

I too am stuck and have not been able to proceed past 2021_03_11_003540_rename_toner_table

/opt/librenms/validate.php

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

Component Version
LibreNMS 21.4.0
DB Schema 2021_03_11_003540_rename_toner_table (205)
PHP 7.3.28-1+ubuntu18.04.1+deb.sury.org+1
Python 3.6.9
MySQL 10.5.9-MariaDB-1:10.5.9+maria~bionic
RRDTool 1.7.0
SNMP NET-SNMP 5.7.3
====================================

[OK] Composer Version: 2.0.13
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] Your database is out of date!
[FIX]:
./lnms migrate

When I run ./lnms migrate, I get this:

Migrating: 2021_03_11_003713_rename_printer_columns

In SchemaException.php line 86:

There is no column with name ‘toner_id’ on table ‘printer_supplies’.

When I check the printer_supplies table it appears that all the column renames have been done correctly:

MariaDB [librenms]> SHOW COLUMNS FROM printer_supplies;
+---------------------+------------------+------+-----+---------+----------------+
| Field               | Type             | Null | Key | Default | Extra          |
+---------------------+------------------+------+-----+---------+----------------+
| supply_id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| device_id           | int(10) unsigned | NO   | MUL | 0       |                |
| supply_index        | int(11)          | NO   |     | NULL    |                |
| supply_type         | varchar(64)      | NO   |     | NULL    |                |
| supply_oid          | varchar(64)      | NO   |     | NULL    |                |
| supply_descr        | varchar(255)     | NO   |     |         |                |
| supply_capacity     | int(11)          | NO   |     | 0       |                |
| supply_current      | int(11)          | NO   |     | 0       |                |
| supply_capacity_oid | varchar(64)      | YES  |     | NULL    |                |
+---------------------+------------------+------+-----+---------+----------------+

At this point I do not know what to do next. LibreNMS appears to be working OK but validate continues to report that the database is out of date but lnms migrate fails. Any thoughts on what to try next?