CB Response: Postgres Error on Upgrade - "watchlist_action" violates foreign key constraint "watchlist_action_watchlist_id_fkey"

Products

• Carbon Black EDR (formerly Cb Response)

Issue/Introduction

• Error on upgrade :

sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) insert or update on table "watchlist_action" violates foreign key constraint "watchlist_action_watchlist_id_fkey" 
DETAIL: Key (watchlist_id)=(<watchlist_id>) is not present in table "watchlist_entries". 
[SQL: 'INSERT INTO watchlist_action (watchlist_id, alliance_feed_id, action_type) VALUES (%(watchlist_id)s, %(alliance_feed_id)s, %(action_type)s) RETURNING watchlist_action.id'] [parameters: {'action_type': 1, 'alliance_feed_id': None, 'watchlist_id': <watchlist_id>}] (Background on this error at: http://sqlalche.me/e/gkpj)

 

Environment

• CB Response Server: 6.3.0 and Higher

Cause

During upgrade the Postgres service fails checks due to a previously deleted watchlist having a reference in the watchlist_action_settings table.

Resolution

1. Ensure all cb-enterprise services are stopped. 

service cb-enterprise status 

service cb-enterprise stop

ps -ef | grep cb

• If anything is returned, kill those processes.

killall -KILL -u cb

 2. Start the cb-pgsql service : 

service cb-pgsql start

3. Login to the postgresql shell : 

sudo psql cb -p 5002

4. Verify there are one or more rows in the watchlist_action_settings table with the <watchlist_id> from the error: 

SELECT * FROM watchlist_action_settings WHERE watchlist_id='<watchlist_id>';

5. Verify there is NO result for the following query: 

SELECT * FROM watchlist_entries WHERE id=<watchlist_id>

6. Delete the entry once confirmed: 

DELETE FROM watchlist_action_settings WHERE watchlist_id='<watchlist_id>';

7. Stop the cb-pgsql service 

service cb-pgsql stop

8. Restart the upgrade

Additional Information

• Step 6 will permanently delete values from the database. Consider taking a backup before deleting the rows. 

  • pg_dump -C -Fp -f psqldump_full.sql cb -p 5002
    pg_dumpall -p 5002 --roles-only -f psqlroles.sql

• The <watchlist_id> will vary by environments. This value is tied to the specific ID of a watchlist that was deleted in the past. This value can be obtained from the error provided in they Symptoms section