VDSL2 stats are not shown in Web UI and SQL Integrity constraint violation errors in logs

Sweeny42 · 2 December 2023 13:42

Hi all,
I have an issue when running discovery against a Draytek 167. The VDSL2 statistics aren’t shown in the web UI and an SQL error is present in the logs:

Error discovering xdsl module for 192.168.2.1.[0m PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0' for key 'ports_adsl_port_id_unique' in /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php:580

My install appears to be correct with no issues:

===========================================
Component | Version
--------- | -------
LibreNMS  | 23.11.0-14-g13c934ab0 (2023-12-01T23:34:21+00:00)
DB Schema | 2023_11_21_172239_increase_vminfo.vmwvmguestos_column_length (274)
PHP       | 8.1.26
Python    | 3.9.2
Database  | MariaDB 10.11.5-MariaDB-log
RRDTool   | 1.7.2
SNMP      | 5.9
===========================================

[OK]    Composer Version: 2.6.5
[OK]    Dependencies up-to-date.
[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
[OK]    Database schema correct
[OK]    MySQl and PHP time match
[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

Please find as well the full output of the Discovery processes in the PasteBin: Untitled - LibreNMS

Any help with this issue would be greatly appreciated, thank you in advance.

PipoCanaja · 3 December 2023 19:55

Hi @Sweeny42
Do you have this issue with all your Draytek 167 devices, or only one ? If yes, it might be something with this one (was it added a long time ago, a migration might have done something weird with one DB column) ?

Sweeny42 · 3 December 2023 22:29

Hi @PipoCanaja,
I sadly only have a single Draytek 167 to test with. I have tried re-adding the device etc but to no avail. same issue is produced. This is a rather old install, I believe it’s been running for over 3 years so it’s possible a migration may have gone a tad sideways. Is there any method to do a healthcheck on the DB?
Many thanks.

Sweeny42 · 3 December 2023 23:54

In further answer to your question, no this device was only added in the last week.

PipoCanaja · 4 December 2023 09:37

Nope, if the device was added last week, then it did not have time to suffer from any migration issue.

Could you collect the SNMP data with this command:

snmpwalk -v2c -OUneb -Cc -c <community> <ipOfDevice> . > capture.ouneb.txt and send me the result here ? That should allow me to reproduce the issue, and hopefully fix it.

Sweeny42 · 4 December 2023 11:57

Hi @PipoCanaja

Of course, please find the output below in the following link: https://pastebin.com/raw/J67y1T8M

Do let me know if I can be of any further help and thanks again for looking into this issue for me.

PipoCanaja · 4 December 2023 19:06

issue reproduced. Will try to fix it

PipoCanaja · 4 December 2023 19:38

Hi @Sweeny42
Found the issue, but this is a bug in the device itself. The device provides “0” as the index, instead of the index of interface that is the DSL port. So LibreNMS cannot find the interface to attach to and does indeed crash.
I’ll remove the crash by checking if mapping is wrong, but I cannot fix the device to provide the real index. So your best hope is to upgrade the device to the latest version and hope they did fix it. Or change device of course

librenms@librenms:~$ '/usr/bin/snmpbulkwalk' '-M' '/opt/librenms/mibs' '-m' 'SNMPv2-TC:SNMPv2-MIB:IF-MIB:IP-MIB:TCP-MIB:UDP-MIB:NET-SNMP-VACM-MIB' '-v2c' '-c' 'draytek_full'  'udp:127.1.6.1:1161' 'ADSL-LINE-MIB::adslMibObjects'
ADSL-LINE-MIB::adslLineCoding.0 = INTEGER: dmt(2)
ADSL-LINE-MIB::adslLineType.0 = INTEGER: fastOrInterleaved(4)
ADSL-LINE-MIB::adslLineSpecific.0 = OID: SNMPv2-SMI::zeroDotZero
ADSL-LINE-MIB::adslLineConfProfile.0 = STRING: VDSL2
<snip>
ADSL-LINE-MIB::adslAturChanCurrTxRate.0 = Gauge32: 10233856 bps
ADSL-LINE-MIB::adslAturChanPrevTxRate.0 = Gauge32: 0 bps
ADSL-LINE-MIB::adslAturChanCrcBlockLength.0 = Gauge32: 2

PipoCanaja · 4 December 2023 21:48

Fix is here :

github.com/librenms/librenms

bug - ADSL ifIndex to port error not handled

librenms:master ← PipoCanaja:dray

opened 07:45PM - 04 Dec 23 UTC

PipoCanaja

+6 -0

Function ifIndexToId (in `$portAdsl->port_id = $os->ifIndexToId($ifIndex);`) can… fail and return 0 instead of a valid port_id. A crash of XDSL module will occur when syncing data if we don't catch it properly here. ``` PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '0' for key 'ports_adsl_port_id_unique' in /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php:580 Stack trace: #0 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php(580): PDOStatement->execute() #1 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php(788): Illuminate\Database\Connection->Illuminate\Database\{closure}() #2 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php(755): Illuminate\Database\Connection->runQueryCallback() #3 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php(581): Illuminate\Database\Connection->run() #4 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Connection.php(533): Illuminate\Database\Connection->statement() #5 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Query/Processors/Processor.php(32): Illuminate\Database\Connection->insert() #6 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(3383): Illuminate\Database\Query\Processors\Processor->processInsertGetId() #7 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php(1925): Illuminate\Database\Query\Builder->insertGetId() #8 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php(1333): Illuminate\Database\Eloquent\Builder->__call() #9 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php(1298): Illuminate\Database\Eloquent\Model->insertAndSetId() #10 /opt/librenms/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Model.php(1137): Illuminate\Database\Eloquent\Model->performInsert() #11 /opt/librenms/vendor/laravel/framework/src/Illuminate/Collections/HigherOrderCollectionProxy.php(60): Illuminate\Database\Eloquent\Model->save() #12 /opt/librenms/vendor/laravel/framework/src/Illuminate/Collections/Traits/EnumeratesValues.php(236): Illuminate\Support\HigherOrderCollectionProxy->Illuminate\Support\{closure}() #13 /opt/librenms/vendor/laravel/framework/src/Illuminate/Collections/HigherOrderCollectionProxy.php(61): Illuminate\Support\Collection->each() #14 /opt/librenms/LibreNMS/DB/SyncsModels.php(70): Illuminate\Support\HigherOrderCollectionProxy->__call() #15 /opt/librenms/LibreNMS/Modules/Xdsl.php(163): LibreNMS\Modules\Xdsl->syncModels() #16 /opt/librenms/LibreNMS/Modules/Xdsl.php(74): LibreNMS\Modules\Xdsl->pollAdsl() #17 /opt/librenms/includes/discovery/xdsl.inc.php(8): LibreNMS\Modules\Xdsl->discover() #18 /opt/librenms/includes/discovery/functions.inc.php(164): include('...') #19 /opt/librenms/discovery.php(108): discover_device() #20 {main} ``` After fix: ``` #### Load disco module xdsl #### ADSL( Failed to discover this port, ifIndex invalid : 2/33.17 Mbps/10.23 Mbps) .. >> Runtime for discovery module 'xdsl': 0.1390 seconds with 443096 bytes >> SNMP: [3/0.13s] MySQL: [3/0.02s] RRD: [0/0.00s] #### Unload disco module xdsl #### ``` DO NOT DELETE THE UNDERLYING TEXT #### Please note > Please read this information carefully. You can run `./lnms dev:check` to check your code before submitting. - [x] Have you followed our [code guidelines?](https://docs.librenms.org/Developing/Code-Guidelines/) - [x] If my Pull Request does some changes/fixes/enhancements in the WebUI, I have inserted a screenshot of it. - [x] If my Pull Request makes discovery/polling/yaml changes, I have added/updated [test data](https://docs.librenms.org/Developing/os/Test-Units/). #### Testers If you would like to test this pull request then please run: `./scripts/github-apply <pr_id>`, i.e `./scripts/github-apply 5926` After you are done testing, you can remove the changes with `./scripts/github-remove`. If there are schema changes, you can ask on discord how to revert.

It removes the crash only in your case.

Sweeny42 · 4 December 2023 22:54

Hi @PipoCanaja
Thanks again for the validation check. I will contact Draytek with a bug report and hope to hear back soon. I have merged your pull request and can confirm the check works as expected for me. Once again thanks!

system · 11 December 2023 22:54

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.