MySQL + phpMyAdmin Backups = fail

The System is DownSo… my father often says to me that you shouldn’t make mistakes yourself. You should watch as the people around you make mistakes and learn from them what not to do. That clearly wasn’t the case here. >_<

This is the story of what I’ve been doing in the past 3 days hehe. I’ve been trying to migrate all of the content from our old mod, RPG-X’s forum, to the new UberGames forum me and some friends have been working on. The reasoning for this is to consolidate resources as well as effort.

So, before the migration, I got phpMyAdmin to dump the UberGames forum to a big SQL source file (roughly 30MB worth of letters ) and stored it on my hard drive, and then installed the upgrade script.

My first mistake, I didn’t Read The Flippity Manual (RTFM btw), and I executed the upgrade script from the wrong entry point. This subsequently made the script confused about what system the UG forum was (It figured it was Invision in the end O_o ) and subsequently corrupted several tables in the process. This wasn’t too bad as the tables were really small, and I pasted the data back in from my backup.

Now the colossal fail. Once I’d sorted out the import script and executed it in vBulletin mode, the forum looked like it just exploded. In the process, a whole pile of data mingled as well (ie my account got overwritten by RedTechie’s…? O_o ) and I’m still wondering if that was my fault, or some residual glitches from before.

Sadly… and here’s the kicker, when I went to revert the database, not only did it remove the RPG-X stuff, but it deleted a whole pile of UberGames posts with it, as well as all of the admin accounts, which meant I was no longer logged in… and it kind of collapsed in on itself. XP

Sooo….. I realised I had no choice but to restore the entire SQL database I made, since by this point, the database was riddled with corrupted tables.
At this point, I found out I couldn’t just feed the data back into phpMyAdmin, because it was too big and caused timeouts.

So, RedTechie suggested I go and find a script that handles staggered MySQL queries, and use that. Thankfully, I found an awesome one called BigDump (http://www.ozerov.de/bigdump.php).

And now… icing on the cake, BigDump refused to load my file because it was encoded wrong. Turns out it can’t handle INSERT queries that try to insert all of the data at once… something called extended inserts.

So… long story short, I’ve spent up until now, writing a utlity in C# .NET that could take my SQL file, resequence it into 1 on 1 insertion queries and then debugged it until it worked.

What was meant to be an easy 4 hour task has dragged onto 3 days. Ugh…
I guess I should be happy I learnt a tonne of new stuff in the meanwhile… but I’m not so sure.

So the moral of this story is:
1) ALWAYS (!!!) RTFM!!!!!!!! No matter how simple something looks, there’s always a way to screw it up.
2) When exporting SQL from phpMyAdmin, make sure to uncheck the ‘extended inserts’ checkbox.
3) Test backup restoration before assuming it’ll just work.
4) Save the alcohol-based celebrations until after it works. XD

So yeah…. the forum’s all back online, and I’ve taken proper backups now… so I’m ready to try again haha.

In the meantime, I might also release my ‘SQL Splitter’ program as well, in case anyone else runs into the same problem I had…

  • Karen Kobayashi

    Hi there TiM (^-^)

    The ‘details’ of this topic is quite a different language for me, however I was kind of happy that I was at least familiar with “C#,” thanks to you :)

    I’m sorry to hear that you had to go through all that you have described above, which left you with a “tale of fail” to tell, but I guess sometimes we just have to learn the hard way…(>_<)…

    Taihen datta ne, honto ni!!
    「大変な三日間、お疲れ様でした!!!」

  • Karen Kobayashi

    Hi there TiM (^-^)

    The ‘details’ of this topic is quite a different language for me, however I was kind of happy that I was at least familiar with “C#,” thanks to you :)

    I’m sorry to hear that you had to go through all that you have described above, which left you with a “tale of fail” to tell, but I guess sometimes we just have to learn the hard way…(>_<)…

    Taihen datta ne, honto ni!!
    「大変な三日間、お疲れ様でした!!!」

  • http://www.tim-oliver.com -=TiM=-

    Hey Karen!

    Haha yeah, this is a pretty technical topic, and I’m thinking right now I haven’t really detailed and articulated what I was doing well enough… I might go back and change it a bit hehe.

    Ahaha that’s cool, no problems. :) Yeah I chose C# to write the utility so I could practice using that language a bit more (although I was tempted to use C++, another language I use a bit more often XD)

    Hahahaha yeah thanks. I haven’t gotten any sympathy from anyone else on this matter (mainly because not following ‘RTFM’ is automatic fail har har), and I think I’ve learnt my lesson now.

    Either way, I’ve managed to restore my database and I’ve made a clean backup now, so I’m going to try again now!

    あはは、ありがとうな! でも超やれやれだろうねこれは!

  • http://www.ubergames.org/ Tim

    Hey Karen!

    Haha yeah, this is a pretty technical topic, and I’m thinking right now I haven’t really detailed and articulated what I was doing well enough… I might go back and change it a bit hehe.

    Ahaha that’s cool, no problems. :) Yeah I chose C# to write the utility so I could practice using that language a bit more (although I was tempted to use C++, another language I use a bit more often XD)

    Hahahaha yeah thanks. I haven’t gotten any sympathy from anyone else on this matter (mainly because not following ‘RTFM’ is automatic fail har har), and I think I’ve learnt my lesson now.

    Either way, I’ve managed to restore my database and I’ve made a clean backup now, so I’m going to try again now!

    あはは、ありがとうな! でも超やれやれだろうねこれは!