production.ERROR: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry

Help
1

Receive the following error in librenms.log which i envisage is related to poller.php

[2019-01-19 19:08:44] production.ERROR: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘1-108’ for key ‘device_ifIndex’

When the query dumped on log was ran inside of MySQL / mariadb CLI, the below was output:

SQL Error [1264] [22003]: (conn:60115) Out of range value for column ‘ifOutOctets_delta’ at row 1

The SQL dumped on log pertains to a Cisco ASA and is listed as below:

UPDATE ports set ifIndex=108,poll_time=1547921324,poll_prev=1522170989,poll_period=25750335,ifLastChange=427531600,ifInOctets=643597659,ifInOctets_prev=746674518,ifInOctets_rate=0,ifInOctets_delta=-103076859,ifOutOctets=48944,ifOutOctets_prev=66861001,ifOutOctets_rate=0,ifOutOctets_delta=-66812057,ifInErrors=0,ifInErrors_prev=0,ifInErrors_rate=0,ifInErrors_delta=0,ifOutErrors=0,ifOutErrors_prev=0,ifOutErrors_rate=0,ifOutErrors_delta=0,ifInUcastPkts=12871577,ifInUcastPkts_prev=1875421,ifInUcastPkts_rate=0.42702962893492,ifInUcastPkts_delta=10996156,ifOutUcastPkts=1748,ifOutUcastPkts_prev=519477,ifOutUcastPkts_rate=0,ifOutUcastPkts_delta=-517729 WHERE port_id = 14725

I receive these a number of these errors on different ports tied into a Cisco ASA appliance (Cisco Adaptive Security Appliance Version 9.1(2))

Please advise as unsure whether this is a fix that should be on applied on dbtable ‘datatype’ or whether it is a bad result return when querying the snmp-device.

2

Run the poller against that device with the -d switch.

3

https://p.libren.ms/view/5c24af72

4

What port association mode do you have set for this device?

5

Port association: IfName

7

Guys, did you mange to get any solution to this ERROR ?
I just found the same in our logs.

Looking at the “problematic” device it appears to be the librenms host itself (Ubuntu). In my case the name of the interfaces are kind of non-standard: for example:

  • internet.123 instead of data.123
  • connection.456 instead of data.456
    where the raw device name of the interface is “data” (we are using cumulus ifupdown2 which gives us this functionality).

Is there any way to workaround it?
Regards!

8

same to me:

[2019-04-26 14:39:30] production.ERROR: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘177-3’ for key ‘device_ifIndex’ (SQL: UPDATE ports set ifIndex=3,poll_time=1556282370,poll_prev=1539005900,poll_period=17276470,ifLastChange=586231347,ifPhysAddress=002590651eb5,ifInOctets=4812512497175,ifInOctets_prev=829945236854,ifInOctets_rate=230519.7335058,ifInOctets_delta=3982567260321,ifOutOctets=30862051518661,ifOutOctets_prev=7069953753403,ifOutOctets_rate=1377138.8348,ifOutOctets_delta=23792097765258,ifInErrors=86,ifInErrors_prev=0,ifInErrors_rate=4.9778687428624E-6,ifInErrors_delta=86,ifOutErrors=0,ifOutErrors_prev=0,ifOutErrors_rate=0,ifOutErrors_delta=0,ifInUcastPkts=14629666791,ifInUcastPkts_prev=23822174755,ifInUcastPkts_rate=0,ifInUcastPkts_delta=-9192507964,ifOutUcastPkts=27297268547,ifOutUcastPkts_prev=7653909762,ifOutUcastPkts_rate=1137.0007174498,ifOutUcastPkts_delta=19643358785 WHERE port_id = 4163) (SQL: UPDATE ports set ifIndex=3,poll_time=1556282370,poll_prev=1539005900,poll_period=17276470,ifLastChange=586231347,ifPhysAddress=002590651eb5,ifInOctets=4812512497175,ifInOctets_prev=829945236854,ifInOctets_rate=230519.7335058,ifInOctets_delta=3982567260321,ifOutOctets=30862051518661,ifOutOctets_prev=7069953753403,ifOutOctets_rate=1377138.8348,ifOutOctets_delta=23792097765258,ifInErrors=86,ifInErrors_prev=0,ifInErrors_rate=4.9778687428624E-6,ifInErrors_delta=86,ifOutErrors=0,ifOutErrors_prev=0,ifOutErrors_rate=0,ifOutErrors_delta=0,ifInUcastPkts=14629666791,ifInUcastPkts_prev=23822174755,ifInUcastPkts_rate=0,ifInUcastPkts_delta=-9192507964,ifOutUcastPkts=27297268547,ifOutUcastPkts_prev=7653909762,ifOutUcastPkts_rate=1137.0007174498,ifOutUcastPkts_delta=19643358785 WHERE port_id = 4163)#0 /opt/librenms/includes/polling/ports.inc.php(901): dbUpdate(Array, ‘ports’, ’port_id = ?’, Array)
#1 /opt/librenms/includes/polling/functions.inc.php(305): include(’/opt/librenms/i…’)
#2 /opt/librenms/poller.php(157): poll_device(Array, false)
#3 {main}
[2019-04-26 14:39:30] production.ERROR: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘177-2’ for key ‘device_ifIndex’ (SQL: UPDATE ports set ifIndex=2,poll_time=1556282370,poll_prev=1539005900,poll_period=17276470,ifLastChange=1459115534,ifPhysAddress=002590651eb4,ifInOctets=31016081716654,ifInOctets_prev=7103405779152,ifInOctets_rate=1384118.1640406,ifInOctets_delta=23912675937502,ifOutOctets=4787857831210,ifOutOctets_prev=825164539169,ifOutOctets_rate=229369.38460467,ifOutOctets_delta=3962693292041,ifInErrors=2103,ifInErrors_prev=98388,ifInErrors_rate=0,ifInErrors_delta=-96285,ifOutErrors=0,ifOutErrors_prev=0,ifOutErrors_rate=0,ifOutErrors_delta=0,ifInUcastPkts=31817515328,ifInUcastPkts_prev=7994220518,ifInUcastPkts_rate=1378.9445882174,ifInUcastPkts_delta=23823294810,ifOutUcastPkts=14634124617,ifOutUcastPkts_prev=6626818711,ifOutUcastPkts_rate=463.48043934901,ifOutUcastPkts_delta=8007305906 WHERE port_id = 4164) (SQL: UPDATE ports set ifIndex=2,poll_time=1556282370,poll_prev=1539005900,poll_period=17276470,ifLastChange=1459115534,ifPhysAddress=002590651eb4,ifInOctets=31016081716654,ifInOctets_prev=7103405779152,ifInOctets_rate=1384118.1640406,ifInOctets_delta=23912675937502,ifOutOctets=4787857831210,ifOutOctets_prev=825164539169,ifOutOctets_rate=229369.38460467,ifOutOctets_delta=3962693292041,ifInErrors=2103,ifInErrors_prev=98388,ifInErrors_rate=0,ifInErrors_delta=-96285,ifOutErrors=0,ifOutErrors_prev=0,ifOutErrors_rate=0,ifOutErrors_delta=0,ifInUcastPkts=31817515328,ifInUcastPkts_prev=7994220518,ifInUcastPkts_rate=1378.9445882174,ifInUcastPkts_delta=23823294810,ifOutUcastPkts=14634124617,ifOutUcastPkts_prev=6626818711,ifOutUcastPkts_rate=463.48043934901,ifOutUcastPkts_delta=8007305906 WHERE port_id = 4164)#0 /opt/librenms/includes/polling/ports.inc.php(901): dbUpdate(Array, ‘ports’, ’port_id = ?’, Array)
#1 /opt/librenms/includes/polling/functions.inc.php(305): include(’/opt/librenms/i…’)
#2 /opt/librenms/poller.php(157): poll_device(Array, false)
#3 {main}

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

Component Version
LibreNMS 1.50.1
DB Schema 2019_02_10_220000_add_dates_to_fdb (132)
PHP 7.2.17-1+ubuntu16.04.1+deb.sury.org+3
MySQL 5.7.21-20-log
RRDTool 1.5.5
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

new librenms-service with 5 polllers.

9

Just noticed these same errors yesterday in the logs.

Our port association mode is ‘ifName’.

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

Component Version
LibreNMS 1.53.1
DB Schema 2019_05_30_225937_device_groups_rewrite (135)
PHP 7.2.19
MySQL 5.5.60-MariaDB
RRDTool 1.4.8
SNMP NET-SNMP 5.7.2

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

10

Just bumping the topic, as the issue is still actual in 1.57 .

11

Is this an actual issue?

12

This is an issue, because the Port_ID might change, and an update will report an error because some port-id allready exists.

I’ve debugged, the actual ifIndex bellow allready exists…

UPDATE ports set ifIndex=9,ifLastChange=192800,ifInOctets=344282942,ifInOctets_prev=15634164221,ifInOctets_rate=0,ifInOctets_delta=-15289881279,ifOutOctets=913979242,ifOutOctets_prev=40588341753,ifOutOctets_rate=0,ifOutOctets_delta=-39674362511,ifInErrors=0,ifInErrors_prev=0,ifInErrors_rate=0,ifInErrors_delta=0,ifOutErrors=0,ifOutErrors_prev=0,ifOutErrors_rate=0,ifOutErrors_delta=0,ifInUcastPkts=2935938,ifInUcastPkts_prev=157501635,ifInUcastPkts_rate=0,ifInUcastPkts_delta=-154565697,ifOutUcastPkts=2362235,ifOutUcastPkts_prev=136482756,ifOutUcastPkts_rate=0,ifOutUcastPkts_delta=-134120521,poll_time=1606306089,poll_prev=1605296002,poll_period=1010087 WHERE port_id = 491

ERROR:
[2020-11-25 12:08:09] production.ERROR: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘27-9’ for key ‘ports_device_id_ifindex_unique’ (SQL: UPDATE ports set ifIndex=9,ifLastChange=192800,ifInOctets=344282942,ifInOctets_prev=15634164221,ifInOctets_rate=0,ifInOctets_delta=-15289881279,ifOutOctets=913979242,ifOutOctets_prev=40588341753,ifOutOctets_rate=0,ifOutOctets_delta=-39674362511,ifInErrors=0,ifInErrors_prev=0,ifInErrors_rate=0,ifInErrors_delta=0,ifOutErrors=0,ifOutErrors_prev=0,ifOutErrors_rate=0,ifOutErrors_delta=0,ifInUcastPkts=2935938,ifInUcastPkts_prev=157501635,ifInUcastPkts_rate=0,ifInUcastPkts_delta=-154565697,ifOutUcastPkts=2362235,ifOutUcastPkts_prev=136482756,ifOutUcastPkts_rate=0,ifOutUcastPkts_delta=-134120521,poll_time=1606306089,poll_prev=1605296002,poll_period=1010087 WHERE port_id = 491) (SQL: UPDATE ports set ifIndex=9,ifLastChange=192800,ifInOctets=344282942,ifInOctets_prev=15634164221,ifInOctets_rate=0,ifInOctets_delta=-15289881279,ifOutOctets=913979242,ifOutOctets_prev=40588341753,ifOutOctets_rate=0,ifOutOctets_delta=-39674362511,ifInErrors=0,ifInErrors_prev=0,ifInErrors_rate=0,ifInErrors_delta=0,ifOutErrors=0,ifOutErrors_prev=0,ifOutErrors_rate=0,ifOutErrors_delta=0,ifInUcastPkts=2935938,ifInUcastPkts_prev=157501635,ifInUcastPkts_rate=0,ifInUcastPkts_delta=-154565697,ifOutUcastPkts=2362235,ifOutUcastPkts_prev=136482756,ifOutUcastPkts_rate=0,ifOutUcastPkts_delta=-134120521,poll_time=1606306089,poll_prev=1605296002,poll_period=1010087 WHERE port_id = 491)#0 /opt/librenms/includes/polling/ports.inc.php(906): dbUpdate(Array, ‘ports’, ’port_id = ?’, Array)
#1 /opt/librenms/includes/polling/functions.inc.php(317): include(’/opt/librenms/i…’)
#2 /opt/librenms/poller.php(140): poll_device(Array, false)
#3 {main}

The actual SQL constraits strickly prohibits this update:
ALTER TABLE ports
ADD PRIMARY KEY (port_id),
ADD UNIQUE KEY ports_device_id_ifindex_unique (device_id,ifIndex),
ADD KEY ports_ifdescr_index (ifDescr);

There should be some workaround done in discovery procedure, as to resolve this poller error when he tries to Update “ifIndex”, in fact, it shouldn’t update ifIndex! only discovery procedure should be allowed to do this, but validating duplicate ifIndex’s, maybe, assigning a temporary one during a “swap” of ifIndex’s , we should be careful on this swap, as to ensure that the right RRD file retains it’s relation with the right ifIndex, as per the “port association mode” in this case ‘ifName’, as it is also the one I’m using…

My Version:

Version 1.69-29-gf4ea49e38 - Thu Nov 12 2020 13:40:02 GMT+0000
Database Schema 2020_11_02_164331_add_powerstate_enum_to_vminfo (190)
Web Server
PHP 7.3.22
Python 3.8.2
MySQL 10.2.36-MariaDB-1:10.2.36+maria~bionic
Laravel 8.11.2
RRDtool 1.7.2