Journal Friends Archive Profile Memories Chalain May. 28th, 2008 10:16 am Grotendous Hack! (Wheee!) Today on "Grotendous Hack", my own personal corner¹ of the Coding Horror/Daily WTF world, I present: The Shadow Database!



So last Thursday night we performed a major server migration. Our main box had served our company long and well, but it was becoming overtaxed and it was time to move it not just to a new machine, but to a new cluster of machines. We prepared and overprepared and Murphy struck exactly as anticipated. Things went wrong, plan B's were resorted to, and after 3.5 hours our half-hour migration was complete and everything was cut over to the new boxes. And there was much rejoicing².



Friday morning Ralph, our analyst, came in and said, "Umm, I can't run reports." I had some spare cycles so I was given the task to resolve it. Never mind that I'm the new guy... how hard could this be? It would not be long before I discovered why everyone else volunteered to be unavailable for that task....



So, here's his reporting system: He has his own database server, it's this tiny little 500MHz box running... Windows XP. Okay, this is new to me. I wasn't even aware of this machine, and Ralph says he only has read access to it so I'm guessing it's just a cache machine. He's probably got a script that downloads and imports the nightly backup. Shouldn't be too hard.



Then Ralph says, "So, first I start SSH..." and I say "...what?"



He pulls up this obviously homegrown app with two buttons and a blinkenlight. The buttons say "Start SSH" and "Stop SSH", and the blinkenlight is either red or green. It's currently red.



"Um. What's that?" I ask.



I get that sick I-just-fell-down-a-rabbithole feeling as Ralph launches the Visual Basic IDE and starts showing me code....



So. Fast forward an hour, and here's how it all works: There is no database on that machine. The VB app creates an SSH tunnel (port forward) direct to the database server. The DB servers don't accept remote connections, so this little VB app makes this Windows box behave like a MySQL server. Okay, as a hack it's fairly clever. Riding on top of the SSH tunnel is an ODBC driver that connects to localhost:3306 and provides an ODBC connection.



All right, this sort of makes sense. Nobody remembers who wrote the original VB app, but we have source code and anyway I just need to find the code that creates the SSH tunnel and point it at the new server, right? What could possibly go wrong?



Ugh.



So one other detail: we renamed the database when we moved it. We carefully tuned all the other application code to make sure nobody was hardcoding database names. A quick scan through the VB app showed that it wasn't hardcoding either. Okay... all I have to do is open the ODBC connector and change the database, and...



...and the new database name is too long to fit in the ODBC adapter's text box. Who makes a database connector that only accepts 20 characters for the database name?!? Especially when ODBC allows up to 64? Our new database name is 24 characters long.



So.



I spent the weekend thinking my way around this. Update the ODBC drivers? Worth trying. Rename the database again? No way, not gonna happen. On the way to work Tuesday, the solution hit me: I would create a shadow database. Every table in the database would actually be a view--a view on the original table.



So yeah. In pseudocode, it looked like this: for table in db1.tables do execute "CREATE VIEW shadow.#{table} AS SELECT * FROM db1.#{table}" end In practice? It works great. It's gory and gross, and best of all, I didn't have to change--by which I mean, take ownership of--the VB project.



Now, two problems immediately appear with this code: the first is that a developer might stumble onto the shadow database and wonder what the heck it is. Our team is pretty good at communicating, but better safe than sorry. I created a table called _README_ with one column, readme , and filled it with a blurb explaining the purpose and weirdnesses of the database. Now if somebody decides to properly fix the issue later but doesn't destroy the shadow database, a maintenance programmer can know that he can wipe it out.



The second problem is that MySQL caches the table definition when it creates the view. If we migrate the database, it might break the views. To solve that, I simply wrote a script to destroy and rebuild the shadow database (including recreating the _README_ table).



So... yeah. Pretty grotendous. But I kinda like it. It desperately needs a good refactoring, for example, we're building an in-house database server that will slave replicate from the master, and when it's done we can just have Ralph connect straight to that, obviating the need for the custom SSH tunnel app for Windows³.



¹ or contribution, if you prefer.



² yes, we ate Robin's minstrels.



³ some of you may be thinking, "Why no just use PuTTY's plink.exe program?" Answer: we do. The VB app shells out to plink. And then it provides a blinkenlight. If you have to ask, you clearly do not understand the value of a good blinkenlight. Current Mood: amused

Current Music: Storm - Yoshida Brothers

6 comments - Leave a comment From: samwibatt Date: May 28th, 2008 08:17 pm (UTC) (Link) best of all, I didn't have to change--by which I mean, take ownership of--the VB project.



Dude, you are totally a genius. You definitely consider all the important parts of a solution - time, workiness, and Avoiding Crap That Shouldn't Be Your Problem. Reply ) ( Thread From: (Anonymous) Date: May 28th, 2008 10:11 pm (UTC) This is nasty from the beginning to the end. (Link) Allow connection from the intranet and make it clean.

Every times you create a new table, you have to create a new view ?!?! Reply ) ( Thread From: chalain Date: May 29th, 2008 06:07 am (UTC) Re: This is nasty from the beginning to the end. (Link) I completely agree. It is pretty appalling, innit.



I agree that it is utter befoulment. But removing any foulness would require an expenditure of time I'm unwilling to give. For example, how would Ralph connect to an intranet database, if he cannot connect to DSNs longer than 20 characters? Answer: Well, the ODBC drivers are--no, stop. See, we're all outta time for that problem.



As for creating a new view for every table? Yes. It's even worse than that, though: you also have to drop and recreate a view if you change an existing view. That's why I wrapped it up as a script. If and only if you make a change that needs to go into the reporting module--which is MUCH more stable than the database schema itself--you just run ./make_shadow_database and it rebuilds the entire list of views. Herein lies wisdom: it's nasty entrails and disgusting hackery, but it's not hard to work with.



That's the dual horror/beauty of this hack, really; and it's the reason I posted it in the first place. See, I wrote this up as a horror story: "You're not going to believe what's in the box! It's all tentacles and chicken entrails!" But if you put the lid on the box and step back and look at it, it's a box that generates the reports we were always able to get. Work goes on. Life goes on. I didn't fix the maintenance nightmare, it is true. But I didn't make it any worse, and in fact, the problem is now better documented and better understood for the next programmer that has to open that box and fish around in it. Reply ) ( Parent ) ( Thread From: pozorvlak Date: May 28th, 2008 10:11 pm (UTC) (Link) That's pretty painful :-) Mind if I friend you? Reply ) ( Thread From: chalain Date: May 29th, 2008 06:14 am (UTC) (Link) To paraphrase the old joke, I would never be seen hanging around with somebody with such low standards as to have me as a friend. Welcome aboard, and I may friend back. You write some interesting stuff. Reply ) ( Parent ) ( Thread From: pozorvlak Date: May 29th, 2008 11:57 pm (UTC) (Link) Thank you! So do you. Reply ) ( Parent ) ( Thread