Daily.sh breaks MySQL DB trying to update schema

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.

[email protected]:/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:
[email protected]:/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:wink:
/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):wink:
/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:wink:
/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 severity severity tinyint(4) NULL DEFAULT ‘2’ :wink:
/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
[email protected]:/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:

[email protected]:/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 severity severity 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 severity severity 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 would check your mysql server. It seems to odd that it would drop the connection/crash when running that statement.

I tried just running a mysqlcheck and get this when it checks the tables.

[email protected]:~# 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!

You could try and do a mysqldump for the db, if that works then drop the old db, create it again and re-import.