Unable to remove an IP address in Plesk: The IP address is already used for hosting

Symptoms

1. An attempt to delete old IP address from Tools & Settings > IP Addresses fails:

Error: The IP address `<ip_address>` is already used for hosting.

2. One of the following symptoms may take place in Tools & Settings > IP Addresses:

• no sites are assigned to the old IP address;
• after switching the domains to a new IP, some of them are still shown as assigned to the old address.

Cause

Plesk database inconsistency. The
psa.IpAddressesCollections
table was not correctly updated when IP addresses were changed for domains.

Resolution

1. Login to Plesk via SSH or RDP.
2. Back up Plesk database.
3. Login to MySQL server.
4. Find out which "id" is assigned to the IP address in question, and find any (sub)domains that have this IP address assigned (replace the 203.0.113.2 with actual IP address to be deleted):

   mysql> select @ip_id := id from IP_Addresses where ip_address='203.0.113.2';
   mysql> SELECT d.name, d.webspace_id, ipac.ipCollectionId,ds.type,ip.ip_address FROM DomainServices ds INNER JOIN IpAddressesCollections ipac ON ds.ipCollectionId = ipac.ipCollectionId INNER JOIN domains d ON d.id = ds.dom_id JOIN IP_Addresses ip on ipac.ipaddressid=ip.id WHERE (ds.type = 'web' OR ds.type = 'mail') and ipac.ipAddressId = @ip_id;

5. Find records in the
IpAddressesCollections
table that correspond to the IP address in question:

   mysql> select * from IpAddressesCollections where ipaddressid=@ip_id;

6. Find out which
id
is assigned to the IP address that should be used for the (sub)domains from the step 5 command output. To find it out, use the first query from step 4 placing existing, correct IP to the query.
7. Update the corresponding records of the
IpAddressesCollections
table. For example, the address 203.0.113.3   should be assigned to
example.com
:

   mysql> set @domain_name := 'example.com';
   mysql> select @ip_id_new := id from IP_Addresses where ip_address='203.0.113.3';
   mysql> update IpAddressesCollections ipac INNER JOIN DomainServices ds ON ds.ipCollectionId = ipac.ipCollectionId INNER JOIN domains d ON d.id = ds.dom_id JOIN IP_Addresses ip on ipac.ipaddressid=ip.id set ipac.ipAddressId=@ip_id_new WHERE (ds.type = 'web' OR ds.type = 'mail') and ipac.ipAddressId = @ip_id and d.name=@domain_name;