Re: INSERT DELAYED and NOW()
Eric Bergen wrote: How about something like this: mysql select @t := now(); +-+ | @t := now() | +-+ | 2005-06-09 09:55:49 | +-+ 1 row in set (0.00 sec) if the timestamp is not needed on the client with : mysql do @t := now(); you would spare a bit of bandwidth my 2cts. -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DELAYED and NOW()
More queries yes but not more disk i/o. The first query will never touch a disk. [EMAIL PROTECTED] wrote: Eric Bergen [EMAIL PROTECTED] wrote on 06/09/2005 12:56:59 PM: How about something like this: mysql select @t := now(); +-+ | @t := now() | +-+ | 2005-06-09 09:55:49 | +-+ 1 row in set (0.00 sec) mysql insert delayed into t set t = @t; Query OK, 1 row affected (0.00 sec) mysql select * from t; +-+ | t | +-+ | 2005-06-09 09:55:49 | +-+ 1 row in set (0.01 sec) This way you get the current time of the call and it doesn't matter how long the insert delayed sits for. Jochem van Dieten wrote: On 6/9/05, Jeremiah Gowdy wrote: Does this seem to break SQL / application logic in some fashion? Not worse then it is currently broken :) According to the SQL standard CURRENT_TIMESTAMP, which in MySQL is a synonym for NOW(), is supposed to have a value that does not change during a transaction. At which point during the transaction that value is 'sampled' is implementation defined. (ISO/IEC 9075:2003-2 section 6.31) Since both NOW() and INSERT DELAYED are MySQL extensions I don't particularly care how they behave/interfere, but I would prefer any solution/hack not to complicate MySQL ever becomming standard compliant in this regard (and standard compliance is an official goal). Does the standard specify when the timestamp is evaluated? During the transaction. I agree that it might be better for it to be a seperate function, but since DELAYED isn't part of the standard, I'm not sure there's anything that keeps an implementation from evaluating the CURRENT_TIMESTAMP for a query upon receipt of the query from the network, rather than when the SQL statement is evaluated. Let me reiterate: Since both NOW() and INSERT DELAYED are MySQL extensions I don't particularly care how they behave/interfere. If I wrote a SQL server from scratch, would this not be a valid implementation, to timestamp upon network receive of a complete query, rather than upon finding the CURRENT_TIMESTAMP (or NOW()) function while parsing a query? That depends on some more implementation issues: perceivably your network receive could even be before the start of the transaction. Evaluate CURRENT_TIMESTAMP only once per transaction, between the start of the transaction and the end of the transaction. Jochem The problem with that is that you have just doubled the query count at the central logging server. That's a lot of traffic it can probably do without. I like the QNOW() approach. (Use an extension, the new function, to deal with a side effect of an extension, DELAYED. It's a universal balance kind of thing.) Some alternative names: QUEUEDNOW(), QUEUEDTIMESTAMP(), RECEIVEDTIME(), RECEIVEDTIMESTAMP(), ARRIVALTIMESTAMP() Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSERT DELAYED and NOW()
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
Re: INSERT DELAYED and NOW()
On Thursday 09 June 2005 09:39 am, Jeremiah Gowdy wrote: 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. Why cant you use the application to do a timestamp.. so when you send the insert, it send with the timestamp of when the query would have actually been inserted? Jeff pgpGXWuwLb4Lp.pgp Description: PGP signature
Re: INSERT DELAYED and NOW()
- Original Message - From: Jeff Smelser [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, June 09, 2005 7:50 AM Subject: Re: INSERT DELAYED and NOW() On Thursday 09 June 2005 09:39 am, Jeremiah Gowdy wrote: 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. Why cant you use the application to do a timestamp.. so when you send the insert, it send with the timestamp of when the query would have actually been inserted? As I said, there are multiple computers hosting this telecom application, and their timestamps need to be synchronized. Even with an NTP AtomTime type program, events are never going to be truly synchronized and ordered using per-computer application time stamps based on the local computer's system time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DELAYED and NOW()
On 6/9/05, Jeremiah Gowdy wrote: 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? Not worse then it is currently broken :) According to the SQL standard CURRENT_TIMESTAMP, which in MySQL is a synonym for NOW(), is supposed to have a value that does not change during a transaction. At which point during the transaction that value is 'sampled' is implementation defined. (ISO/IEC 9075:2003-2 section 6.31) Since both NOW() and INSERT DELAYED are MySQL extensions I don't particularly care how they behave/interfere, but I would prefer any solution/hack not to complicate MySQL ever becomming standard compliant in this regard (and standard compliance is an official goal). 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. Wouldn't it be confusing to have different behaviour of the NOW function for INSERT and INSERT DELAYED statements? 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 would prefer this option. Changing it for NOW() as a whole only makes MySQL deviate further from the standard and has backward compatibility issues. Changing it just for NOW() in combination with INSERT DELAYED is potentially confusing. So if you really need a new function, this seems like the right idea. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DELAYED and NOW()
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? Not worse then it is currently broken :) According to the SQL standard CURRENT_TIMESTAMP, which in MySQL is a synonym for NOW(), is supposed to have a value that does not change during a transaction. At which point during the transaction that value is 'sampled' is implementation defined. (ISO/IEC 9075:2003-2 section 6.31) Since both NOW() and INSERT DELAYED are MySQL extensions I don't particularly care how they behave/interfere, but I would prefer any solution/hack not to complicate MySQL ever becomming standard compliant in this regard (and standard compliance is an official goal). Does the standard specify when the timestamp is evaluated? I'm not really sure it would break CURRENT_TIMESTAMP to have it evaluated when a request is received rather than when it is actually processed. Does the spec get down to that level of implementation? 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. Wouldn't it be confusing to have different behaviour of the NOW function for INSERT and INSERT DELAYED statements? Yeah, this is probably the worst option. 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 would prefer this option. Changing it for NOW() as a whole only makes MySQL deviate further from the standard and has backward compatibility issues. Changing it just for NOW() in combination with INSERT DELAYED is potentially confusing. So if you really need a new function, this seems like the right idea. I agree that it might be better for it to be a seperate function, but since DELAYED isn't part of the standard, I'm not sure there's anything that keeps an implementation from evaluating the CURRENT_TIMESTAMP for a query upon receipt of the query from the network, rather than when the SQL statement is evaluated. As long as you do it in a uniform fashion, wouldn't it be an implementation issue? If I wrote a SQL server from scratch, would this not be a valid implementation, to timestamp upon network receive of a complete query, rather than upon finding the CURRENT_TIMESTAMP (or NOW()) function while parsing a query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DELAYED and NOW()
On 6/9/05, Jeremiah Gowdy wrote: Does this seem to break SQL / application logic in some fashion? Not worse then it is currently broken :) According to the SQL standard CURRENT_TIMESTAMP, which in MySQL is a synonym for NOW(), is supposed to have a value that does not change during a transaction. At which point during the transaction that value is 'sampled' is implementation defined. (ISO/IEC 9075:2003-2 section 6.31) Since both NOW() and INSERT DELAYED are MySQL extensions I don't particularly care how they behave/interfere, but I would prefer any solution/hack not to complicate MySQL ever becomming standard compliant in this regard (and standard compliance is an official goal). Does the standard specify when the timestamp is evaluated? During the transaction. I agree that it might be better for it to be a seperate function, but since DELAYED isn't part of the standard, I'm not sure there's anything that keeps an implementation from evaluating the CURRENT_TIMESTAMP for a query upon receipt of the query from the network, rather than when the SQL statement is evaluated. Let me reiterate: Since both NOW() and INSERT DELAYED are MySQL extensions I don't particularly care how they behave/interfere. If I wrote a SQL server from scratch, would this not be a valid implementation, to timestamp upon network receive of a complete query, rather than upon finding the CURRENT_TIMESTAMP (or NOW()) function while parsing a query? That depends on some more implementation issues: perceivably your network receive could even be before the start of the transaction. Evaluate CURRENT_TIMESTAMP only once per transaction, between the start of the transaction and the end of the transaction. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DELAYED and NOW()
How about something like this: mysql select @t := now(); +-+ | @t := now() | +-+ | 2005-06-09 09:55:49 | +-+ 1 row in set (0.00 sec) mysql insert delayed into t set t = @t; Query OK, 1 row affected (0.00 sec) mysql select * from t; +-+ | t | +-+ | 2005-06-09 09:55:49 | +-+ 1 row in set (0.01 sec) This way you get the current time of the call and it doesn't matter how long the insert delayed sits for. Jochem van Dieten wrote: On 6/9/05, Jeremiah Gowdy wrote: Does this seem to break SQL / application logic in some fashion? Not worse then it is currently broken :) According to the SQL standard CURRENT_TIMESTAMP, which in MySQL is a synonym for NOW(), is supposed to have a value that does not change during a transaction. At which point during the transaction that value is 'sampled' is implementation defined. (ISO/IEC 9075:2003-2 section 6.31) Since both NOW() and INSERT DELAYED are MySQL extensions I don't particularly care how they behave/interfere, but I would prefer any solution/hack not to complicate MySQL ever becomming standard compliant in this regard (and standard compliance is an official goal). Does the standard specify when the timestamp is evaluated? During the transaction. I agree that it might be better for it to be a seperate function, but since DELAYED isn't part of the standard, I'm not sure there's anything that keeps an implementation from evaluating the CURRENT_TIMESTAMP for a query upon receipt of the query from the network, rather than when the SQL statement is evaluated. Let me reiterate: Since both NOW() and INSERT DELAYED are MySQL extensions I don't particularly care how they behave/interfere. If I wrote a SQL server from scratch, would this not be a valid implementation, to timestamp upon network receive of a complete query, rather than upon finding the CURRENT_TIMESTAMP (or NOW()) function while parsing a query? That depends on some more implementation issues: perceivably your network receive could even be before the start of the transaction. Evaluate CURRENT_TIMESTAMP only once per transaction, between the start of the transaction and the end of the transaction. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DELAYED and NOW()
Eric Bergen [EMAIL PROTECTED] wrote on 06/09/2005 12:56:59 PM: How about something like this: mysql select @t := now(); +-+ | @t := now() | +-+ | 2005-06-09 09:55:49 | +-+ 1 row in set (0.00 sec) mysql insert delayed into t set t = @t; Query OK, 1 row affected (0.00 sec) mysql select * from t; +-+ | t | +-+ | 2005-06-09 09:55:49 | +-+ 1 row in set (0.01 sec) This way you get the current time of the call and it doesn't matter how long the insert delayed sits for. Jochem van Dieten wrote: On 6/9/05, Jeremiah Gowdy wrote: Does this seem to break SQL / application logic in some fashion? Not worse then it is currently broken :) According to the SQL standard CURRENT_TIMESTAMP, which in MySQL is a synonym for NOW(), is supposed to have a value that does not change during a transaction. At which point during the transaction that value is 'sampled' is implementation defined. (ISO/IEC 9075:2003-2 section 6.31) Since both NOW() and INSERT DELAYED are MySQL extensions I don't particularly care how they behave/interfere, but I would prefer any solution/hack not to complicate MySQL ever becomming standard compliant in this regard (and standard compliance is an official goal). Does the standard specify when the timestamp is evaluated? During the transaction. I agree that it might be better for it to be a seperate function, but since DELAYED isn't part of the standard, I'm not sure there's anything that keeps an implementation from evaluating the CURRENT_TIMESTAMP for a query upon receipt of the query from the network, rather than when the SQL statement is evaluated. Let me reiterate: Since both NOW() and INSERT DELAYED are MySQL extensions I don't particularly care how they behave/interfere. If I wrote a SQL server from scratch, would this not be a valid implementation, to timestamp upon network receive of a complete query, rather than upon finding the CURRENT_TIMESTAMP (or NOW()) function while parsing a query? That depends on some more implementation issues: perceivably your network receive could even be before the start of the transaction. Evaluate CURRENT_TIMESTAMP only once per transaction, between the start of the transaction and the end of the transaction. Jochem The problem with that is that you have just doubled the query count at the central logging server. That's a lot of traffic it can probably do without. I like the QNOW() approach. (Use an extension, the new function, to deal with a side effect of an extension, DELAYED. It's a universal balance kind of thing.) Some alternative names: QUEUEDNOW(), QUEUEDTIMESTAMP(), RECEIVEDTIME(), RECEIVEDTIMESTAMP(), ARRIVALTIMESTAMP() Shawn Green Database Administrator Unimin Corporation - Spruce Pine
