librenms SQL Error: Numbers to be written into MariaDB are too large

The problem

In librenms.log i see the following error:

SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'drop' at row 1 (Connection: mysql, SQL: UPDATE cef_switching set drop=12527614076,punt=7308805563,punt2host=2420079175,drop_prev=0,punt_prev=0,punt2host_prev=0,updated=1733837421,updated_prev=0 WHERE device_id = 384 AND entPhysicalIndex = 3016 AND afi = ipv4 AND cef_index = 2) (Connection: dbFacile, SQL: UPDATE cef_switching set drop=12527614076,punt=7308805563,punt2host=2420079175,drop_prev=0,punt_prev=0,punt2host_prev=0,updated=1733837421,updated_prev=0 WHERE device_id = 384 AND entPhysicalIndex = 3016 AND afi = ipv4 AND cef_index = 2)#0 /opt/librenms/includes/polling/cisco-cef.inc.php(78): dbUpdate() #1 /opt/librenms/LibreNMS/Modules/LegacyModule.php(115): include('...') #2 /opt/librenms/app/Jobs/PollDevice.php(139): LibreNMS\Modules\LegacyModule->poll() #3 /opt/librenms/app/Jobs/PollDevice.php(63): App\Jobs\PollDevice->pollModules() #4 /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(36): App\Jobs\PollDevice->handle() #5 /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/Util.php(41): Illuminate\Container\BoundMethod::Illuminate\Container{closure}() #6 /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(93): Illuminate\Container\Util::unwrapIfClosure() #7 /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(35): Illuminate\Container\BoundMethod::callBoundMethod() #8 /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/Container.php(662): Illuminate\Container\BoundMethod::call() #9 /opt/librenms/vendor/laravel/framework/src/Illuminate/Bus/Dispatcher.php(128): Illuminate\Container\Container->call() #10 /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(144): Illuminate\Bus\Dispatcher->Illuminate\Bus{closure}() #11 /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(119): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline{closure}() #12 /opt/librenms/vendor/laravel/framework/src/Illuminate/Bus/Dispatcher.php(132): Illuminate\Pipeline\Pipeline->then() #13 /opt/librenms/vendor/laravel/framework/src/Illuminate/Queue/CallQueuedHandler.php(123): Illuminate\Bus\Dispatcher->dispatchNow() #14 /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(144): Illuminate\Queue\CallQueuedHandler->Illuminate\Queue{closure}() #15 /opt/librenms/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(119): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline{closure}() #16 /opt/librenms/vendor/laravel/framework/src/Illuminate/Queue/CallQueuedHandler.php(122): Illuminate\Pipeline\Pipeline->then() #17 /opt/librenms/vendor/laravel/framework/src/Illuminate/Queue/CallQueuedHandler.php(70): Illuminate\Queue\CallQueuedHandler->dispatchThroughMiddleware() #18 /opt/librenms/vendor/laravel/framework/src/Illuminate/Queue/Jobs/Job.php(102): Illuminate\Queue\CallQueuedHandler->call() #19 /opt/librenms/vendor/laravel/framework/src/Illuminate/Queue/SyncQueue.php(43): Illuminate\Queue\Jobs\Job->fire() #20 /opt/librenms/vendor/laravel/framework/src/Illuminate/Bus/Dispatcher.php(254): Illuminate\Queue\SyncQueue->push() #21 /opt/librenms/vendor/laravel/framework/src/Illuminate/Bus/Dispatcher.php(230): Illuminate\Bus\Dispatcher->pushCommandToQueue() #22 /opt/librenms/vendor/laravel/framework/src/Illuminate/Bus/Dispatcher.php(95): Illuminate\Bus\Dispatcher->dispatchToQueue() #23 /opt/librenms/vendor/laravel/framework/src/Illuminate/Foundation/Bus/Dispatchable.php(76): Illuminate\Bus\Dispatcher->dispatchSync() #24 /opt/librenms/app/Console/Commands/DevicePoll.php(79): App\Jobs\PollDevice::dispatchSync() #25 /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(36): App\Console\Commands\DevicePoll->handle() #26 /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/Util.php(41): Illuminate\Container\BoundMethod::Illuminate\Container{closure}() #27 /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(93): Illuminate\Container\Util::unwrapIfClosure() #28 /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(35): Illuminate\Container\BoundMethod::callBoundMethod() #29 /opt/librenms/vendor/laravel/framework/src/Illuminate/Container/Container.php(662): Illuminate\Container\BoundMethod::call() #30 /opt/librenms/vendor/laravel/framework/src/Illuminate/Console/Command.php(211): Illuminate\Container\Container->call() #31 /opt/librenms/vendor/symfony/console/Command/Command.php(326): Illuminate\Console\Command->execute() #32 /opt/librenms/vendor/laravel/framework/src/Illuminate/Console/Command.php(180): Symfony\Component\Console\Command\Command->run() #33 /opt/librenms/vendor/symfony/console/Application.php(1096): Illuminate\Console\Command->run() #34 /opt/librenms/vendor/symfony/console/Application.php(324): Symfony\Component\Console\Application->doRunCommand() #35 /opt/librenms/vendor/symfony/console/Application.php(175): Symfony\Component\Console\Application->doRun() #36 /opt/librenms/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php(201): Symfony\Component\Console\Application->run() #37 /opt/librenms/app/Console/Kernel.php(66): Illuminate\Foundation\Console\Kernel->handle() #38 /opt/librenms/lnms(38): App\Console\Kernel->handle() #39 {main}

A similar error is

SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'punt' at row 1 (Connection: mysql, SQL: UPDATE cef_switching set drop=1676398539,punt=2172089168,punt2host=231828459,drop_prev=1667446343,punt_prev=2147483285,punt2host_prev=229441740,updated=1718355731,updated_prev=1716508702 WHERE device_id = 63 AND entPhysicalIndex = 7 AND afi = ipv4 AND cef_index = 3)

Possible fix: Change the data type of the database column:

ALTER TABLE `cef_switching` CHANGE `punt` `punt` bigint(20) not NULL;
ALTER TABLE `cef_switching` CHANGE `punt_prev` `punt_prev` bigint(20) not NULL;

The same error also happens in the table 'port_statistics' . I fixed it with

ALTER TABLE `ports_statistics` CHANGE `ifInNUcastPkts_rate` `ifInNUcastPkts_rate` bigint(20) unsigned NULL ;
ALTER TABLE `ports_statistics` CHANGE `ifOutNUcastPkts_rate` `ifOutNUcastPkts_rate` bigint(20) unsigned NULL ;

ALTER TABLE `ports_statistics` CHANGE `ifInDiscards_rate` `ifInDiscards_rate` bigint(20) unsigned NULL ;
ALTER TABLE `ports_statistics` CHANGE `ifOutDiscards_rate` `ifOutDiscards_rate` bigint(20) unsigned NULL ;

ALTER TABLE `ports_statistics` CHANGE `ifInUnknownProtos_rate` `ifInUnknownProtos_rate` bigint(20) unsigned NULL ;

ALTER TABLE `ports_statistics` CHANGE `ifInBroadcastPkts_rate` `ifInBroadcastPkts_rate` bigint(20) unsigned NULL ;
ALTER TABLE `ports_statistics` CHANGE `ifOutBroadcastPkts_rate` `ifOutBroadcastPkts_rate` bigint(20) unsigned NULL ;

ALTER TABLE `ports_statistics` CHANGE `ifInMulticastPkts_rate` `ifInMulticastPkts_rate` bigint(20) unsigned NULL ;
ALTER TABLE `ports_statistics` CHANGE `ifOutMulticastPkts_rate` `ifOutMulticastPkts_rate` bigint(20) unsigned NULL ;

So far, i have not yet encountered any negative side effects.

Output of ./validate.php

librenms@librenms-prod:~$ ./validate.php
Cannot load Zend OPcache - it was already loaded
Cannot load Zend OPcache - it was already loaded
Cannot load Zend OPcache - it was already loaded
===========================================
Component | Version
--------- | -------
LibreNMS  | 24.11.0-84-gac7c8e747 (2024-12-08T16:21:08+01:00)
DB Schema | 2024_10_24_131715_mpls_sdp_bindings_enum_string (310)
PHP       | 8.2.26
Python    | 3.11.2
Database  | MariaDB 10.11.6-MariaDB-0+deb12u1
RRDTool   | 1.7.2
SNMP      | 5.9.3
===========================================

[OK]    Composer Version: 2.8.3
[OK]    Dependencies up-to-date.
[OK]    Database connection successful
[OK]    Database connection successful
[OK]    Database Schema is current
[OK]    SQL Server meets minimum requirements
[OK]    lower_case_table_names is enabled
[OK]    MySQL engine is optimal
[OK]    Database and column collations are correct
[FAIL]  We have detected that your database schema may be wrong
Database: incorrect column (cef_switching/punt)
Database: incorrect column (cef_switching/punt_prev)
        [FIX]:
        Run the following SQL statements to fix it
        SQL Statements:
         ALTER TABLE `cef_switching` CHANGE `punt` `punt` int NOT NULL ;
         ALTER TABLE `cef_switching` CHANGE `punt_prev` `punt_prev` int NOT NULL ;
Attempt to fix this issue (y or n)?:n
[OK]    MySQL and PHP time match
Cannot load Zend OPcache - it was already loaded
Cannot load Zend OPcache - it was already loaded
Cannot load Zend OPcache - it was already loaded
Cannot load Zend OPcache - it was already loaded
Cannot load Zend OPcache - it was already loaded
Cannot load Zend OPcache - it was already loaded
Cannot load Zend OPcache - it was already loaded
Cannot load Zend OPcache - it was already loaded
Cannot load Zend OPcache - it was already loaded
Cannot load Zend OPcache - it was already loaded
[OK]    Active pollers found
[OK]    Dispatcher Service not detected
[OK]    Locks are functional
[OK]    Python poller wrapper is polling
[OK]    Redis is unavailable
[OK]    rrdtool version ok
[OK]    Connected to rrdcached

What was the last working version of LibreNMS?

21.11.0

Anything in the logs that might be useful for us?

see above

Dec 10 '24 13:12 rAGSebastianRost

We are having the same problem, and there seem to be two similar issues open as well: #16064, #16874.

Is there something we can do to help? Would the fix described in the issue be accepted / helpful as a PR?

Feb 04 '25 16:02 NavidSassan

@NavidSassan seems unlikely. Almost all of these seem like bad values attempting to be written to the database. For example one field is supposed to be a 0-100 (percent). Likely these need code specific fixes, but are difficult to reproduce. Some may be reproducible by capturing an snmprec file, others are more difficult as they require a change from some (unknown) value to another (unknown) value across multiple polls.

Feb 05 '25 19:02 murrant

No, this is not a invalid value, as noted in the Cisco Documentation the HC values are 64-bit integers. While integer is used in the application see migraration.

So to fix this a new migration is need to upgrade to BIGINT as noted by OP.

Feb 08 '25 13:02 dot-mike

The code does not appear to currently be using cefSwitchingHCPunt though. It's using cefSwitchingPunt which is the 32-bit counter:

https://github.com/librenms/librenms/blob/d8ae30d16e70ff07e94f2d10c6359b9b08a19efb/includes/polling/cisco-cef.inc.php#L82

Feb 10 '25 11:02 mivsvit

We did troubleshoot this a bit and PHP can't do unsigned 64 bit integers, so _rate is never going to work without gmp or bcmath if it is trying to calculate an unsigned 64 bit number over INT_MAX. We can fix the database fields for the others and rrdtool is unaffected.

May 01 '25 22:05 murrant

This issue has been mentioned on LibreNMS Community. There might be relevant details there:

https://community.librenms.org/t/error-in-daily-log-daily-php/28872/3

Dec 17 '25 14:12 librenms-bot