Loading
×Sorry to interrupt
CSS Error





When upgrading Kong a foreign key constraint error is raised

Title
When upgrading Kong a foreign key constraint error is raised
URL Name
When-upgrading-Kong-a-foreign-key-constraint-error-is-raised
Article Number
000001009
When upgrading Kong a foreign key constraint error is raised

time  May 5, 21 • Knowledge • Article Number: 000001009


QUESTION
When running kong migrations to upgrade kong, an error is raised the the foreign key constraint on rbac_user_roles is violated. The error message will look like the below but the actual value for the user_id will be different;
 
/usr/local/share/lua/5.1/kong/cmd/utils/migrations.lua:164: [PostgreSQL error] cluster_mutex callback threw an error: /usr/local/share/lua/5.1/kong/cmd/utils/migrations.lua:159: [PostgreSQL error] failed to run migration '001_034_to_035' up: ERROR: insert or update on table "rbac_user_roles" violates foreign key constraint "rbac_user_roles_user_id_fkey"
Key (user_id)=(c0c69b4c-93ac-48ff-9cd5-d863a266a3c2) is not present in table "rbac_users".
This is caused as when deleting Admins in Kong prior to 0.34-1, not all related data was deleted. The orhpaned data gives rise to the foreign key violation. From Kong EE 0.34-1 and later, deleting Admins correctly deletes all related data but you may see this error when upgrading if you have deleted Admins in earlier versions.

ANSWER
This error occurs when there is orphaned data in the RBAC tables that has not been deleted fully. To check and remove the orphaned data please run the statements below.

Note, before making any direct database changes, please ensure that you have a database backup to revert to in the event that you need to rollback the changes.

- To check the data that will be deleted;
SELECT * FROM rbac_user_roles WHERE user_id NOT IN (SELECT id FROM rbac_users);
SELECT * FROM rbac_user_roles WHERE role_id NOT IN (SELECT id FROM rbac_roles);

- To delete the data with missing id's;
DELETE FROM rbac_user_roles WHERE user_id NOT IN (SELECT id FROM rbac_users);
DELETE FROM rbac_user_roles WHERE role_id NOT IN (SELECT id FROM rbac_roles);

Was this article helpful?
0Likes0Dislikes
Loading