laziness, impatience, and hubris | |
PerlMonks |
Another Endless Loopby samtregar (Abbot) |
on Feb 23, 2007 at 03:14 UTC ( [id://601680]=perlmeditation: print w/replies, xml ) | Need Help?? |
I always enjoy a good bug-hunting story. Sometimes they can even teach you something, even if it's just not to do that again! This one isn't the most interesting, but I found it instructive.
I wrote a script some time ago to sanitize a list of email addresses for use in demos. The idea was to take a real list of email addresses and turn them into something safe. The easiest way I could think of to do this was to change the domain to a known safe one, like our own domain where I work: plusthree.com. The first version went something like (ignoring chunking, transactions, error checking):
This worked fine until it hit the first duplicate. Email addresses in this DB have to be unique. When bob@microsoft.com and bob@villa.com both become bob@plusthree.com - boom! I figured I'd just have the code trap the error and put an underscore after the address until it worked, producing bob@plusthree.com, bob_@plusthree.com, bob__@plusthree.com, etc. The code looked like:
This worked great - until it didn't. One day I ran the script and it got halfway through a big list of emails and then stopped. Well, actually it didn't stop, it just stopped making progress. Looking at 'top' I could see MySQL was plenty busy so I decided to take a peek at the running query (using mytop, incidentally). I found:
Where I put in ... there were around 100,000 underscores. Ouch! The cause was obvious once I saw the query. The email column is only 255 characters wide which means each prefix can only have a maximum of 254 dups (or less) before MySQL can't tell the difference and starts returning a duplicate error for every one. The fix was fairly simple - I replaced the underscores with a numeric count. That should give the code plenty of room to breath. I see a few lessons here. 1) Look hard at any while(1) loops in your code - be sure the exit condition will definitely be hit. 2) Beware of column overflow when expecting your database to detect duplicates - the DB can only check what it can store. And, perhaps, 3) switch to a DB that throws an error on column size violations rather than silently ignoring them. I'm hoping MySQL's new strict-mode will fullfill #3 but I haven't played with it yet. -sam
Back to
Meditations
|
|