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]
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.
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.
Huh, I must upgrade from 5.5.6 to 10.5
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
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.
-> 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
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/$/;/'
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)
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.