Restore hard deleted topic from backup

Do not post support requests, bug reports or feature requests. Discuss phpBB here. Non-phpBB related discussion goes in General Discussion!
Suggested Hosts
Foxina
Registered User
Posts: 23
Joined: Mon Jan 03, 2022 9:20 am

Restore hard deleted topic from backup

Post by Foxina »

Hello phpBB community.
Returning to this recurring issue with a new solution.
Before applying it to the production database, all kind advises welcome.

Reference:

3.2 Support Forum "restore a single topic from backup"

In this old topic, @stevemaury suggested to get the data for the deleted topic from a full backup like this:

SELECT * from phpbb_posts WHERE topic_id = X;
SELECT * FROM phpbb_topics WHERE topic_id = X;
SELECT * FROM phpbb_topics_posted WHERE topic_id = X;
SELECT * FROM phpbb_topics_watch WHERE topic_id = X;
SELECT * FROM phpbb_topics_track WHERE topic_id = X

And then restoring the lines to production, with update clauses.
I found this a bit complicated, but the SELECT statements from above useful.

Came up with this BASH script.

Code: Select all

#!/bin/bash
# Get data for topic 4330

mysqldump phpBBdatabase phpbb_posts --no-create-info --where="topic_id=4330" --complete-insert > recordfile
mysqldump phpBBdatabase phpbb_topics --no-create-info --where="topic_id=4330" --complete-insert >> recordfile
mysqldump phpBBdatabase phpbb_topics_posted --no-create-info --where="topic_id=4330" --complete-insert >> recordfile
mysqldump phpBBdatabase phpbb_topics_watch --no-create-info --where="topic_id=4330" --complete-insert >> recordfile
mysqldump phpBBdatabase phpbb_topics_track --no-create-info --where="topic_id=4330" --complete-insert >> recordfile

mysql -u root -p phpBBdatabase < recordfile
### END
Replace phpBBdatabase and topic number with yours.

As it is, the backup which contains the deleted post is for 3.2.11 and by the time the deletion was noticed, we had done an upgrade to 3.3.11.

In any case, as I tested it, the topic is restored correctly even to 3.3.11.
Question is, am I missing something?
These five tables enough?
Test environment did not break at least, but I am hesitating to run this against production.

The above method of course requires, you have full terminal access to your board system and an test environment.
Last edited by Foxina on Tue Apr 23, 2024 5:43 pm, edited 2 times in total.
Foxina
Registered User
Posts: 23
Joined: Mon Jan 03, 2022 9:20 am

Re: Restore hard deleted post from backup

Post by Foxina »

Of course, in completion, one would run the upper part of the code in the environment where you have the backup.

And then move the recordfile to the production, and run

Code: Select all

mysql -u root -p phpBBdatabase < recordfile
there.
User avatar
AmigoJack
Registered User
Posts: 6114
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン

Re: Restore hard deleted post from backup

Post by AmigoJack »

Your subject and your text says "post", but it all restores a complete topic (with one or more posts).

Given the imprecise approach it will neither sync each post author's post count, nor restore bookmarked topics.
  • "The problem is probably not my English but you do not want to understand correctly. ... We will not come anybody anyway, nevertheless, it's best to shit this." Affin, 2018-11-20
  • "But this shit is not here for you. You can follow with your. Maybe the question, instead, was for you, who know, so you shoved us how you are." axe70, 2020-10-10
  • "My reaction is not to everyone, especially to you." Raptiye, 2021-02-28
User avatar
warmweer
Jr. Extension Validator
Posts: 11372
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Bel ... gium

Re: Restore hard deleted post from backup

Post by warmweer »

There's also phpbb_poll_votes if that topic was a poll.
and attachments in those topics may have been deleted and should the also be restored (both the files and the entries in phpbb_attachments

+ you'll have to repopulate the search index

some extensions may have added features: e.g Thanks for post (or similar)

Disclaimer: no guarantee that this is complete (memory can't always be trusted)

BTW don't be discouraged by AmigoJack and my addition: you did think it through but for some "minor" details so you got the basic idea and can test on a copy before manipulating your live board.
Spelling is freeware, which means you can use it for free.
On the other hand, it is not open source, which means you cannot change it or publish it in a modified form.


Time flies like an arrow, but fruit flies like a banana.
Foxina
Registered User
Posts: 23
Joined: Mon Jan 03, 2022 9:20 am

Re: Restore hard deleted post from backup

Post by Foxina »

AmigoJack wrote: Tue Apr 23, 2024 3:28 pm Your subject and your text says "post", but it all restores a complete topic (with one or more posts).

Given the imprecise approach it will neither sync each post author's post count, nor restore bookmarked topics.
Corrected.
Any advice how to make it precise?
Foxina
Registered User
Posts: 23
Joined: Mon Jan 03, 2022 9:20 am

Re: Restore hard deleted topic from backup

Post by Foxina »

warmweer wrote: Tue Apr 23, 2024 5:43 pm There's also phpbb_poll_votes if that topic was a poll.
and attachments in those topics may have been deleted and should the also be restored (both the files and the entries in phpbb_attachments

+ you'll have to repopulate the search index
Good point.
Not a poll though and no attachments on this topic in question, but generally something to be checked.
Search index re-population could be an issue:
It is phpBB Native Fulltext , with more than 3 million in the indexed words and more than 40 million number of word to post relations indexed,
So probably would need a cli/script solution to that, instead of ACP gui.
Last edited by Foxina on Wed Apr 24, 2024 12:15 pm, edited 1 time in total.
User avatar
Mick
Support Team Member
Support Team Member
Posts: 26639
Joined: Fri Aug 29, 2008 9:49 am

Re: Restore hard deleted topic from backup

Post by Mick »

How many topics do you want to recover?
  • "The more connected we get the more alone we become" - Kyle Broflovski©
  • "The good news is hell is just the product of a morbid human imagination.
    The bad news is, whatever humans can imagine, they can usually create.
    " - Harmony Cobel
Foxina
Registered User
Posts: 23
Joined: Mon Jan 03, 2022 9:20 am

Re: Restore hard deleted topic from backup

Post by Foxina »

Mick wrote: Wed Apr 24, 2024 10:33 am How many topics do you want to recover?
Just one.
A moderator accidentally hard deleted a long continuing topic.
Bound to happen sometimes.
User avatar
Kailey
Community Team Leader
Community Team Leader
Posts: 3770
Joined: Mon Sep 01, 2014 1:00 am
Location: sudo rm -rf /
Name: Kailey Snay

Re: Restore hard deleted post from backup

Post by Kailey »

Foxina wrote: Tue Apr 23, 2024 5:44 pm Any advice how to make it precise?
As a starting point, you could use the (unofficial) ERD for table relationships. It's for 3.2, but should be mostly correct for 3.3.
Kailey Snay - Community Team Leader
Knowledge Base | Documentation | Community rules
If you have any questions about the rules/customs of this website, feel free to send me a PM.

My little corner of the world | Administrator @ phpBB Modders
Foxina
Registered User
Posts: 23
Joined: Mon Jan 03, 2022 9:20 am

Re: Restore hard deleted post from backup

Post by Foxina »

Kailey wrote: Wed Apr 24, 2024 12:57 pm
As a starting point, you could use the (unofficial) ERD for table relationships. It's for 3.2, but should be mostly correct for 3.3.
Indeed.
Found at least these tables with reference to topic_id.

phpbb_bookmarks
phpbb_poll_options
phpbb_poll_votes
phpbb_attachments
phpbb_drafts
phpbb_log

Will test and revert with these additions.
User avatar
warmweer
Jr. Extension Validator
Posts: 11372
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Bel ... gium

Re: Restore hard deleted post from backup

Post by warmweer »

Foxina wrote: Wed Apr 24, 2024 3:53 pm Found at least these tables with reference to topic_id.
There's more.
Just ran a query listing (default) tables having a field topic_id.

phpbb_attachments
phpbb_bookmarks
phpbb_drafts
phpbb_log
phpbb_poll_options
phpbb_poll_votes
phpbb_posts
phpbb_topics
phpbb_topics_posted
phpbb_topics_track
phpbb_topics_watch

+ the search index has to be updated: there's no topic_id but the phpbb_search_wordmatch has a field word_id and post_id

and as I mentioned, some extensions also create a table with a topic_id field (e.g. Thanks for post >>> phpbb_thanks)

Added:

and just thought about something extra: there are tables with a field post_id but no topic_id (e.g.phpbb_reports: there may be more)
I don't know offhand whether entries related to the deleted posts (that are being restored) are also deleted when a topic is deleted, I suspect not in which no further action is needed, but for completeness you might also consider a search for those post_ids.
Spelling is freeware, which means you can use it for free.
On the other hand, it is not open source, which means you cannot change it or publish it in a modified form.


Time flies like an arrow, but fruit flies like a banana.
Foxina
Registered User
Posts: 23
Joined: Mon Jan 03, 2022 9:20 am

Re: Restore hard deleted post from backup

Post by Foxina »

warmweer wrote: Wed Apr 24, 2024 5:06 pm
Yes yes, I meant in addition to the 5 tables mentioned in the first post of this topic.
So, 11 tables with reference to the topic_id.

Thanks: I will check about the post_id and revert.
Last edited by Mick on Thu Apr 25, 2024 6:18 am, edited 1 time in total.
Reason: Removed unnecessary full quotes.
User avatar
Mick
Support Team Member
Support Team Member
Posts: 26639
Joined: Fri Aug 29, 2008 9:49 am

Re: Restore hard deleted topic from backup

Post by Mick »

Foxina wrote: Wed Apr 24, 2024 12:14 pma long continuing topic
How many pages?
  • "The more connected we get the more alone we become" - Kyle Broflovski©
  • "The good news is hell is just the product of a morbid human imagination.
    The bad news is, whatever humans can imagine, they can usually create.
    " - Harmony Cobel
Foxina
Registered User
Posts: 23
Joined: Mon Jan 03, 2022 9:20 am

Re: Restore hard deleted topic from backup

Post by Foxina »

Mick wrote: Thu Apr 25, 2024 9:42 am
11 pages, but still spanning over some years.
With the extended mysldump for all 11 tables referring to topic_id, got an error with the phpbb_drafts table while restoring the topic:
ERROR 1062 (23000) at line 364: Duplicate entry '2330' for key 'phpbb_drafts.PRIMARY'
Maybe the draft id 2330 is now used for someone else.

If I remove the phpbb_drafts part, the topic with all posts is restored.
Did not yet get to test with post_id reference.
Foxina
Registered User
Posts: 23
Joined: Mon Jan 03, 2022 9:20 am

Re: Restore hard deleted topic from backup

Post by Foxina »

Script to get the data for lost topic from backup database now looks like this:

Code: Select all

#!/bin/bash
# Get data for topic 4330

mysqldump phpBBdatabase phpbb_posts --no-create-info --where="topic_id=4330" --complete-insert > recordfile
mysqldump phpBBdatabase phpbb_topics --no-create-info --where="topic_id=4330" --complete-insert >> recordfile
mysqldump phpBBdatabase phpbb_topics_posted --no-create-info --where="topic_id=4330" --complete-insert >> recordfile
mysqldump phpBBdatabase phpbb_topics_watch --no-create-info --where="topic_id=4330" --complete-insert >> recordfile
mysqldump phpBBdatabase phpbb_topics_track --no-create-info --where="topic_id=4330" --complete-insert >> recordfile
mysqldump phpBBdatabase phpbb_bookmarks --no-create-info --where="topic_id=4330" --complete-insert >> recordfile
mysqldump phpBBdatabase phpbb_poll_options --no-create-info --where="topic_id=4330" --complete-insert >> recordfile
mysqldump phpBBdatabase phpbb_poll_votes --no-create-info --where="topic_id=4330" --complete-insert >> recordfile
mysqldump phpBBdatabase phpbb_attachments --no-create-info --where="topic_id=4330" --complete-insert >> recordfile
#mysqldump phpBBdatabase phpbb_drafts --no-create-info --where="topic_id=4330" --complete-insert >> recordfile
mysqldump phpBBdatabase phpbb_log --no-create-info --where="topic_id=4330" --complete-insert >> recordfile


# need to get all post_ids which are related to the topic 4330
post_ids=$(mysql phpBBdatabase -N -e "SELECT GROUP_CONCAT(post_id SEPARATOR ',') FROM phpbb_posts WHERE topic_id = 4330;")

# to populate phpbb_search_wordmatch and phpbb_reports tables
if [ -n "$post_ids" ]; then
mysqldump phpBBdatabase phpbb_search_wordmatch --no-create-info --where="post_id IN ($post_ids)" --complete-insert >> recordfile
mysqldump phpBBdatabase phpbb_reports --no-create-info --where="post_id IN ($post_ids)" --complete-insert >> recordfile
else
    echo "No post_id values found."
fi

### END
mysql -u root -p phpBBdatabase < recordfile in the test machine gives a duplicate error:
Duplicate entry 'XXXXX-XXX-X' for key 'phpbb_search_wordmatch.unq_mtch'
So, maybe again a record that is used and has the same unq_mtch as in the deleted topic?
Also need to solve the issue with index rebuild.
A cli tool is needed, due to size of database.

Return to “phpBB Discussion”