XF 2.2 Is it possible to restore a deleted membership?

[Ahmad Fahad]

Hi
By mistake, I deleted someone's user account
Can this be done without going back to back up ?
Is there a solution other than back-up?

Thanks!

 

[Paul B]

In general it requires a restore from a backup.

 

[Harpers Tate]

I have been in the same situation and, yes, ultimately we had to restore from a backup, which (I gather) was no mean feat in itself. I have thought for a long time that there should be a (time-limited) "oh darn!" system here; i.e. that a delete should initially be "soft" and therefore reversible. We are human and therefore prone to error. And you and I are not alone.

See

XF 2.2 - Deleting members - can you undelete?

I have a member who wants me to delete their account. Can I delete them, and then later on if they change their mind, give them their account back?

xenforo.com

and

Lack of interest - Make User Account deletion reversible

For any deletion - including those requested under GDPR - I suggest that these should be made reversible (undoable) for a limited time after deletion. The limited time would be determined by the legal requirement defined by GDPR when it is a GDPR request being actioned. Possible/suggested...

xenforo.com

Since you can no longer upvote my initial suggestion (as it was closed "no interest") maybe a repeat is in order?

 

[Mr Lucky]

This query will at least restore their posts. It involves creating a new user with the same name

In this case user Horace was deleted at the deletion was renamed Deleted member 16

Create a new member Horace and note their user ID (in this case 4401). This then attached all the old posts to the new user (but not their actions, conversations . )

I'm sure you can probably edit the join date in the database.

I found this works:

UPDATE `xf_post`
SET `username` = replace(username, 'Deleted member 16', 'Horace')

followed by

UPDATE `xf_post`
SET `user_id` = replace(user_id, '0', '4401')
WHERE username = 'Horace'
AND user_id = 0;

OR (better I think) Just this one query:

UPDATE `xf_post`
SET `user_id` = replace(user_id, '0', '4401')
WHERE username = 'Deleted member 16'

 

[Kirby]

Depending on what kind of backup data is available, there are two options to restore a deleted accout without completely rolling back to the latest backup:

Just in time recovery using binary logs
Restore the latest full backup, replay binary logs up to just the exact point in time where the deletion happened, skip the queries (deletes and updates) that were run to delete the account and resume replay exactly after those queries.

Partial restore
Restore the latest backup to a temporary DB, re-insert deleted records from that temporary DB to the profuction DB and update records needed.

Unfortunately, in 99,999% of all cases where such a question is being asked none of those options is viable as binary logs are usually not available and a partial restore requires solid DBA and XF schema knowledge.

I've done the second option a few times by now (as it does not require bringing down the production DB which would be necessary for the binlog aporoach), but it is a major PITA and I only do this if affected account was really important.

 

[Tom McIntyre]

One of my associates was asked to remove some members from a usergroup that we use for committee members. He accidentally hit the delete button after gathering the group of 4 users.

I used the create a new account approach and was able to restore usable accounts wih the posts associated with the accounts.

I foolishly tried to restore the userid for our user 14. I was thinking it carried some status sine he had it since 2001 and over several different forum systems in that period.

That user also had a number of conversations that I tried to move to the new account. I have not been able to come up with a clear way to re-enable the conversations because the security relationships are rather more complex than a simple update to the tables (as far as I can tell). Has anyone done this account restoration including all the different sorts of posts the user might have created?

 

[Agatha]

Depending on what kind of backup data is available, there are two options to restore a deleted accout without completely rolling back to the latest backup:

Just in time recovery using binary logs
Restore the latest full backup, replay binary logs up to just the exact point in time where the deletion happened, skip the queries (deletes and updates) that were run to delete the account and resume replay exactly after those queries.

Partial restore
Restore the latest backup to a temporary DB, re-insert deleted records from that temporary DB to the profuction DB and update records needed.

Unfortunately, in 99,999% of all cases where such a question is being asked none of those options is viable as binary logs are usually not available and a partial restore requires solid DBA and XF schema knowledge.

I've done the second option a few times by now (as it does not require bringing down the production DB which would be necessary for the binlog aporoach), but it is a major PITA and I only do this if affected account was really important.

Thank you. What does it mean to '
Restore the latest full backup, replay binary logs up to just the exact point in time where the deletion happened, skip the queries (deletes and updates) that were run to delete the account and resume replay exactly after those queries."

May I ask how one does this?

 

[Kirby]

MySQL :: MySQL 8.0 Reference Manual :: 7.5 Point-in-Time (Incremental) Recovery

dev.mysql.com

point in time recovery mysql binary log - Google Search