Still "Whoops, looks like something went wrong. Check your librenms.log"

Still “Whoops, looks like something went wrong. Check your librenms.log”

When running ./validate.php:

====================================

Component Version
LibreNMS 21.6.0
DB Schema 2021_02_09_122930_migrate_to_utf8mb4 (204)
PHP 7.4.20
Python 3.8.10
MySQL 10.5.10-MariaDB-1:10.5.10+maria~focal
RRDTool 1.7.2
SNMP NET-SNMP 5.8
====================================

[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

Ubuntu Version:

No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 20.04.2 LTS
Release: 20.04
Codename: focal

When running ./lnms migrate:


  • Application In Production!     *
    

Do you really wish to run this command? (yes/no) [no]:

y

Migrating: 2021_03_11_003540_rename_toner_table

In Connection.php line 678:

SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘librenms.toner’ doesn’t exist (SQL: rename table toner to printer_supplies)

In Exception.php line 18:

SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘librenms.toner’ doesn’t exist

In PDOStatement.php line 112:

SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘librenms.toner’ doesn’t exist

When checking status of MariaDB:

● mariadb.service - MariaDB 10.5.10 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: active (running) since Wed 2021-06-23 16:05:20 CEST; 19h ago
Docs: man:mariadbd(8)
systemd - MariaDB Knowledge Base
Process: 410 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
Process: 420 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 431 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=cd /usr/bin/..; /usr/bin/galera_recovery; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR |>
Process: 525 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 527 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
Main PID: 457 (mariadbd)
Status: “Taking your SQL requests now…”
Tasks: 20 (limit: 2200)
Memory: 574.5M
CGroup: /system.slice/mariadb.service
└─457 /usr/sbin/mariadbd

Jun 23 16:05:20 librenms systemd[1]: Started MariaDB 10.5.10 database server.
Jun 23 16:05:20 librenms /etc/mysql/debian-start[530]: Upgrading MySQL tables if necessary.
Jun 23 16:05:20 librenms /etc/mysql/debian-start[533]: /usr/bin/mysql_upgrade: the ‘–basedir’ option is always ignored
Jun 23 16:05:20 librenms /etc/mysql/debian-start[533]: Looking for ‘mysql’ as: /usr/bin/mysql
Jun 23 16:05:20 librenms /etc/mysql/debian-start[533]: Looking for ‘mysqlcheck’ as: /usr/bin/mysqlcheck
Jun 23 16:05:20 librenms /etc/mysql/debian-start[533]: This installation of MariaDB is already upgraded to 10.5.10-MariaDB, use --force if you still need to run mysql_upgrade
Jun 23 16:05:20 librenms /etc/mysql/debian-start[544]: Checking for insecure root accounts.
Jun 23 16:05:20 librenms /etc/mysql/debian-start[548]: Triggering myisam-recover for all MyISAM tables and aria-recover for all Aria tables
Jun 23 16:05:21 librenms mariadbd[457]: 2021-06-23 16:05:21 0 [Note] InnoDB: Buffer pool(s) load completed at 210623 16:05:21
Jun 23 16:10:53 librenms mariadbd[457]: 2021-06-23 16:10:53 36 [Warning] Aborted connection 36 to db: ‘librenms’ user: ‘librenms’ host: ‘localhost’ (Got an error reading communication packets)

Any suggestions?

Hi @Christer_Stanghelle
Seems that you faced the “unsupported MySQL” issue. And now your MariaDB is updated above the minimum version.
So you will have to fix the tables that failed to evolve because of this older MySQL you were running before.

It seems that the table “toner” is already renamed to printer_supplies, or missing. You’ll have to connect to your mysql server to find out.
If it is already renamed, I suggest to revert it to “toner” and run lnms migrate.

Thank you! Yes, I would love to rename the tables, but I do not seem to understandhow. After logging in to my SQL (logging in, “use librenms”, rename table toner to printer_supplies but to no avail. Is there a level of privileges that I am missing?

This is what it looks like for me when trying to edit something on my SQL:

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 50013
Server version: 10.5.10-MariaDB-1:10.5.10+maria~focal mariadb.org binary distribution

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]> show tables

every line must be ended with “;” …

so :
show tables;
instead of
show tables

Same for all SQL commands.

I am facepalming so hard here right now! THANK YOU! You just made my day, haha!
Can not believe i forgot about that…
Thank you!

1 Like

Now I ended up getting this:

In SchemaException.php line 86:
There is no column with name ‘toner_id’ on table ‘printer_supplies’.

Any suggestions here? I see others have the same issue.

check the columns in printer_supplies. Seems that it is already completely migrated to the new scheme and lnms migrate is failing to upgrade it again.
Please show :
describe printer_supplies;

It looks like this when the migration is done :

MariaDB [librenms]> describe printer_supplies;
+---------------------+------------------+------+-----+---------+----------------+
| Field               | Type             | Null | Key | Default | Extra          |
+---------------------+------------------+------+-----+---------+----------------+
| supply_id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| device_id           | int(10) unsigned | NO   | MUL | 0       |                |
| supply_index        | int(11)          | NO   |     | NULL    |                |
| supply_type         | varchar(64)      | NO   |     | NULL    |                |
| supply_oid          | varchar(64)      | NO   |     | NULL    |                |
| supply_descr        | varchar(255)     | NO   |     |         |                |
| supply_capacity     | int(11)          | NO   |     | 0       |                |
| supply_current      | int(11)          | NO   |     | 0       |                |
| supply_capacity_oid | varchar(64)      | YES  |     | NULL    |                |
+---------------------+------------------+------+-----+---------+----------------+

My output;

 MariaDB [librenms]> describe printer_supplies;
 +--------------------+------------------+------+-----+---------+----------------+
 | Field              | Type             | Null | Key | Default | Extra          |
 +--------------------+------------------+------+-----+---------+----------------+
 | supply_id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
 | device_id          | int(10) unsigned | NO   | MUL | 0       |                |
 | supply_index       | int(11)          | NO   |     | NULL    |                |
 | supply_type        | varchar(64)      | NO   |     | NULL    |                |
 | supply_oid         | varchar(64)      | NO   |     | NULL    |                |
 | toner_descr        | varchar(32)      | NO   |     |         |                |
 | toner_capacity     | int(11)          | NO   |     | 0       |                |
 | toner_current      | int(11)          | NO   |     | 0       |                |
 | toner_capacity_oid | varchar(64)      | YES  |     | NULL    |                |
 | supply_descr       | varchar(10)      | YES  |     | NULL    |                |
 +--------------------+------------------+------+-----+---------+----------------+
 10 rows in set (0.000 sec)

You are somewhere in the middle unfortunately. Easiest would probably be to return to the original state so the lnms migrate can happily do it again correctly.

You can find the original names here :
librenms@librenms:~/database/migrations/2021_03_11_003713_rename_printer_columns.php

Rename the columns to their original names and run lnms migrate again .

1 Like

You can also have a look here:

1 Like

I renamed them all and ran (first validate) lnms migrate, but got this, and then ran lnms migrate once again and went back to my first issue:

[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
librenms@librenms:~$ ./lnms migrate


  • Application In Production!     *
    

Do you really wish to run this command? (yes/no) [no]:

y

Migrating: 2021_03_11_003713_rename_printer_columns

In Connection.php line 678:

SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name ‘supply_descr’ (SQL: ALTER TABLE printer_supplies CHANGE toner_descr supply_descr VARCHAR(32) DEFAULT ‘’ NOT NULL)

In Exception.php line 18:

SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name ‘supply_descr’

In PDOStatement.php line 112:

SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name ‘supply_descr’

librenms@librenms:~$ ./lnms migrate


  • Application In Production!     *
    

Do you really wish to run this command? (yes/no) [no]:

y

Migrating: 2021_03_11_003713_rename_printer_columns

In SchemaException.php line 86:

There is no column with name ‘toner_id’ on table ‘printer_supplies’.

librenms@librenms:~$

Hi, more over this issue. I looked over Librenms.log and found this errors.

Error in printer-supplies module. SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘librenms.printer_supplies’ doesn’t exist (SQL: select * from printer_supplies where printer_supplies.device_id = 1 and printer_supplies.device_id is not null)

2021-06-25 10:05:37] production.ERROR: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘devices.device_id’ in ‘where clause’ (SQL: SELECT * FROM toner WHERE (devices.device_id = 100) AND toner.toner_current < 10 AND toner.toner_type = “toner”) (SQL: SELECT * FROM toner WHERE (devices.device_id = 100) AND toner.toner_current < 10 AND toner.toner_type = “toner”)#0 /opt/librenms/LibreNMS/Alert/AlertRules.php(69): dbFetchRows()

It appears to be as like the table printer_supplies was not present. But it is!!!

MariaDB [librenms]> desc printer_supplies;
±--------------------±-----------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±--------------------±-----------------±-----±----±--------±---------------+
| supply_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| device_id | int(10) unsigned | NO | MUL | 0 | |
| supply_index | int(11) | NO | | NULL | |
| supply_type | varchar(64) | NO | | NULL | |
| supply_oid | varchar(64) | NO | | NULL | |
| supply_descr | varchar(255) | YES | | NULL | |
| supply_capacity | int(11) | NO | | 0 | |
| supply_current | int(11) | NO | | 0 | |
| supply_capacity_oid | varchar(64) | YES | | NULL | |
±--------------------±-----------------±-----±----±--------±---------------+
9 rows in set (0.001 sec)

Suggestions are welcome :woozy_face:

Hi @Christer_Stanghelle
It is a try and fail multistep process. You need to feed lnms migrate with the expected state of DB so it runs successfully. You can check each migration file in the migrations directory. File name is given here :
Migrating: 2021_03_11_003713_rename_printer_columns

It means that the DB must be in the old status (with table toner) so that lnms migrate can change it to printer-supplies

1 Like

@PipoCanaja - Thank you so much for your help so far, I really appreciate it, but I haven’t solved the issue yet.
I renamed the DB to the old status, but when running lnms migrate it rearranges it back to the trouble-soup that I first started with.
I took line by line, made sure all was listed good, but to no avail.
I totally agree on that the DB must be in the old status in order to re-migrate it, but there seems to be something I am missing out on, and I can not for the good of me find out what it is.

Is there someone who has experienced this and that has a kind of recipe for commands to fix it?

I finally made it! LibreNMS is back online!

The devil was in the details, as usual.
The clue was to first DROP the ‘supply_descr’ before renaming them one by one.

ALTER TABLE printer_supplies DROP supply_descr;

After that, rename all tables one by one (don’t do all in one gulp, that will most likely be useless)

ALTER TABLE printer_supplies CHANGE toner_id supply_id INT UNSIGNED AUTO_INCREMENT NOT NULL;

ALTER TABLE printer_supplies CHANGE toner_index supply_index INT NOT NULL;

ALTER TABLE printer_supplies CHANGE toner_type supply_type VARCHAR(64) NOT NULL;

ALTER TABLE printer_supplies CHANGE toner_oid supply_oid VARCHAR(64) NOT NULL;
ALTER TABLE printer_supplies CHANGE toner_descr supply_descr VARCHAR(32) DEFAULT ‘’ NOT NULL;

ALTER TABLE printer_supplies CHANGE toner_capacity supply_capacity INT DEFAULT 0 NOT NULL;

ALTER TABLE printer_supplies CHANGE toner_current supply_current INT DEFAULT 0 NOT NULL;

ALTER TABLE printer_supplies CHANGE toner_capacity_oid supply_capacity_oid VARCHAR(64) DEFAULT NULL;

ALTER TABLE printer_supplies CHANGE supply_descr supply_descr VARCHAR(255) CHARACTER SET utf8mb4 DEFAULT NULL COLLATE utf8mb4_unicode_ci;

After that I ran ./validate and ./lnms migrate and got some new errors;

Table ‘librenms.port_group_port’ doesn’t exist

Then solved it with first running this (mysql -u librenms -p, use 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;

and:

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;

Then went on to this (still in sql, one by one):

SET FOREIGN_KEY_CHECKS = 0;

drop table port_group_port; drop table port_groups;

SET FOREIGN_KEY_CHECKS = 1;

Now my output looks like this:

MariaDB [librenms]> desc printer_supplies;
+---------------------+------------------+------+-----+---------+----------------+
| Field               | Type             | Null | Key | Default | Extra          |
+---------------------+------------------+------+-----+---------+----------------+
| supply_id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| device_id           | int(10) unsigned | NO   | MUL | 0       |                |
| supply_index        | int(11)          | NO   |     | NULL    |                |
| supply_type         | varchar(64)      | NO   |     | NULL    |                |
| supply_oid          | varchar(64)      | NO   |     | NULL    |                |
| supply_capacity     | int(11)          | NO   |     | 0       |                |
| supply_current      | int(11)          | NO   |     | 0       |                |
| supply_capacity_oid | varchar(64)      | YES  |     | NULL    |                |
| supply_descr        | varchar(255)     | NO   |     |         |                |
+---------------------+------------------+------+-----+---------+----------------+

====================================

Component Version
LibreNMS 21.6.0
DB Schema 2021_25_01_0127_create_isis_adjacencies_table (210)
PHP 7.4.20
Python 3.8.10
MySQL 10.5.11-MariaDB-1:10.5.11+maria~focal
RRDTool 1.7.2
SNMP NET-SNMP 5.8
====================================

[OK] Composer Version: 2.1.3
[OK] Dependencies up-to-date.
[OK] Database connection successful
[OK] Database schema correct
librenms@librenms:~$

I am really thankful to all the help I got from @PipoCanaja on this! Thank you very much!

1 Like

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