Database Schema Error - Interface Counter Delta

Help
1

In the process of tracking down another issue, I’ve noticed that I have some issues with my database schema.

# ./validate.php
====================================
Component | Version
--------- | -------
LibreNMS  | 1.51-18-geb8600e
DB Schema | 2019_02_10_220000_add_dates_to_fdb (132)
PHP       | 7.1.8
MySQL     | 5.7.24
RRDTool   | 1.4.8
SNMP      | NET-SNMP 5.7.2
====================================

[OK]    Composer Version: 1.8.5
[OK]    Dependencies up-to-date.
[OK]    Database connection successful
[FAIL]  Database: incorrect column (ports/ifInUcastPkts_delta)
[FAIL]  Database: incorrect column (ports/ifOutUcastPkts_delta)
[FAIL]  Database: incorrect column (ports/ifInOctets_delta)
[FAIL]  Database: incorrect column (ports/ifOutOctets_delta)
[FAIL]  We have detected that your database schema may be wrong, please report the following to us on Discord (https://t.libren.ms/discord) or the community site (https://t.libren.ms/5gscd):
	[FIX]:
	Run the following SQL statements to fix.
	SQL Statements:
	 ALTER TABLE `ports` CHANGE `ifInUcastPkts_delta` `ifInUcastPkts_delta` bigint(20) unsigned NULL ;
	 ALTER TABLE `ports` CHANGE `ifOutUcastPkts_delta` `ifOutUcastPkts_delta` bigint(20) unsigned NULL ;
	 ALTER TABLE `ports` CHANGE `ifInOctets_delta` `ifInOctets_delta` bigint(20) unsigned NULL ;
	 ALTER TABLE `ports` CHANGE `ifOutOctets_delta` `ifOutOctets_delta` bigint(20) unsigned NULL ;

However, when I try to fix the schema, it doesn’t work.

mysql> ALTER TABLE `ports` CHANGE `ifInUcastPkts_delta` `ifInUcastPkts_delta` bigint(20) unsigned NULL ;
ERROR 1264 (22003): Out of range value for column 'ifInUcastPkts_delta' at row 15422
mysql> ALTER TABLE `ports` CHANGE `ifOutUcastPkts_delta` `ifOutUcastPkts_delta` bigint(20) unsigned NULL ;
ERROR 1264 (22003): Out of range value for column 'ifOutUcastPkts_delta' at row 15422
mysql> ALTER TABLE `ports` CHANGE `ifInOctets_delta` `ifInOctets_delta` bigint(20) unsigned NULL ;
ERROR 1264 (22003): Out of range value for column 'ifInOctets_delta' at row 15225
mysql> ALTER TABLE `ports` CHANGE `ifOutOctets_delta` `ifOutOctets_delta` bigint(20) unsigned NULL ;
ERROR 1264 (22003): Out of range value for column 'ifOutOctets_delta' at row 15225
mysql>

My unscientific conclusion is that this is because there’s a negative delta value in those rows which of course won’t work with an unsigned integer. Any suggestions?

2

I would suggest to update the value of ‘ifOutOctets_delta’ and ‘ifInOctets_delta’ whenever the current value is ‘<0’.
Most probably you have a disabled device or so, that has a wrong value currently and it prevents the ALTER TABLE to be successful.

Bye

3