Unable to modify Edge gateway in vCloud Director (KB#00104)

Hello Guys,

This is not a general issue everyone faces but I have this issue in my platform and customer used to report it after every few days. To help myself and team, I am creating this blog but I will be glad if it help anyone out there.

Issue - Edge gateway modification action failing repeatedly with below like error -

[ e0fe75fa-4eff-48d5-9d9a-73f0020a29d5 ] Cannot update edge gateway "Edge-Gateway" - Cannot update edge gateway "Edge-Gateway" - Could not execute JDBC batch update - Batch entry 0 /* Method: unknown */ /* Method: unknown */ /* delete com.vmware.vcloud.common.model.net.AllocatedIpAddressModel */ delete from allocated_ip_address where id='5a6ee85c-f052-4ba5-b77f-b7932302b4df'::uuid was aborted: ERROR: update or delete on table "allocated_ip_address" violates foreign key constraint "fk_alloc_ip_gw_assigned_ip" on table "gateway_assigned_ip" Detail: Key (id)=(5a6ee85c-f052-4ba5-b77f-b7932302b4df) is still referenced from table "gateway_assigned_ip". Call getNextException to see other errors in the batch. - ERROR: update or delete on table "allocated_ip_address" violates foreign key constraint "fk_alloc_ip_gw_assigned_ip" on table "gateway_assigned_ip" Detail: Key (id)=(5a6ee85c-f052-4ba5-b77f-b7932302b4df) is still referenced from table "gateway_assigned_ip".

Observation - Even if you try to update the description, it fails with similar error. No update is possible until and unless you resolve above mystery.

Resolution - It will be fixed from VCD DB but if you are not that good in DB then I would suggest to open a case with VMware. They will help you out for sure. They always keep on saying that don't change anything in the DB by your own and we will fix it for you. Leverage the support! :) But sometime, you have to fix the issue right now. In that situation, this can help you. I am little good now in DB and know what I am doing so I used to a lot operations in the VCD DB. It is up to you. 

So, if you read the error then you will come to know that the DB table "allocated_ip_address" is having some issues with id "5a6ee85c-f052-4ba5-b77f-b7932302b4df". Let's see what is this ID.

1. Login primary vcd cell and then DB with below command

root@vcdcell01# sudo -u postgres psql vcloud

1. vcloud=# select * from allocated_ip_address where id='5a6ee85c-f052-4ba5-b77f-b7932302b4df';

You will have output like below

-[ RECORD 1 ]-+-------------------------------------

id            | 5a6ee85c-f052-4ba5-b77f-b7932302b4df

scope_id      | 72ead83f-7216-436b-a94f-43cd01aa534e

address       |

addr1         | 968820163

timeout       | 9999-12-31 23:59:59.997

last_modified | 2020-03-06 15:54:51.327

state         | 1

Now run next command in the DB only

2. vcloud=# select * from logical_network where scope_id = '72ead83f-7216-436b-a94f-43cd01aa534e';

You will get possibly below output

-[ RECORD 0 ]-+-------------------------------------

 Now you need to find out that where this address is configured. Simplest way, as this error on a edge gateway so, go to edge gateway in GUI, browse to the network and subnet and try to find this IP address which must be assigned to one of the external network. Once you find it then run below command

3. vcloud=# select * from logical_network where name = 'problematic_network_name';

Note that problematic network is the name of the network which has the IP address From above command you will have the ip_scope_id value. Note that please.

Now to fix the issue, please run below command.

4. vcloud=# update allocated_ip_address set scope_id = 'ip_scope_id value, noted from command 3' where id = '5a6ee85c-f052-4ba5-b77f-b7932302b4df';

Now once updated, you will see that there is no more error on the edge gateway while updating it.

Also, this is one of the use case where you can have this issue. I myself know few more use cases which are tough to explain in post.

If you have such issue at your workplace and if VMware too struggle a lot about this, you may try this or reach me out. I will try my best to help you in this situation.