SQL Logger Error: Insertion of large integer value.

Posted on
Thu Oct 29, 2020 12:35 pm
autolog offline
Posts: 3988
Joined: Sep 10, 2013
Location: West Sussex, UK [GMT aka UTC]

SQL Logger Error: Insertion of large integer value.

I am testing out the excellent new miniUniFi plugin from Joe. :)

Using it, I am experiencing an error with the SQL Logger of the form:
29 Oct 2020 at 17:44:01
Enabling plugin "miniUniFi 0.0.15"
Starting plugin "miniUniFi 0.0.15" (pid 71581)
Started plugin "miniUniFi 0.0.15"
miniUniFi Starting miniUniFi
miniUniFi new device 2: Starting Device
miniUniFi UniFi Cloud Key Controller G2: Starting Device
SQL Logger Error exception trying to insert row into table device_history_1825415154 ("new device 2") -- unable to modify table
SQL Logger Error ERROR: value "13125719727" is out of range for type integer at character 14741

SQL Logger Error INSERT INTO device_history_1825415154 ("port_table_5_poe_power", "switch_caps_max_aggregate_sessions", "port_table_0_tx_bytes", "port_table_1_op_mode", "port_table_4_tx_bytes", "port_table_8_sfp_serial", "port_table_4_enable", "uplink_type", "port_table_6_full_duplex", "port_table_3_tx_broadcast", "mgmt_network_id", "port_table_1_rx_bytes", "port_table_3_satisfaction",
.... [full screen of fields listed]

The issue appears to be that the states causing the issue are defined to Indigo as an integer. This displays correctly in the Indigo custom states. However, the state value is being inserted by SQL Logger into an int field defined for the state in the device history for the device. The value is larger than can be accommodated for an int. It seems a bigint might be required?

According to the PostgreSQL documentation, the maximum value an int field can hold is 2147483647 and these state values exceed that. The state values are a probably lifetime total of tx and rx bytes.

Is there a way round this?

Posted on
Thu Oct 29, 2020 12:37 pm
FlyingDiver offline
User avatar
Posts: 7189
Joined: Jun 07, 2014
Location: Southwest Florida, USA

Re: SQL Logger Error: Insertion of large integer value.

Also, to clarify, these states are dynamically defined as type "Number" using this method:

Code: Select all
                    dynamic_state = self.getDeviceStateDictForNumberType(unicode(key), unicode(key), unicode(key))

joe (aka FlyingDiver)
my plugins: http://forums.indigodomo.com/viewforum.php?f=177

Posted on
Thu Oct 29, 2020 3:02 pm
matt (support) offline
Site Admin
User avatar
Posts: 21411
Joined: Jan 27, 2003
Location: Texas

Re: SQL Logger Error: Insertion of large integer value.

This is a buglet in the SQL Logger that we've already fixed and will be in 7.4.2.

Image

Page 1 of 1

Who is online

Users browsing this forum: No registered users and 1 guest