Chalain (chalain) wrote,
  • Mood: amused
  • Music: Storm - Yoshida Brothers

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?


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.


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}"
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.
Tags: code, hacks
  • Post a new comment


    default userpic

    Your IP address will be recorded