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.
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
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.
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.
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$
did you update mariadb before?
It’s a lookup table, I’ve never used the feature and don’t know when it was introduced.
Have you used this section Under Port → Manage Groups?
You can define groups there, and then assign them to ports when editing a device:
From there they are used when constructing custom graphs I think, a bit like custom version of Transit/Peering/Customer etc. port groups.
And a migration script will recreate it just after so at the end of the migration process, you should end up with those tables anyway. If not, ./validate.php will tell you.
For me, ./lnms migrate was unable to recreate the two tables after dropping them. For anyone running into the same, here are the table schemas to quickly recreate them.
port_groups:
CREATE TABLE `port_groups` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`desc` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `port_groups_name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
port_group_port:
CREATE TABLE `port_group_port` (
`port_group_id` int(10) unsigned NOT NULL,
`port_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`port_group_id`,`port_id`),
KEY `port_group_port_port_group_id_index` (`port_group_id`),
KEY `port_group_port_port_id_index` (`port_id`),
CONSTRAINT `port_group_port_port_group_id_foreign` FOREIGN KEY (`port_group_id`) REFERENCES `port_groups` (`id`) ON DELETE CASCADE,
CONSTRAINT `port_group_port_port_id_foreign` FOREIGN KEY (`port_id`) REFERENCES `ports` (`port_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Run ./lnms migrate again, and it should complete.
*Edited to make sure backticks show
What is the context for recreating these schemas? I am incredibly lost when it comes to db functions. I am still having the problem with ./lnms migrate, and getting the error about port groups as you are showing here.
I appreciate you sharing the commands, I literally have just a hair over zero experience with the sql stuff and it freaks me out (and I dont think I am alone in that). I figured out that I had to use the mysql tool. So for others out there maybe in the same boat as me- I ran the command mysql -u librenms -p librenms then I added everything that @tigerdjohnson had as above and when i completed those steps I was able to run ./lnms migrate and everything is back to happy land. The missing piece for me what where do the commands go and or is there a file that needs to have this added to, it is a real feeling of being lost and alone. I appreciate everyone’s transparency in sharing. Hopefully my last two cents here will help someone with the same amount of mysql experience as me. 
Hi Guys,
Same problem here of course. But it seems that I can’t fix it.
Database upgraded and ./lnms migrate has been ran:
| Component | Version |
|---|---|
| LibreNMS | 21.3.0-98-g3a29198 |
| DB Schema | 2021_02_09_122930_migrate_to_utf8mb4 (207) |
| PHP | 7.3.27 |
| 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 have used tested the SQL commands from JhonathanDDF but they fail and the following ./lnms migrate also fails:
Any idea how to fix this?
Best regards,
Johan
Hi Johan,
I had the same, the problem is pretty much what you see - the port_group_port table is not there for the lnms script to alter.
My fix was (Thanks to tigerdjohnson):
Login to mysql
libreuser@libreserver:~$ mysql -p
After entering your password select the librenms database
MariaDB [(none)]> use librenms;
At this prompt
MariaDB [librenms]>
Paste the port_group_port table creation schema as per tigerdjohnson’s reply above
CREATE TABLE port_group_port (
port_group_id int(10) unsigned NOT NULL,
port_id int(10) unsigned NOT NULL,
PRIMARY KEY (port_group_id,port_id),
KEY port_group_port_port_group_id_index (port_group_id),
KEY port_group_port_port_id_index (port_id),
CONSTRAINT port_group_port_port_group_id_foreign FOREIGN KEY (port_group_id) REFERENCES port_groups (id) ON DELETE CASCADE,
CONSTRAINT port_group_port_port_id_foreign FOREIGN KEY (port_id) REFERENCES ports (port_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
All the best,
Mark
