Issues updating database schema

[carinity]

Morning.
I have rebooted our librenms ubuntu server but the overview page does not respond / resolve however all things look ok on the server.
Can anyone advise ?

[carinity]

I have restarted mysql and can now access librenms overpage etc however again the server stopped polling at exactly midnight last night which previously was resolved by the below
"Every now & then net-snmp decides to create a .index file in the /opt/librenms/mibs directory that it can't use. Deleting it solves the problem.:

strange behaviour. advice welcome.

[paulgear]

I looked at @carinity's system while this behaviour was happening, and the problem was that basic SQL queries like 'select * from devices' were hanging. This was not due to the .index file, but seems to be due to the db schema update from 055 to 056 having to do a lot of work. I paused polling, dumped & reloaded the database, ran the schema update, and started polling again, and things seem to be OK. We may need to look into a better method for ensuring the schema update occurs in a timely fashion.

[paulgear]

To be clear: I don't believe this had anything to do with the server reboot.

[carinity]

thanks to paulgear for support . Cant live with out librenms now :)

[f0o]

Looking at Schema 056, the only extensive operation in it is the updates-statement but even that shouldn't be able to bring down the host.
I cant see a real performance-killer in the 056-schema and it ran through fine on my installs :S

Is it worth adding a schema-check to the poller-wrapper.py?

[paulgear]

Before I dumped & restored, I ran the schema update by hand, and it hung on the 'alter table devices ...'

[XxPatrickxX]

https://github.com/librenms/librenms/blob/master/sql-schema/056.sql

work-around for me

1. disable cron jobs
2. restart...could just wait for all jobs to finish as well
3. run daily.sh
4. re-enable the cron jobs

postmortem

1. would be nice if the web interface showed a warning message of update failures
2. would be nice if we could load through the update.php process a manual update to a specific schema....if if your having problems and are multiple schema versions behind the ability to run from however old to schema 55.
3. As my only install is now working, it's hard for me to troubleshoot beyond. Looking at the schema 56 script itself lines 1 through 3 look harmless but lines 4 and 5 is where I suspect the issue is. I'm guessing that the poller is row locking while it's trying to run in the changes. "Show open tables librenms" might confirm the lock. Locking the table for writes would resolve this issue but would introduce any table updates occurring by the poller during may try to insert incorrect values into the table...ala status_reason.

Long-term, during any schema update we may want to have a mechanism in-place to pause all polling operations first.

[paulgear]

This bit me again on my test install yesterday. Still need to investigate further.

[carinity]

This issue has occurred again this morning with the overview screen blank however I can find dev ices and display their graphs properly . not resolved as yet.

[paulgear]

FTR, I checked @carinity's install, and the problem today was due to local customisations conflicting with the recent code reformat (https://groups.google.com/forum/#!topic/librenms-project/U9ihWj7euqg), and not anything to do with this bug.

[Rosiak]

I'm able to reproduce the issue on a install.
The SQL starts hanging after doing this:
mysql> LOCK TABLES devices WRITE;

Part of this:
058.sql

If the locations table exists and we run the 058.sql, it goes through just fine.
If the locations tables does not exist we're able to reproduce the problem if we run the 058.sql manually again, then the mysql starts hanging.

This issue is seen on Description:
Ubuntu 14.04.2 LTS
Release: 14.04
mysql Ver 14.14 Distrib 5.5.43, for debian-linux-gnu (x86_64) using readline 6.3

Doing the same on a Centos 6.6 with the following MySQL version works without any issues:
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

[Rosiak]

PR #1563 should have resolved this issue.