+- The VG Resource (https://www.vg-resource.com)
+-- Forum: Archive (https://www.vg-resource.com/forum-65.html)
+--- Forum: July 2014 Archive (https://www.vg-resource.com/forum-139.html)
+---- Forum: Main Stuff (https://www.vg-resource.com/forum-1.html)
+----- Forum: Site Discussion (https://www.vg-resource.com/forum-3.html)
+----- Thread: The Full Story Behind Our Recent Disaster (/thread-24411.html)
Pages:
1
2
The Full Story Behind Our Recent Disaster - Petie - 01-09-2014
So, as promised, here are all of the details regarding the deletion of our sprites table and its subsequent recovery.
TL;DR Was working on revisions, accidentally deleted all of the sprites, didn't have a backup, attempted to automatically scrape Google cache to no avail, wrote bookmarklet to pull data from cached pages and script to process that data, used script to rebuild sheet info.
Our story begins on Monday. I woke up, got on Skype, and called Dazz as I do every "weekend" (my days off are Monday and Tuesday right now). We talked for a bit about what we were going to work on and settled on finishing the revisions system so I started writing and testing the new code. After a few tests, I needed to clear the revisions table of the junk I put in it so I went to truncate it (a database term which basically means empty). This is where things took a turn for the worse.
Truncating a table is as simple as right-clicking on the table name, choosing Truncate, and then confirming in the following dialogue box. Having been working with the revisions table, I simply followed that procedure on the highlighted table and confirmed without reading. What I forgot, however, was that I had also opened the main sheets table to confirm a few column names. Unfortunately, that happened to be the last table that I interacted with so instead of removing the junk data from the revisions table, I deleted all of the sheet information. After realizing what I had just done, I immediately freaked out, told Dazz what happened, shut down the database server, and began frantically searching for the backup I was certain we had (spoiler: we didn't, but I'll get there in a bit).
Before continuing, a little background information is necessary to fully understand what exactly deleting the sheets table meant - what was lost and what wasn't. The way the new site is designed, all of the organizational data is stored in the database. This greatly simplifies the process of moving a sheet from one game to another or a game from one console to another. However, when I deleted this information, everything that was used to tell the site where the sheets actually belonged was lost. The only information we lost, however, was information directly related to the sheet (i.e. its name, the extensions of its files, the game it was in, and what section of that game it belonged to, among other things). The game data, submitters, sections, updates, and so on were all still in tact. This meant that if we could just figure out the what name, game, and section went with each sheet ID, we could rebuild that data and the site would be saved.
Now that that's out of the way, we can move on. So, after spending a while looking for a backup that didn't exist (we had automatic backups on the old server, and even copied over the scripts but we never changed the paths they were pointing at, an oversight caused by the fact that the new version wound up going live immediately after the server transfer - something we hadn't planned for - See Rob's post here), Dazz and I took to the internet to figure out if there was any way to recover the data. We spent the better part of a couple of hours going back and forth between research and frantically trying to contact Rob to see if he had a secret backup or some black magic he could work to recover the lost data. He got back to us, told us he'd jump on as soon as he got home from work, and left us to more unfruitful research.
That night, when he came home, he started scanning for the remnants of the missing data and we spent a good chunk of time comparing two very long lists of numbers to try to find the correct page files to restore from. After a while, we realized that the missing data couldn't possibly be here because the files were too small and gave up home on recovery, instead switching our focus to coming up with a plan to manually rebuild that data.
The first plan was simple. Manually re-upload every sheet. We still had all the icons, sheets, and games. We could reference Google, the forums, and our memory to try to ensure everything was correct. For obvious reasons though, this was not appealing. First, it would have been 56,000 uploads which would have meant an extremely extended downtime. Second, preserving the existing sheet IDs was desirable so we could keep the "sheets in this update" sections accurate. Seeing the futility in this plan early on, we moved to another.
Our most promising sounding plan was to write a scraper (i.e. a script that would grab data from a specified page) and set it to retrieve everything we could from Google's cache. If everything worked the way we wanted it to, this would have been an almost entirely automated process with just some manual cleanup at the end, plus whatever Google didn't have. This plan also quickly fell to pieces because Google doesn't allow automated crawling of its data (even though it's the biggest web crawler on the planet so this seems kind of hypocritical). At some point towards the end of this part, Dazz and Charlie had to go to bed but Rob and I stayed up and continued brainstorming. What we arrived at, was plan 3.
Our third and final plan consisted of writing a bookmarklet (a bit of JavaScript code that you can run from a bookmark in your browser, usually on the bookmarks bar) to grab the sheet IDs and section names from each game page and then use that data to manually rebuild the database entries. The initial plan was to have a form where you select the game from a drop down, then select the section you were adding sheets to, and finally add the captured list of sheet IDs and sheet names in the last two text boxes. After coming up with what seemed like a workable, if not a little tedious, idea, we both went to bed (and major kudos to Rob, who stayed up until 7:30 AM to finish planning this out).
This brings us to Tuesday morning.
As with Monday, I got up, got on Skype, and started talking with Dazz about our plan. We bounced ideas off of one another and refined it further and further until it was condensed down into basically one step with no manual entry (at the cost of the hit counts for the sheets, a small price to pay). The end result was a bookmarklet that called some JavaScript that we would run from the cached game pages. What it did was take the console and game short name from the URL, generate a text box, and add them to the first line of that box. Then, it would scan the page for all of the section headers and then all of the sheets under them, compile that data, and insert it all into the text box on lines two through however many sheets there were. A sample entry would have looked like this:
Code:
arcade|gamename
12345|Sheet 1|Characters
12346|Sheet 2|Enemies
12347|Sheet 3|GeneralOnce we had that output, it was a simple matter of pasting it into a new form I created for this very purpose. When submitted, a script broke apart that information, grabbed the necessary game and section IDs, and automatically wrote the sheets back into the database, preserving their original IDs. The only problem was that we were missing a little bit of information about the sheets themselves, a problem that Rob was able to solve for us when he got home later that day.
To get that missing info, Rob wrote a script that scanned the filesystem where we kept the sheets and outputted all of the relevant into info an array. I then took that array, saved it, and attempted to include it in my script. At almost 56,000 elements though, it failed to load and I would up instead using that data to create a temporary database table with that information in it. Once that was complete, we were ready to begin. A quick test, a bug fix or two, and we got started grabbing everything we could from Google. I went to bed after about 20 games since I had to be up early for work but Dazz and Rob stayed up for a while and continued. By the time I got home from work the next day, Dazz and Charlie had finished everything they could grab from Google, leaving us with 124 games unaccounted for.
This could have been bad as there were still quite a few sheets remaining but luckily, a wonderful member of this community, Palculator, stepped forward to tell us that he had a complete archive from December 27. With his archive and a couple of tweaks to the bookmarklet, we were able to grab about 100 of those 124 remaining games, leaving us with just over 400 sheets that now needed manual recovery.
At this stage, Dazz, Charlie, Rob, and I sat down and prepared ourselves for the very manual process of finding a place for those remaining 400 or so sheets. After generating a couple of lists from the database and comparing it to the files themselves, we were able to figure out what the missing sheets actually were and from there, I was able to generate a page to display them all at once. This, combined with cached versions of the home page updates from Google (which let us at least see which console and game a sheet belonged to), let us fairly quickly cut that down to about 200 before Charlie and Rob had to head off.
Dazz and I stayed up, continued digging through cached updates, wiki pages, and anything else we could find to help us figure out what to do with these last few sheets. With so few (by comparison to the original 56,000 number anyway) left, we wanted to finish before bed and we did just that. A couple of hours of mind-melting work and some cleanup on the database (like figuring out what happened to all of the playable character sheets in Shin Megami Tensei: Devil Survivor - a process accomplished through some database detective work that I'm rather proud of) later, and we were done and almost ready to come back online.
We removed our rebuilding scripts, made sure all sections had sheets and that the site was functioning correctly, did one final check of everything to make sure we didn't miss anything, performed a full backup of every database on the server, and then brought the sites back online. We stuck around for about 10 minutes, marveling at what we managed to accomplish over the last three days (and making sure that the site didn't crash and burn once we were hit with visitors again) and then went to bed.
And that's pretty much it. I don't think I left anything out but I'm still pretty tired so Dazz, Charlie, and Rob, feel free to let me know if I skipped anything once you've had a chance to read this.
I know we originally said we'd be asking for community help, and we certainly appreciate all of the offers we got from you guys but our final process was easy enough that the four of us (mostly Dazz and Charlie) managed to get it done quickly enough on our own. Still, thank you for being so eager to help us resolve this.
I'd just like to conclude this with one more apology for the inconvenience and the scare and one more huge thank you to Dazz, Charlie, and Rob for their immense help in fixing my enormous mistake (and for also only making fun of me every once in a while throughout the process, instead of all the time).
I know this is really long but I hope you enjoyed it all the same. If you take one thing away from this tragic tale, let it be the knowledge that you should always have a backup, in multiple places and from multiple times. If you guys have any questions or would like anything explained further, just let me know. Thanks for reading!
RE: The Full Story Behind Our Recent Disaster - zKenshi - 01-09-2014
Was a very interesting read, noticed the downtime and was really hoping you guys would post what happened. Unfortunately I don't understand a lot about creating or editing websites so much of this I didn't understand but still fun to read! Glad you guys managed to get it back up.
RE: The Full Story Behind Our Recent Disaster - Omega - 01-09-2014
So how did you find that SMT playable character sheet you're so proud of? :p
RE: The Full Story Behind Our Recent Disaster - Ton - 01-10-2014
Great work recovering everything, guys. It's not about the mistake. It's about the solution.
RE: The Full Story Behind Our Recent Disaster - tigerlily - 01-10-2014
That's a very extensive version of what happened!
My version was this.
'Pete broke it.'
'wtf'
*numbers, symbols, letters, more numbers* (said by Pete and Rob of course)
'copy/paste, highlight green' <-- me and Dazz
*brain melt* <-- all of us
*FIXED*
There was a bit of sleep and normal day job thrown in there too at some point.
RE: The Full Story Behind Our Recent Disaster - puggsoy - 01-10-2014
Very interesting, and once again awesome job guys! What doesn't kill a site makes it stronger, I guess?
RE: The Full Story Behind Our Recent Disaster - Dazz - 01-10-2014
I think over the course of 72 hours I managed to rack up about 15 hours of sleep, mostly in cat naps. 3 hours here, 20 minutes there.
The entire process of all of this was a really push on all of us mentally and physically at times, and I can honestly say that, even if one of the four of us wasn't there throughout this process, it would have fallen apart. I think it's a real demonstration of teamwork, and it makes me proud to say that I am part of that great team.
Not only that, but that I am part of this amazing community, without whom this would have all been for nothing. I owe it to you all to keep this place alive, and it was that determination that really made us strive for a solution, one that was completely nuts... And really not textbook.
RE: The Full Story Behind Our Recent Disaster - PatientZero - 01-10-2014
I think you'll find the real hero of the whole situation was me, trying to help debug some of Pete's code.
That didn't actually have any bugs in it.
For like an hour.
Dammit Pete.
RE: The Full Story Behind Our Recent Disaster - Petie - 01-10-2014
(01-10-2014, 03:13 PM)PatientZero Wrote: I think you'll find the real hero of the whole situation was me, trying to help debug some of Pete's code.
That didn't actually have any bugs in it.
For like an hour.
Dammit Pete.
Yeah... about that...
Nice work?
RE: The Full Story Behind Our Recent Disaster - Raz - 01-10-2014
(01-10-2014, 03:13 PM)PatientZero Wrote: I think you'll find the real hero of the whole situation was me, trying to help debug some of Pete's code.
That didn't actually have any bugs in it.
For like an hour.
Dammit Pete.
Don't worry, he only showed you one of his best snippets. There's been some pretty funky code. (Not that I could do any better).
RE: The Full Story Behind Our Recent Disaster - Petie - 01-10-2014
Madness! What is this funky code of which you speak?!
RE: The Full Story Behind Our Recent Disaster - Raz - 01-10-2014
I seem to recall a certain piece of code which listed all items alphabetically, then there was the time that you used views (killing a server in the process!).. should I go on?
In fairness though.. I once wrote a hit counter in PHP which accessed a MySQL database... on a site that was getting about 200 concurrent visits. Good job, Rob!
RE: The Full Story Behind Our Recent Disaster - Petie - 01-10-2014
Those were quality, well written views! It's not my fault that MySQL sucks at handling them.
Was the alphabetical thing the code for the letters on this site or something else? It it is, that was pretty poorly planned, I'll give you that.
RE: The Full Story Behind Our Recent Disaster - Phaze - 01-10-2014
That was an interesting story, to say the least. What were you using to truncate the table? phpMyAdmin or some other piece of software?
I can actually sort of feel an echo of that panicky feeling one gets when it dawns on them that they deleted something they shouldn't or just lost something. Horrible feeling. Good work, though.
(01-09-2014, 08:53 PM)zKenshi Wrote: Was a very interesting read, noticed the downtime and was really hoping you guys would post what happened. Unfortunately I don't understand a lot about creating or editing websites so much of this I didn't understand but still fun to read! Glad you guys managed to get it back up.
Honestly, it's not too difficult, especially if you have something that removes the need to manually log in to the mysql installation on the actual server. Usually no coding knowledge is needed since there is usually something to do what you want - it sure helps, though! I became staff on a website once to fix little errors here and there since the headmin couldn't code.
It's something you learn as you go along in bits and pieces. 7 years ago I was literally afraid to open "My Computer" on my brother's desktop (because he put an irrational fear of it into me to prevent me possibly wrecking something) but now I'm very comfortable with running a Linux system on my old computer to function as a ghetto server and having all of my friends yell at me when something inevitably breaks
RE: The Full Story Behind Our Recent Disaster - Raz - 01-10-2014
(01-10-2014, 07:12 PM)Phaze Wrote: That was an interesting story, to say the least. What were you using to truncate the table? phpMyAdmin or some other piece of software?
I can actually sort of feel an echo of that panicky feeling one gets when it dawns on them that they deleted something they shouldn't or just lost something. Horrible feeling. Good work, though.
(01-09-2014, 08:53 PM)zKenshi Wrote: Was a very interesting read, noticed the downtime and was really hoping you guys would post what happened. Unfortunately I don't understand a lot about creating or editing websites so much of this I didn't understand but still fun to read! Glad you guys managed to get it back up.
Honestly, it's not too difficult, especially if you have something that removes the need to manually log in to the mysql installation on the actual server. Usually no coding knowledge is needed since there is usually something to do what you want - it sure helps, though! I became staff on a website once to fix little errors here and there since the headmin couldn't code.
It's something you learn as you go along in bits and pieces. 7 years ago I was literally afraid to open "My Computer" on my brother's desktop (because he put an irrational fear of it into me to prevent me possibly wrecking something) but now I'm very comfortable with running a Linux system on my old computer to function as a ghetto server and having all of my friends yell at me when something inevitably breaks
Petie and I use MySQL Workbench when connecting to the MySQL server. Of course the server itself isn't public facing and we need to tunnel in for security purposes but basically with MySQL Workbench it's as easy as right clicking and selecting Truncate as Petie said.