vpxd fails to start and crashes due to "ODBC error: (22001) - ERROR: value too long for type character varying(30)"

Products

VMware vCenter Server

Issue/Introduction

This article was written for a specific issue. Please verify all of the symptoms before following the recommendations under "Workaround"

Symptoms:

when attempting to restart vpxd, if fails and a core dump is written in /var/core
after vcsa reboot the services crashes as well

vpxd logs in /var/log/vmware/vpxd/ contain the following entries:

2021-09-27T09:22:56.103Z error vpxd[07163] [Originator@6876 sub=Default opID=DbParallelLoad-1c06dac8] [VdbStatement] Execute result code: -1
2021-09-27T09:22:56.103Z error vpxd[07163] [Originator@6876 sub=Default opID=DbParallelLoad-1c06dac8] [VdbStatement] SQL execution failed: insert into VPX_TABLE (TABLE_NAME, COL_NAME, COL_TYPE, COL_LEN, PK_FL
G) values (?, ?, ?, ?, ?)
2021-09-27T09:22:56.103Z error vpxd[07163] [Originator@6876 sub=Default opID=DbParallelLoad-1c06dac8] [VdbStatement] Execution elapsed time: 20 ms
2021-09-27T09:22:56.103Z error vpxd[07163] [Originator@6876 sub=Default opID=DbParallelLoad-1c06dac8] [VdbStatement] Statement diagnostic data from driver is 22001:0:1:ERROR: value too long for type character
 varying(30);
--> Error while executing the query
...
2021-09-27T09:22:56.103Z error vpxd[07163] [Originator@6876 sub=Default opID=DbParallelLoad-1c06dac8] [Vdb::IsRecoverableErrorCode] Unable to recover from 22001:1
2021-09-27T09:22:56.103Z error vpxd[07163] [Originator@6876 sub=Default opID=DbParallelLoad-1c06dac8] [VdbStatement] SQLError was thrown: "ODBC error: (22001) - ERROR: value too long for type character varying(30);
--> Error while executing the query" is returned when executing SQL statement "insert into VPX_TABLE (TABLE_NAME, COL_NAME, COL_TYPE, COL_LEN, PK_FLG) values (?, ?, ?, ?, ?)"
2021-09-27T09:22:56.103Z error vpxd[07163] [Originator@6876 sub=Default opID=DbParallelLoad-1c06dac8] [VdbSchemaLoader::StoreTableSchemaInfoToDB] Error inserting into VPX_TABLE for table VPX_NON_ORM_VM_CONFIG_INFO: "ODBC error: (22001) - ERROR: value too long for type character varying(30);
--> Error while executing the query" is returned when executing SQL statement "insert into VPX_TABLE (TABLE_NAME, COL_NAME, COL_TYPE, COL_LEN, PK_FLG) values (?, ?, ?, ?, ?)"

Cause

This issue can occur due to incorrect entries in the vCenter database in the DVS port group configuration

Resolution

connect to VCSA per SSH
change from the default appliance shell into the BASH shell by running:
```
# shell
```

start psql:

# /opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres

run the following commands to identify the entries:

with uopg as (select dvs_id, id, dvportgroup_name, dvportgroup_key, dvportgroup_type, uplink_portgroup_flg from VPX_DVPORTGROUP where uplink_portgroup_flg=1)
select vds.dvs_id, vds.dvportgroup_id , vds.vlan_id, vds.vlan_object_flg from VPX_DVPORT_SETTING  vds 
join uopg on (vds.dvs_id = uopg.dvs_id and vds.dvportgroup_id=uopg.id)
order by 1,2;

any lines in the output with a value not NULL (or empty) for vlan_id or where vlan_object_flg is not 3 can cause the issue and should be changed.

for example the second entry in the following output is wrong and needs to be changed:

 dvs_id | dvportgroup_id | vlan_id | vlan_object_flg
--------+----------------+---------+-----------------
     28 |             29 |         |               3
 701937 |         701938 |    3333 |               1

note down the values in dvs_id and dvportgroup_id

next, run the following command to identify further entries that need to be changed:

with vlan as (select dvs_id,dvportgroup_id,runtimeinfo_flg,vlan_id_start,vlan_id_end, dvport_key from VPX_DVPORT_VLAN  where dvportgroup_id <> 0  order by 1,2)
select vdvpg.dvs_id, vdvpg.id, dvportgroup_name,  vlan.runtimeinfo_flg,vlan.vlan_id_start,vlan.vlan_id_end, vlan.dvport_key from VPX_DVPORTGROUP  vdvpg
left join vlan on (vlan.dvs_id=vdvpg.dvs_id and vlan.dvportgroup_id=vdvpg.id)
where vdvpg.uplink_portgroup_flg=1;

Example for the output of this command:

 dvs_id |    id  |     dvportgroup_name      | runtimeinfo_flg | vlan_id_start | vlan_id_end | dvport_key
--------+--------+---------------------------+-----------------+---------------+-------------+------------
   2011 |   2012 | dswitch-DVUplinks-2011    |               0 |             0 |        4094 |
 701937 | 701938 | dswitch-DVUplinks-701937  |                 |               |             |

Any lines in this output with NULL (empty) values for runtimeinfo_flg and vlan_id_start as well as vlan_id_end should be corrected as well, therefore similar as before, note down the dvs_id and the id (which is the dvportgroup_id)

Using the values noted down before, run the following commands to fix the entry (replace <dvs_id> and <dvportgroup_id> with the correct values from above:

# update vpx_dvport_setting set vlan_object_flg =3 where dvs_id=<dvs_id> and dvportgroup_id =<dvportgroup_id>;
# update vpx_dvport_setting set vlan_id=NULL where dvs_id=<dvs_id> and dvportgroup_id =<dvportgroup_id>;
# insert into vpx_dvport_vlan (dvs_id, dvportgroup_id, runtimeinfo_flg, vlan_id_start, vlan_id_end) values(<dvs_id>,<dvportgroup_id>,0,0,4094);

to follow the example above you would run:

# update vpx_dvport_setting set vlan_object_flg =3 where dvs_id=701937 and dvportgroup_id =701938;
# update vpx_dvport_setting set vlan_id=NULL where dvs_id=701937 and dvportgroup_id =701938;
# insert into vpx_dvport_vlan (dvs_id, dvportgroup_id, runtimeinfo_flg, vlan_id_start, vlan_id_end) values(701937,701938,0,0,4094);

exit psql and start vpxd by running:
```
# vmon-cli -i vpxd
```

Additional Information

Impact/Risks:
The commands in this article are writing directly into the vCenter database. Please ensure that you have a fresh backup of vCenter when following these recommendations.