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
2023-03-13T14:05:53.492+07:00 error vpxd[23421] [Originator@6876 sub=Default opID=HB-host-35@47794-4ba99c20] An unrecoverable problem has occurred, stopping the VMware VirtualCenter service. Error: Error[VdbODBCError] (-1) "ODBC error: (XX001) - ERROR: invalid page in block 4740 of relation base/16386/21037;--> 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 (?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?)"2023-03-13T14:05:53.496+07:00 panic vpxd[23421] [Originator@6876 sub=Default opID=HB-host-35@47794-4ba99c20]

postgres log will have entries similar to:

postgresql.log:

2023-03-13 07:20:34.957 UTC 640ece4f.89a3 0  LOG: Writing instance status...
2023-03-13 07:20:34.957 UTC 640ece4f.89a3 0  LOG: Wrote instance status successfully.
2023-03-13 07:20:34.957 UTC 640ece4f.89a3 0  LOG: Updated instance status successfully.
2023-03-13 07:20:36.413 UTC 640ecebf.c0e4 24999847 VCDB vc WARNING: page verification failed, calculated checksum 9516 but expected 29432
2023-03-13 07:20:36.413 UTC 640ecebf.c0e4 24999847 VCDB vc ERROR: invalid page in block 4740 of relation base/16386/21037
2023-03-13 07:20:36.413 UTC 640ecebf.c0e4 24999847 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)
2023-03-13 07:20:40.899 UTC 640ecec8.c838 0 VCDB vc LOG: unexpected EOF on client connection with an open transaction
2023-03-13 07:20:42.997 UTC 640ece4f.89a3 0  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.