phpBB2Refugees.com Logo
Not affiliated with or endorsed by the phpBB Group

Register •  Login 

Continue the legacy...

Welcome to all phpBB2 Refugees!Wave Smilie

This site is intended to continue support for the legacy 2.x line of the phpBB2 bulletin board package. If you are a fan of phpBB2, please, by all means register, post, and help us out by offering your suggestions. We are primarily a community and support network. Our secondary goal is to provide a phpBB2 MOD Author and Styles area.

partial restore of posts?


 
Search this topic... | Search General Support... | Search Box
Register or Login to Post    Index » General Support  Previous TopicPrint TopicNext Topic
Author Message
tryx
Board Member



Joined: 09 Jan 2009

Posts: 8


flag
PostPosted: Fri Jan 09, 2009 8:39 am 
Post subject: partial restore of posts?

I have a PHPBB2 install and had some server issues on 1/5/09. I had to restore a backup from 1/1/09 and my users continued to post on that backup. I was just able to retrieve a backup made right before the error, but is there a way to restore just the posts that are missing i.e. 1/1/09 -> 1/5/09 onto the current db? I figure the post_ids have already been used as soon as the new posts were created. Is there still a way to inject a portion of posts?

After some searching, I found some info on a merge script, but it involves merging all tables. I have over 8100 users with 419000 posts and would like to avoid bogging down my server scanning through unecessary tables like users, categories, forums, etc... There must be an easier way to just merge the posts, posts_text, and topics tables.
Back to top
drathbun
Board Member



Joined: 24 Jul 2008

Posts: 653
Location: Texas


flag
PostPosted: Fri Jan 09, 2009 1:34 pm 
Post subject: Re: partial restore of posts?

I just had to do this for a client, so the experience is fresh in my mind. icon_smile.gif The tables you absolutely need are the phpbb_topics, phpbb_posts, and phpbb_posts_text. If you have a rebuild search index MOD you'll need to run that next, or you'll have to figure out how to restore rows to the phpbb_search_wordlist and phpbb_search_wordmatch as well, else the restored posts won't be searchable.

If you are not comfortable writing SQL scripts and running them in a tool like phpMyAdmin or the mysql command line interface then these instructions are not going to be useful for you, I guess.

What I did was restore the backup to a different database. I then built tables that included a list of topic_id values and post_id values that were missing for use in later queries. These tables will be filled using something like this:
Code:
create table missing_topics as
select topic_id from backup.phpbb_topics
where topic_id NOT IN
  (select topic_id from live.phpbb_topics)

That creates the table and fills it all in one step. It looks for topic ID values that exist in the "old" or correct database that are missing from the active / incorrect database currently in use. I did the same thing for posts.

Then I created unique indexes on the two tables in order to make the next step more efficient. The next step looks like this:
Code:
insert into live.phpbb_topics
select * from backup.phpbb_topics
where topic_id in (select topic_id from missing_topics)

Repeat something similar to fill up missing posts and posts text.

You can use a similar technique to pull over the search_wordlist and search_wordmatch rows.

Finally, you have to resync the forums to reflect the new post and topic count as well as the last post_id values.

That's just a rough summary. If you are comfortable running SQL scripts it might help with your process.

_________________
phpBBDoctor Blog
Back to top
tryx
Board Member



Joined: 09 Jan 2009

Posts: 8


flag
PostPosted: Fri Jan 09, 2009 6:00 pm 
Post subject: Re: partial restore of posts?

Thank you for that solution, but I think my real dilema was caused by the the fact that topic_id and post_id is auto_increment.

So for example, in my case:
backup.phpbb_posts shows the first post_id after 1/1/09 as 509024 which was made sometime on 1/2/09.

live.phpbb_posts shows the first post_id after 1/1/09 also as 509024 which was made on 1/5/09 right after I restored the db.

So I have multiple posts (nearly 300) that have duplicate post_ids. I'm sure the same problem occurs with topic_id but in a smaller scale. Using NOT IN probably won't work for my situation unless theres something about the structure I don't fully understand.
Back to top
drathbun
Board Member



Joined: 24 Jul 2008

Posts: 653
Location: Texas


flag
PostPosted: Fri Jan 09, 2009 7:25 pm 
Post subject: Re: partial restore of posts?

Oh.

Right.

What I had to do was restore posts losts via a prune, so of course the topic / post ID values did not conflict. You're going to have to do more work. How much value is there in the missing posts? Meaning, how badly do you or your board members want them back?

_________________
phpBBDoctor Blog
Back to top
tryx
Board Member



Joined: 09 Jan 2009

Posts: 8


flag
PostPosted: Fri Jan 09, 2009 8:38 pm 
Post subject: Re: partial restore of posts?

unfortunately, my users value those posts quite a bit. I run a forum for my car club so the posts that were lost held rollcalls for upcomming events a lot of planning checklists. We now don't know who has checked in, or sign up for something.

I figure I could use an INSERT for the _posts table and omit the post_id field so it would use the next available number, but then I would have to way to link _topics and _posts_text table.

I'm not too familiar with PHP coding, but would there be a way to
-insert a line from the _posts table (using a newly generated post_id)
-insert a line from the _posts_text table (using the same generated number)
then move to the next line?

That just blew up in my face as I typed it icon_mad.gif Didn't think about a way to insert the _topics table with a new topic_id and still keep it linked to the posts table.

To be honest, I probably wouldn't mind manually entering the missing posts if there weren't nearly 300 of them to do.
Back to top
drathbun
Board Member



Joined: 24 Jul 2008

Posts: 653
Location: Texas


flag
PostPosted: Fri Jan 09, 2009 9:01 pm 
Post subject: Re: partial restore of posts?

The way phpBB creates a new topic is to insert the new row into the topics table, note the topic_id assigned, then insert the new post into the post table using that topic id, noting the new post_id assigned, and finally inserting the post_text row using the post_id. The process also inserts the words into the search_wordlist and search_wordmatch tables as required. You can simulate that process, but the problem is you don't have a "hole" in your id sequence, so things might sort out of order. At one point the posts were sorted by post_id, so if you insert older posts after newer posts then the chronological order will be wrong. Sorting by post_time would fix that.

If this were my board, what I would try to do is create a hole to insert the missing topics. It's not to hard to do that if you're careful. Then I would insert the missing data into the hole.

Can you identify which topics are missing from the "live" board? If a topic is missing, is it completely missing, or are there some topics that might only be missing a few posts? I hope not...

_________________
phpBBDoctor Blog
Back to top
tryx
Board Member



Joined: 09 Jan 2009

Posts: 8


flag
PostPosted: Fri Jan 09, 2009 11:13 pm 
Post subject: Re: partial restore of posts?

Unfortunately there is a good mix of new topics with new posts and existing topics with missing posts.

Would there be a way to write a script that emulates the normal function of creating a new topic? The missing 'new' topics would be fine and the missing 'existing' posts would be in a new topic under the same title. I could then just merge the duplicate topics to get all posts into one topic.

Again, just thinking out loud.
Back to top
Ptirhiik
Board Member



Joined: 19 Nov 2008

Posts: 114


flag
PostPosted: Sat Jan 10, 2009 9:28 am 
Post subject: Re: partial restore of posts?

This is how I would proceed in such a case:

Board A is the backup that has been relived then stoped, board B is the live one (eg the targeted one). The problem is to move the new posts from A to B, so the topics and posts_text, keeping their sort. I assume you have no specific mods like attachments installed, or other mod carrying tables with post or topic ids.

The hardest part is to deal with the search tables, so the simpler is to get a mod dedicated to reconstruct them at end, what will simplify our lifes icon_wink.gif.

- get the lower and highest post ids created on A, and isolate them in a seperate table (checking the existence on post id then the equality on user id and post time should allow to identify the same posts, so to except them),
- do the same for topics table.

Now you have two tables with the data created on A during the break, with the right ids. In the following when talking about tables from A, I will refer only to these isolated table.

We have now to make some gap in B tables in order to put A tables data back at their place. So:

- the lowest topic_id in A matches the lowest topic id created in B since B restart. The topic gap to create is equal to A highest topic_id - A lowest topic_id + 1. The tasks to do are:
o table are for the tables from B board,
o field_id is the field to put the gap on (topic_id),
o topic_gap is the topic gap to do,
o lower_id is the lower topic id isolated in A
UPDATE table SET field_id = field_id + topic_gap WHERE field_id >= lower_id

The tables you have to process are:
- posts, field: topic_id,
- topics, fields: topic_id, topic_moved_id,
- topics_watch, field: topic_id,
- vote_desc, field: topic_id

Do the same with the posts gap (A highest post_id - A lowest post id + 1). The tables are:
- forums, field: forum_last_post_id,
- posts, fields: post_id
- posts_text, field: post_id,
- topics, field: topic_first_post_id, topic_last_post_id

Now the gaps are done, you just have to put in B the topics and posts you have isolated in B, forcing their ids to be kept. You have also to reinsert the posts_text table, filtering on post_id present in the A posts table.

What will remain:
- rebuild the search tables (mod),
- resync forums for the count of topics and posts (acp option),
- resync the users posts count (the numnber of posts re-added for the user can be get with counting the isolated posts group by poster_id),
- ensure there are no polls nor votes created on A during the break,
- the same for topics_watch table
Back to top
Display posts from previous:   
Register or Login to Post    Index » General Support  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT
 
Jump to:  

Index • About • FAQ • Rules • Privacy • Search •  Register •  Login 
Not affiliated with or endorsed by the phpBB Group
Powered by phpBB2 © phpBB Group
Generated in 0.0189 seconds using 15 queries. (SQL 0.0024 Parse 0.0006 Other 0.0160)
phpBB Customizations by the phpBBDoctor.com
Template Design by DeLFlo and MomentsOfLight.com Moments of Light Logo