If you use Twitter, no doubt you’ve read something about the “Twitpocalypse” that occurred this week.  It’s one of those things that a regular Twitter user should never really have to know anything about, except that the problem can (and actually did) cause a few applications to stop working.

I made it through relatively unscathed.  The majority of my Twitter programs were fine.  I learned a long time ago to define my ids as character types.  This provides the greatest degree of flexibility if a another service implements an unexpected change.

To understand the Twitpocalypse, it’s helpful to know how Twitter stores your tweets.  A numeric id is assigned to every tweet in the Twitter system.  Presumably, the tweet ids began at the number 1 and have been incrementing ever since.  In the world of computing, numbers can be stored as different types and each type has a maximum value.  At certain points, the tweet ids will exceed the limits of each numeric type.  Although this doesn’t directly affect the Twitter service (they’ve used numeric types that have maximums well beyond the current ids), it can have an impact on services that download tweets.  Some services may have defined the tweet id as a numeric type that can’t handle the larger numbers. 

Picture a classroom where a teacher uses one of those fancy calculators with a display that goes well beyond the basic eight digits.  Most of the students use the same calculator, but one of the students didn’t get the note and bought a simple eight-digit device.  The student with the basic calculator will do fine for most of the class and will have no problems keeping up with all of the computations.  Then one fateful day, the teacher puts up a nine-digit problem.  Even though everyone else is fine, the one student’s work is kaput.  (We might stop here to discuss the value of throwing away all of the calculators and doing the math by hand, but that’s a different post.)

So to complete the example, the teacher (Twitter) can continue to calculate numbers on into the future, but the student with the basic calculator is stuck (Twitterific) until he buys a calculator that can handle the additional digits.  Most Twitter applications developers have been aware of this situation for awhile and it should have come as no surprise.

I did have one interesting issue pop up (I won’t mention the application since I’m its only user).  The bug was something that I wouldn’t necessarily call obvious, and although in hindsight it seems simple enough, it was a little tricky to figure out.  This bug also highlights the fact that you can run into problems with your code even if the data field is defined correctly in the database.

I also treated the id as a numeric value and didn’t place quotes around it.

The section of code that was causing an error contained several select statements, an update, and an insert.  The error message that I received was roughly along these lines, “varchar could not be converted to int and resulted in an overflow error.”  I was expecting one of the update or insert statements to be the cause.  I spent several minutes scratching my head while trying to figure out why everything looked right and still wasn’t working.  As it turns out, it was the select statement that was throwing the error.  That was a surprise, but how did it happen?

Within my code, I had previous pulled a twitter id from the database in one of the early select statements.  Since this id didn’t come from an external source, I wasn’t performing a check of the input.  I also treated the id as a numeric value and didn’t place quotes around it.  The statement worked fine until the Twitpocalypse.

“select * from {table} where id = 2560000000″

“id” is defined as varchar in the database — notice the absence of quotes around the literal, 2560000000.

My lazy programming worked because the database was recognizing the number (sans the quotation marks) as an integer, which it then converted to varchar for the comparison to id.  After the Twitpocalypse, the number was too large to be converted to a signed integer.  Instead of simply converting the number directly to a character or a different numeric type, the database threw an error and the select statement failed.

So, obviously I should I have used quotation marks in the first place, or better yet, I should have used parameters.  In a rush in the wee hours of the night, we don’t always write perfect code.  This is a great reminder for me to skip shortcuts and hopefully it will help someone else avoid this issue in the future.