Monday, December 12, 2011

vCenter 5 upgrade failure 'failed to execute SQL script'


I had the pleasure of doing a View 4.6 -> 5.0 upgrade over the weekend, part of which involved upgrading the supporting vSphere 4.1 infrastructure. The View components upgraded fine (yay!) but we hit the wall when attempting to upgrade vCenter from 4.1 -> 5.0. As the installer attempted to update the database it would fail with the error:

failed to execute the SQL script.


Upon review of the VCDatabaseUpgrade.log we see:

[10/12/2011 9:36:41 PM] Error: Failed to execute command: if exists(select 1 from information_schema.columns
where TABLE_NAME='VPX_HIST_STAT1'
AND COLUMN_NAME='COUNTER_ID'
AND DATA_TYPE='numeric')
ALTER TABLE VPX_HIST_STAT1 ALTER COLUMN COUNTER_ID BIGINT NOT NULL;
[10/12/2011 9:36:41 PM] Got exception: ERROR [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]The index '_dta_index_VPX_HIST_STAT1_5_2002106173__K2_1' is dependent on column 'COUNTER_ID'.
ERROR [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]ALTER TABLE ALTER COLUMN COUNTER_ID failed because one or more objects access this column.
[10/12/2011 9:36:41 PM] Error while upgrading: ERROR [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]The index '_dta_index_VPX_HIST_STAT1_5_2002106173__K2_1' is dependent on column 'COUNTER_ID'.
ERROR [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]ALTER TABLE ALTER COLUMN COUNTER_ID failed because one or more objects access this column.
[10/12/2011 9:36:41 PM] Info: Exiting Upgrade Wizard
[10/12/2011 9:36:41 PM] Successfully run: alter database [PRODVC] set recovery SIMPLE

Subsequently, if you were to re-try the install against the same DB the installer would succeed, however the application would fail to start citing error in the vpxd.log

[VpxdMain] Failed to initialize: vmodl.fault.ManagedObjectNotFound

So it appears as though the failed installer modified the database enough, that it *seems* like a valid vSphere 5 DB, when it has not actually updated the required tables.

arent you glad you took a backup :D

I wont bother you all with the hours of wailing, hair pulling , waiting for DB restores, reading frustratingly similar KB articles, waiting for the support guys etc etc…..it was a dark night…..

Thankfully, we happened to have a fantastic DBA (you know who you are) on call who noticed that he could see the offending index (_dta_index_VPX_HIST_STAT1_5_2002106173__K2_1) when he listed all the indexes on the DB, but he could not drop the index as it would return that he had insufficient permissions, or the index did not exist.

Side note: these DTA indexes are created by the SQL server thanks to the "Database Tuning Engine Advisor"

He then put us onto the following code to strip these DTA indexes from the DB: http://bit.ly/w2ljr8

After dropping the indexes, the upgrade proceeded fine.

Hopefully this will help someone else before the KB comes out….

1 comments:

Anonymous said...

We also had the same problem. The cause was the lack of some unused and deleted indexes of the VMWare database. To solve that problem we check the existence of every 5.1 index. To verify the indexes we made an SQL script like that:
IF NOT EXISTS (SELECT 1 FROM sysindexes WHERE name = 'VPX_TOPN_PAST_DAY_P1') print Index VPX_TOPN_PAST_DAY_P1 is Missing'
The list of indexes can be obtained from the 5.1 installer in the manual creation database script.
You only need to create the missing indexes and try to install again.

Labels

blogs I read

Authors

My photo

Enjoys spending time in layer 6 & 7. Passionate about delivering quality education.
Based in Sydney.

certz