Error port device

baldmaggots · 9 April 2021 18:35

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.

rhinoau · 9 April 2021 22:54

Confirmed fully resolved with this, thanks @JhonathanDDF !

Valerio · 12 April 2021 08:45

Is it safe in a production environment?

Drop a whole table seems (to me) a risky action to lose some parts …

JhonathanDDF · 12 April 2021 10:19

yes, in my case i did a snapshop before the procedure and i had no problems.

newmy · 12 April 2021 12:47

tnx. fixed my installation after upgrade to 10.5.

Dennis_Knight · 12 April 2021 14:26

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$

JhonathanDDF · 12 April 2021 16:11

did you update mariadb before?

rhinoau · 13 April 2021 02:16

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.

PipoCanaja · 13 April 2021 07:13

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.

tigerdjohnson · 13 April 2021 15:56

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

walleyeguy · 13 April 2021 16:43

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.

walleyeguy · 13 April 2021 21:16

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.

jmandersson · 14 April 2021 05:55

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

Mark_O · 14 April 2021 07:49

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

jmandersson · 14 April 2021 10:32

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)

Mark_O · 14 April 2021 12:59

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.

tigerdjohnson · 14 April 2021 15:24

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.

jmandersson · 15 April 2021 05:12

Thank you, @Mark_O and @tigerdjohnson, we are now up and running again.

Zach · 18 April 2021 15:19

Thank you. This fixed the issue I was having.

system · 25 April 2021 15:19

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.