Performance data error when upgrading to vCenter Server 6.0
search cancel

Performance data error when upgrading to vCenter Server 6.0

book

Article ID: 322180

calendar_today

Updated On:

Products

VMware vCenter Server

Issue/Introduction

Set the VPX_U1 index to case sensitive in the vCenter Server database to resolve performance data issues after upgrading vCenter Server to 6.0.

Symptoms:
  • New performance data is not collected after upgrading vCenter Server from 5.x to 6.0.
  • In the performance data charts, you see this error:

    Performance data is currently not available for this entity

    Note: For additional symptoms and log entries, see the Additional Information section.
  • From vCenter Server 5.x with external MSSQL who have configured statistics at level 3 or higher, would not see any new data coming in.
    Similar error messages is seen in vpxd log files:
2015-07-09T03:58:05.015+02:00 error vpxd[07000] [Originator@6876 sub=StatsCollector] Flush failed. Error : "ODBC error: (23000) - [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert duplicate key row in object 'dbo.vpx_device' with unique index 'VPX_DEVICE_U1'. The duplicate key value is (host/system/ft)." is returned when executing SQL statement "INSERT INTO VPX_DEVICE( DEVICE_ID, DEVICE_NAME) VALUES(?,?)"^M


Environment

VMware vCenter Server 6.0.x

Cause

This issue occurs due to a unique case insensitive index in the vCenter Server database schema.

Resolution

To resolve this issue, set the VPX_U1 index to be case sensitive:

Note:
  1. Using the vCenter Server database user(e.g. vpxuser), connect to the vCenter Server database(e.g. VCDB). You can use Management Studio for SQL Server.
  2. Change the VPX_DEVICE_U1 index to be case sensitive:
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'VPX_DEVICE_U1')
   DROP INDEX VPX_DEVICE_U1 ON VPX_DEVICE;
go

ALTER TABLE VPX_DEVICE ALTER COLUMN DEVICE_NAME NVARCHAR(450) COLLATE Latin1_General_CS_AS;
go

CREATE UNIQUE INDEX VPX_DEVICE_U1 ON VPX_DEVICE(DEVICE_NAME);
go​


Additional Information

You experience these additional symptoms:
  • Performance data gathered before the upgrade is visible.
  • In the C:\ProgramData\VMware\vCenterServer\logs\vmware-vpx\vpxd.log file, you see entries similar to:

    <YYYY-MM-DD>T<time>error vpxd[07000] [Originator@6876 sub=Default] [VdbStatement] Execute result code: -1</time>
    <YYYY-MM-DD>T<time>warning vpxd[07000] [Originator@6876 sub=Default] [VdbStatement] SQL execution failed: INSERT INTO VPX_DEVICE( DEVICE_ID, DEVICE_NAME) VALUES(?,?)</time>
    <YYYY-MM-DD>T<time>warning vpxd[07000] [Originator@6876 sub=Default] [VdbStatement] Execution elapsed time: 4 ms</time>
    <YYYY-MM-DD>T<time>warning vpxd[07000] [Originator@6876 sub=Default] [VdbStatement] Statement diagnostic data from driver is 23000:0:2601:[Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert duplicate key row in object 'dbo.vpx_device' with unique index 'VPX_DEVICE_U1'. The duplicate key value is (host/system/ft).</time>
    <YYYY-MM-DD>T<time>warning vpxd[07000] [Originator@6876 sub=Default] [VdbStatement] Statement diagnostic data from driver is 01000:1:3621:[Microsoft][SQL Server Native Client 10.0][SQL Server]The statement has been terminated.</time>
    <YYYY-MM-DD>T<time>error vpxd[07000] [Originator@6876 sub=Default] [Vdb::IsRecoverableErrorCode] Unable to recover from 23000:2601</time>
    <YYYY-MM-DD>T<time>error vpxd[07000] [Originator@6876 sub=Default] [Vdb::IsRecoverableErrorCode] Unable to recover from 01000:3621</time>
    <YYYY-MM-DD>T<time>error vpxd[07000] [Originator@6876 sub=Default] [VdbStatement] SQLError was thrown: "ODBC error: (23000) - [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert duplicate key row in object 'dbo.vpx_device' with unique index 'VPX_DEVICE_U1'. The duplicate key value is (host/system/ft)." is returned when executing SQL statement "INSERT INTO VPX_DEVICE( DEVICE_ID, DEVICE_NAME) VALUES(?,?)"</time>
    <YYYY-MM-DD>T<time>error vpxd[07000] [Originator@6876 sub=StatsCollector] Flush failed. Error : "ODBC error: (23000) - [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert duplicate key row in object 'dbo.vpx_device' with unique index 'VPX_DEVICE_U1'. The duplicate key value is (host/system/ft)." is returned when executing SQL statement "INSERT INTO VPX_DEVICE( DEVICE_ID, DEVICE_NAME) VALUES(?,?)"</time>
    <YYYY-MM-DD>T<time>error vpxd[07000] [Originator@6876 sub=StatsCollector] Commit failed. Records: 20, SQL : insert into VPX_HIST_STAT1_53 (counter_id, time_id, stat_val) values(?, ?, ?), Error : "ODBC error: (23000) - [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert duplicate key row in object 'dbo.vpx_device' with unique index 'VPX_DEVICE_U1'. The duplicate key value is (host/system/ft)." is returned when executing SQL statement "INSERT INTO VPX_DEVICE( DEVICE_ID, DEVICE_NAME) VALUES(?,?)"</time>
    <YYYY-MM-DD>T<time>error vpxd[07000] [Originator@6876 sub=MoDatacenter] VpxdVmOps::CollectLocal database error while flushing stats data: "ODBC error: (23000) - [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert duplicate key row in object 'dbo.vpx_device' with unique index 'VPX_DEVICE_U1'. The duplicate key value is (host/system/ft)." is returned when executing SQL statement "INSERT INTO VPX_DEVICE( DEVICE_ID, DEVICE_NAME) VALUES(?,?)"</time>


    Note: This log excerpt is an example. Date, time, and environmental variables may vary depending on your environment.

Backing up the vCenter Server database running on Microsoft SQL or SQL Express server
升级到 vCenter Server 6.0 时遇到性能数据错误
vCenter Server 6.0 にアップグレードするときのパフォーマンス データ エラー

Powered by Wolken Software