BIGINT UNSIGNED value is out of range

LibreNMS is running this query…

~~
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…)

What are the values of ifInOctets_rate and ifOutOctets_rate?

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.

Oh, and…

$ ./validate.php

Component Version
LibreNMS 1.50
DB Schema 2019_02_10_220000_add_dates_to_fdb (132)
PHP 7.2.15-0ubuntu0.18.04.2
MySQL 10.1.38-MariaDB-0ubuntu0.18.04.1
RRDTool 1.7.0
SNMP NET-SNMP 5.7.3

====================================

[OK] Composer Version: 1.8.5
[OK] Dependencies up-to-date.
[OK] Database connection successful
[OK] Database schema correct
$

Thanks!
Allie

@Allie as previously mentioned, this is already fixed. You are running stable, so you will have to wait for 1.51 for the fix.

@murrant I didn’t realize you’d found a fix for it. That’s great.

Thanks for all your help!

You mentioned you saw it merged in the other thread. Also, please don’t start new threads for the same thing :wink:

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.

./validate.php

Component Version
LibreNMS 1.51
DB Schema 2019_02_10_220000_add_dates_to_fdb (132)
PHP 7.2.17-0ubuntu0.18.04.1
MySQL 10.1.38-MariaDB-0ubuntu0.18.04.1
RRDTool 1.7.0
SNMP NET-SNMP 5.7.3

====================================

[OK] Composer Version: 1.8.5
[OK] Dependencies up-to-date.
[OK] Database connection successful
[OK] Database schema correct