Database Upadate Table issue Missing table. Manually recreate tables

bhaggu · 21 April 2021 12:53

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

zenoss_bpa · 26 April 2021 23:55

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

Mike_Fraser · 27 April 2021 20:30

Same issue here.

teguh.dhanu · 28 April 2021 09:42

mi librebnms had same issue, and I cannot login

seamanjeff · 30 April 2021 19:35

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

TxEdgeMan · 11 May 2021 20:51

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.

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

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 */;
Paste this into the mysql CLI. Do a ’ show tables;’ and you should now see a toner table. quit the mysql CLI.
librenms:~$ ./lnms migrate

Hope this helps…

lucasv92 · 22 June 2021 21:27

====================================
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!

umang42 · 7 July 2021 07:48

backup database safeside using mysqldump
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
run ./lnms migrate
Check by ./validate
Done

kwinz · 21 October 2021 08:46

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:

[email protected]:~$ ./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)

system · 25 April 2022 08:47

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

Database Upadate Table issue Missing table. Manually recreate tables

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

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