MSSQL

Unable to create subscription/domain: IP address does not exist

Symptoms
An attempt to create a subscription / domain fails with the following error:
Error: Unable to find service node for ip address with id=2
Error: Failed domain creation: Unable to update domain data: IP address with ID ‘2’ does not exist
It is not possible to manage Web Hosting Access. An attempt fails with the following error:
Error: Unable to find service node for ip address with id=2

Cause

Database inconsistency.
Resolution

Create a backup of the psa database:

# mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` psa  > /root/psa.sql

Check if the IP_Addresses table has record(s) with an IP address that does not exist in the ip_pool tables:

    mysql> select * from ip_pool where ip_address_id not in (select id from IP_Addresses);
    +----+---------------+--------+
    | id | ip_address_id | type   |
    +----+---------------+--------+
    |  1 |             2 | shared |
    +----+---------------+--------+
    1 row in set (0.00 sec)

    mysql> select * from IP_Addresses where id =2;
    Empty set (0.00 sec)

    mysql> select id, serviceNodeId, ip_address from IP_Addresses;
    +----+---------------+--------------+
    | id | serviceNodeId | ip_address   |
    +----+---------------+--------------+
    |  1 |             1 | x.x.x.x      |
    +----+---------------+--------------+
    1 rows in set (0.00 sec)

As can be seen in this example, the ip_pool table includes an entry regarding a nonexistent IP address ( ip_address_id = 2);

Delete this record from the ip_pool table:

    mysql> delete from ip_pool where ip_address_id=2;
    Query OK, 1 row affected (0.00 sec)