~~
Database changed
MariaDB [librenms]> select port_id, device_id, ifName, ifDescr, ifAlias from ports where poll_time > 1550747053 and exists (select * from devices where ports.device_id = devices.device_id) group by port_id, device_id, ifName, ifDescr, ifAlias order by SUM(ifInOctets_rate + ifOutOctets_rate) DESC limit 100;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in ‘(librenms.ports.ifInOctets_rate + librenms.ports.ifOutOctets_rate)’
MariaDB [librenms]>
~~
Anyone already analyzed what the fix would be?
(Because some ifinoctets_rate + ifOutOctets_rate maxes out my BIGINT UNSIGNED…)
I don’t know…
It sometimes happens, sometimes not.
How could I retrieve/guess with which device_id it is busy with?
I also see a local UserID popping up…
~~
{“userId”:8,“email”:“d.denouden@”,“exception”:"[object]
~~
/usr/local/opt/librenms/poller.php 158 2019-02-22 14:05:48 - 1 devices polled in 18.50 secs
/usr/local/opt/librenms/poller.php 147 2019-02-22 14:05:50 - 1 devices polled in 3.952 secs
[2019-02-22 13:05:52] production.ERROR: SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in ‘(librenms.ports.ifInOctets_rate + librenms.ports.ifOutOctets_rate)’ (SQL: select port_id, device_id, ifName, ifDescr, ifAlias from ports where poll_time > 1550840152 and exists (select * from devices where ports.device_id = devices.device_id) group by port_id, device_id, ifName, ifDescr, ifAlias order by SUM(ifInOctets_rate + ifOutOctets_rate) DESC limit 100) {“userId”:8,“email”:“d.denouden@”,“exception”:"[object] (Illuminate\Database\QueryException(code: 22003): SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in ‘(librenms.ports.ifInOctets_rate + librenms.ports.ifOutOctets_rate)’ (SQL: select port_id, device_id, ifName, ifDescr, ifAlias from ports where poll_time > 1550840152 and exists (select * from devices where ports.device_id = devices.device_id) group by port_id, device_id, ifName, ifDescr, ifAlias order by SUM(ifInOctets_rate + ifOutOctets_rate) DESC limit 100) at /usr/local/opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, Doctrine\DBAL\Driver\PDOException(code: 22003): SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in ‘(librenms.ports.ifInOctets_rate + librenms.ports.ifOutOctets_rate)’ at /usr/local/opt/librenms/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:119, PDOException(code: 22003): SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in ‘(librenms.ports.ifInOctets_rate + librenms.ports.ifOutOctets_rate)’ at /usr/local/opt/librenms/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117)
[stacktrace] #0 /usr/local/opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php(624): Illuminate\Database\Connection->runQueryCallback(‘select port_id...', Array, Object(Closure)) #1 /usr/local/opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php(333): Illuminate\\Database\\Connection->run('selectport_id…’, Array, Object(Closure)) #2 /usr/local/opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2095): Illuminate\Database\Connection->select(‘select `port_id…’, Array, true) #3 /usr/local/opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2083): Illuminate\Database\Query\Builder->runSelect() #4 /usr/local/opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2569): Illuminate\Database\Query\Builder->Illuminate\Database\Query\{closure}() #5 /usr/local/opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2084): Illuminate\Database\Query\Builder->onceWithColumns(Array, Object(Closure)) #6 /usr/local/opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(519): Illuminate\Database\Query\Builder->get(Array) #7 /usr/local/opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(503): Illuminate\Database\Eloquent\Builder->getModels(Array) #8 /usr/local/opt/librenms/app/Http/Controllers/Widgets/TopInterfacesController.php(64): Illuminate\Database\Eloquent\Builder->get()
… #74 /usr/local/opt/librenms/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(116): Illuminate\Foundation\Http\Kernel->sendRequestThroughRouter(Object(Illuminate\Http\Request)) #75 /usr/local/opt/librenms/html/index.php(53): Illuminate\Foundation\Http\Kernel->handle(Object(Illuminate\Http\Request)) #76 {main}
"}
/usr/local/opt/librenms/poller.php 52 2019-02-22 14:05:54 - 1 devices polled in 10.00 secs
I’ve previously posted about this in discord and @murrant offered lots of help but I’ve not yet found a resolution. The Top Devices Widget Traffic Query on my server is unusable due to frequent “Problem with backend” errors.
Other queries work fine.
Other widgets work fine.
Setting app_debug=true provides me the following:
QueryException: SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in ‘(librenms.ports.ifInOctets_rate + librenms.ports.ifOutOctets_rate)’ (SQL: select device_id from ports where poll_time > 1554854201 and exists (select * from devices where ports.device_id = devices.device_id) group by device_id order by SUM(ifInOctets_rate + ifOutOctets_rate) desc limit 15)
After frequently checking (FROM ports ORDER BY ifOutOctets_rate DESC & FROM ports ORDER BY ifInOctets_rate DESC) I’ve never seen it occur on one of our switches and it seems primarily to happen with our APCs and VMware host devices.
I will happily provide any additional information.
I apologize if I’m posting this in the wrong place. I’m a bit out of my depth here.
I waited for the 1.51 release like you said, and the upgrade was successful, but it didn’t fix the 1690 BIGINT UNSIGNED value is out of range error.
I went through the 1.51 release notes but couldn’t figure out which bug fix you were talking about. So I couldn’t do any further testing on my server. The last fix I received for it was #10024 from release 1.50.
Do you have any suggestions? My supervisors are getting…impatient. And I’d appreciate any help you can give.
