Hi guys,
I want to resurrect this topic as proposed solution does not work anymore (if worked at all).
The problem is that the suggested SQL query is invalid when preset PHP settings are used, because of strict mode enabled by default in config/database.php
'mysql' => [
'driver' => 'mysql',
'url' => env('DATABASE_URL'),
'host' => env('DB_HOST', 'localhost'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'librenms'),
'username' => env('DB_USERNAME', 'librenms'),
'password' => env('DB_PASSWORD', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'prefix_indexes' => true,
'strict' => true,
This query
SELECT *,AVG(processors.processor_usage) as cpu_avg FROM devices,processors WHERE (devices.device_id = ? AND devices.device_id = processors.device_id) AND (devices.status = 1 && (devices.disabled = 0 && devices.ignore = 0)) = 1 and devices.os = âlinuxâ and HAVING AVG(processors.processor_usage) > 90
works from terminal or editor, but LibreNMS complains :
SQLSTATE[42000]: Syntax error or access violation: 1055 âlibrenms.processors.processor_descrâ isnât in GROUP BY
To make LibreNMS happy, all fields from processor table must be included (because of mode strict is set to true), but then query stops making sense because results are not grouped.
Can I disable mode strict or is there a better workaround?