I had an issue start last week that seemed to break the connection to the mysql database. I restored from backup and have found that whenever the server runs daily.sh it breaks again.
To recreate the issue: 1. Boot and run validate.php and get message that schema is older than latest.
root@nms:/opt/librenms# ./validate.php
Component
Version
LibreNMS
1.43-38-g9f0afa2
DB Schema
262
PHP
7.0.32-0ubuntu0.16.04.1
MySQL
10.0.36-MariaDB-0ubuntu0.16.04.1
RRDTool
1.5.5
SNMP
NET-SNMP 5.7.3
====================================
[OK] Composer Version: 1.7.2
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] Your database schema (262) is older than the latest (265).
2. Run ./daily.sh and get error output:
root@nms:/opt/librenms# ./daily.sh
Re-running /opt/librenms/daily.sh as librenms user
Updating to latest codebase OK
Updating Composer packages OK
Updated from 9f0afa2 to 41cc344 OK
Updating SQL-Schema FAIL
– Updating database schema
262 -> 263 …SQL Error! SQLSTATE[42000]: Syntax error or access violation: 1091 Can’t DROP ‘access_level’; check that column/key exists (SQL: ALTER TABLE devices_perms DROP access_level
/opt/librenms/includes/sql-schema/update.php:92
SQL Error! SQLSTATE[42000]: Syntax error or access violation: 1068 Multiple primary key defined (SQL: ALTER TABLE device_perf ADD PRIMARY KEY (id)
/opt/librenms/includes/sql-schema/update.php:92
SQL Error! SQLSTATE[42000]: Syntax error or access violation: 1091 Can’t DROP ‘id’; check that column/key exists (SQL: ALTER TABLE device_perf DROP INDEX id
/opt/librenms/includes/sql-schema/update.php:92
SQL Error! SQLSTATE[HY000]: General error: 2006 MySQL server has gone away (SQL: ALTER TABLE eventlog CHANGE severityseverity tinyint(4) NULL DEFAULT ‘2’
/opt/librenms/includes/sql-schema/update.php:92 ********************************** Cut a bunch more lines with “SQL Error! SQLSTATE[HY000]: General error: 2006 MySQL server has gone away” **********************************
SQL Error! Could not connect to database! SQLSTATE[HY000] [2002] Connection refused (SQL: UPDATE dbSchema set version=263)
/opt/librenms/includes/sql-schema/update.php:112
263 -> 264 …PHP Fatal error: Uncaught Error: Call to a member function exec() on null in /opt/librenms/includes/dbFacile.php:77
Stack trace: #0 /opt/librenms/includes/sql-schema/update.php(92): dbQuery(‘ALTER TABLE `pd…’) #1 {main}
thrown in /opt/librenms/includes/dbFacile.php on line 77
Fatal error: Uncaught Error: Call to a member function exec() on null in /opt/librenms/includes/dbFacile.php:77
Stack trace: #0 /opt/librenms/includes/sql-schema/update.php(92): dbQuery(‘ALTER TABLE `pd…’) #1 {main}
thrown in /opt/librenms/includes/dbFacile.php on line 77
Updating submodules OK
Cleaning up DB OK
Fetching notifications OK
Caching PeeringDB data OK
root@nms:/opt/librenms#
This server has been running for over a year with no issues and just broke out of nowhere. Can someone please assist?
Thank you!
Not sure why you are seeing this unless maybe you’ve tested the pull request that included 263 schema change or you’ve made changes manually.
I’d suggest to update manually here:
Login to mysql as well so you have a shell open to your linux box + mysql.
cd /opt/librenms
git remote update
git pull origin master
Then in mysql do:
UPDATE dbSchema SET version=263;
Then run php includes/sql-schema/update.php
Now line by line paste these in, ignore any errors but if you find mysql crashes at one of them just restart mysql and carry on after that last line:
ALTER TABLE `access_points` CHANGE `channel` `channel` tinyint(3) unsigned NOT NULL DEFAULT '0' ;
ALTER TABLE `bill_data` CHANGE `delta` `delta` bigint(20) NOT NULL ;
ALTER TABLE `bill_data` CHANGE `in_delta` `in_delta` bigint(20) NOT NULL ;
ALTER TABLE `bill_data` CHANGE `out_delta` `out_delta` bigint(20) NOT NULL ;
ALTER TABLE `component` CHANGE `id` `id` int(10) unsigned NOT NULL auto_increment;
ALTER TABLE `component` CHANGE `device_id` `device_id` int(10) unsigned NOT NULL ;
ALTER TABLE `component_prefs` CHANGE `id` `id` int(10) unsigned NOT NULL auto_increment;
ALTER TABLE `component_prefs` CHANGE `component` `component` int(10) unsigned NOT NULL ;
ALTER TABLE `component_statuslog` CHANGE `id` `id` int(10) unsigned NOT NULL auto_increment;
ALTER TABLE `component_statuslog` CHANGE `component_id` `component_id` int(10) unsigned NOT NULL ;
ALTER TABLE `dashboards` CHANGE `access` `access` tinyint(1) NOT NULL DEFAULT '0' ;
ALTER TABLE `devices` CHANGE `device_id` `device_id` int(10) unsigned NOT NULL auto_increment;
ALTER TABLE `devices` CHANGE `status` `status` tinyint(1) NOT NULL DEFAULT '0' ;
ALTER TABLE `devices` CHANGE `ignore` `ignore` tinyint(1) NOT NULL DEFAULT '0' ;
ALTER TABLE `devices_perms` DROP `access_level`;
ALTER TABLE `device_groups` CHANGE `id` `id` int(10) unsigned NOT NULL auto_increment;
ALTER TABLE `device_perf` CHANGE `id` `id` int(10) unsigned NOT NULL auto_increment;
ALTER TABLE `device_perf` CHANGE `xmt` `xmt` int(11) NOT NULL ;
ALTER TABLE `device_perf` CHANGE `rcv` `rcv` int(11) NOT NULL ;
ALTER TABLE `device_perf` CHANGE `loss` `loss` int(11) NOT NULL ;
ALTER TABLE `device_perf` CHANGE `min` `min` double(8,2) NOT NULL ;
ALTER TABLE `device_perf` CHANGE `max` `max` double(8,2) NOT NULL ;
ALTER TABLE `device_perf` CHANGE `avg` `avg` double(8,2) NOT NULL ;
ALTER TABLE `device_perf` ADD PRIMARY KEY (`id`);
ALTER TABLE `device_perf` DROP INDEX `id`;
ALTER TABLE `device_relationships` CHANGE `parent_device_id` `parent_device_id` int(10) unsigned NOT NULL DEFAULT '0' ;
ALTER TABLE `device_relationships` CHANGE `child_device_id` `child_device_id` int(10) unsigned NOT NULL ;
ALTER TABLE `eventlog` CHANGE `severity` `severity` tinyint(4) NULL DEFAULT '2' ;
ALTER TABLE `ipv4_addresses` DROP INDEX `interface_id_2`;
ALTER TABLE `ipv6_addresses` DROP INDEX `interface_id_2`;
ALTER TABLE `links` CHANGE `active` `active` tinyint(1) NOT NULL DEFAULT '1' ;
ALTER TABLE `locations` CHANGE `lat` `lat` double(10,6) NOT NULL ;
ALTER TABLE `locations` CHANGE `lng` `lng` double(10,6) NOT NULL ;
ALTER TABLE `locations` ADD PRIMARY KEY (`id`);
ALTER TABLE `locations` DROP INDEX `id`;
ALTER TABLE `mefinfo` CHANGE `mefID` `mefID` int(11) NOT NULL ;
ALTER TABLE `mefinfo` CHANGE `mefMTU` `mefMTU` int(11) NOT NULL DEFAULT '1500' ;
ALTER TABLE `mempools` CHANGE `mempool_used` `mempool_used` bigint(20) NOT NULL ;
ALTER TABLE `mempools` CHANGE `mempool_free` `mempool_free` bigint(20) NOT NULL ;
ALTER TABLE `mempools` CHANGE `mempool_total` `mempool_total` bigint(20) NOT NULL ;
ALTER TABLE `mempools` CHANGE `mempool_largestfree` `mempool_largestfree` bigint(20) NULL ;
ALTER TABLE `mempools` CHANGE `mempool_lowestfree` `mempool_lowestfree` bigint(20) NULL ;
ALTER TABLE `munin_plugins` CHANGE `mplug_total` `mplug_total` tinyint(1) NOT NULL DEFAULT '0' ;
ALTER TABLE `munin_plugins` CHANGE `mplug_graph` `mplug_graph` tinyint(1) NOT NULL DEFAULT '1' ;
ALTER TABLE `netscaler_vservers` CHANGE `vsvr_port` `vsvr_port` int(11) NOT NULL ;
ALTER TABLE `ports_fdb` CHANGE `port_id` `port_id` int(10) unsigned NOT NULL ;
ALTER TABLE `poller_cluster_stats` CHANGE `depth` `depth` int(10) unsigned NOT NULL ;
ALTER TABLE `poller_cluster_stats` CHANGE `devices` `devices` int(10) unsigned NOT NULL ;
ALTER TABLE `poller_cluster_stats` CHANGE `workers` `workers` int(10) unsigned NOT NULL ;
ALTER TABLE `poller_cluster_stats` CHANGE `frequency` `frequency` int(10) unsigned NOT NULL ;
ALTER TABLE `ports_fdb` CHANGE `vlan_id` `vlan_id` int(10) unsigned NOT NULL ;
ALTER TABLE `ports_fdb` CHANGE `device_id` `device_id` int(10) unsigned NOT NULL ;
ALTER TABLE `ports_perms` DROP `access_level`;
ALTER TABLE `ports_vlans` CHANGE `priority` `priority` bigint(20) NOT NULL ;
ALTER TABLE `ports_vlans` CHANGE `untagged` `untagged` tinyint(1) NOT NULL DEFAULT '0' ;
ALTER TABLE `processes` CHANGE `pid` `pid` int(11) NOT NULL ;
ALTER TABLE `processes` CHANGE `vsz` `vsz` int(11) NOT NULL ;
ALTER TABLE `processes` CHANGE `rss` `rss` int(11) NOT NULL ;
ALTER TABLE `processors` DROP INDEX `device_id_2`;
ALTER TABLE `route` CHANGE `ipRouteDest` `ipRouteDest` varchar(39) NOT NULL ;
ALTER TABLE `route` CHANGE `ipRouteNextHop` `ipRouteNextHop` varchar(39) NOT NULL ;
ALTER TABLE `sensors` CHANGE `device_id` `device_id` int(10) unsigned NOT NULL DEFAULT '0' ;
ALTER TABLE `sensors` CHANGE `sensor_current` `sensor_current` double NULL ;
ALTER TABLE `sensors` CHANGE `sensor_limit` `sensor_limit` double NULL ;
ALTER TABLE `sensors` CHANGE `sensor_limit_warn` `sensor_limit_warn` double NULL ;
ALTER TABLE `sensors` CHANGE `sensor_limit_low` `sensor_limit_low` double NULL ;
ALTER TABLE `sensors` CHANGE `sensor_limit_low_warn` `sensor_limit_low_warn` double NULL ;
ALTER TABLE `sensors` CHANGE `sensor_prev` `sensor_prev` double NULL ;
ALTER TABLE `state_translations` CHANGE `state_value` `state_value` smallint(6) NOT NULL DEFAULT '0' ;
ALTER TABLE `storage` DROP INDEX `device_id_2`;
ALTER TABLE `tnmsneinfo` CHANGE `neID` `neID` int(11) NOT NULL ;
ALTER TABLE `ucd_diskio` DROP INDEX `device_id_2`;
ALTER TABLE `users` CHANGE `can_modify_passwd` `can_modify_passwd` tinyint(1) NOT NULL DEFAULT '1' ;
ALTER TABLE `users_prefs` CHANGE `user_id` `user_id` int(11) NOT NULL ;
ALTER TABLE `users_prefs` DROP INDEX `user_id.pref`;
ALTER TABLE `users_prefs` ADD UNIQUE `users_prefs_user_id_pref_unique` (`user_id`,`pref`);
ALTER TABLE `wireless_sensors` CHANGE `device_id` `device_id` int(10) unsigned NOT NULL DEFAULT '0' ;
ALTER TABLE `wireless_sensors` CHANGE `sensor_current` `sensor_current` double NULL ;
ALTER TABLE `wireless_sensors` CHANGE `sensor_prev` `sensor_prev` double NULL ;
ALTER TABLE `wireless_sensors` CHANGE `sensor_limit` `sensor_limit` double NULL ;
ALTER TABLE `wireless_sensors` CHANGE `sensor_limit_warn` `sensor_limit_warn` double NULL ;
ALTER TABLE `wireless_sensors` CHANGE `sensor_limit_low` `sensor_limit_low` double NULL ;
ALTER TABLE `wireless_sensors` CHANGE `sensor_limit_low_warn` `sensor_limit_low_warn` double NULL ;
ALTER TABLE `pollers` MODIFY `id` int(11) NOT NULL;
ALTER TABLE `pollers` DROP INDEX `id`;
ALTER TABLE `pollers` DROP INDEX `PRIMARY`;
ALTER TABLE `pollers` ADD PRIMARY KEY (`id`);
ALTER TABLE `pollers` MODIFY `id` int(11) NOT NULL auto_increment;
ALTER TABLE `pollers` ADD UNIQUE `poller_name` (`poller_name`);
ALTER TABLE `poller_cluster` MODIFY `id` int(11) NOT NULL;
ALTER TABLE `poller_cluster` DROP INDEX `id`;
ALTER TABLE `poller_cluster` DROP INDEX `PRIMARY`;
ALTER TABLE `poller_cluster` ADD PRIMARY KEY (`id`);
ALTER TABLE `poller_cluster` MODIFY `id` int(11) NOT NULL auto_increment;
ALTER TABLE `poller_cluster` ADD UNIQUE `poller_cluster_node_id_unique` (`node_id`);
ALTER TABLE `poller_cluster_stats` MODIFY `id` int(11) NOT NULL;
ALTER TABLE `poller_cluster_stats` DROP INDEX `id`;
ALTER TABLE `poller_cluster_stats` DROP INDEX `PRIMARY`;
ALTER TABLE `poller_cluster_stats` ADD PRIMARY KEY (`id`);
ALTER TABLE `poller_cluster_stats` MODIFY `id` int(11) NOT NULL auto_increment;
ALTER TABLE `poller_cluster_stats` ADD UNIQUE `parent_poller_poller_type` (`parent_poller`, `poller_type`);
ALTER TABLE `poller_cluster_stats` CHANGE `parent_poller` `parent_poller` int(11) NOT NULL DEFAULT 0 ;
ALTER TABLE `poller_cluster_stats` CHANGE `poller_type` `poller_type` varchar(64) NOT NULL DEFAULT '' ;
Thank you for the reply. I completed all those steps successfully, but still have some problems. When I run validate.php now I get:
root@nms:/opt/librenms# ./validate.php
Component
Version
LibreNMS
1.43-107-g4f6e2b4
DB Schema
268
PHP
7.0.32-0ubuntu0.16.04.1
MySQL
10.0.36-MariaDB-0ubuntu0.16.04.1
RRDTool
1.5.5
SNMP
NET-SNMP 5.7.3
====================================
[OK] Composer Version: 1.7.2
[OK] Dependencies up-to-date.
[OK] Database connection successful
[FAIL] Database: incorrect column (eventlog/severity)
[FAIL] We have detected that your database schema may be wrong, please report the following to us on Discord (https://t.libren.ms/discord) or the community site (https://t.libren.ms/5gscd):
[FIX] Run the following SQL statements to fix.
SQL Statements:
ALTER TABLE eventlog CHANGE severityseverity tinyint(4) NULL DEFAULT ‘2’ ;
I ran the above command at the mysql command line and got the following:
MariaDB [librenms]> ALTER TABLE eventlog CHANGE severityseverity tinyint(4) NULL DEFAULT ‘2’ ;
ERROR 2013 (HY000): Lost connection to MySQL server during query
I restarted mysql and run the command again and same thing. The good news is the web interface is working, but I’d like to get this fixed if possible. Thanks again for any assistance.
I tried just running a mysqlcheck and get this when it checks the tables.
root@nms:~# mysqlcheck -A --auto-repair
librenms.access_points OK
librenms.alert_device_map OK
********* CUT OUTPUT *******
librenms.entityState OK
mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE … ’
I realize this is probably no longer a LibreNMS issue, but can anyone advise on what I might do to repair the database?
Thanks!
