Database out of date - validate.php - lnms update

[PipoCanaja]

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

[111]

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

[Chas]

Thanks for your solution Ran into the same issues

[ds-04]

@Dan_Baker

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

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

[Loic_PLESSIS]

i will try when i have time.
thanks!

[mpopov]

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.

[Arlen_Jenks]

@111

PHP       | 7.3.27-9+ubuntu18.04.1+deb.sury.org+1
Python    | 3.6.9

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

[Md_Ariful_Islam]

Thanks @ [Dan_Baker] My Problem is Solved

[Loic_PLESSIS]

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

[Loic_PLESSIS]

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…

[John_Roberts]

@Arlen_Jenks

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

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

[PipoCanaja]

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

[John_Roberts]

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

[Drewville]

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?