Datastore moref NULL (Exception: Index: 0, Size: 0 error)

Once upon a time, our engineering team was facing an interesting situation with some VMs in vCloud Director (version 9.5.0.6). The same situation occurs after upgrade from 9.7.0.6 to 10.2.1. Clients were complaining about the inability to change any properties in “Hardware section” of VM and “Unavailiable” status of VM console monitor.

WARNING: Before you start create a DB backup. For making any changes in the DB of products you should contact support and create SR to get proper assistance! Use materials from this post at your own risk. We don’t take responsibility and/or give any warranty if you reuse this content.

[ 424d2aab-9ecc-450a-9d1a-fae9cf09174c ] Index: 0, Size: 0:
java.lang.IndexOutOfBoundsException: Index: 0, Size: 0
    at java.util.ArrayList.rangeCheck(ArrayList.java:657)
    at java.util.ArrayList.get(ArrayList.java:433)
    at com.vmware.ssdc.backend.dao.impl.VAppVirtualMachineDaoImpl.getStorageContainerValRefForVappVm(VAppVirtualMachineDaoImpl.java:121)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:283)

The reason why we got this error: vCloud Director via SQL query was getting incorrect value in the moref datastore field (NULL). In a normal situation we should moref of Datastore (for example for VM called srv415 datastore moref is datastore-224).

select * from vapp_vm where name like '%srv415%'

SELECT dsInv.moref, dsInv.parent_type, dsInv.parent_moref, dsInv.vc_id FROM datastore_inv dsInv
INNER JOIN vm ON (dsInv.vc_id = vm.vc_id AND dsInv.moref = vm.dstore_moref)
INNER JOIN vapp_vm ON vm.id = vapp_vm.svm_id WHERE vapp_vm.id = 0x392592495E37438690F01262279B6C85;

For an unknown reason, we were facing an issue where a lot of VMs in vCloud Director DB in vm table got Datastore moref NULL values. We supposed that these problems could be caused by problems on the storage side or because of storage outage. By the logic of vCloud Director when there is some storage outage internal mechanism change moref Datastore of affected VM’s to NULL.

To fix these problems we should get all VMs that were affected, get their datastore moref, and update their datastore moref record in the vm table.

Steps to fix the issue:

1) We can find all of this VMs with SQL query:

select * from vm where creation_status = 'RESOLVED' and dstore_moref IS NULL order by location_path

If you got the same problem then the output of the command should be like this:

[DataStore_SSD_L03] dvl-gfs-wrk-1-MVCw/dvl-gfs-wrk-1-MVCw.vmx
[DataStore_SSD_L04] rds-ts-jgUo/rds-ts-jgUo.vmx
[DataStore_SSD_L06] FS-M-KLx9/FS-M-KLx9.vmx
[DataStore_SSD_L08] traffic-ceph-1-3wRO/traffic-ceph-1-3wRO.vmx

2) Then you should get Datastore moref that will be based on Datastore name, where affected VMs are placed with this query:

select * from datastore_inv where vc_display_name like '%DataStore_SSD_L03%'

Data that you need is placed in the field with the name ‘moref’. For example, datastore-104410.
3) Finally, we should create a query that will update NULL entries in the vm table with the needed dstore_moref that we’ve got in the previous step for every Datastore. For example, if we need to fix VMs that are placed on datastore DataStore_SSD_L03 (moref datastore-104410) SQL query looks like:

update vm set dstore_moref = 'datastore-104410' where creation_status = 'RESOLVED' and dstore_moref IS NULL and location_path like '%DataStore_SSD_L03%'

After upgrade from 9.7.0.6 to 10.2.1 and repeating this situation we get more convient and faster way to update all the broken VM using next query. This query creates all Update strings with needed datastore moref and VM UUIDs that are afftected (vm.dstore_moref is NULL and vm_inv.is_deleted is false):

select 'update vm set dstore_moref = ' || '''' || datastore_inv.moref || '''' || ' where id = ' || '''' || vm.id || '''' || ';' from vm

inner join vapp_vm on vapp_vm.svm_id = vm.id

inner join vm_inv on vm_inv.moref = vm.moref

inner join datastore_inv on datastore_inv.vc_display_name = (substring(vm.location_path,2,(POSITION(']' in vm.location_path))-2))

where vm.dstore_moref is NULL and vm_inv.is_deleted is false;

After running this query we'll get output with one row that we should copy to text editor and remove symbol "

After that copy all the queries and run them in pgAdmin. After all queries is done check that now VM settings can be changed. Awesome!