Fixing MySQL errors "out of range"

Help
1

Running through a “./validate.php” it’s telling me that my database schema may be wrong and lists a couple MySQL commands to fix. When I run the commands on the database, I get “out of range” errors though. I’m not well versed in MySQL, any help on a fix?

librenms@librenms:~$ ./daily.sh
Updating SQL-Schema OK
Cleaning up DB OK
librenms@librenms:~$ ./validate.php

Component Version
LibreNMS 1.45-11-geea8ced
DB Schema 270
PHP 7.0.32-0ubuntu0.16.04.1
MySQL 5.7.23-0ubuntu0.16.04.1
RRDTool 1.5.5
SNMP NET-SNMP 5.7.3
====================================
[OK] Composer Version: 1.7.3
[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/ifInErrors_delta)
[FAIL] Database: incorrect column (ports/ifOutErrors_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 (LibreNMS) or the community site (Report database schema issues here - LibreNMS Community): 
    [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 `ifInErrors_delta` `ifInErrors_delta` bigint(20) unsigned NULL ;
     ALTER TABLE `ports` CHANGE `ifOutErrors_delta` `ifOutErrors_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 ;

mysql> use librenms;
Database changed
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 138
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 138
mysql>

2

Check what is at row 138, most likely means you need to update the data in that column to be a valid number.

3

I tried modifying the column value, which seems fine, and then the ALTER TABLE command error’d on another column. Did that several times and it just seems to be kicking the bucket down the line each time. There is just under 2500 columns in that table. Anyway to clean up the whole table in one go?

4

Show us what a bad entry looks like

5

I think this query is correct. Looked like port_id increments, so I used that to grab the specified row that the ALTER TABLE command failed at.

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 150
mysql> select port_id, ifInUcastPkts_delta FROM ports where port_id = 150;
±--------±--------------------+
| port_id | ifInUcastPkts_delta |
±--------±--------------------+
| 150 | 367 |
±--------±--------------------+
1 row in set (0.00 sec)

6

That one really doesn’t look wrong.

Might be just easier to set those columns to null: UPDATE ports SET ifInUcastPkts_delta=0, ifOutUcastPkts_delta=0;. The data will be populated again within 2 polls. Just re-run the alter queries straight after.

7

Thanks, I ran through and updated all the rows from each error. I’m getting a clean validate.php output now. Thanks for the help!