Ambari server upgrade fails with the error message "Error executing schema upgrade"

Products

• Services Suite

Issue/Introduction

Symptoms:

Upgrading the Ambari server fails with the error message, "Error executing schema upgrade".
 

The following output is produced when upgrading the Ambari server:

# rading ambari-server
Updating properties in ambari.properties ...
WARNING: Can not find ambari.properties.rpmsave file from previous version, skipping import of settings
WARNING: Can not find ambari-env.sh.rpmsave file from previous version, skipping restore of environment settings
Fixing database objects owner
Ambari Server configured for Embedded Postgres. Confirm you have made a  backup of the Ambari Server database [y/n] (y)?
Upgrading database schema
ERROR: Error executing schema upgrade, please check the server logs.
ERROR: Ambari server upgrade failed. lease look at /var/log/ambari-server/ambari-server.log, for more details.
ERROR: Exiting with exit code 11.
REASON: Schema upgrade failed.

The following output is produced in the/var/log/ambari-server/ambari-server.log.

05 May 2016 05:09:41,788  INFO [main] DBAccessorImpl:718 -
 Executing query: ALTER SCHEMA ambari OWNER TO "ambari";
05 May 2016 05:09:41,789  INFO [main] DBAccessorImpl:718 - Executing query: ALTER ROLE "ambari" SET search_path to 'ambari';
05 May 2016 05:09:41,797  INFO [main] DBAccessorImpl:718 - Executing query: UPDATE hostcomponentstate SET id = 1 WHERE cluster_id = 2 AND service_name = 'GANGLIA' AND component_name = 'GANGLIA_MONITOR' and host_id = 3
05 May 2016 05:09:41,799 ERROR [main] DBAccessorImpl:724 - Error executing query: UPDATE hostcomponentstate SET id = 1 WHERE cluster_id = 2 AND service_name = 'GANGLIA' AND component_name = 'GANGLIA_MONITOR' and host_id = 3

Environment

Cause

This issue is caused by a known code issue of AMBARI-13662 where the Ambari server fails to upgrade from v2.1.0 to v2.1.2.

Resolution

Follow the steps below to resolve the issue: 

1. From the Ambari server, connect to the PostgreSQL database.

# psql -p 10432 -U ambari -d ambari -W 

2. Confirm that there is a constraint of pk_hostcomponentstate with the ambari.hostcomponentstate table.

ambari=> \d+ hostcomponentstate
 Table "ambari.hostcomponentstate"
 Column Type| Modifiers | Storage | Description
-----------------------+------------------------+-----------+----------+-------------
cluster_id| bigint| not null | plain |
component_name  | character varying(255) | not null  | extended |
current_stack_version | character varying(255) | not null  | extended |
current_state | character varying(255) | not null  | extended |
service_name  | character varying(255) | not null | extended |
upgrade_state | character varying(32) || extended |
version | character varying(32)| extended |
security_state | character varying(32)| extended |
host_id| bigint| not null | plain |
current_stack_id | bigint|| plain |
id| bigint| not null | plain |
Indexes:
"pk_hostcomponentstate" PRIMARY KEY, btree (id)
"idx_host_component_state" btree (host_id, component_name, service_name, cluster_id)
Foreign-key constraints:
"fk_hcs_current_stack_id" FOREIGN KEY (current_stack_id) REFERENCES stack(stack_id)
"fk_hostcomponentstate_host_id" FOREIGN KEY (host_id) REFERENCES hosts(host_id)
"hstcomponentstatecomponentname" FOREIGN KEY (component_name, cluster_id, service_name) REFERENCES servicecomponentdesiredstate(component_name, cluster_id, service_name)
Has OIDs: no

3. Drop the constraint of pk_hostcomponentstate.

ambari=> alter table ambari.hostcomponentstate drop constraint pk_hostcomponentstate;
ALTER TABLE

4. Drop the column "id" from the ambari.hostcomponentstate table.

ambari=> alter table ambari.hostcomponentstate drop column id;

5. Rerun the Ambari server upgrade and start the Ambari server.

# ambari-server upgrade
# ambari-server start

6. Restart the Ambari Agent from all nodes in the cluster.

# ambari-agent start