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$
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.
1 Like
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.
1 Like
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
7 Likes
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;
- Quit mysql and run ./lnms migrate again.
All the best,
Mark
1 Like
Thanks Mark!
I run into problems when running tigerdjohansons commands. We seem to miss the port_group table and get error when trying to create it. Any idea?
Database changed
MariaDB [librenms]> 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;
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 ‘desc varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (id),
…’ at line 4
(edit: We started with mariaDB 5.6 (?) and I upgraded it to 10.5.9-MariaDB)
No worries Johan 
,
Someone correct me if I’m wrong here but I think Mysql thinks ‘desc’ in line 4 is the “sort descending” attribute used in the ORDER BY command and not a simple table field title that we want.
I didnt have this issue because in my case, after I dropped the port_groups and port_group_port tables the first run of ./lmns migrate created the port_groups table, so I only needed to run the port_group_port table schema.
Do you know for sure if you’re port_groups table hasn’t already been created? The mysql command show tables; will list it or you can check if it exists (and its fields) with the describe port_groups; command.
This is where I’d get stuck too. 
D’oh, I didn’t notice the backticks got removed in my original post. @jmandersson try surrounding ‘desc’ with backticks like so:
`desc`
I’ll update my original post to fix that.
1 Like
Thank you. This fixed the issue I was having. 
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.