Ambari upgrade_id shows NULL in clusters tables, not able to upgrade ambari because of this.

[#1]

I checked the Ambari DB and Saw some inconsistencies while upgrading it,

mysql> select cluster_id,resource_id,upgrade_id,provisioning_state,security_type,desired_stack_id from clusters;
+------------+-------------+------------+--------------------+---------------+------------------+
| cluster_id | resource_id | upgrade_id | provisioning_state | security_type | desired_stack_id |
+------------+-------------+------------+--------------------+---------------+------------------+
|          2 |           4 |       NULL | INSTALLED          | KERBEROS      |                2 |
+------------+-------------+------------+--------------------+---------------+------------------+
1 row in set (0.00 sec)
mysql> update clusters SET upgrade_id = '' where cluster_id = 2;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ambari`.`clusters`, CONSTRAINT `FK_clusters_upgrade_id` FOREIGN KEY (`upgrade_id`) REFERENCES `upgrade` (`upgrade_id`))

Getting the Above error while updating the Upgrade_id.

How to update it. Please help.

[#2]

@arjun more

Why do you want to set an Empty String to the upgrade_id column? The upgrade_id column is mapped with the "upgrade" table's (upgrade_id).

The NULL value in the clusters table (upgrade_id) field shows that the upgrade is not yet started.

.

Can you please share the error that made you edit that table?

[#3]

I want to set this filed to blank value. And I am getting error,
ERROR 1452(23000):Cannot add or update a child row: a foreign key constraint fails (`ambari`.`clusters`, CONSTRAINT `FK_clusters_upgrade_id` FOREIGN KEY (`upgrade_id`) REFERENCES `upgrade`(`upgrade_id`))

[#4]

@arjun more

What we wanted to say is ... that the "NULL" value is how it is shown for MySQL.

Other databases like PostgreSQL might show that NULL column value as blank.

So as you are using MySQL so the NULL value is fine there (until there is a upgrade already going on) ... Otherwise the value for that column in MYSQL can be seen as NULL.

So your Database shows correct value if there is no upgrade initiated.

NULL is not same as blank string. So your update query is Incorrect where you are trying to set a blank string value there.

For Example: in Postgres same value you can see as blank (Empty) but actually that is NULL

# psql -U ambari ambari
Password for user ambari: 
psql (9.2.18)
Type "help" for help.

ambari=> SELECT cluster_id , resource_id , upgrade_id , cluster_info , cluster_name, provisioning_state FROM clusters;
 cluster_id | resource_id | upgrade_id | cluster_info | cluster_name | provisioning_state 
------------+-------------+------------+--------------+--------------+--------------------
          2 |           4 |            |              | plain_ambari | INSTALLED

<br>

.

Similar Table on MySQL is shown as following with NULL value:

mysql> SELECT cluster_id , resource_id , upgrade_id , cluster_info , cluster_name, provisioning_state FROM clusters;
+------------+-------------+------------+--------------+--------------+--------------------+
| cluster_id | resource_id | upgrade_id | cluster_info | cluster_name | provisioning_state |
+------------+-------------+------------+--------------+--------------+--------------------+
|          2 |           4 |       NULL |              | plain        | INSTALLED          |
+------------+-------------+------------+--------------+--------------+--------------------+

The Main Question here is "Why do you want to set the value of that column to blank String" ? It is already NULL.

[#5]

Thanks for all discriptive answer.

[#6 · Accepted solution]

@arjun more

You don't need to edit this column. As the error itself says "FOREIGN KEY (`upgrade_id`)" this will be set as @Jay SenSharma suggested.

Please check the Type of column you are trying to edit,

mysql> desc clusters;
+-----------------------+--------------+------+-----+---------+-------+
| Field                 | Type         | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------+-------+
| cluster_id            | bigint(20)   | NO   | PRI | NULL    |       |
| resource_id           | bigint(20)   | NO   | MUL | NULL    |       |
| upgrade_id            | bigint(20)   | YES  | MUL | NULL    |       |
| cluster_info          | varchar(255) | NO   |     | NULL    |       |
| cluster_name          | varchar(100) | NO   | UNI | NULL    |       |
| provisioning_state    | varchar(255) | NO   |     | INIT    |       |
| security_type         | varchar(32)  | NO   |     | NONE    |       |
| desired_cluster_state | varchar(255) | NO   |     | NULL    |       |
| desired_stack_id      | bigint(20)   | NO   | MUL | NULL    |       |

As the Column is of type bigint(20), it's default value is "NULL".

The blank filed will be show in postgresql DB because of the type of column upgrade_id.

[#7]

Thanks nshelke it explains everything.