#native_company# #native_desc#

MySQL bug – prompted by Unix billions

By Christine Korza
on September 10, 2001

Hi there!

I just needed to share this with everyone.

While all our time-related functions appeared to be working properly and were being stored in our database with the new 10th digit, one small function did not work after the billion-rollover.

MySQL version: 3.23.33

The following query worked perfectly until the rollover:

SELECT * FROM opentickets WHERE AskDate < '$red' ORDER BY TicketID ASC

(I removed the non-pertinent bits)

$red is a value set to 345600 seconds behind the current time, which is assigned to $timenow.

$timenow and $red were being set correctly, but my query would pull up tickets with timestamps in the correct range, PLUS any ticket with a timestamp over 1,000,000,000. Through a bunch of debugging, I found that the query would work when I removed the little quotes around ‘$red’. Of course we’re used to using single quotes around PHP variables in MySQL queries because most variables (especially stuff ‘INSERT’ed into tables) need single quotes to work. In this case, the quotes NO LONGER WORK. I’m so glad I figured this strange problem out, and I wanted to share it with everyone I can so no one else wastes the time I spent on this.

Christine Korza
protonic.com Administration