Database Upadate Table issue Missing table. Manually recreate tables

I am trying to update the MariaDB as per the latest release of LibreNMS. However during update something went wrong and the NMS is down now. Few details are as follows.

bash-4.2$ ./daily.sh
Fetching new release information OK
Updating to latest release OK
Updating Composer packages OK
Updating SQL-Schema OK
Updating submodules OK
Cleaning up DB OK
Fetching notifications OK
Caching PeeringDB data OK
bash-4.2$ ./validate.php

Component Version
LibreNMS 21.4.0
DB Schema 2021_02_09_122930_migrate_to_utf8mb4 (204)
PHP 7.4.12
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
bash-4.2$ ./lnms migrate


  • Application In Production!     *
    

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

yes

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

bash-4.2$ ./lnms migrate --pretend


  • Application In Production!     *
    

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

yes

RenameTonerTable: rename table toner to printer_supplies

In SchemaException.php line 86:

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

bash-4.2$

While checking the Database, I found that “printer_supplies” or “toner” table is not there at all. Can someone help me provide command to create the table again. ( I am not DB or Linux expert ) Moreover there is a missing “Syslog” table as well.

Can someone provide the commands to recreate those tables, so I can move ahead in migration / troubleshoot .

Bhargav

i’m having the same issue. anyone can help?

1 Like

Same issue here.

1 Like

mi librebnms had same issue, and I cannot login

1 Like

Yeah somehow an update was pushed that scrozzled all of us. I am in the same boat.

2 Likes

This workaround to manually recreate the toner table got us going again. We don’t store toner/printer info in our instance, so having an empty table is no big deal.

  1. Make sure you don’t have either the toner or the printer_supplies table

librenms:~$ mysql -ulibrenms -p
Enter password:
then
use librenms;
show tables;

If you see printer_supplies, you can ALTER the table name to toner (alter table printer_supplies rename to toner;) and rerun lnms_migrate

  1. Pull the create table toner from the database schema file from source code:
    https://github.com/librenms/librenms/blob/master/database/schema/mysql-schema.dump
    Currently, it looks like this:
    DROP TABLE IF EXISTS toner;
    /*!40101 SET @saved_cs_client = @@character_set_client /;
    /
    !40101 SET character_set_client = utf8 /;
    CREATE TABLE toner (
    toner_id int(10) unsigned NOT NULL AUTO_INCREMENT,
    device_id int(10) unsigned NOT NULL DEFAULT ‘0’,
    toner_index int(11) NOT NULL,
    toner_type varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
    toner_oid varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
    toner_descr varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ‘’,
    toner_capacity int(11) NOT NULL DEFAULT ‘0’,
    toner_current int(11) NOT NULL DEFAULT ‘0’,
    toner_capacity_oid varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    PRIMARY KEY (toner_id),
    KEY toner_device_id_index (device_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    /
    !40101 SET character_set_client = @saved_cs_client */;

  2. Paste this into the mysql CLI. Do a ’ show tables;’ and you should now see a toner table. quit the mysql CLI.

  3. librenms:~$ ./lnms migrate

Hope this helps


1 Like

====================================
Component | Version
--------- | -------
LibreNMS | 21.6.0-8-g748186c05
DB Schema | 2021_03_11_003540_rename_toner_table (204)
PHP | 7.4.20
Python | 3.6.9
MySQL | 10.5.10-MariaDB-1:10.5.10+maria~bionic
RRDTool | 1.7.0
SNMP | NET-SNMP 5.7.3
====================================

[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

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

Hi, i’m stuck in a similar situation.

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

I droped Printer_supplies table and recreated toner table without success.

I also run the table update procees manually

rename table toner to printer_supplies;
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;

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 | |
±--------------------±-----------------±-----±----±--------±---------------+

the same error message is showed runing the migration.

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

There is any way to remove this table conversion from the migration process?

Thanks!

  1. backup database safeside using mysqldump
  2. upgrade mysql to 10.5 → follow
     How to upgrade MySQL 5.5 to 5.6/5.7 or MariaDB 5.5 to 10.x on Linux? – Plesk Help Center
  3. run ./lnms migrate
  4. Check by ./validate
    Done

Here is what fixed it for me:

I determined that the update script had basically already renamed the columns but was still looking for the old column names in the new table. So I checked what columns the migration was expecting by looking at https://github.com/librenms/librenms/blob/7c157c1577c7d7dceb01269114f100d08609b911/database/migrations/2021_03_11_003713_rename_printer_columns.php.

Renaming the columns is not easy below MariaDB 10.5.2 which Ubuntu 20.04.3 LTS does not yet have. You need to repeat the datatypes from the schema in every rename. I got this information via mysqldump --user=librenms --password librenms printer_supplies

And then basically reverted column names to what the migration script is looking for by executing the following SQL commands:

use librenms;
alter table printer_supplies change column supply_index toner_index int(11) NOT NULL;
alter table printer_supplies change column supply_type toner_type varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL;
alter table printer_supplies change column supply_oid toner_oid  varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL;
alter table printer_supplies change column supply_descr toner_descr  varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '';
alter table printer_supplies change column supply_capacity toner_capacity int(11) NOT NULL DEFAULT 0;
alter table printer_supplies change column supply_current toner_current int(11) NOT NULL DEFAULT 0;
alter table printer_supplies change column supply_capacity_oid toner_capacity_oid varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL;

after that the migration script ran without problems:

librenms@nms01nessus:~$ ./lnms migrate
**************************************
*     Application In Production!     *
**************************************

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

Migrating: 2021_03_11_003713_rename_printer_columns
Migrated:  2021_03_11_003713_rename_printer_columns (129.67ms)
Migrating: 2021_03_26_014054_change_cache_to_mediumtext
Migrated:  2021_03_26_014054_change_cache_to_mediumtext (1,398.70ms)
Migrating: 2021_04_08_151101_add_foreign_keys_to_port_group_port_table
Migrated:  2021_04_08_151101_add_foreign_keys_to_port_group_port_table (367.73ms)
Migrating: 2021_06_07_123600_create_sessions_table
Migrated:  2021_06_07_123600_create_sessions_table (215.46ms)
Migrating: 2021_06_11_084830_slas_add_rtt_field
Migrated:  2021_06_11_084830_slas_add_rtt_field (211.41ms)
Migrating: 2021_07_06_1845_alter_bill_history_max_min
Migrated:  2021_07_06_1845_alter_bill_history_max_min (149.91ms)
Migrating: 2021_08_04_102914_add_syslog_indexes
Migrated:  2021_08_04_102914_add_syslog_indexes (89.68ms)
Migrating: 2021_08_26_093522_config_value_to_medium_text
Migrated:  2021_08_26_093522_config_value_to_medium_text (137.92ms)
Migrating: 2021_09_07_094310_create_push_subscriptions_table
Migrated:  2021_09_07_094310_create_push_subscriptions_table (132.24ms)
Migrating: 2021_09_26_164200_create_hrsystem_table
Migrated:  2021_09_26_164200_create_hrsystem_table (69.43ms)
Migrating: 2021_10_02_190310_add_device_outages_index
Migrated:  2021_10_02_190310_add_device_outages_index (216.07ms)
Migrating: 2021_10_03_164200_update_hrsystem_table
Migrated:  2021_10_03_164200_update_hrsystem_table (68.97ms)
Migrating: 2021_25_01_0127_create_isis_adjacencies_table
Migrated:  2021_25_01_0127_create_isis_adjacencies_table (50.28ms)
Migrating: 2021_25_01_0128_isis_adjacencies_add_admin_status
Migrated:  2021_25_01_0128_isis_adjacencies_add_admin_status (5.51ms)
Migrating: 2021_25_01_0129_isis_adjacencies_nullable
Migrated:  2021_25_01_0129_isis_adjacencies_nullable (64.67ms)
1 Like

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