[OK] Composer Version: 2.1.3
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] Your database is out of date!
[FIX]: ./lnms migrate
[WARN] IPv6 is disabled on your server, you will not be able to add IPv6 devices.
bash-4.2$ ./lnms migrate
Application In Production! *
Do you really wish to run this command? (yes/no) [no]:
yes
SQLSTATE[42S01]: Base table or view already exists: 1050 Table ‘port_groups’ already exists (SQL: create table port_groups (id int unsigned not null auto_increment primary key, na me varchar(255) not null, desc varchar(255) null) default character set utf8mb4 collate ‘utf8mb4_unicode_ci’)
In Exception.php line 18:
SQLSTATE[42S01]: Base table or view already exists: 1050 Table ‘port_groups’ already exists
In PDOStatement.php line 112:
SQLSTATE[42S01]: Base table or view already exists: 1050 Table ‘port_groups’ already exists
So today I’ve logged in in librenms and was greeted by the " Whoops, looks like something went wrong. Check your librenms.log. error. It seems after an update of the mariadb the migration was not successful and now I am stuck.
If i run the ./lnms --pretend I get a bunch of commands :
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 != ?)
AddForeignKeysToPortGroupPortTable: 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
AddForeignKeysToPortGroupPortTable: alter table port_group_port add constraint port_group_port_port_id_foreign foreign key (port_id) references ports (port_id) on delete CASCADE
SlasAddRttField: ALTER TABLE slas CHANGE sla_nr sla_nr INT UNSIGNED NOT NULL
SlasAddRttField: alter table slas add rtt double(8, 2) unsigned null after rtt_type
CreateIsisAdjacenciesTable: create table isis_adjacencies (id int unsigned not null auto_increment primary key, device_id int not null, port_id int not null, ifIndex int not null, isisISAdjState varchar(13) not null, isisISAdjNeighSysType varchar(128) not null, isisISAdjNeighSysID varchar(128) not null, isisISAdjNeighPriority varchar(128) not null, isisISAdjLastUpTime bigint unsigned not null, isisISAdjAreaAddress varchar(128) not null, isisISAdjIPAddrType varchar(128) not null, isisISAdjIPAddrAddress varchar(128) not null) default character set utf8mb4 collate ‘utf8mb4_unicode_ci’
CreateIsisAdjacenciesTable: alter table isis_adjacencies add index isis_adjacencies_device_id_index(device_id)
CreateIsisAdjacenciesTable: alter table isis_adjacencies add index isis_adjacencies_port_id_index(port_id)
CreateIsisAdjacenciesTable: alter table isis_adjacencies add index isis_adjacencies_ifindex_index(ifIndex)
But what do I do with those, how to clear the errors so the migration can take place ? Any help is appreciated!
SQLSTATE[42S01]: Base table or view already exists: 1050 Table ‘port_groups’ already exists (SQL: 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’)
In Exception.php line 18:
SQLSTATE[42S01]: Base table or view already exists: 1050 Table ‘port_groups’ already exists
In PDOStatement.php line 112:
SQLSTATE[42S01]: Base table or view already exists: 1050 Table ‘port_groups’ already exists
this is the lnms migrate after the lnms tinker command .
I had this same error, I had to log into the database and DROP TABLE port_groups. Then I ran lnms migrate again and validate.php. I didn’t have any more errors with lnms migrate or when logging into the GUI
The migration was successful after droping the port_groups table. I was able to log in in the GUI. So thank you!
However there is still a problem. When I run the validate.php:
CREATE TABLE device_perf (id int unsigned NOT NULL auto_increment, device_id int unsigned NOT NULL , timestamp datetime NOT NULL , xmt int NOT NULL , rcv int NOT NULL , loss int NOT NULL , min double(8,2) NOT NULL , max double(8,2) NOT NULL , avg double(8,2) NOT NULL , debug text NULL , PRIMARY KEY (id), INDEX device_perf_device_id_index (device_id), INDEX device_perf_device_id_timestamp_index (device_id,timestamp));
It makes sense that the table is missing since I’ve dropped it … but how do I create it ? What is the correct syntax here ?
cd /opt/librenms
cat config.php
mysql -u username -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1186890
Server version: 10.5.10-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use librenms
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [librenms]> CREATE TABLE `device_perf` (`id` int unsigned NOT NULL auto_increment, `device_id` int unsigned NOT NULL , `timestamp` datetime NOT NULL , `xmt` int NOT NULL , `rcv` int NOT NULL , `loss` int NOT NULL , `min` double(8,2) NOT NULL , `max` double(8,2) NOT NULL , `avg` double(8,2) NOT NULL , `debug` text NULL , PRIMARY KEY (`id`), INDEX `device_perf_device_id_index` (`device_id`), INDEX `device_perf_device_id_timestamp_index` (`device_id`,`timestamp`));
Query OK, 0 rows affected (0.16 sec)
I’ve solved the problem. Thank you for helping out.
I had the same issue earlier, and then solved following Ayeisha’s advice.
The question is:
Do we need to recreate the device_perf table ? is it required/necessary ?
In my case as per the posted logs above, when I tried to migrate after dropping the device_perf table. I got an error in the validation. LibreNMS was kind enough to give me the complete sql command to recreate the missing table. The GUI actually worked for me and I was able go log in and view the dashboards, but when I wanted to view a specific device there was an error and a really broken looking device page - I guess it was missing some of the info and was not finding/loading it properly.
From the config.php I got the details about the database connection and the name of the db and executed the sql command. It’s working fine now. If you have the same errors this would resolve them.
You are right. I had also the missing table notification when I ran validate.php, along with exact command to run inside mysql. It worked with no issues and the validation result came clean after that.
I just don’t remember which table was it, i think it was device_perf !