DECIMAL() - A Lesson in Data Retention

So unknown to me, MySQL changed the way it handles DECIMAL columns between 4.x and 5.0.  Typically this wouldn't be a problem, but when my site was migrated to a new server, it also upgraded it to MySQL 5.0.  And I swear, initially the data was fine.  And based on what I read on the change page it should have kept the old table functionality.  But it would appear sometime over the Christmas holiday —while I was out of town and not paying much attention, they must have dumped and reloaded the database.  And doing that changes the column from the old to new functionality.  (See the last paragraph in the above referenced article.)

Now the ugly part for me…my CycleLog uses DECIMAL to store some of the personal data from my rides (weight, body fat and sleep time).  And unfortunately, the weight column was previously configured as DECIMAL(3,1) which in MySQL 4.x allowed values up to 999.9, but in MySQL 5.x only 99.9.  Thus being my weight is over 100 pounds, all of my rides that had a weight associated with them —that would be nearly all of them, had their weight values reset to 99.9 for all rides.

I stumble upon this when I entered a ride and the weight I entered was showing up as 99.9.  Thus the "huh, what's going on" journey began.  Once I found out about the column change, I altered the table to DECIMAL(4,1), updated my data and it then worked again.

I run a nightly database backups to S3, but I only retain 10 days worth.  Going back to the oldest one, the data was already gone.  And initially I thought my hosting provided might be able to get it back as they thought the old server was still around.  But unfortunately, it had been recycled.  Thus I have no backup with the old data.  I know I've certainly lost at least the last 3-5 years worth of data.  The 2009 data will be fine as I had the data for the couple of rides I had taken.  (I've been sick so I have been riding lately.)  And I have the first 5 years worth of data in my old paper logbooks.  So I should be able to painfully recover that, although my wife has offered to do some data entry for me.  So I may have here enter the date and weight on a spreadsheet, export that to CSV and then write a quick script to reinsert it.  I also may have some more of it archived on a CD somewhere as I used to log it on my Palm for later entry for a few years.  But that likely will be more involved as I'll have to Perl it out of the Palm DB files.

So once the initial shock and disbelief passed, and I accepted the fact that a lot of it is gone forever, I'm left with a tough lesson in data retention.  I'm bummed, but it could have been a lot worse had I lost my whole DB.  It certainly makes me wish I had started to archive the database quarterly like I had been intending to do.  It will certainly happen moving forward though.

And the real kicker?  The personal data was a later add-on to the CycleLog.  I'm not sure why I used DECIMAL there, because the rest of the schema uses DOUBLE.  Had I done that this never would have happened.  ARGH!!!!!!!!!!!
January 24, 2009 @ 08:17 am | Category:
comments powered by Disqus