Help with cleaning up deleted member

[PPlanet]

Hello,

I'm having trouble with achieving a complete deletion of a member. The member is deleted (gone from the core_members database table), however it still shows in the ajax panel of the ACP when searching for it (but when you click it tells you it can't be found). But if I want to create a new member with the same username and email address, it tells me that they are already in use.

Where in the database can I clean these left-overs? (I still have written down the member number in case it helps).

[aia]

It sounds like a bug that should be reported to IPS, because if it is, it has consequences for the GDPR.

[opentype]

How much time has passed since deleting the user? There are probably actions taking place as a background task, so it might take a while to delete everything. 

Edited July 16, 2022 by opentype

[PPlanet]

1 hour ago, opentype said:

How much time has passed since deleting the user? There are probably actions taking place as a background task, so it might take a while to delete everything. 

I thought of that. It’s been a few hours and it still shows in the Ajax search.

1 hour ago, 13. said:

It sounds like a bug that should be reported to IPS, because if it is, it has consequences for the GDPR.

Well, there’s a bit more to it. It was a new account that I noticed had posted a compulsory intro post (in an area where posting promotes you to another group). I noticed that this particular member had posted several times in that subforum, something normally not possible. He didn’t get promoted automatically and the posts were not associated to his account. (In his content list nor had increased his post count).

Very odd, never seen that before, I haven’t upgraded either so I don’t think it’s a new bug. I tend to believe it was a glitch at the moment of signing up.

I logged in as this member and tried posting, again the post wouldn’t show as his (in his content list I mean). I thought the easiest would be deleting the account and create it again for him.

I deleted the posts, then I deleted the account but it gave me an error message. (I wish I had kept copy).

So, I decided deleting it from the database (yes, I know, big mistake from me. I’m on the road overseas and not really thinking all that well about these things)

So, the account is gone but there must be somewhere where the username and email still count as existing.

 

Edited July 16, 2022 by PPlanet

[Randy Calvert]

If you deleted it from the database manually that is most likely the problem. There are other references smattered in various tables that were never cleaned up. 

Shame shame shame. Lol. 

[PPlanet]

13 minutes ago, Randy Calvert said:

If you deleted it from the database manually that is most likely the problem. There are other references smattered in various tables that were never cleaned up. 

Shame shame shame. Lol. 

Yes, I know. One of those moments where you remember you shouldn’t do that one second after you’ve done it. 🙂

So, yes, basically what I’m after is those tables that I should look into.

[Richard Arch]

Insert the row back into the table then delete the member from ACP.

The row you insert needs to have the same member_id of the row you deleted, the other columns can be a copy of a similar member.

[PPlanet]

24 minutes ago, Richard Arch said:

Insert the row back into the table then delete the member from ACP.

The row you insert needs to have the same member_id of the row you deleted, the other columns can be a copy of a similar member.

Thanks, good try, but somehow the ghost entry remains in the ajax search (it just returns two results, the new account I've just created, and the original one, even when I used the same ID number to create the new one). Deleting the new account does not remove the result on the ajax search.

[Richard Arch]

From what table did you delete the original row?

 

[Jim M]

The only recommendation here would be to restore your database to before the deletion then delete them via the software. Manually deleting data from the database is not recommended or supported.

[PPlanet]

14 minutes ago, Richard Arch said:

From what table did you delete the original row?

 

From core_members

8 minutes ago, Jim M said:

The only recommendation here would be to restore your database to before the deletion then delete them via the software. Manually deleting data from the database is not recommended or supported.

Hi Jim, probably not worth it, at least not for the sake of that new account. Restoring the database would imply the loss of new content and even several new accounts.

I just wanted to tidy up.

[Jim M]

1 minute ago, PPlanet said:

Hi Jim, probably not worth it, at least not for the sake of that new account. Restoring the database would imply the loss of new content and even several new accounts.

I just wanted to tidy up.

While I certainly appreciate that. Please keep in mind that if any issues arise from this, it would be unsupported by our team so restoring and doing it in a supported manner is advised.

[PPlanet]

3 minutes ago, Jim M said:

While I certainly appreciate that. Please keep in mind that if any issues arise from this, it would be unsupported by our team so restoring and doing it in a supported manner is advised.

Understood.

[Richard Arch]

It doesn't sound like you originally deleted a row from that table since the email address was being detected as already in use when you tried recreating the member from the front end. 

I'd bet the member_id is different on the record you just inserted as the database will have auto numbered it.  I suggest you back out that row by deleting it.

Are you sure it was from that table you deleted the record?

 

Best advice now would be to take a backup of your database, then build a a test site to use that database where you can fix the issue.  Record your steps, then try them again on the test site after another build with that database until you are sure.

[Adriano Faria]

Members ACP live search uses the core_members table to search but there’s a datastore file that should be preventing it to “update the search” with new results. Rebuild the cache or delete the datastore/safeInlineSearch.* file. It may change something. 

[PPlanet]

17 minutes ago, Richard Arch said:

Are you sure it was from that table you deleted the record?

Originally I deleted the account from the ACP, and it gave me an error message, it was only then that I decided to delete it from the database.

Now if I search the database (all tables) for the username, it delivers a series of other tables (like admin logs, where it shows my deletion attempt of the original account, and two other deletions following the two tables I inserted with same id following your suggestion).

It also returns a result from core_members, but when I click on "browse" to look at it says that it returned an empty result set.

 

So, that's a bit odd.

Edited July 16, 2022 by PPlanet

[Adriano Faria]

1 minute ago, PPlanet said:

It also returns a result from core_members, but when I click on "browse" to look at it says that it returned an empty result set.

That’s weird. I wouldn’t waste time and would restore the newest backup. Even if you remove it from live search, you’ll have orphaned records in some tables. 

[PPlanet]

3 minutes ago, Adriano Faria said:

That’s weird. I wouldn’t waste time and would restore the newest backup. Even if you remove it from live search, you’ll have orphaned records in some tables. 

What about if I create a new account and change the relevant details in the other tables? (as belonging to that new account).

Edited July 16, 2022 by PPlanet

[Richard Arch]

Play, fiddle, whatever in a copy on a test site 👍

[PPlanet]

1 minute ago, Richard Arch said:

Play, fiddle, whatever in a copy on a test site 👍

Okay, will do that with a bit more time. In the meantime, it doesn't look like this has affected anything else.

Cheers

[Adriano Faria]

2 minutes ago, PPlanet said:

What about if I create a new account and change the relevant details in the other tables? (as belonging to that new account).

If it’s a member that has no posts, it would be like 10 (or so) core tables (members related, follow, reputations, ignore, etc). If it is a member with many posts, and I’m not talking about topic post, it’s more like any content type, all I said plus dozens of tables (content items, comments, reviews, other tables that stores member ID)… not to mention plugins that stores data.

Believe. Better to restore a backup. 

[PPlanet]

It was a brand new account with a couple of posts that were deleted before his account. I see the tables that are produced with a database search and they are just a few.

[PPlanet]

In a last minute twist... the table is not actually deleted. I'm sure that earlier when I looked for it, it jumped from ID number 246924 to 246926 (being this one 246925).

But now the table is certainly there, it looks quite normal in list view, that is when I look the page with all the other tables, all the info is in each column but when I open it, it's empty.

I'm sure this problem started before my deletion attempt from the database, and it explains all the abnormalities with the account I noticed. If I try to refill all the data, it still saves empty. So, not sure if there's a way of repairing (it looks like changes are not saved).

However, while this may not be a functioning account, at least records are not orphaned (or so I think). Thanks you everyone who gave suggestions here.

[PPlanet]

And a check and repair of the core_members table fixed the whole thing, including the dodgy account. 🙂