DB schema isn't staying up to date

A recent update resulted in a librenms log full of errors related to the database. I was able to fix many of them by running SQL commands I found by running the lnms migrate --pretend command (lnms migrate itself didn’t work). Although librenms is now functional, I’m still getting some errors when I run validate.php and when I try to use the lnms migrate command.

[[email protected] librenms]$ sudo ./validate.php
[sudo] password for sbald:
/etc/snmp/snmp.conf: line 2: Warning: Unknown token: com2sec.
/etc/snmp/snmp.conf: line 4: Warning: Unknown token: group.
/etc/snmp/snmp.conf: line 5: Warning: Unknown token: view.
/etc/snmp/snmp.conf: line 6: Warning: Unknown token: access.
/etc/snmp/snmp.conf: line 8: Warning: Unknown token: syslocation.
/etc/snmp/snmp.conf: line 9: Warning: Unknown token: syscontact.
/etc/snmp/snmp.conf: line 12: Warning: Unknown token: extend.
====================================
Component | Version
--------- | -------
LibreNMS  | 1.69
DB Schema | 2020_07_27_00522_alter_devices_snmp_algo_columns (173)
PHP       | 7.2.24
Python    | 3.6.8
MySQL     | 10.3.17-MariaDB
RRDTool   | 1.7.0
SNMP      | NET-SNMP 5.8
OpenSSL   |
====================================

[OK]    Composer Version: 1.10.17
[OK]    Dependencies up-to-date.
[OK]    Database connection successful
[FAIL]  Your database is out of date!
        [FIX]:
        ./lnms migrate
/etc/snmp/snmp.conf: line 2: Warning: Unknown token: com2sec.
/etc/snmp/snmp.conf: line 4: Warning: Unknown token: group.
/etc/snmp/snmp.conf: line 5: Warning: Unknown token: view.
/etc/snmp/snmp.conf: line 6: Warning: Unknown token: access.
/etc/snmp/snmp.conf: line 8: Warning: Unknown token: syslocation.
/etc/snmp/snmp.conf: line 9: Warning: Unknown token: syscontact.
/etc/snmp/snmp.conf: line 12: Warning: Unknown token: extend.
[WARN]  PHP version 7.3 is the minimum supported version as of November, 2020. We recommend you update PHP to a supported version (7.4 suggested) to continue to receive updates. If you do not update PHP, LibreNMS will continue to function but stop receiving bug fixes and updates.
[WARN]  Your local git contains modified files, this could prevent automatic updates.
        [FIX]:
        You can fix this with ./scripts/github-remove
        Modified Files:
         mibs/tait/TAIT-COMMON-MIB
         mibs/tait/TAIT-INFRA93-94SERIES-COMMON-MIB
         mibs/tait/TAIT-INFRA93-94SERIES-TC-MIB
         mibs/tait/TAIT-INFRA93SERIES-MIB
         mibs/tait/TAIT-INFRA93SERIES-TC-MIB
         mibs/tait/TAIT-TN9300-MIB
         mibs/tait/TAIT-TN9300-TC
[[email protected] librenms]$ sudo -u librenms ./lnms migrate
[sudo] password for sbald:
/etc/snmp/snmp.conf: line 2: Warning: Unknown token: com2sec.
/etc/snmp/snmp.conf: line 4: Warning: Unknown token: group.
/etc/snmp/snmp.conf: line 5: Warning: Unknown token: view.
/etc/snmp/snmp.conf: line 6: Warning: Unknown token: access.
/etc/snmp/snmp.conf: line 8: Warning: Unknown token: syslocation.
/etc/snmp/snmp.conf: line 9: Warning: Unknown token: syscontact.
/etc/snmp/snmp.conf: line 12: Warning: Unknown token: extend.
**************************************
*     Application In Production!     *
**************************************

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

Migrating: 2020_08_28_212054_drop_uptime_column_outages

In Connection.php line 671:

  SQLSTATE[42S02]: Base table or view not found: 1146 Table 'librenms.device_outages' doesn't exist (SQL: alter table `device_outages` drop `uptime`)


In PDOStatement.php line 131:

  SQLSTATE[42S02]: Base table or view not found: 1146 Table 'librenms.device_outages' doesn't exist


In PDOStatement.php line 129:

  SQLSTATE[42S02]: Base table or view not found: 1146 Table 'librenms.device_outages' doesn't exist


How can I get the db schema up to date and ensure it stays that way with future updates?

Hi @Shawn_B

  • First issue I see, you did run the validate script in “sudo” meaning root. This is not good at all. All script must be run as LibreNMS users.
  • Secondly, you have an issue in your snmp config. You mispelled snmpd.conf and wrote snmp.conf … Explaining all the errors on the SNMP side.

I would go, as the LibreNMS user, in /opt/librenms. And run ./daily.sh and get back to a stable state. (meaning removing all files that were already half upgraded). Then, running the upgrades and DB migrations should succeed.

Hi @PipoCanaja, thank you for responding. Where do I have snmpd.conf mispelled? I see both snmp.conf and snmpd.conf in the snmp.d directory.

I tried running daily.sh, validate.php and lnms migrate all as the librenms user, and continue to get the same error:

Migrating: 2020_08_28_212054_drop_uptime_column_outages

In Connection.php line 671:

SQLSTATE[42S02]: Base table or view not found: 1146 Table ‘librenms.device_outages’ doesn’t exist (SQL: alter table device_outages drop uptime)

In PDOStatement.php line 131:

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

In PDOStatement.php line 129:

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

I notice in /opt/librenms/database/migrations there is a file called 2020_06_06_222222_create_device_outages_table.php. Would running that help to create the device outages table that the migration process is trying to delete? And also, how does the database schema get messed up like this to begin with? It’s making me nervous to rely on this in production, although I wouldn’t be surprised if I just messed up something with the initial installation to cause this.

Looking at some of the errors I see, it appears that the file snmp.conf contains directives that are meant for snmpd.conf. That’s why I suppose something got messed up there :


/etc/snmp/snmp.conf: line 2: Warning: Unknown token: com2sec.
/etc/snmp/snmp.conf: line 4: Warning: Unknown token: group.
/etc/snmp/snmp.conf: line 5: Warning: Unknown token: view.
/etc/snmp/snmp.conf: line 6: Warning: Unknown token: access.
/etc/snmp/snmp.conf: line 8: Warning: Unknown token: syslocation.
/etc/snmp/snmp.conf: line 9: Warning: Unknown token: syscontact.
/etc/snmp/snmp.conf: line 12: Warning: Unknown token: extend.

That being said, yes, you ended up with missing migration files that were not run but are marked by the system as run. I cannot tell what was done wrong during the process (updates, etc) to get to that point. It does not happen in a normal setup.

You probably have to run again 2020_06_06_222222_create_device_outages_table.php as you already found out, so the table can be created, and later on modified successfully.

Did you have daily running every night and upgrading your LibreNMS instance, or did you do something “special” with this LibreNMS instance ?

Thanks for the help Pipo. I ended up resolving this by deleting the snmp.conf file from /etc/snmp/ (there was already another file in there called snmpd.conf), and by doing a series of commands like this:

insert into migrations(migration, batch) values('2020_09_24_000500_create_cache_locks_table
',1);

That told the system that the migrations it was trying to perform that had apparently already been performed were complete so I could move forward. Then, validate.php identified a series of changes that needed to be to the made to the DB with SQL commands. After executing those, all is well again.

The only thing I did to tweak my install from the defaults was to change from daily updates to the monthly release schedule that I understood to be more stable. Could that have caused these issues with the DB schema?

Good news. Yes, the “stable release” is indeed more stable than the “master release” considering it does get tested first. But I have a few setups running master and they did not have the DB issue. So something else probably occured first (probably a crash or another interruption during the upgrade process that takes place every night. ).

1 Like