Hello community,
we have embarked on using the API in LibreNMS to establish things like devices etc. We noticed however that we always observed 0 devices, even when there were defintly devices added. Doing some troubleshooting we have a request that looks like this (not worried about exposing the api token as this is an internal lab):
librenms@librelab-node1:~$ curl --location http://localhost/api/v0/devices/ --header 'X-Auth-Token: 34ac8f8c1379e86c0b82241551d44bce'; { "status": "ok", "devices": [], "count": 0 }librenms@librelab-node1:~$ librenms@librelab-node1:~$
This is a lab setup that mirrors (in most part) our prodcution environment, the key piece of information is that we are using a galera mariadb database backend so we have the mysql_cluster parameter enabled. Below is the .env, I’m not worried about exposing the keys / passwords etc as this is just a lab config:
librenms@librelab-node1:~$ cat .env
APP_KEY=base64:YxrNX3x57e+b81jeuLTco3QIA9ZCjlvx7ZwHMJhpNfg=
DB_HOST=10.101.101.21
#APP_URL=
NODE_ID=651ed6ce66679
VAPID_PUBLIC_KEY=BA5c1yv9hGDliYb3xf61fPpn87mFkQ0avSE6OqXHl1sD4UbdaW42eFT5DP0NN7bWEQMW4uNmz_XagynQZe4u3qU
VAPID_PRIVATE_KEY=2KEYVN8cPd4EVF25v-BvS5kafRPp-37AzcpLy5y-Jy0
DB_HOST=10.101.101.21
DB_HOST_R2=10.101.101.22
DB_HOST_W2=10.101.101.22
DB_STICKY=true
DB_CONNECTION=mysql_cluster
DB_DATABASE=librenms
DB_USERNAME=librenms
DB_PASSWORD=eeeeeeeeeeeeeeeeeeeeeeee
If we revert this to point to one of the DB masters like a “regular setup” like so:
librenms@librelab-node1:~$ cat .env
APP_KEY=base64:YxrNX3x57e+b81jeuLTco3QIA9ZCjlvx7ZwHMJhpNfg=
DB_HOST=10.101.101.21
#APP_URL=
NODE_ID=651ed6ce66679
VAPID_PUBLIC_KEY=BA5c1yv9hGDliYb3xf61fPpn87mFkQ0avSE6OqXHl1sD4UbdaW42eFT5DP0NN7bWEQMW4uNmz_XagynQZe4u3qU
VAPID_PRIVATE_KEY=2KEYVN8cPd4EVF25v-BvS5kafRPp-37AzcpLy5y-Jy0
DB_HOST=10.101.101.21
#DB_HOST_R2=10.101.101.22
#DB_HOST_W2=10.101.101.22
#DB_STICKY=true
#DB_CONNECTION=mysql_cluster
DB_DATABASE=librenms
DB_USERNAME=librenms
DB_PASSWORD=eeeeeeeeeeeeeeeeeeeeeeee
librenms@librelab-node1:~$
The API call then works ok (I’ve filtered the output with grep but we can see devices now being more than 0):
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 15728 "devices": [ 0 0 0 --:--:-- --:--:-- --:--:-- 0
"device_id": 5,
"device_id": 12,
0 "device_id": 10,
1 "device_id": 13,
57 "device_id": 16,
28 "device_id": 21,
0 0 386k 0 --:--:-- --:--:-- --:--:-- 393k
"device_id": 18,
librenms@librelab-node1:~$
Looking for reasons why this is occuring, when the first call was run we see this dump in the librenms.log
SQLSTATE[42000]: Syntax error or access violation: 1055 'librenms.d.device_id' isn't in GROUP BY (Connection: mysql_cluster, SQL: SELECT d.*, GROUP_CONCAT(dd.device_id) AS dependency_parent_id, GROUP_CONCAT
(dd.hostname) AS dependency_parent_hostname, `location`, `lat`, `lng` FROM `devices` AS d LEFT JOIN `device_relationships` AS dr ON dr.`child_device_id` = d.`device_id` LEFT JOIN `devices` AS dd ON dr.`par
ent_device_id` = dd.`device_id` LEFT JOIN `locations` ON `locations`.`id` = `d`.`location_id` WHERE 1 GROUP BY d.`hostname` ORDER BY d.`hostname` ASC) (Connection: dbFacile, SQL: SELECT d.*, GROUP_CONCAT(dd
.device_id) AS dependency_parent_id, GROUP_CONCAT(dd.hostname) AS dependency_parent_hostname, `location`, `lat`, `lng` FROM `devices` AS d LEFT JOIN `device_relationships` AS dr ON dr.`child_device_id` = d
.`device_id` LEFT JOIN `devices` AS dd ON dr.`parent_device_id` = dd.`device_id` LEFT JOIN `locations` ON `locations`.`id` = `d`.`location_id` WHERE 1 GROUP BY d.`hostname` ORDER BY d.`hostname` ASC)#0 /opt/
librenms/includes/html/api_functions.inc.php(388): dbFetchRows()
#1 /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(36): list_devices()
#2 /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/Util.php(41): Illuminate\Container\BoundMethod::Illuminate\Container\{closure}()
#3 /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(81): Illuminate\Container\Util::unwrapIfClosure()
#4 /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(37): Illuminate\Container\BoundMethod::callBoundMethod()
#5 /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/Container.php(662): Illuminate\Container\BoundMethod::call()
#6 /opt/librenms/app/Api/Controllers/LegacyApiController.php(43): Illuminate\Container\Container->call()
#7 /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(46): App\Api\Controllers\LegacyApiController->__call()
#8 /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/Route.php(260): Illuminate\Routing\ControllerDispatcher->dispatch()
#9 /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/Route.php(205): Illuminate\Routing\Route->runController()
#10 /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/Router.php(806): Illuminate\Routing\Route->run()
#11 /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(144): Illuminate\Routing\Router->Illuminate\Routing\{closure}()
#12 /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/Middleware/SubstituteBindings.php(50): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#13 /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\Routing\Middleware\SubstituteBindings->handle()
#14 /opt/librenms/vendor/laravel/framework/src/Illuminate/Auth/Middleware/Authenticate.php(57): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#15 /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\Auth\Middleware\Authenticate->handle()
#16 /opt/librenms/app/Http/Middleware/EnforceJson.php(44): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#17 /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): App\Http\Middleware\EnforceJson->handle()
#18 /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(119): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#19 /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/Router.php(807): Illuminate\Pipeline\Pipeline->then()
#20 /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/Router.php(784): Illuminate\Routing\Router->runRouteWithinStack()
#21 /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/Router.php(748): Illuminate\Routing\Router->runRoute()
#22 /opt/librenms/vendor/laravel/framework/src/Illuminate/Routing/Router.php(737): Illuminate\Routing\Router->dispatchToRoute()
#23 /opt/librenms/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(200): Illuminate\Routing\Router->dispatch()
#24 /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(144): Illuminate\Foundation\Http\Kernel->Illuminate\Foundation\Http\{closure}()
#25 /opt/librenms/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php(21): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#26 /opt/librenms/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/ConvertEmptyStringsToNull.php(31): Illuminate\Foundation\Http\Middleware\TransformsRequest->handle()
#27 /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\Foundation\Http\Middleware\ConvertEmptyStringsToNull->handle()
#28 /opt/librenms/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TransformsRequest.php(21): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#29 /opt/librenms/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/TrimStrings.php(40): Illuminate\Foundation\Http\Middleware\TransformsRequest->handle()
#30 /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\Foundation\Http\Middleware\TrimStrings->handle()
#31 /opt/librenms/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/ValidatePostSize.php(27): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#32 /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\Foundation\Http\Middleware\ValidatePostSize->handle()
#33 /opt/librenms/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/PreventRequestsDuringMaintenance.php(99): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#34 /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\Foundation\Http\Middleware\PreventRequestsDuringMaintenance->handle()
#35 /opt/librenms/vendor/laravel/framework/src/Illuminate/Http/Middleware/HandleCors.php(49): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#36 /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\Http\Middleware\HandleCors->handle()
#37 /opt/librenms/vendor/laravel/framework/src/Illuminate/Http/Middleware/TrustProxies.php(39): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#38 /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(183): Illuminate\Http\Middleware\TrustProxies->handle()
#39 /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(119): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}()
#40 /opt/librenms/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(175): Illuminate\Pipeline\Pipeline->then()
#41 /opt/librenms/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(144): Illuminate\Foundation\Http\Kernel->sendRequestThroughRouter()
#42 /opt/librenms/html/index.php(52): Illuminate\Foundation\Http\Kernel->handle()
#43 {main}
My issue is how to troubleshoot this through the next steps now that I can replicate in our prodcution as well as lab environment, so I’m reaching out for any pointers. We havent spotted any other problems, but for the time being we will keep out database connection from our LibreNMS pollers pointing to one DB master.
thanks for reading
Chris