Error port device

Greetings folks, from yesterday to today when I try to access the ports of the devices, I encounter this error, what could have happened? nothing was changed in that period.

[2021-04-07 15:39:16] production.ERROR: SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘librenms.port_group_port’ doesn’t exist (SQL: select port_groups., port_group_port.port_id as pivot_port_id, port_group_port.port_group_id as pivot_port_group_id from port_groups inner join port_group_port on port_groups.id = port_group_port.port_group_id where port_group_port.port_id = 15) {“userId”:1,“exception”:"[object] (Illuminate\Database\QueryException(code: 42S02): SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘librenms.port_group_port’ doesn’t exist (SQL: select port_groups., port_group_port.port_id as pivot_port_id, port_group_port.port_group_id as pivot_port_group_id from port_groups inner join port_group_port on port_groups.id = port_group_port.port_group_id where port_group_port.port_id = 15) at /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php:678)
[stacktrace]

1 Like

DatabaseFailure

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

Same boat here, just saw this today for the first time and my steps also resulted in the same outcome.

Just popped over to the discord, this came up from this morning. It looks like in my case my database version is old, so it looks like I need to go through this.

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


Since the fix of manually applying the 2020-12-14 database updates, I have noticed my spark graphs are missing from summary screens, for example wireless -> clients, and all the others - where they normally appear in the red circle:

When looking at Chrome Inspect, can see the link to the graph, and that link works normally:

For example: http:/xxxx/graphs/to=1617927300/id=16/type=wireless_clients/from=1617840900/ is as normal.

Does anyone else who recalls seeing these who has the ports page working again get this issue now? Not sure if related or from another update.

Edit: if I leave the page for 5-10 mintues, they appear and an img-responsive image class block appears and all is normal. If I then refresh the page, they are gone again:

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

8 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.