TLDR; Don’t store Tweet ID’s as INT. Use BIGINT.

While writing a Twitter application in Rails I was having an issue saving tweets to the database.

A quick brief on the application: The app would download tweets from the mention timeline of a particular user. It would only download tweets it hadn’t already processed based on the last saved tweets unique id (as set by twitter). All the data from the tweets were making it into the database as expected but every time the application downloaded tweets it would re-download the same tweets.

After being quite perplexed for a period of time and trying many different solutions I realized that Twitters unique tweet ID did not match the tweet_id being stored locally. This was caused by the sheer size of Twitters id number. I was storing it as an INT which can only go up to 2147483647 (MySQL). Where Twitters current tweet id’s reside in around 36331797625184256.

I would need to use MySQL’s BIGINT which can go up to 9223372036854775807 to be able to save the id as a number.

Unfortunately BIGINT ins’t a standard type to set via rails migrations. As per this post by Dustin Tinney - Ruby on Rails & MYSQL BIGINT the proper way to use big int is to create a regular integer column via migrations and the change the column to BIGINT.

def self.up
  create_table(:people) do |t|
    t.integer :number_of_facebook_friends
  end

  change_column :people, :number_of_facebook_friends, :bigint
end