List:General Discussion« Previous MessageNext Message »
From:Jeremiah Gowdy Date:June 9 2005 2:39pm
Subject:INSERT DELAYED and NOW()
View as plain text  
I'm wondering if anyone else has run into this issue.

We are logging from a real-time telecom application (we have callers on the phone that are
being handled by threads that are logging to MySQL), and because of the nature of that
application, we use INSERT DELAYED.  There are multiple computers that are hosting this
application, so we'd prefer to use MySQL as the time source because the computer clocks
never stay in perfect sync, even with an NTP service running.  We insert the logs with
NOW() as the date/time for the entry, but (as I would expect) the function is not
evaluated until the delayed thread actually executes the insert.  This results in
variable delays which essentially make NOW() combined with INSERT DELAYED useless.

I am proposing that when a query is received by MySQL, a timestamp could be taken
immediately, and that timestamp could travel with the query until it is actually
processed.  For delayed inserts, the query would still sit in the insert queue, and it
would still say NOW(), but when the query finally gets executed, NOW() is evaluated
simply by returning the timestamp of when the query was received, rather than when it was
processed.  

Does this seem to break SQL / application logic in some fashion?  Does anyone rely on the
fact that NOW() is evaluated upon execute rather than upon receipt of the query?

If that would break something, another option would be that only insert delayed queries
would use the saved timestamp, all other queries would ignore it.

And in the most conservative option, there could be a seperate function like QNOW() or
something that returned when the query was received by the SQL server rather than the
normal NOW() processing.

I am willing and able to do the work to make a patch for this, I'm just wondering if doing
so would break anything.  Any comments, suggestions, or input would be greatly
appreciated.


Jeremiah Gowdy
Senior Software Engineer
FreedomVOICE Systems
http://www.freedomvoice.com
Thread
INSERT DELAYED and NOW()Jeremiah Gowdy9 Jun
  • Re: INSERT DELAYED and NOW()Jeff Smelser9 Jun
  • Re: INSERT DELAYED and NOW()Jeremiah Gowdy9 Jun
  • Re: INSERT DELAYED and NOW()Jochem van Dieten9 Jun
  • Re: INSERT DELAYED and NOW()Jeremiah Gowdy9 Jun
    • Re: INSERT DELAYED and NOW()Jochem van Dieten9 Jun
      • Re: INSERT DELAYED and NOW()Eric Bergen9 Jun
        • Re: INSERT DELAYED and NOW()SGreen9 Jun
          • Re: INSERT DELAYED and NOW()Eric Bergen10 Jun
        • Re: INSERT DELAYED and NOW()Philippe Poelvoorde10 Jun