01-09-2014, 08:44 PM
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:
Once 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!
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!
![[Image: JSW7XoM.png]](http://i.imgur.com/JSW7XoM.png)
![[Image: M9AUckK.png]](http://i.imgur.com/M9AUckK.png)
![[+]](images/collapse_collapsed.png "[+]")
Refs & Love![[Image: 3CczX.gif]](http://i.imgur.com/3CczX.gif)
![[Image: qjGOacY.png]](http://i.imgur.com/qjGOacY.png)