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 (?, ?, ?)"

    [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 10.209.92.146
vpxd[25152] [Originator@6876 sub=Default opID=HB-host-154966@145444-717c198d]
-->
--> Panic: Unrecoverable VmRootError. Panic!

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

vSphere 6.7 is designed to eliminate the probability of this sequence value exhaustion.

This issue is resolved in vSphere 6.7 release, download is available at VMware Download.



 

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. VMware recommends that you stop the VMware VirtualCenter Server service and take a full, reliable backup of your database before attempting this process.
2. Connect to vPostgres vCenter database.
3. Execute following queries:

ALTER TABLE vpx_ip_address ALTER COLUMN surr_key type bigint;

Start vCenter Server services. For more information, see Stopping, starting, or restarting VMware vCenter Server 6.x services (2109881) and Stopping, starting, or restarting VMware vCenter Server Appliance 6.x services (2109887).

Additional Information

How to stop, start, or restart vCenter Server 6.x services
Stopping, starting, or restarting VMware vCenter Server Appliance 6.x services
Location of VMware vCenter Server 6.0 log files
Connecting to the embedded vPostgres Database in a Windows installed vCenter Server 6.0
Interacting with the vCenter Server Appliance 6.5 embedded vPostgres Database

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