Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Solved Go to solution

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

Expert Contributor

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

Accepted Solutions

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

Expert Contributor

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.

11 REPLIES 11

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

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.

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

Expert Contributor

@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.

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

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

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

Expert Contributor
@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

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

Mentor

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

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

Expert Contributor

@Artem Ervits no it is not

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

@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?

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

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

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

Expert Contributor

@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..

Don't have an account?
Coming from Hortonworks? Activate your account here