Update issues - missing 'time_left' on table 'ports_nac'

EagleOne42 · 19 February 2019 16:00

I’ve been having some issues and need some help fixing (my attempts have failed so far). This is my home install, so I would like to learn to fix this, but if all fails I can reinstall (and can run what ever to fix with no risk).

I had the CentOS 7 VM running LibreNMS powered off from Dec 15 until Jan 15 while away. After powering on I did get alerts via Slack, but noticed a week ago that they were not working anymore. The daily cron was failing as there were untracked files so I used the github-remove -d to clean them up and now the ./daily.sh will complete.

It looks like I’m missing some database info. What is the best way to approach adding them/fixing this? My attempts (not really knowing what I’m doing) got it to the point that alerts would trigger and send again but the alerts didn’t fill in the details. They would still contain “%title” and “%senerity”. I have restored the VM back to before my attempts. Here is what I currently get:

-bash-4.2$ ./validate.php

Component	Version
LibreNMS	1.48.1-70-g2eac72d
DB Schema	2018_07_03_091322_add_foreign_keys_to_wireless_sensors_table (129)
PHP	7.2.14
MySQL	5.5.60-MariaDB
RRDTool	1.4.8
SNMP	NET-SNMP 5.7.2
====================================

[OK] Composer Version: 1.8.4
[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: 2019_01_16_132200_add_vlan_and_elapsed_to_nac

In SchemaException.php line 82:

There is no column with name ‘time_left’ on table ‘ports_nac’.

-bash-4.2$

MariaDB [librenms]> select * from migrations;
±----±---------------------------------------------------------------------±------+
| id | migration | batch |
±----±---------------------------------------------------------------------±------+
| 1 | 2018_07_03_091314_create_access_points_table | 1 |
| 2 | 2018_07_03_091314_create_alert_device_map_table | 1 |
| 3 | 2018_07_03_091314_create_alert_group_map_table | 1 |
| 4 | 2018_07_03_091314_create_alert_log_table | 1 |
| 5 | 2018_07_03_091314_create_alert_rules_table | 1 |
| 6 | 2018_07_03_091314_create_alert_schedulables_table | 1 |
| 7 | 2018_07_03_091314_create_alert_schedule_table | 1 |
| 8 | 2018_07_03_091314_create_alert_template_map_table | 1 |
| 9 | 2018_07_03_091314_create_alert_templates_table | 1 |
| 10 | 2018_07_03_091314_create_alert_transport_groups_table | 1 |
| 11 | 2018_07_03_091314_create_alert_transport_map_table | 1 |
| 12 | 2018_07_03_091314_create_alert_transports_table | 1 |
| 13 | 2018_07_03_091314_create_alerts_table | 1 |
| 14 | 2018_07_03_091314_create_api_tokens_table | 1 |
| 15 | 2018_07_03_091314_create_application_metrics_table | 1 |
| 16 | 2018_07_03_091314_create_applications_table | 1 |
| 17 | 2018_07_03_091314_create_authlog_table | 1 |
| 18 | 2018_07_03_091314_create_bgpPeers_cbgp_table | 1 |
| 19 | 2018_07_03_091314_create_bgpPeers_table | 1 |
| 20 | 2018_07_03_091314_create_bill_data_table | 1 |
| 21 | 2018_07_03_091314_create_bill_history_table | 1 |
| 22 | 2018_07_03_091314_create_bill_perms_table | 1 |
| 23 | 2018_07_03_091314_create_bill_port_counters_table | 1 |
| 24 | 2018_07_03_091314_create_bill_ports_table | 1 |
| 25 | 2018_07_03_091314_create_bills_table | 1 |
| 26 | 2018_07_03_091314_create_callback_table | 1 |
| 27 | 2018_07_03_091314_create_cef_switching_table | 1 |
| 28 | 2018_07_03_091314_create_ciscoASA_table | 1 |
| 29 | 2018_07_03_091314_create_component_prefs_table | 1 |
| 30 | 2018_07_03_091314_create_component_statuslog_table | 1 |
| 31 | 2018_07_03_091314_create_component_table | 1 |
| 32 | 2018_07_03_091314_create_config_table | 1 |
| 33 | 2018_07_03_091314_create_customers_table | 1 |
| 34 | 2018_07_03_091314_create_dashboards_table | 1 |
| 35 | 2018_07_03_091314_create_dbSchema_table | 1 |
| 36 | 2018_07_03_091314_create_device_graphs_table | 1 |
| 37 | 2018_07_03_091314_create_device_group_device_table | 1 |
| 38 | 2018_07_03_091314_create_device_groups_table | 1 |
| 39 | 2018_07_03_091314_create_device_mibs_table | 1 |
| 40 | 2018_07_03_091314_create_device_oids_table | 1 |
| 41 | 2018_07_03_091314_create_device_perf_table | 1 |
| 42 | 2018_07_03_091314_create_device_relationships_table | 1 |
| 43 | 2018_07_03_091314_create_devices_attribs_table | 1 |
| 44 | 2018_07_03_091314_create_devices_perms_table | 1 |
| 45 | 2018_07_03_091314_create_devices_table | 1 |
| 46 | 2018_07_03_091314_create_entPhysical_state_table | 1 |
| 47 | 2018_07_03_091314_create_entPhysical_table | 1 |
| 48 | 2018_07_03_091314_create_entityState_table | 1 |
| 49 | 2018_07_03_091314_create_eventlog_table | 1 |
| 50 | 2018_07_03_091314_create_graph_types_table | 1 |
| 51 | 2018_07_03_091314_create_hrDevice_table | 1 |
| 52 | 2018_07_03_091314_create_ipsec_tunnels_table | 1 |
| 53 | 2018_07_03_091314_create_ipv4_addresses_table | 1 |
| 54 | 2018_07_03_091314_create_ipv4_mac_table | 1 |
| 55 | 2018_07_03_091314_create_ipv4_networks_table | 1 |
| 56 | 2018_07_03_091314_create_ipv6_addresses_table | 1 |
| 57 | 2018_07_03_091314_create_ipv6_networks_table | 1 |
| 58 | 2018_07_03_091314_create_juniAtmVp_table | 1 |
| 59 | 2018_07_03_091314_create_links_table | 1 |
| 60 | 2018_07_03_091314_create_loadbalancer_rservers_table | 1 |
| 61 | 2018_07_03_091314_create_loadbalancer_vservers_table | 1 |
| 62 | 2018_07_03_091314_create_locations_table | 1 |
| 63 | 2018_07_03_091314_create_mac_accounting_table | 1 |
| 64 | 2018_07_03_091314_create_mefinfo_table | 1 |
| 65 | 2018_07_03_091314_create_mempools_table | 1 |
| 66 | 2018_07_03_091314_create_mibdefs_table | 1 |
| 67 | 2018_07_03_091314_create_munin_plugins_ds_table | 1 |
| 68 | 2018_07_03_091314_create_munin_plugins_table | 1 |
| 69 | 2018_07_03_091314_create_netscaler_vservers_table | 1 |
| 70 | 2018_07_03_091314_create_notifications_attribs_table | 1 |
| 71 | 2018_07_03_091314_create_notifications_table | 1 |
| 72 | 2018_07_03_091314_create_ospf_areas_table | 1 |
| 73 | 2018_07_03_091314_create_ospf_instances_table | 1 |
| 74 | 2018_07_03_091314_create_ospf_nbrs_table | 1 |
| 75 | 2018_07_03_091314_create_ospf_ports_table | 1 |
| 76 | 2018_07_03_091314_create_packages_table | 1 |
| 77 | 2018_07_03_091314_create_pdb_ix_peers_table | 1 |
| 78 | 2018_07_03_091314_create_pdb_ix_table | 1 |
| 79 | 2018_07_03_091314_create_perf_times_table | 1 |
| 80 | 2018_07_03_091314_create_plugins_table | 1 |
| 81 | 2018_07_03_091314_create_poller_cluster_stats_table | 1 |
| 82 | 2018_07_03_091314_create_poller_cluster_table | 1 |
| 83 | 2018_07_03_091314_create_poller_groups_table | 1 |
| 84 | 2018_07_03_091314_create_pollers_table | 1 |
| 85 | 2018_07_03_091314_create_ports_adsl_table | 1 |
| 86 | 2018_07_03_091314_create_ports_fdb_table | 1 |
| 87 | 2018_07_03_091314_create_ports_nac_table | 1 |
| 88 | 2018_07_03_091314_create_ports_perms_table | 1 |
| 89 | 2018_07_03_091314_create_ports_stack_table | 1 |
| 90 | 2018_07_03_091314_create_ports_statistics_table | 1 |
| 91 | 2018_07_03_091314_create_ports_stp_table | 1 |
| 92 | 2018_07_03_091314_create_ports_table | 1 |
| 93 | 2018_07_03_091314_create_ports_vlans_table | 1 |
| 94 | 2018_07_03_091314_create_processes_table | 1 |
| 95 | 2018_07_03_091314_create_processors_table | 1 |
| 96 | 2018_07_03_091314_create_proxmox_ports_table | 1 |
| 97 | 2018_07_03_091314_create_proxmox_table | 1 |
| 98 | 2018_07_03_091314_create_pseudowires_table | 1 |
| 99 | 2018_07_03_091314_create_route_table | 1 |
| 100 | 2018_07_03_091314_create_sensors_table | 1 |
| 101 | 2018_07_03_091314_create_sensors_to_state_indexes_table | 1 |
| 102 | 2018_07_03_091314_create_services_table | 1 |
| 103 | 2018_07_03_091314_create_session_table | 1 |
| 104 | 2018_07_03_091314_create_slas_table | 1 |
| 105 | 2018_07_03_091314_create_state_indexes_table | 1 |
| 106 | 2018_07_03_091314_create_state_translations_table | 1 |
| 107 | 2018_07_03_091314_create_storage_table | 1 |
| 108 | 2018_07_03_091314_create_stp_table | 1 |
| 109 | 2018_07_03_091314_create_syslog_table | 1 |
| 110 | 2018_07_03_091314_create_tnmsneinfo_table | 1 |
| 111 | 2018_07_03_091314_create_toner_table | 1 |
| 112 | 2018_07_03_091314_create_transport_group_transport_table | 1 |
| 113 | 2018_07_03_091314_create_ucd_diskio_table | 1 |
| 114 | 2018_07_03_091314_create_users_prefs_table | 1 |
| 115 | 2018_07_03_091314_create_users_table | 1 |
| 116 | 2018_07_03_091314_create_users_widgets_table | 1 |
| 117 | 2018_07_03_091314_create_vlans_table | 1 |
| 118 | 2018_07_03_091314_create_vminfo_table | 1 |
| 119 | 2018_07_03_091314_create_vrf_lite_cisco_table | 1 |
| 120 | 2018_07_03_091314_create_vrfs_table | 1 |
| 121 | 2018_07_03_091314_create_widgets_table | 1 |
| 122 | 2018_07_03_091314_create_wireless_sensors_table | 1 |
| 123 | 2018_07_03_091322_add_foreign_keys_to_component_prefs_table | 1 |
| 124 | 2018_07_03_091322_add_foreign_keys_to_component_statuslog_table | 1 |
| 125 | 2018_07_03_091322_add_foreign_keys_to_device_group_device_table | 1 |
| 126 | 2018_07_03_091322_add_foreign_keys_to_device_relationships_table | 1 |
| 127 | 2018_07_03_091322_add_foreign_keys_to_sensors_table | 1 |
| 128 | 2018_07_03_091322_add_foreign_keys_to_sensors_to_state_indexes_table | 1 |
| 129 | 2018_07_03_091322_add_foreign_keys_to_wireless_sensors_table | 1 |
±----±---------------------------------------------------------------------±------+
129 rows in set (0.00 sec)

Chas · 19 February 2019 23:18

This post had the same missing columns Validate.php warns "Your database is out of date!"

Perhaps try the steps mentioned in there

EagleOne42 · 20 February 2019 15:08

Thanks - I did try that and following that and fixing other things that validate showed I can get alerts to send again, but that is where I get the alerts that don’t replace the details like "%title” and “%severity” I just setup email alerts too and get the same issue.

git checkout 1.47
./validate.php
./scripts/composer_wrapper.php install --no-dev

sql-schema/274.sql
sql-schema/275.sql

./validate.php

git checkout 1.48
./scripts/composer_wrapper.php install --no-dev

-bash-4.2$ ./lnms migrate

```
Application In Production!     *
```

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

yes

Migrating: 2019_01_16_132200_add_vlan_and_elapsed_to_nac
Migrated: 2019_01_16_132200_add_vlan_and_elapsed_to_nac
Migrating: 2019_01_16_195644_add_vrf_id_and_bgpLocalAs
Migrated: 2019_01_16_195644_add_vrf_id_and_bgpLocalAs

sql-schema/242.sql
sql-schema/1000.sql
sql-schema/277.sql
sql-schema/278.sql

-bash-4.2$ ./lnms migrate

```
Application In Production!     *
```

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

yes
In Connection.php line 647:

SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name ‘vlan’ (SQL: alter table ports_nac add vlan int un
signed null, add time_elapsed varchar(50) null)

alter table ports_nac drop column vlan;
alter table ports_nac drop column time_elapsed;

alter table bgpPeers drop column vrf_id;
alter table vrfs drop column bgpLocalAs;

    Run the following SQL statements to fix.
    SQL Statements:
     ALTER TABLE `alert_rules` DROP `query_builder`;
     ALTER TABLE `eventlog` CHANGE `device_id` `device_id` int(10) unsigned NULL ;
     ALTER TABLE `eventlog` CHANGE `reference` `reference` varchar(64) NULL ;
     ALTER TABLE `eventlog` CHANGE `severity` `severity` tinyint(4) NOT NULL DEFAULT '2' ;
     ALTER TABLE `eventlog` DROP `host`;
     ALTER TABLE `eventlog` DROP INDEX `host`;
     ALTER TABLE `sensors` ADD `group` varchar(255) NULL  AFTER `sensor_descr`;
     ALTER TABLE `sensors_to_state_indexes` DROP INDEX `sensors_to_state_indexes_ibfk_1`;
     ALTER TABLE `sensors_to_state_indexes` ADD INDEX `state_index_id` (`state_index_id`);
     DROP TABLE `graph_types_dead`;

EagleOne42 · 20 February 2019 17:15

Example of the alerts I’m seeing
Email subject is fine “Device ap-1 recovered from Is Down” but the body is not filled in:
%title Severity: %severity {if %state == 0}Time elapsed: %elapsed {/if}Timestamp: %timestamp Unique-ID: %uid Rule: {if %name}%name{else}%rule{/if} {if %faults}Faults: {foreach %faults} #%key: %value.string {/foreach}{/if}Alert sent to: {foreach %contacts}%value <%key> {/foreach}

Slack is the same - the subject is fine, but the rest of the alert is not filled. Anyone know where I can start for this? I don’t see any errors in the librenms.log around this time.

EagleOne42 · 4 March 2019 18:03

Looks like the last issue was that the template wasn’t updated. I think I missed this when rolling back to an old backup to before I updated them to the new format.

Looks like everything is working again.