ActiveRecord and getting the timing right

Have you ever with returning record that are depedndent on time based criteria. One of my last projects had a token based logging in system. A user clicked a login button, it created a token on a remote site and redirected the user away to the other site if the token matched and wasn’t out of date (more than 30 seconds old) then the user was logged in.

So we’d employ something like

Token.create(:token => 'something random', :expires_at => 30.seconds.from_now)

On the remote server we’d then attempt to recover the token

Token.find(:first, :conditions => ["token = ? AND expires_at < now()",  'something random'])

We then started to get problems with people just not being able to login. It didn’t take 30 seconds to redirect and recover the token from the database so what was going on.

The database server was on a different server to the web server which hosted both the Main site and the one that allowed token access. However the database server time was about 10mins behind the time on the webserver so Ruby’s Time.now and SQL’s now() or CURRENT_TIME() were returning the incorrect results.

At that point running ‘ntpdate’ to update the server time on the database server worked. But going back and fixing every instance of ‘now()’ was probably the smart move.

Token.find(:first, :conditions => ["token = ? AND expires_at < ?",  'something random', Time.now])

There’s a lesson to be learned from that, if you’re going to trust the time then it should be the same across all the servers you are using. But also if we are using an ORM to encapsulate SQL logic then stray into SQL at your peril as you may get unexpected results.