ERROR: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'location' in 'field list'

DR3EVR8u8c · 13 December 2018 04:36

Hello,
I left the daily.sh run automatically to follow the month release channel and I just noticed that the Librenms GUI has broken.

any devices you click on will redirect to a empty device which is no System Name and other informations. post screenshot here for better information:

image1192×385 30 KB
the global search on the top right corner only find PORT.
Alerts shows “No results found|” but I know there are some.
librenms.log shows SQL error and unknown column “location”:

[2018-12-13 15:18:19] production.ERROR: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'location' in 'field list' (SQL: SELECT location FROM devi
ces AS D GROUP BY location ORDER BY location) (SQL: SELECT location FROM devices AS D GROUP BY location ORDER BY location)
  /opt/librenms/html/includes/functions.inc.php:887
  /opt/librenms/html/includes/print-menubar.php:185
  /opt/librenms/html/legacy_index.php:163
  /opt/librenms/app/Http/Controllers/LegacyController.php:14
  :
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/Controller.php:55
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php:44
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/Route.php:203
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/Route.php:160
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/Router.php:572
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:30
  /opt/librenms/app/Http/Middleware/VerifyTwoFactor.php:38
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:148
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/Middleware/SubstituteBindings.php:41
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:148
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Auth/Middleware/Authenticate.php:43
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:148
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
  /opt/librenms/app/Http/Middleware/LegacySession.php:44
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:148
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
  /opt/librenms/app/Http/Middleware/LegacyExternalAuth.php:45
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:148
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/VerifyCsrfToken.php:65
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:148
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
  /opt/librenms/vendor/laravel/framework/src/Illuminate/View/Middleware/ShareErrorsFromSession.php:49
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:148
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Session/Middleware/StartSession.php:64
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:148
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/AddQueuedCookiesToResponse.php:37
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:148
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/EncryptCookies.php:59
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php:148
  /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/Pipeline.php:53
  /opt/librenms/app/Http/Middleware/CheckInstalled.php:46

/var/log/php-fpm/www-error.log shows:

#3 {main}
  thrown in /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/QueryException.php on line 32
[13-Dec-2018 15:41:33 Australia/Sydney] PHP Fatal error:  Uncaught TypeError: Argument 2 passed to Illuminate\Database\QueryException::__construct() must be of the type array, null given, called in /opt/librenms/includes/dbFacile.php on line 290 and defined in /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/QueryException.php:32
Stack trace:
#0 /opt/librenms/includes/dbFacile.php(290): Illuminate\Database\QueryException->__construct('SELECT `alerts`...', NULL, Object(Illuminate\Database\QueryException))
#1 /opt/librenms/html/includes/table/alerts.inc.php(124): dbFetchRows('SELECT `alerts`...', NULL)
#2 /opt/librenms/html/ajax_table.php(44): include_once('/opt/librenms/h...')
#3 {main}
  thrown in /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/QueryException.php on line 32

validate.php output said database scheme correct:

-bash-4.2$ ./validate.php

Component	Version
LibreNMS	1.46-43-g377b77d
DB Schema	273
PHP	7.0.32
MySQL	5.5.47-MariaDB
RRDTool	1.6.0
SNMP	NET-SNMP 5.7.2
====================================

[OK] Composer Version: 1.8.0
[OK] Dependencies up-to-date.
[OK] Database connection successful
[OK] Database schema correct
[WARN] Some devices have not been polled in the last 5 minutes. You may have performance issues.
[FIX]:
Check your poll log and see: Performance - LibreNMS Docs
Devices:
us4-int-qnap01.memjet.local
[FAIL] Discovery has not completed in the last 24 hours.
[FIX]:
Check the cron job to make sure it is running and using discovery-wrapper.py
[WARN] Your install is over 24 hours out of date, last update: Tue, 11 Dec 2018 04:20:53 +0000
[FIX]:
Make sure your daily.sh cron is running and run ./daily.sh by hand to see if there are any errors.
[FAIL] Some folders have incorrect file permissions, this may cause issues.
[FIX]:
sudo chown -R librenms:librenms /opt/librenms
sudo setfacl -d -m g::rwx /opt/librenms/rrd /opt/librenms/logs /opt/librenms/bootstrap/cache/ /opt/librenms/storage/
sudo chmod -R ug=rwX /opt/librenms/rrd /opt/librenms/logs /opt/librenms/bootstrap/cache/ /opt/librenms/storage/
Files:
/opt/librenms/storage/framework/views/d6129f4b22f45f3b3e20f8b813652e70

After I notice this, I have manually update to the latest release but the issue still the same.
Please help to fix the problem.
Thanks and regards,
Roger

laf · 14 December 2018 11:25

I’d 100% say this is down to an out of date install.

What does git status say?

DR3EVR8u8c · 16 December 2018 23:21

Hello @laf,

here is the output of git status:

-bash-4.2$ git status
# On branch master
# Untracked files:
#   (use "git add <file>..." to include in what will be committed)
#
#	MemjetLogo.png
#	MemjetLogo.svg
#	check_files.pl
#	config.php.org
#	html/images/os/memjetprinter.png
#	includes/definitions/memjetprinter.yaml
#	includes/services/check_flexlm_licenses.pl.inc.php
#	includes/services/check_mysql_slavestatus.sh.inc.php
#	includes/services/check_snmp_win.pl.inc.php
#	memjet-dots.png
#	memjetprinter.png
#	test.log
#	test.txt
nothing added to commit but untracked files present (use "git add" to track)

I have some uncommitted the changes. but, I believe that it should not cause the problem.

DR3EVR8u8c · 19 December 2018 05:03

Hello there,
any suggestion for how to troubleshoot. I am completely no clues what should be checked.
I have reverted the librenms back to 1.43 by git checkout and update couple times. But the problem has no changes at all.

Thanks for your help.

murrant · 21 December 2018 17:20

This happens when the database and webui code are not at the same level.

Also, You cannot revert without losing data this particular change, the only way is forward.

One more thing, I have seen people using different databases than they think. Check the settings in .env. How many LibreNMS servers do you have? One?