Fab75
Enthusiast
Enthusiast

vCenter Upgrade from 5.1 to 6.0 with external SQL 2008 fails during schema validation

Jump to solution

We have:

- vCenter 5.1 on Windows 2008 server

- external SQL2008R2 SP2

We are trying to upgrade to vCenter 6.0 on the Windows server, but during the validation we get this error: "Source vCenter Server Schema validation found an issue. Resolution: read the vcdb_err.log file and address the issues found."

This is the beginning of the vcdb_err.log:

_____________________________________________________

WARNING: Cannot execute statement(rc=100).

DELETE FROM VPX_TABLE

^^^^^^^^^^

WARNING: Cannot execute statement(rc=100).

DELETE FROM VPX_INDEX_COLUMN

^^^^^^^^^^

WARNING: Cannot execute statement(rc=100).

DELETE FROM VPX_SCHEMA_HASH

^^^^^^^^^^

Error while executing .\Upgrade-vMN-Next-to-v2013\mssql\validate_mssql.sql:176, reason: Cannot execute statement(rc=-1).

_____________________________________________________

Has anyone else run in this problem?

Regards,

F.

1 Solution

Accepted Solutions
Fitzx
Contributor
Contributor

I just dealt with this upgrading from 5.0 to 6.0.

Look right at the end of the error log - do you see something like ERROR ! Extra constraints: VPX_EXT_PRIVS.PK_VPX_EXT_PRIVS;    ?

If so, BACKUP THE DATABASE (just in case...), then connect to the database and execute the SQL command:

alter table VPX_EXT_PRIVS drop constraint PK_VPX_EXT_PRIVS

...then try the upgrade again.

View solution in original post

0 Kudos
11 Replies
lourdupaul
Contributor
Contributor

Hi

Did you find any workaround for this?

Pete

0 Kudos
JGGF
Contributor
Contributor

Another victim of this error.

I'm upgrading from 5.5 u2 to 6. We have also an external SQL 2008.

Will contact VMWare for support. Seems that there is no KB for this.

I've already faced these other errors:

VMware KB: Upgrading VMware vCenter Server from 5.x to 6.0 fails with the error: The user associated...

http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=211343...

0 Kudos
ahahum
Contributor
Contributor

I have this error too, JGGF. 

Any luck on your resolution?

0 Kudos
Robuea
Enthusiast
Enthusiast

Hi,

we have:

vCenter 5.1U3 (upgraded from clean install of 5.1.0b)

External MS SQL Server 2008 R2 SP1

Windows 2008 R2 SP1

our upgrade is failing the same:

---------------------------

install.vpxd.action.failed

---------------------------

VMware VirtualCenter failed firstboot.

Database in-place upgrade failed. Please see vcdb_inplace.err and vcdb_inplace.out for details.

Please refer to vSphere documentation to troubleshoot or Please contact VMware Support.

---------------------------

OK  

---------------------------

and in the vcdb_req.err file we also see:

WARNING: Cannot execute statement(rc=100).

DELETE FROM VPX_TABLE

^^^^^^^^^^

WARNING: Cannot execute statement(rc=100).

DELETE FROM VPX_INDEX_COLUMN

^^^^^^^^^^

WARNING: Cannot execute statement(rc=100).

DELETE FROM VPX_SCHEMA_HASH

^^^^^^^^^^

We are running the upgrade as a user with 'sa' rights to MSSQL and 'admin' rights to vCenter, SSO and Windows.  vCenter is installed to run as an AD service account.  This AD account has db_owner role assigned for the vCenter DB. 

Our original vCenter 5.1.0b DB was manually created, and then populated by the vCenter installer.  It does not use the VMX schema mentioned in the vSphere6 documentation.  Instead, the vSphere 5 installer mapped the AD service account to the vCenter DB user 'dbo', with a schema of 'dbo'.

Our upgrade would not progress until we added the AD Service account the ability to VIEW SERVER STATE to the instance.  Without this the installer failed accessing the system tables when trying to get the SQL instance port.  Apparently this is 'optional' in the vSphere 6 notes!

I have checked the SQL permissions, and remembered to add the temporary permissions to MSDB for the upgrade.

However - I CAN UPGRADE SUCCESSFULLY if I add the vCenter AD service account to the SQL role 'sysadmin' - so this has to be a SQL rights issue.  I could just do this temporarily for the install, then remove afterwards, but who knows what could break in the future if you do this.

I'll raise a support ticket with VMware and update if I get anything useful.

Regards,

Rob.

0 Kudos
Robuea
Enthusiast
Enthusiast

the end result of my support call was that this is a known issue:

http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=211976...

the resolution was to add the vc service account to the 'sysadmin' role in SQL for the upgrade.

This can then be removed after the upgrade, so it doesnt have access to any other DB's on the instance (i.e. SSO).

Regards,

Rob.

admin
Immortal
Immortal

those are warning messages. they are not the real error which prevented the upgrade.

you should take a look downwards for more specific issues which would start with "ERROR" instead of "warning".

Fitzx
Contributor
Contributor

I just dealt with this upgrading from 5.0 to 6.0.

Look right at the end of the error log - do you see something like ERROR ! Extra constraints: VPX_EXT_PRIVS.PK_VPX_EXT_PRIVS;    ?

If so, BACKUP THE DATABASE (just in case...), then connect to the database and execute the SQL command:

alter table VPX_EXT_PRIVS drop constraint PK_VPX_EXT_PRIVS

...then try the upgrade again.

0 Kudos
Ondrej_Pwc
Contributor
Contributor

Hi, I got similar problem.

Here is vcdb_req.err:

WARNING: Cannot execute statement(rc=100).

DELETE FROM VPX_TABLE

^^^^^^^^^^

WARNING: Cannot execute statement(rc=100).

DELETE FROM VPX_INDEX_COLUMN

^^^^^^^^^^

WARNING: Cannot execute statement(rc=100).

DELETE FROM VPX_SCHEMA_HASH

^^^^^^^^^^

ERROR! The following jobs should be cleaned up:

Past Day stats rollupvCenterDB

ERROR: insufficient-permissions

VIEW SERVER STATE

VIEW ANY DEFINITION

Any idrea how to solve this ?

0 Kudos
GhentGunn
Contributor
Contributor

I had an error almost the same as yours, only there was more tables under "ERROR! The following jobs should be cleaned up:"

I was searching for hours, and finally ran across a blog that mentioned you need to log in as the user that vCenter is running under. Logged into windows as the correct user before launching the installer and the pre-check gave no error.

It's one of those little things that you should know, but doesn't seem written down in a lot of obvious places.

0 Kudos
Fab75
Enthusiast
Enthusiast

After a more careful look we saw the vcdb_err.req.log reporting this error:

"1 [42000](50000) [Microsoft][SQL Server Native Client 10.0][SQL Server]ERROR ! Extra tables or columns: VPX_EVENT_ARG_BKP.ALARM_ID; VPX_EVENT_ARG_BKP.ARG_DATA; VPX_EVENT_ARG_BKP.ARG_ID;...." (followed by a long list of tables)

This was probably due to something the customer had done during the previous updates from version 3.x to 4.x (they couldn't remember what...).

Well, in this case we just dropped those tables and then the update worked, as suggested by KB 2113439 (changing the table names, of course).

Fabio

(My apologies to everyone for having forgotten to reply to this thread, hope it will still be useful for someone)

0 Kudos
it-eskky
Contributor
Contributor

We had a similar situation, but when upgrading 6.0 to 6.5 and with external SQL 2014:

vcsUpgrade/vcdb_req.err

at the end there is line:

1 [42000](50000) [Microsoft][SQL Server Native Client 11.0][SQL Server]ERROR ! Extra tables or columns: VPX_POPULATED_COUNTERS.INSERTED_COUNTER_ID; VPX_POPULATED_COUNTERS.SC_COUNTER_ID; VPX_POPULATED_COUNTERS.SC_STAT_ID;

SQL Server Management Studio, new query:

use VIM_VCDB

GO

alter table VPX_POPULATED_COUNTERS drop column INSERTED_COUNTER_ID

alter table VPX_POPULATED_COUNTERS drop column SC_COUNTER_ID

alter table VPX_POPULATED_COUNTERS drop column SC_STAT_ID

* if last one can't be executed, because SC_STAT_ID would be single one and table must have some content, then you can drop table itself:

drop table VPX_POPULATED_COUNTERS

After this upgrade worked ok.