# Good News and Bad News concerning the missing posts



## Michael Morris

Ok, first the good news - the binary log files which mysql uses to track transactional changes on the database seem to be intact. Theoretically these logs can be played back to restore *everything*

But there's a catch (and here's the bad news) - binary logging restoration is something I've never tried until tonight and I wasn't able to get it to even start due to duplicate entry keys. I need to find the point to start at and this will take time -- a considerable amount of time considering the log files are several times larger than the db itself.

Presuming I can find the correct point to start I can run a restore then, using a process similar to the one used to merge the posts of the ISRP board I can pull posts out of this database and into the restored one.

If this works though it will take time - a considerable amount of time I might add -- according to one website it took a server considerably beefier than ours 9 hours to playback the logs. Also, to be safe during the playback I'll need to shut down any and all external db processes - that means no circvs maximvs, no store, nothing.  If I do this ENWorld goes dead to the world except for a pointer page for at least two days.

The reason I'm posting this here instead of the staff forum is I've never done this before and I know there are at least a couple of database techies on this board.  I could use some help, cause at this point this little PHP jockey is over his head.


----------



## Bront

Good luck 

I hope you can bring things back.  And, as mentioned before, if we need to loose what we've done since then, that's no problem with me.  Particularly if you can keep it up in paralell so we can grab stuff.


----------



## BSF

I am *not* familiar with log restores on mysql.  I have done them on DB2 and it worked beautifully.  Admittedly, it wasn't a lot of logs; it was a controlled test situation; and the hardware was pretty beefy.  

I will check around to see if I can find somebody with some real world experience with mysql that might be able to offer advice.


----------



## resistor

Good luck Spoony!  I really respect your work.

I'm not a database guru, but I'll volunteer my technical skills if you need any help.  I have a lot of storage space and some fast hosting if you need data mirrored or anything.


----------



## resistor

Actually, I just had another idea.

If you'd like, I'd be willing to donate one of my machines to run the logs offline.  That is, we could transfer the backup and the logs to the machine, restore the backup, replay the logs, make a new backup, and then restore that new backup onto the ENWorld server.  Would save a lot of ENWorld downtime, at least.


----------



## Michael Morris

Not a bad idea, though transmission between the machines may take a little while (the logs are a whopping 4GB).

Also having the process done externally is safer than doing it on the production machine.

My email is billgates@microsoft.com - except replace billgates with mlmorr0 and replace microsoft.com with uky.edu


----------



## Bront

Good luck


----------



## Rodrigo Istalindir

I'll sacrifice a rubber chicken for the cause  (and someday I'll post that story -- the sysadmins around here will get a kick out of it).

I've done similar processes with Exchange and MS SQL Server, never with MySQL, but I'll do some reading at work today and see if I can edumacate myself.  So long as a good backup is made beforehand, though, the most you'd risk is a couple days of downtime.


----------



## Umbran

Brilliant!


----------



## der_kluge

Spoony, I've done a lot of database work. Is there something I can help take a look at?


----------



## Michael Morris

Yes please. Email me.


----------



## BOZ

hey, if you can get it, then do it.  

i don't even care if the posts look odd they way they did in the transition from the wizards ISRP, as long as they're back.


----------



## jmucchiello

But won't you lose everything that has happened since you brought the backup online?


----------



## BOZ

not if he does that thing he suggested before, where he leaves the active forums up, and these forums as a "read-only" where we can grab any posts we made in the last few days...


----------



## Mark CMG

If it were done when 'tis done, then 'twere well It were done quickly - _Macbeth_ (Act 1. Scene VII) by William Shakespeare.


----------



## Rystil Arden

Glee!  I knew there must be write-ahead logs for recovery


----------



## Nyaricus

Spoony, thanks again for all the hard work. If I could help you man, I'd be right there beside you.



			
				BOZ said:
			
		

> not if he does that thing he suggested before, where he leaves the active forums up, and these forums as a "read-only" where we can grab any posts we made in the last few days...



Would it be possible to move the recent posts (those made after the boards were officially back on after the crash) over to the new set of boards once this whole process (hopefully) goes through? That way, we could have all the new threads too.

Sorry, just thinking aloud here - I honestly have not idea how hard or simple this would be.

Just a thought. Thanks again SB


----------



## Michael Morris

No.  What I will do is write a script that reposts all the posts in this database made from the start of this db's use forward into the new server. It won't be *easy*, but it can be done.


----------



## CanadienneBacon

If you write a script to repost all the posts in this database made from the start of this db's use forwarded into the new server, what will happen for those cases where old and new threads bear the same title?

To provide a concrete example, I had a thread before the crash entitled CB's City of the Spider Queen v3.5.  The thread was the in-character thread for a play-by-post game that I DM.  I waited a day or so after the crash to make sure that we were, in fact, sticking with the db from 29 Dec 05.  Once I saw the thread in Meta announcing that the EN World admin wouldn't be able to retrieve lost data, and had scoped out EN World's homepage for the general announcement dated Thursday re: the same, I went ahead and created a new in-character thread for my City of the Spider Queen game.  And, you guessed it--I gave it the exact same title as the old one.  

What effect, if any, will duplicate threads have on thread restoration?  

Thank you, kind sir, for taking time to answer questions.  I do appreciate your effort and time.


----------



## Michael Morris

Threads go by their unique thread id - the name of the thread is largely irrelevant to the computer. Same for posts and (for that matter) forums.


----------



## CanadienneBacon

Thanks for the reply and thanks, too, for reminding me that threads are actually numbered with a topic number.  

Good luck with things and a third thanks for helping out restoring the db and for being willing to disseminate information to the membership.


----------



## kirinke

I dunno. We have the forums back up thanks to Spoony and Twin Rose. Sure, we've lost a few months of posts, but Google has them in cach, so we can reference them if needs be and grab them from there. Isn't there a chance that something could drastically go wrong with this experiment? 

Like the man says, a bird in the hand is worth two in the bush. 

Just sayin....

From now on, Enworld can do a monthly backup or whatever you guys do to the database to keep it on file. (My programming skills are pretty weak, so I don't know what that entails.) I say, just chalk this up to experience and make sure it doesn't happen again.


----------



## resistor

kirinke said:
			
		

> Google has them in cach, so we can reference them if needs be and grab them from there.




That won't last.  Sooner, rather than later, Google's webcrawlers will notice that the pages have changed and will replace the caches with what's there now.  The caches are only temporary.


----------



## Bront

Spoony Bard said:
			
		

> Threads go by their unique thread id - the name of the thread is largely irrelevant to the computer. Same for posts and (for that matter) forums.



That may result in some dupes, but we can deal with that


----------



## Steve Jung

Gentlemen, we can rebuild it. We have the technology. We have the capability to build the world's first bionic forum. ENWorld will be that forum. Better than it was before. Better, stronger, faster.

This sounds promising, Spoony. Thanks for trying ti out.


----------



## Erekose13

Spoony, are you planning to run through these transactions on a local/offline copy of the db, then replace this one at a later point?


----------



## Barendd Nobeard

Sorry I can't help, but I'm an Oracle guy, not a MySql guy.

Good luck!


----------



## Michael Morris

Erekose13 said:
			
		

> Spoony, are you planning to run through these transactions on a local/offline copy of the db, then replace this one at a later point?



 Basically yes. Doing it on this server will tie up all it's resources.


----------



## kirinke

resistor said:
			
		

> That won't last.  Sooner, rather than later, Google's webcrawlers will notice that the pages have changed and will replace the caches with what's there now.  The caches are only temporary.




Like I said, I can use a computer, I can even build a computer. But program it? Naa...   
Anyway, G'luck. I have no idea what mojo ya'll are going to do.


----------



## Darth K'Trava

Steve Jung said:
			
		

> Gentlemen, we can rebuild it. We have the technology. We have the capability to build the world's first bionic forum. ENWorld will be that forum. Better than it was before. Better, stronger, faster.
> 
> This sounds promising, Spoony. Thanks for trying ti out.




You just wrecked his chances by quoting an aohell commercial!   



Seriously, hopefully, this will work! :crosses fingers: :sacrifices rubber chicken:


----------



## BSF

Darth K'Trava said:
			
		

> You just wrecked his chances by quoting an aohell commercial!
> 
> 
> 
> Seriously, hopefully, this will work! :crosses fingers: :sacrifices rubber chicken:




Funny, I thought he was spoofing the Six Million Dollar Man.


----------



## James Heard

Computers are magic. You guys are amazing for even attempting this.


----------



## der_kluge

Spoony, I dropped you an email. Write me back if you still need my help.


----------



## MavrickWeirdo

Steve Jung said:
			
		

> Gentlemen, we can rebuild it. We have the technology. We have the capability to build the world's first bionic forum. ENWorld will be that forum. Better than it was before. Better, stronger, faster.
> 
> This sounds promising, Spoony. Thanks for trying ti out.




As long as it doesn't cost 6 million dollars.


----------



## Steve Jung

BardStephenFox said:
			
		

> Funny, I thought he was spoofing the Six Million Dollar Man.



Well, now I just feel old. 


			
				MavrickWeirdo said:
			
		

> As long as it doesn't cost 6 million dollars.



You didn't see that $1,000,000 option in the Community Supporter list?


----------



## BOZ

so, any luck yet?


----------



## Endur

kirinke said:
			
		

> Sure, we've lost a few months of posts, but Google has them in cach, so we can reference them if needs be and grab them from there.




I just want to point out that Google does not have the vast majority of missing posts in cache.

For long threads, google only stored the beginning of the thread in its cache.  And the beginning of the thread was probably more than six months ago anyways.  So for long play by post threads, this is the only way to retrieve those posts.  The google and yahoo caches are useless.


----------



## Darth K'Trava

BardStephenFox said:
			
		

> Funny, I thought he was spoofing the Six Million Dollar Man.




Aohell beat you to it.


----------



## Conaill

Endur said:
			
		

> I just want to point out that Google does not have the vast majority of missing posts in cache.
> 
> For long threads, google only stored the beginning of the thread in its cache.  And the beginning of the thread was probably more than six months ago anyways.  So for long play by post threads, this is the only way to retrieve those posts.  The google and yahoo caches are useless.



Actually, typically it caches the last page as well (often links such as .../showthread.php?goto=lastpost&t=155697"). Although those cached pages may have been updated by now, since Google has probably noticed that the content of the "last" page has changed...


----------



## Jdvn1

BOZ said:
			
		

> so, any luck yet?



 An ETA (or other information) might be nice too, but I don't know if that's asking too much at this point.


----------



## BOZ

an ETA may not even be possible?


----------



## Michael Morris

BOZ said:
			
		

> an ETA may not even be possible?



 Having never done this before (restore from binary) under these circumstances (nearest backup 5+ months old) I don't even know if it CAN be done, let alone give an ETA. At this point I say we have a 50/50 shot at getting the stuff back.


----------



## buzz

Well, I just started a sign-up thread for an ENworld Gameday that's happening in mid-June. I'd kinda hate to lose it. But, as long as we get some advance warning before we lose everything again...


----------



## deltadave

why does the theme for "Mission Impossible" keep running thru my head as I read this thread?


----------



## RangerWickett

Mission Impossible? Fshaw, you're optimistic. I've got the music from the chase sequence on the freeway in The Matrix Reloaded playing. The agents are Google. The freeway is the internet superhighway. The ghost twins are, um, . . . evil Rastafarians, who, like, crashed out database. Spoony is obviously Neo, who will come in and save us. EN World is Trinity, the Keymaker is the database, and Morpheus? That's Morrus, with his bad-ass katana-uzi combination.


----------



## el-remmen

Having simply accepted that we lost 5 months of stuff and moved on, I only worry this might be more trouble than it is worth and potentially dangerous. . .


----------



## Michael Morris

Recovery will be done into a seperate database. If that is accomplished I'll worry about how to merge posts back in. It may be that we'll need to set up a special archive for the lost posts seperate from the main site. Whatever solution is acheived though I'll try to make sure disruption is minimal.


----------



## Jdvn1

Cool, thanks for the update! 

I'm really interested in how all of this is coming along, eventhough I know very little if anything about the technical aspects.


----------



## johndaw16

Well all I got to say is good luck with the recovery.  I for one think that its a great idea and would much rather see the 5 months that we lost come back in any way.  It can't hurt to try, and what's been posted in the past few days seems to be a small compared to the amount of info. posted in the previous 5 months.


----------



## Steverooo

Thanks, Spoonster!  Great luck!


----------



## Bront

Any news on this?  I unfortunately have at least one game waiting on the results of this


----------



## Ankh-Morpork Guard

Bront said:
			
		

> Any news on this?  I unfortunately have at least one game waiting on the results of this



 Yeah, I'm also waiting on this for my Story Hour...don't want to end up with a ton of double posts if I don't have to.


----------



## Morrus

Mike's attempting it over this weekend.  So we should know more in a couple of days.


----------



## Wilphe

I don't really understand this, but it sounds complicated and difficult and I appreciate the effort being put in


----------



## deltadave

Good luck and god speed, Spoony!


----------



## Bront

Morrus said:
			
		

> Mike's attempting it over this weekend.  So we should know more in a couple of days.



Good to hear.  Hope everything goes well


----------



## William Ronald

Good luck, Spoony!  I greatly appreciate the efforts.


----------



## Michael Morris

Bellsouth nixed my connection over the weekend - they changed my plan, didn't bill me, and cut the line anyway.  That's it - switching to cable (cheeper and I own a cell). That will make me scarce for a week though I still have access via ITT Tech.

I'm waiting to get either my check from WotC or my income tax return. With either I'm going to drop about $500 on a dev box. I'll try this rebuild on the dev box as soon as it's up.


----------



## Henry

Spoony Bard said:
			
		

> Bellsouth nixed my connection over the weekend - they changed my plan, didn't bill me, and cut the line anyway.  That's it - switching to cable (cheeper and I own a cell). That will make me scarce for a week though I still have access via ITT Tech.
> 
> I'm waiting to get either my check from WotC or my income tax return. With either I'm going to drop about $500 on a dev box. I'll try this rebuild on the dev box as soon as it's up.




Sucks to hear it, Mike. I've had similar... *ahem* _fun_ with Bellsouth before.  Good fortune, though, and let us know how it goes, when it goes.


----------



## Dannyalcatraz

I don't know if it helps or not- as I recall, right before the board went _kerfluffle_, I had stopped getting my daily updates of subscribed threads.

I thought I'd pass this along in case this was a symptom of what caused all the fun.


----------



## BOZ

ugh, sounds like no-fun...  hope you can get things working here when you can get things working at home.


----------



## ssampier

Spoony, do you need members to contribute to a dev box? I hate to have you spend your own money if we can help out.

Personally I think the reviews are most important, the missing posts second.


----------



## Zander

CanadienneBacon said:
			
		

> ...threads are actually numbered with a topic number.



I'm not a number. I am a free thread!

(Kudos if you recognise the original.)


----------



## Steverooo

Zander said:
			
		

> I'm not a number. I am a free thread!
> 
> (Kudos if you recognise the original.)




ORANGE ALERT!  ORANGE ALERT!!


----------



## Bront

*poke* where's this at?


----------



## Knightfall

Spoony Bard said:
			
		

> Recovery will be done into a seperate database. If that is accomplished I'll worry about how to merge posts back in. It may be that we'll need to set up a special archive for the lost posts seperate from the main site. Whatever solution is acheived though I'll try to make sure disruption is minimal.




I think a special, seperate archive would be the best thing. Simply merging in the old posts with the new posts would cause too much chaos. Please keep it seperate. I don't care about getting my "posts" back that much, but if it's possible without disrupting what has come aftrwards then I say...

Go For It!

KF72


----------



## hong

ssampier said:
			
		

> Personally I think the reviews are most important, the missing posts second.


----------



## Michael Morris

The next day I'm gonna make an attempt on this is Sunday.  I have a full time job coding PHP and by the time I finish my shift each day I'm mentally wore out.


----------



## Bront

Spoony Bard said:
			
		

> The next day I'm gonna make an attempt on this is Sunday.  I have a full time job coding PHP and by the time I finish my shift each day I'm mentally wore out.



Whever you can Spoony, we all appreciate it.  Thanks for keeping us up to date.


----------



## Staffan

Get some rest. If you haven't got your health, you haven't got anything.


----------



## Jdvn1

Spoony Bard said:
			
		

> The next day I'm gonna make an attempt on this is Sunday.  I have a full time job coding PHP and by the time I finish my shift each day I'm mentally wore out.



 Thanks for the update, SB.


----------



## BOZ

Spoony Bard said:
			
		

> The next day I'm gonna make an attempt on this is Sunday.  I have a full time job coding PHP and by the time I finish my shift each day I'm mentally wore out.




dude, absolutely.  get some rest.


----------



## James Heard

See? This is why I'm in favor of cloning.


----------



## BSF

Spoony Bard said:
			
		

> The next day I'm gonna make an attempt on this is Sunday.  I have a full time job coding PHP and by the time I finish my shift each day I'm mentally wore out.




Whoa!  Hey man, take a break if you need it.  Sure it would be cool to get some of that stuff back, but don't hurt yourself over it.


----------



## RangerWickett

BardStephenFox said:
			
		

> Whoa!  Hey man, take a break if you need it.  Sure it would be cool to get some of that stuff back, but don't hurt yourself over it.




I just got the mental image of Spoony with his computer in a binding circle, surrounded by candles, holding up his arm with a curved ritual dagger as he offers his blood to the dark powers in order to make the server keep running.

Then Piratecat bursts in, along with Teflon Billy and Queen Doppelpopolis, and they banish the evil spirit that has possessed Spoony. An epic battle takes place as all the evil spirits folks created on the creature catalogue forums break free, and at the end, just as the demon is about to destroy his friends, Spoony regains his senses and decapitates the fiend with his dual _wands of vorpal missiles_.

And the day is saved.


----------



## Jdvn1

Wow, that's an epic battle indeed, RW. And I want a wand of vorpal missiles.


----------



## Rystil Arden

It is indeed an epic battle.  What with the log restorations, and the new spammers thanks to the breaking of PhP's code, there's a lot with which to deal, and you have gone above and beyond the call of duty to come out of retirement and help us all.  Thanks so much for all the hard work, Spoony, and happy birthday!


----------



## Michael Morris

Someone noticed!  I was beginning to despair - seriously. Thanks Rystil.


----------



## Piratecat

It's your birthday?

HAPPY BIRTHDAY!

May it be a joyous one, and bring on a good year.


----------



## Knight Otu

I don't scroll fully downwards often enough. Happy birthday, Spoony.


----------



## Jdvn1

Happy Birthday!

Though, you can't even sleep in on your birthday...


----------



## RangerWickett

Jdvn1 said:
			
		

> Wow, that's an epic battle indeed, RW. And I want a wand of vorpal missiles.




Your wish is my command: http://www.enworld.org/showthread.php?t=164850

*Vorpal Missile*
Evocation
Level: Sor/Wiz 4
Components: V, S
Casting Time: 1 standard action
Range: Medium (100 ft. + 10 ft./level)
Target: One creature
Duration: Instantaneous
Saving Throw: None
Spell Resistance: Yes

A disk-shaped missile of magical energy darts forth from your fingertip. If you succeed a ranged touch attack against your target, you deal 3d6 points of force damage. If your attack roll was a natural 20 and you confirm the critical hit, the missile severs the creature's head (if it has one) from its body. Some creatures, such as many aberrations and all oozes, have no heads. Others, such as golems and undead creatures other than vampires, are not affected by the loss of their heads. Most other creatures, however, die when their heads are cut off.

Inanimate objects are not damaged by the spell.


----------



## JoeBlank

Happy B-day, Spoony.

And thanks for all you do!


----------



## Steve Jung

Have a belated Happy Birthday, Michael.


----------



## Steverooo

AW, I MISSED IT!!!  Here's hoping your B-day was a happy one, and you got lotsa spooning, Spooney Bard!


----------



## BOZ

getting older is still better than the alternative.


----------



## Jdvn1

BOZ said:
			
		

> getting older is still better than the alternative.



 I age at a rate of 1 second every second.


----------



## BOZ

woah, that's freaky - that means you'll die of old age one day!

get away from me, man, it might be contagious...


----------



## Knight Otu

*pushes BOZ into Jdvn64*


----------



## Jdvn1

*Jdvn437 trips Knight Otu*


----------



## Knight Otu

*Gnome Uto punches Jdvn437*


----------



## Jdvn1

*Jdvns234-819 surround Unionites*


----------



## hafrogman

*hafrogman is confused by Jdvn8675309*


----------



## Knight Otu

*Ashardalon incinerates Jdvns234-819*


----------



## Jdvn1

*Jdvn8675309 gives hafrogman knowledge of number*

*Jdvns234-819 turn into a JdvnMoltenGolem and eats Ashardalon*


----------



## Knight Otu

*Guinea Pig infects JdvnMoltenGolem with Quick-Rot Disease*


----------



## Michael Morris

Cut the spam guys...

Ok, update - Good news - I found a start point. I don't know if it is THE start point, but I've found a place where it will start playback. Unfortunately it always hangs within 5 minutes. I'll keep trying for awhile yet.


----------



## Fenris

Spoony Bard said:
			
		

> Cut the spam guys...




I can hear Spoony now

"Don't make me turn this thread around you two!"


----------



## Knight Otu

Spoony Bard said:
			
		

> Cut the spam guys...



We've moved it into Hivemind after my last post.



			
				Spoony Bard said:
			
		

> Ok, update - Good news - I found a start point. I don't know if it is THE start point, but I've found a place where it will start playback. Unfortunately it always hangs within 5 minutes. I'll keep trying for awhile yet.



Sounds good. Good luck with the further attempts.


----------



## Jdvn1

Spoony Bard said:
			
		

> Cut the spam guys...



I didn't realize that qualified as spam, sorry.


----------



## BOZ

well, yes, "hiveminding" in an unrelated thread on an unrelated topic could definitely be considered spam, especially if other users still wanted to discuss the previous topic.  


and Michael - great!  good luck with that!


----------



## grodog

Spoony Bard said:
			
		

> Ok, update - Good news - I found a start point. I don't know if it is THE start point, but I've found a place where it will start playback. Unfortunately it always hangs within 5 minutes. I'll keep trying for awhile yet.




Thanks for your efforts, Michael.  Did you ever get any tech help from folks?  If not, some of the admins over at the Canonfire! Greyhawk forum are quite sharp with MySQL, and may be able to assist.  If you want to ping them, shoot me an email and I'll ping them.


----------



## William Ronald

Grodog, I think everyone here thanks you for your generous offer. 

So, can anyone else give Michael a hand?


----------



## Odhanan

I can't help out on that, but really, thanks to Michael for his dedication, time and efforts. Thanks a lot, mate.


----------



## grodog

Michael---

Any update on the viability of a full restore?


----------



## Michael Morris

I've tried multiple times without much success. When I get a dev platform setup I'll try again. At this point though the odds are about 1 in 100. Sorry.


----------



## Piratecat

No sorry needed - not your fault! And you're great to even try.


----------



## Henry

Even if it's a lost cause, so far I think we've overcome it nicely. I think after a month, most people wound up forgetting about it. 

And thank you for keeping at it for this long, Mike.


----------



## francisca

Spoony Bard said:
			
		

> I've tried multiple times without much success. When I get a dev platform setup I'll try again. At this point though the odds are about 1 in 100. Sorry.



I'm sure I'm not the only one who appreciates that you keep on chipping away at this.

Thanks.


----------

