question Re: org.apache.ambari.server.AmbariException: Cannot add foreign key constraint in Archives of Support Questions (Read Only)

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

chrsvarma — Fri, 16 Sep 2022 10:01:57 GMT

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?

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

aervits — Thu, 04 Feb 2016 02:09:56 GMT

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

chrsvarma — Thu, 04 Feb 2016 02:19:54 GMT

@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

aervits — Thu, 04 Feb 2016 02:23:03 GMT

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

chrsvarma — Thu, 04 Feb 2016 02:50:00 GMT

@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

aervits — Thu, 04 Feb 2016 02:59:38 GMT

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

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

chrsvarma — Thu, 04 Feb 2016 08:10:00 GMT

@Artem Ervits no it is not

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

nsabharwal — Thu, 04 Feb 2016 08:36:17 GMT

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

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

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

(1 row)

ambari213=> select * from cluster_version;

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

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

nsabharwal — Thu, 04 Feb 2016 08:41:39 GMT

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

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

chrsvarma — Fri, 05 Feb 2016 08:33:32 GMT

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.

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

aervits — Fri, 05 Feb 2016 08:35:14 GMT

@Raja Sekhar Chintalapati did that solve your issue?

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

chrsvarma — Fri, 05 Feb 2016 08:35:35 GMT

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