cannot delete user "Associated data exists, please delete them first"

[maddyobrienjones]

When trying to delete a user, the error message "Associated data exists, please delete them first" appears. All charts and dashboards created by that account have been deleted. I have also set the user as inactive and deleted its role setting. I am able to edit the user but not delete it.

I found the following similar issue which was marked as stale with no response: https://github.com/apache/superset/issues/8752

Expected results

Successful deletion of a user

Actual results

"Associated data exists, please delete them first" error message

Logs when navigating to user list and trying to delete user (domain has been replaced with deploymentlink.com): 10.4.15.23 - - [25/Feb/2021:19:48:28 +0000] "GET /users/list/ HTTP/1.1" 200 26741 "deploymentlink.com/superset/welcome" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.150 Safari/537.36" 10.4.15.23 - - [25/Feb/2021:19:48:34 +0000] "POST /users/delete/3 HTTP/1.1" 302 299 "deploymentlink.com/users/list" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.150 Safari/537.36" 10.4.15.23 - - [25/Feb/2021:19:48:34 +0000] "GET /users/list/ HTTP/1.1" 200 26786 "-" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/88.0.4324.150 Safari/537.36"

Screenshots

How to reproduce the bug

1. Go to 'List Users'
2. Navigate to user row
3. Click on 'Delete' icon
4. See error

Environment

(please complete the following information):

• superset version: superset version: Superset 0.999.0dev
• python version: python --version: 3.7.9
• node.js version: node -v: couldn't find

Checklist

Make sure to follow these steps before submitting your issue - thank you!

• [x] I have checked the superset logs for python stacktraces and included it here as text if there are any.
• [ ] I have reproduced the issue with at least the latest released version of superset.
• [x] I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

none

[oguzhanyediel]

Any update?

[maddyobrienjones]

no updates from my end

[oguzhanyediel]

Thanks for the quick reply @maddyobrienjones

I encountered the same error a couple of days ago. I don't know what I will do because I can't remove the user although all charts and dashboards created by that user have been deleted.

By the way, the reason that I want for the user removal is the following situation.

I don't know again what I will do and I wanted to delete&add the user again. It will be greatly appreciated if anyone knows a solution to this strange situation.

Note: There are many dashboards, and not even one of them appeared in the 'ADD TO DASHBOARD' part. Superset Version: 0.999.0dev

[oguzhanyediel]

My second problem is solved by @nytai He told me that any user who wants to add a chart to any dashboard may have to add himself/herself as an owner of the dashboard.

The other error 'Associated data exists, please delete them first' is still ongoing.

[kushmangal]

Any update here?

[maddyobrienjones]

no updates from me. this is low priority in my backlog of tasks.

[nytai]

I think there's also a logs table. Tbh deleting a user if quite difficult since cascade hasn't been set on a lot of the foreign key relationships, so you’ll have to find them and delete the records manually.

If possible, just mark the user as inactive and change all the fields that might cause issues with unique constraints if the same user is trying to sign up again

[nytai]

Also this stackoverflow post mentions a query you can run to find all the foreign key constraints on ab_users.id

[Petah]

I tried to delete a user I just made, and it doesn't work giving this error. How does a new user own anything, and how can I find and delete it?

[sdezza]

Same problem here. Really need something to be able to delete a user

[ChangbingChen]

I think there's also a logs table. Tbh deleting a user if quite difficult since cascade hasn't been set on a lot of the foreign key relationships, so you’ll have to find them and delete the records manually.

If possible, just mark the user as inactive and change all the fields that might cause issues with unique constraints if the same user is trying to sign up again

@nytai , it's great! it works for me. When i tried to delete a user, it took a long time and timed out(log info, [CRITICAL] WORKER TIMEOUT (pid:5783)). So i deleted the records of the logs table, and the user can be deleted normally.

[bjornhauge]

I'm getting General Error <class 'sqlalchemy.exc.CircularDependencyError'> whenever I try to delete a user. If the delete button doesn't work, then let's just get rid of it. Especially because the UI even has a message saying

"It's not good policy to remove a user, just make it inactive"

[simonvanderveldt]

Has anything been done for this for 2.0.0? Deletion should just work, after a warning everything should just automatically be removed, one shouldn't have to manually go through tabs or cleaning up records in a database (to be able to) delete a user.

[bonamim]

Hey guys,

The version 2.0.0 has the same problem ⚠️ We were able to reproduce the problem after a user ran a query, and then we were unable to remove this user.

It was necessary to remove the user data in this case from two tables:

• logs
• query

[ivan-price-acted]

puting this here for future use:

-- user to be deleted is id XXX
delete from favstar where user_id=XXX;
delete from key_value where changed_by_fk=XXX;
delete from key_value where created_by_fk=XXX;
delete from logs where user_id=XXX;
delete from ab_user where id=XXX;

[C-monC]

This script has worked for me without problems and I continue to use it. It was written quickly so test it first.

The reason for the select is that I use this with a != on the surname.

delete from favstar where user_id in (select id from ab_user where email = 'users-email');
delete from key_value where changed_by_fk in (select id from ab_user where email = 'users-email');
delete from key_value where created_by_fk in (select id from ab_user where email = 'users-email');
delete from logs where user_id in (select id from ab_user where email = 'users-email');
delete from ab_user_role where user_id in (select id from ab_user where email = 'users-email');
update ab_user set created_by_fk=null  where created_by_fk in (select id from ab_user where email = 'users-email');
update ab_user set changed_by_fk=null  where changed_by_fk in (select id from ab_user where email = 'users-email');
update tables set created_by_fk=null  where created_by_fk in (select id from ab_user where email = 'users-email');
update tables set changed_by_fk=null  where changed_by_fk in (select id from ab_user where email = 'users-email');
delete from sql_metrics where created_by_fk in (select id from ab_user where email = 'users-email');
update table_columns set changed_by_fk=null  where changed_by_fk in (select id from ab_user where email = 'users-email');
update table_columns set created_by_fk=null  where created_by_fk in (select id from ab_user where email = 'users-email');
update sqlatable_user set user_id=null  where user_id in (select id from ab_user where email = 'users-email');
update sl_datasets set created_by_fk=null  where created_by_fk in (select id from ab_user where email = 'users-email');
update sl_datasets set changed_by_fk=null  where changed_by_fk in (select id from ab_user where email = 'users-email');
update sl_columns set created_by_fk=null  where created_by_fk in (select id from ab_user where email = 'users-email');
update sl_columns set changed_by_fk=null  where changed_by_fk in (select id from ab_user where email = 'users-email');

delete from sl_dataset_users where user_id in (select id from ab_user where email = 'users-email');
update dashboards set created_by_fk=null  where created_by_fk in (select id from ab_user where email = 'users-email');
update dashboards set changed_by_fk=null  where changed_by_fk in (select id from ab_user where email = 'users-email');

update slices set created_by_fk=null  where created_by_fk in (select id from ab_user where email = 'users-email');
update slices set changed_by_fk=null  where changed_by_fk in (select id from ab_user where email = 'users-email');
delete from dashboard_user where user_id in (select id from ab_user where email = 'users-email');
delete from slice_user where user_id in (select id from ab_user where email = 'users-email');
update slices set last_saved_by_fk=null  where last_saved_by_fk in (select id from ab_user where email = 'users-email');

update sl_tables set created_by_fk=null  where created_by_fk in (select id from ab_user where email = 'users-email');
update sl_tables set changed_by_fk=null  where changed_by_fk in (select id from ab_user where email = 'users-email');

update annotation_layer set created_by_fk=null  where created_by_fk in (select id from ab_user where email = 'users-email');
update annotation_layer set changed_by_fk=null  where changed_by_fk in (select id from ab_user where email = 'users-email');

update tab_state set created_by_fk=null  where created_by_fk in (select id from ab_user where email = 'users-email');
update tab_state set changed_by_fk=null  where changed_by_fk in (select id from ab_user where email = 'users-email');
delete from tab_state where user_id in (select id from ab_user where email = 'users-email');

update dbs set created_by_fk=null  where created_by_fk in (select id from ab_user where email = 'users-email');
update dbs set changed_by_fk=null  where changed_by_fk in (select id from ab_user where email = 'users-email');
delete from query where user_id in (select id from ab_user where email = 'users-email');

delete from ab_user where id in (select id from ab_user where email = 'users-email');

[max-addison]

Still having this issue. The user im trying to delete has never created a chart or dashboard

[C-monC]

If you're using the latest version it may be possible that there are new tables.

Run the above script and check what the error message is for the last line. Modify the script accordingly and post it here. I come grab this snippet whenever I need to delete a user.

[LeoDiep]

just want to save time for who don't know how to do as C-monC did above, if you run superset by docker, the default metadata of superset is from the container name 'superset_db' and the image is prosgre. This database is already connected on Superset UI as example database when you load the examples. However you cannot delete records in this database if you haven't eddited its setting and allow DML.

After that, just go to sqllab, find the user_id you want to delete, run 'delete from ab_user where id = ...' , it will show you which table have data associated with this user_id. Then run delete all rows having that user_id of all related tables and run 'delete from ab_user where id = ...' again, you can now remove that user.

[julizet]

I ran into the same problem. I can't delete any user even though the use was recently created and doesn't own any chart, board or dataset.

How could it be that the user deletion doesn't cascade through every data like e.g. logs?

Appreciate some solutions (two years after initial report) for how to solve this bug.

[beeritis]

Airflow v2.6.0

Just had the same problem - following worked for me -

delete  from dag_run_note where user_id='<userid>;
delete from ab_user_role where user_id='<userid>';
delete  from ab_user where username='<username>';

Not a fan of force deleting but this is a fundamental bug that needs fixing.

A basic feature to allow updating of a user password should be implemented as this was the only reason I needed to delete a user.

[jaroet]

Same bug for me on 3.1.0. I have no access to the SuperSet database so I will set the user as inactive. But it should really be fixed.

[ziggekatten]

This is driving me nuts as well. I do not feel comfortable doing deletes outside of the ORM, as it might wreck stuff and make upgrades fail

[xavier-GitHub76]

Hello, I have already create and delete users without problem. We ca consider the cascade delete is existing but for an unknown reason, I have also this issue on superset v3.

The message should detail the data to remove. I will test :

puting this here for future use:

-- user to be deleted is id XXX
delete from favstar where user_id=XXX;
delete from key_value where changed_by_fk=XXX;
delete from key_value where created_by_fk=XXX;
delete from logs where user_id=XXX;
delete from ab_user where id=XXX;

[xavier-GitHub76]

Hello, I systematically reproduce the problem "impossible to delete a user : Associated data exists, please delete them first".

Creation of a user (alpha) + Login of the user + Creation of a chart by the user + Deletion of the chart by the user + Deletion of the user => Associated data exists, please delete them first

I have identified the cause: This only concerns users who have created elements in Superset. Despite the deletion of elements created by a user from the graphical user interface, the deletion of the user is impossible because the "key_value" table is not cleaned at the time of deletion, this creates an integrity constraint preventing the deletion of the user.

The other tables (logs, slices, slice_user) are correctly updated.

The “key_value” table has a relationship only with the “ab_user” table.

It is possible to work around the problem by executing a delete query on the key_value table : delete from public.key_value where created_by_fk =

Testing the workaround : "Deleted Row" 👍

The following tables are well updated: • ab_user.sql ◦ Deletion of the line relating to the user • ab_user_role.sql ◦ Deletion of the line relating to the user • logs.sql ◦ Lines mentioning now mention user = None (like a user deleted without objects created)

Best regards

[rusackas]

Interesting, thanks for the context. @michael-s-molina or @villebro would know best about the intricacies of deleting key_value and whether it's preferable to remove them along with the user, or leave them orphaned (i.e. if they're needed by other users in other contexts, like sharing a URL with someone)

[villebro]

@rusackas @michael-s-molina @xavier-GitHub76 IMO orphaning the rows is definitely the right course of action here, as permalinks etc should continue existing after a user record is deleted. And for other temporary key value pairs, there's usually an expiration date attached to them, which will ensure that the records disappear in due course.

[xavier-GitHub76]

So the best practice would be to wait until the expiration date has passed before being able to delete the user ? If so, several questions arise:

• How can an administrator find out about this date ?
• What is the mechanism that deletes keys that have become obsolete ?
• Is it possible to speed up this deletion ?

Thank you for any details you can provide us.