ODBC error: (22003) - ERROR: integer out of range when vCenter Server Crashes

Products

• VMware vCenter Server

Issue/Introduction

This article provides a workaround to resolve the error when we try to insert higher number than permitted into an integer type of data.

Symptoms:

• For Postgres:
  • In the Postgres log, you see an error similar to:
    ERROR: integer out of range
• • In vpxd logs, you see entries similar to:

    [date time] error vpxd[7F4AB1866700] [Originator@6876 sub=Default opID=HB-host-xxx@xxxxxx-xxxxxxxx] An unrecoverable problem has occurred, stopping the VMware VirtualCenter service. Error: Error[VdbODBCError] (-1) "ODBC error: (22003) - ERROR: integer out of range; --> Error while executing the query" is returned when executing SQL statement "INSERT INTO VPX_IP_ADDRESS (ENTITY_ID, DEVICE_ID, IP_ADDRESS) VALUES (?, ?, ?)"

  • An unrecoverable problem has occurred, stopping the VMware VirtualCenter service. Error: Error[VdbODBCError] (-1) "ODBC error: (22003) - ERROR: integer out of range
    
    Error while executing the query" is returned when executing SQL statement "INSERT INTO VPX_DVHOST_HC_VLAN_RESULT (DVS_ID,HOST_ID,UPLINK_PORT_KEY,TRUNKED_FLG,VLAN_ID_START,VLAN_ID_END) VALUES (?,?,?,?,?,?)"

  • [date time] panic vpxd[7F4AB1866700] [Originator@6876 sub=Default opID=HB-host-932@203496-568eee72] --> --> Panic: Unrecoverable VmRootError. Panic! --> Backtrace:
• For MSSQL:
  • You see MSSQL error similar to:

error vpxd[25152] [Originator@6876 sub=vpxCommon opID=HB-host-154966@145444-717c198d] [Vpxd_HandleVmRootError] Received unrecoverable VmRootError. Generating minidump ...
error vpxd[25152] [Originator@6876 sub=Default opID=HB-host-154966@145444-717c198d] An unrecoverable problem has occurred, stopping the VMware VirtualCenter service. Error: Error[VdbODBCError] (-1) "ODBC error: (22003) - [Microsoft][SQL Server Native Client 10.0][SQL Server]Arithmetic overflow error converting IDENTITY to data type int." is returned when executing SQL statement "INSERT INTO VPX_IP_ADDRESS WITH (ROWLOCK) (ENTITY_ID, DEVICE_ID, IP_ADDRESS) VALUES (?, ?, ?)"
vpxd[19012] [Originator@6876 sub=Vsan opID=HB-host-158171@121744-4e57e889] [VsanClusterConfigIssueLRO] Host vim.HostSystem:host-158171 does not belong to a cluster
vpxd[07804] [Originator@6876 sub=HostAccess opID=HB-host-259529@862456-1a21aff4] Using vpxapi.version.version10 to communicate with vpxa at host **.***.**.***
vpxd[25152] [Originator@6876 sub=Default opID=HB-host-154966@145444-717c198d]
-->
--> Panic: Unrecoverable VmRootError. Panic!

Environment

vCenter 6
vCenter 7
vCenter 8

Cause

On large environments this sequence value can be exhausted after long usage. higher value than 2,147,483,647 would cause "integer out of range" failure and vCenter service crash.

Resolution

Workaround:
For MSSQL:

1. VMware recommends that you stop the VMware Virtual Center Server service and take a full, reliable backup of your database before attempting this process.
2. Connect to MS SQL Server vCenter database.
3. Execute following queries:

NOTE: Change VCDB with name vCenter database name.

USE VCDB
go
CREATE PROCEDURE REORDER_IP
AS
BEGIN
DECLARE @surrkey INT
SELECT @surrkey=MAX(SURR_KEY) FROM VPX_IP_ADDRESS;
IF @surrkey > 2147000000
   BEGIN
   Alter table VPX_IP_ADDRESS drop constraint PK_VPX_IP_ADDRESS;
   Alter table VPX_IP_ADDRESS drop column SURR_KEY;
   Alter table VPX_IP_ADDRESS add SURR_KEY int IDENTITY not null;
   Alter table VPX_IP_ADDRESS add CONSTRAINT PK_VPX_IP_ADDRESS PRIMARY KEY CLUSTERED (ENTITY_ID, DEVICE_ID, SURR_KEY);
   END
END
go


EXEC msdb.dbo.sp_update_jobstep
    @job_name = N'Event Task Cleanup VCDB',
    @step_id = 1,
    @on_success_action=3
GO

EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'Event Task Cleanup VCDB',
    @step_name = N'Re-order SURR_KEY column',
 @step_id = 2,
    @subsystem = N'TSQL',
    @command = N'EXECUTE REORDER_IP',
 @database_name = N'VCDB',
 @retry_attempts = 0,
 @retry_interval = 0
GO

For vPostgres:

1. Ensure to have full backup of vcenter taken prior, snapshots & offline snapshots if linked mode
2. Connect to vPostgres vCenter database.
   /opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB
3. Execute following query:
   
   ALTER TABLE vpx_ip_address ALTER COLUMN surr_key type bigint;
   
   NOTE: You may need to change "vpx_ip_address" to the table mentioned in the error message in /var/log/vmware/vpxd/vpxd.log
   
   For example:
   
   

   Error while executing the query" is returned when executing SQL statement "INSERT INTO VPX_DVHOST_HC_VLAN_RESULT (DVS_ID,HOST_ID,UPLINK_PORT_KEY,TRUNKED_FLG,VLAN_ID_START,VLAN_ID_END) VALUES (?,?,?,?,?,?)"

   Then the command to run would be:
   
   ALTER TABLE VPX_DVHOST_HC_VLAN_RESULT ALTER COLUMN surr_key type bigint;
   
4. Exit the Database:
   \q
5. Restart services:
   service-control --stop --all && service-control --start --all

 

Additional Information

Impact/Risks:
Workaround here would re-initialize the sequence values and no schema changes made to the vCenter database.