Error port device

Were you able to update? I’m having an error installing the new version of mariadb

I managed to update the mariadb, but even so the error continues, I cannot see the ports of the devices.

image

./validate.php

Component Version
LibreNMS 21.3.0-76-gf5f72dd39
DB Schema 2021_03_11_003713_rename_printer_columns (204)
PHP 7.4.16
Python 3.8.5
MySQL 10.5.9-MariaDB-1:10.5.9+maria~focal
RRDTool 1.7.2
SNMP NET-SNMP 5.8

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

[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

I too am facing this issue on two different installs at two different sites. Both are Ubuntu 18.04.

sudo -u librenms /opt/librenms/validate.php
====================================
Component | Version
--------- | -------
LibreNMS  | 21.3.0-76-gf5f72dd39
DB Schema | 2021_03_11_003713_rename_printer_columns (204)
PHP       | 7.4.16
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.12
[OK]    Dependencies up-to-date.
[OK]    Database connection successful
[FAIL]  Your database is out of date!
        [FIX]:
        ./lnms migrate

Huh, I must upgrade from 5.5.6 to 10.5 :frowning:
Firstly upgrade from 5.5 to 10.1 and then from 10.1 to 10.5

-bash-4.2$ ./validate.php
====================================
Component | Version
--------- | -------
LibreNMS | 21.3.0-76-gf5f72dd
DB Schema | 2021_03_11_003713_rename_printer_columns (204)
PHP | 7.4.12
Python | 3.6.8
MySQL | 5.5.60-MariaDB
RRDTool | 1.4.8
SNMP | NET-SNMP 5.7.2
====================================
[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

Fixed the port page error …

Upgraded database, but same issue, so then moved on to the migrate script.

Manually applying the database migration commands - from this [Your database is out of date! [FIX]: ./lnms migrate - error] it advised to --pretend the lnms migrate and apply the commands manually. In my case, the commands were:

This fixed the port page errors, but validate still shows the same database out of date and migrate output:

image

Did anyone get any solution? I’m still having the same problem, librenms works normally but when I try to open the ports tab of any device from the error, and in the validate it still gives a database error.

I get syntax error when I try to run

-> MigrateToUtf8mb4: ALTER SCHEMA librenms DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘CreatePortGroupsTable: create table port_groups (id int unsigned not null…’ at line 1

I’ve just updated MariaDB at 10.5.9

That’s was just the raw output from ./lnms migrate --pretend - remove the xxxx: tags at the start and end each line with a ; - here’s my output when I did it:

The final select commands have placeholders the php script fills in - and guessing they are how it validates success … they’ll fail manually and are not relevant.

Maybe save some time with this: ./lnms migrate --pretend --force | cut -f2 -d':' | sed -e 's/$/;/'

it seems that it is a common problem, unfortunately i have the same error when i try to manually execute the command in mariadb.

Thank you very much. I can see ports now, although validate still shows the database out of date.

1 Like

Same solution, manually modify MySql:


alter table `port_groups` add unique `port_groups_name_unique`(`name`);

create table port_group_port (port_group_id int unsigned not null, port_id int unsigned not null) default character set utf8mb4 collate ‘utf8mb4_unicode_ci’;
alter table port_group_port add primary key port_group_port_port_group_id_port_id_primary(port_group_id, port_id);
alter table port_group_port add constraint port_group_port_port_group_id_foreign foreign key (port_group_id) references port_groups (id) on delete CASCADE;
alter table port_group_port add constraint port_group_port_port_id_foreign foreign key (port_id) references ports (port_id) on delete CASCADE;
alter table port_group_port add index port_group_port_port_group_id_index(port_group_id);
alter table port_group_port add index port_group_port_port_id_index(port_id);
ALTER SCHEMA librenms DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;


But validator still says:


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

Component Version
LibreNMS 21.3.0-76-gf5f72dd
DB Schema 2021_03_11_003713_rename_printer_columns (204)
PHP 7.4.16
Python 3.6.8
MySQL 10.5.9-MariaDB
RRDTool 1.4.8
SNMP NET-SNMP 5.7.2

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

[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


I can confirm that manually running the commands that are output after running ./lnms migrate --pretend into mariadb/mysql solved the issue. It worked on both of my installs.

I had forgotten how to get into my database but all I had to run was…

sudo mysql -u root

That gets you into mariadb/mysql but you’re still not connected to the librenms database. So run…

connect librenms

Then I was able to run the sql commands one by one, while also making sure that a ; was at the end of every line.

It sucks that this happened in the first place, but I’m glad that we have found a fix/workaround.

This is how I dropped the tables. mysql -u librenms -p use librenms; SET FOREIGN_KEY_CHECKS = 0; drop table port_group_port; drop table port_groups; SET FOREIGN_KEY_CHECKS = 1; quit; rerun ./lnms migrate ./validate.php Saved my bacon.(editado)

Thanks

9 Likes

Same situation here; port_groups table already exists so ‘create table’ failed, but manually running all the rest of the statements successfully fixed the ports view via web interface. But it does not resolve the issue with ‘lnms migrate’ reporting an out of date schema.
So use the ‘drop table…’ steps above, re-run ‘./lnms migrate’ and you should be good.

Confirmed fully resolved with this, thanks @JhonathanDDF !

1 Like

Is it safe in a production environment?

Drop a whole table seems (to me) a risky action to lose some parts …

yes, in my case i did a snapshop before the procedure and i had no problems.

tnx. fixed my installation after upgrade to 10.5.

1 Like

I dropped the tables like JhonathanDDF suggested and then get the below errors with ./lnms migrate:

MariaDB [librenms]> SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected (0.000 sec)
MariaDB [librenms]> drop table port_group_port;
ERROR 1051 (42S02): Unknown table ‘librenms.port_group_port’
MariaDB [librenms]> drop table port_groups;
Query OK, 0 rows affected (0.004 sec)
MariaDB [librenms]> SET FOREIGN_KEY_CHECKS = 1;
Query OK, 0 rows affected (0.000 sec)
MariaDB [librenms]> quit
Bye
bash-4.2$ pwd
/opt/librenms
bash-4.2$ ./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
Migrated: 2020_12_14_091314_create_port_groups_table (65.06ms)
Migrating: 2021_02_09_122930_migrate_to_utf8mb4

In Connection.php line 678:

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: ‘0000-00-00 00:00:00’ for column librenms.entityState.entStateLastChanged at row 243229 (SQL: ALTER TABLE entityState CHARACTER SET utf8mb4 COLLATE utf
8mb4_unicode_ci;)

In Exception.php line 18:

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: ‘0000-00-00 00:00:00’ for column librenms.entityState.entStateLastChanged at row 243229

In PDOConnection.php line 53:

SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: ‘0000-00-00 00:00:00’ for column librenms.entityState.entStateLastChanged at row 243229

bash-4.2$