Support Questions

Find answers, ask questions, and share your expertise

org.apache.ambari.server.AmbariException: Cannot add foreign key constraint

avatar
Super Collaborator

ambari upgrade

upgrading ambari-server from 1.7 to 2.2 with mysql 5.6

while running ambari-server upgrade it failed with org.apache.ambari.server.AmbariException: Cannot add foreign key constraint

any work around, i couldn't even create the FK manually either.. is it a known issue?

1 ACCEPTED SOLUTION

avatar
Super Collaborator

This is known issue for MYSQL DBs and we need to convert all the tables to use InnoDB engine.

You can use the below SQL to generate the commands to alter the tables to InnoDB engine.

SELECT concat('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;')

FROM Information_schema.TABLES

WHERE TABLE_SCHEMA = 'ambaridb' AND ENGINE = 'MyISAM' AND TABLE_TYPE='BASE TABLE';

Once we get the output , remove the ' | ' and run the alter commands.

Once we run alter commands, commit and exit mysql.

and than run ambari-server upgrade.

View solution in original post

11 REPLIES 11

avatar
Master Mentor

what version of HDP is it? @Raja Sekhar Chintalapati you need to upgrade your Ambari 1.7 to 2.1 first then to 2.2. You also need to make sure JDK is at least 1.7 with Ambari 2.2. So follow the documentation for upgrade of Ambari and review system requirements. You also have to be on certain OS levels, RHEL 5 was deprecated.

avatar
Super Collaborator

@Artem Ervits HW doc doesnt say anything about that...

If your current Ambari version is 1.6.1 or below, you must upgrade the Ambari Server version to 1.7 before upgrading to version 2.2.

avatar
Master Mentor

there are system requirements you need to strictly follow like OS and JDK. Try upgrading your ambari to 2.0 first. @Raja Sekhar Chintalapati

avatar
Super Collaborator
@Artem Ervits

Error executing query: ALTER TABLE cluster_version ADD CONSTRAINT FK_cluster_version_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id)

clusters table has one row and cluster_version table is empty. that was the reason it is not able to add FK

avatar
Master Mentor

@Raja Sekhar Chintalapati so you're past the error now?

avatar
Super Collaborator

@Artem Ervits no it is not

avatar
Master Mentor
@Raja Sekhar Chintalapati

The good news is that you can upgrade from 1.7 to 2.2 directly. "You mentioned this already"

Issue is with the bug in the upgrade process.

You followed the instructions in this link and hitting this error

Error executing query: ALTER TABLE cluster_version ADD CONSTRAINT FK_cluster_version_cluster_id FOREIGN KEY (cluster_id) REFERENCES clusters (cluster_id) clusters table has one row and cluster_version table is empty. that was the reason it is not able to add FK

ambari213=> select * from clusters;

cluster_id | resource_id | cluster_info | cluster_name | provisioning_state | security_type | desired_cluster_state | desired_stack_id

------------+-------------+--------------+--------------+--------------------+---------------+-----------------------+------------------

2 | 4 | | HDPTEST | INSTALLED | NONE | | 4

(1 row)

ambari213=> select * from cluster_version;

id | repo_version_id | cluster_id | state | start_time | end_time | user_name

----+-----------------+------------+---------+---------------+---------------+------------

1 | 1 | 2 | CURRENT | 1447960627766 | 1447960627785 | _anonymous

(1 row)

ambari213=>

Please see the above. We need to find the root cause on "0 rows in cluster_version"

Can you paste the output of above queries? Also, Do you have access to support?

avatar
Master Mentor

@Raja Sekhar Chintalapati Did you backup the db before upgrade?

avatar
Super Collaborator

@Neeraj Sabharwal yea...we did follow with support and they said it is a know issue...i posted the comments below..alll we need to do is change the DB engine for all the tables which are MyISAM to InnoDB.

thanks for your response though..