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): 

        my $sth = 
          $dbh->prepare('UPDATE person SET email = ? WHERE person_id =
+ ?');
        foreach my $id (@ids) {
            my $email = $email_addresses->[$id];
            $email =~ s!\@.*!\@plusthree.com!;
            $sth->execute($email, $id);
        }

[download]

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: 

        my $sth = 
          $dbh->prepare('UPDATE person SET email = ? WHERE person_id =
+ ?');
        foreach my $id (@ids) {
            my $email = $email_addresses->[$id];
            $email =~ s!\@.*!\@plusthree.com!;

            while (1) {
                eval { $sth->execute($email, $id) };
                last if not $@;

                if ($@ =~ /duplicate/i) {
                    $email =~ s!\@!_@!;
                } else {
                    die $@;
                }
            }
        }

[download]

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: 

   UPDATE person SET email = 'info___...___@plusthree.com' WHERE perso
+n_id = 408700;

[download]
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

In reply to Another Endless Loop by samtregar

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.