VPXD fails to start with error | Error while executing the query" is returned when executing SQL statement "INSERT INTO VPX_HOST_VM_CONFIG_OPTION"

Products

• VMware vCenter Server

Issue/Introduction

VPXD service  fails to start with error | Error while executing the query" is returned when executing SQL statement "INSERT INTO VPX_HOST_VM_CONFIG_OPTION"
core dumps generating under /var/core.


vpxd.log
[YYYY-MM-DDTHH:MM:SS] error vpxd[#####] [Originator@#### sub=Default opID=HB-host-##@#####-########] An unrecoverable problem has occurred, stopping the VMware VirtualCenter service. Error: Error[VdbODBCError] (-1) "ODBC error: (XX00#) - ERROR: invalid page in block #### of relation base/#####/#####;--> Error while executing the query" is returned when executing SQL statement "INSERT INTO VPX_HOST_VM_CONFIG_OPTION (HOST_ID,CONFIG_OPTION_VER,DATA,ARRAY_INDEX,CONFIG_OPTION_DESC,CREATE_SUPPORTED_FLG,DEFAULT_CONFIG_OPTION_FLG,RUN_SUPPORTED_FLG,UPGRADE_SUPPORTED_FLG) VALUES (?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?)"[YYYY-MM-DDTHH:MM:SS] panic vpxd[#####] [Originator@#### sub=Default opID=HB-host-##@#####-########]

postgres log will have entries similar to:

postgresql.log:

[YYYY-MM-DDTHH:MM:SS] UTC ########.#### #  LOG: Writing instance status...
[YYYY-MM-DDTHH:MM:SS] UTC ########.#### #  LOG: Wrote instance status successfully.
[YYYY-MM-DDTHH:MM:SS] UTC ########.#### #  LOG: Updated instance status successfully.
[YYYY-MM-DDTHH:MM:SS] UTC ########.#### ######## VCDB vc WARNING: page verification failed, calculated checksum #### but expected #####
[YYYY-MM-DDTHH:MM:SS] UTC ########.#### ######## VCDB vc ERROR: invalid page in block #### of relation base/#####/#####
[YYYY-MM-DDTHH:MM:SS] UTC ########.#### ######## VCDB vc STATEMENT: INSERT INTO VPX_HOST_VM_CONFIG_OPTION (HOST_ID,CONFIG_OPTION_VER,DATA,ARRAY_INDEX,CONFIG_OPTION_DESC,CREATE_SUPPORTED_FLG,DEFAULT_CONFIG_OPTION_FLG,RUN_SUPPORTED_FLG,UPGRADE_SUPPORTED_FLG) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9),($10,$11,$12,$13,$14,$15,$16,$17,$18),($19,$20,$21,$22,$23,$24,$25,$26,$27),($28,$29,$30,$31,$32,$33,$34,$35,$36),($37,$38,$39,$40,$41,$42,$43,$44,$45),($46,$47,$48,$49,$50,$51,$52,$53,$54),($55,$56,$57,$58,$59,$60,$61,$62,$63),($64,$65,$66,$67,$68,$69,$70,$71,$72),($73,$74,$75,$76,$77,$78,$79,$80,$81),($82,$83,$84,$85,$86,$87,$88,$89,$90),($91,$92,$93,$94,$95,$96,$97,$98,$99),($100,$101,$102,$103,$104,$105,$106,$107,$108),($109,$110,$111,$112,$113,$114,$115,$116,$117),($118,$119,$120,$121,$122,$123,$124,$125,$126),($127,$128,$129,$130,$131,$132,$133,$134,$135)
[YYYY-MM-DDTHH:MM:SS] UTC ########.#### # VCDB vc LOG: unexpected EOF on client connection with an open transaction
[YYYY-MM-DDTHH:MM:SS] UTC ########.#### # LOG: Updating instance status...

Environment

VMware vCenter Server 6.7.x
VMware vCenter Server 7.0.0

Cause

This can happen due to Postgres corruption if VCSA was not shutdown properly.

Resolution

 Make sure we have offline snapshots of VC's part of ELM before proceeding with next steps.

 Connect to VCDB: /opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres

Since this particular issue is related to "vpx_host_vm_config_option", follow the below steps:

VCDB=# create table vpx_host_vm_config_option_temp AS select * from vpx_host_vm_config_option;
VCDB=# truncate table vpx_host_vm_config_option;
VCDB=# insert into vpx_host_vm_config_option select * from vpx_host_vm_config_option_temp ;
VCDB=# reindex table vpx_host_vm_config_option;
VCDB=# drop table vpx_host_vm_config_option_temp;
VCDB=# vacuum verbose;


Exit from DB and start vpxd service.

 

Additional Information

If the above steps doesn't work, there can be high chance of Postgres corruption which can't be repairable, so we can advise customer to restore VC from backup.