tisdag 10 april 2012

DATETIME DEFAULT NOW() finally available.

Having been rather desired for a while now, this feature is finally available as of MySQL Server version 5.6.5. It started out as the innocuous bug #27645 back in 2007, not really commanding much attention from anyone. But since, the page has seen around a hundred posts from users. This is a lot of posts for a bug page.

When I got to work on this, I started out looking at how functions in the default clause worked for the one supported case, CURRENT_TIMESTAMP (a.k.a. NOW() in MySQL) for TIMESTAMP columns. It turned out to be a little more complex than it had to, the TIMESTAMP type has a lot of special rules attached to it that no other types have, not even DATETIME.

One thing that struck me as a little odd was that you can only have one column with a function in the default or on update clause. So I had to take the decision how to deal with that restriction as we introduced DATETIME DEFAULT CURRENT_TIMESTAMP. Should we forbid having a TIMESTAMP DEFAULT CURRENT TIMESTAMP if there was a DATETIME DEFAULT CURRENT_TIMESTAMP? No matter how I looked at it, there would have to be a bunch of new convoluted special rules added that I couldn't motivate to myself. Moreover, a lot of users seemed to request having one DATETIME column with "default now" and another one "on update now", so they could track both creation and last update time to a row. The request was not lost on me: I decided to lift the one-column-with-NOW() restriction altogether. It made documentation simpler and avoided having rules applying only TIMESTAMP spill over on DATETIME. As expected, there was no technical reason for forbidding it, and the code involved needed an overhaul anyways.

The rules

I will not attempt to describe the various TIMESTAMP rules here, the manual does a great job at it already. Instead I will present the new set of rules for how DATETIME DEFAULT / ON UPDATE CURRENT_TIMESTAMP works.
  1. Trying to store a null value in a DATETIME NOT NULL column will always yield an error.
  2. A DATETIME column accepting nulls has NULL as default value unless specified.
  3. There are no implicit defaults for DATETIME columns except the one above.
  4. A DATETIME DEFAULT CURRENT_TIMESTAMP column will store CURRENT_TIMESTAMP if and only if an insert statement does not list the column or store a full row.
  5. A DATETIME ON UPDATE CURRENT_TIMESTAMP column will store CURRENT_TIMESTAMP if the row is updated and the new row is different from the previous row.
These rules sure look simple enough, don't they? Well, of course there are some more ins and outs to it if you look close enough. Hopefully you should not have to memorize all of these scenarios but it would be unfair of me not to at least mention them.

In rule #4, "an insert statement" includes, of course INSERT, LOAD and REPLACE statements. LOAD statements will store "0000-00-00 00:00:00" (a.k.a. the 'zero date') in a DATETIME column if the file contains a null at the column's position. Under strict sql_mode, the entire statement will fail. TIMESTAMP columns still store CURRENT_TIMESTAMP in this case, regardless of default.

In rule #5, the statements that can update a row are UPDATE and INSERT ... ON DUPLICATE KEY UPDATE ... when there is a duplicate primary key.

Lifting the one-column-with-function-default restriction also has some side-effects when you add or move columns.

  • As before, the first column in a table that is declared simply "TIMESTAMP" will implicitly be promoted to TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. If you add another column that is "TIMESTAMP" before it, they will both be as that long line I just wrote. Previously, you would get an error in this case.
  • Oh, yeah, error number 1293, a.k.a. ER_TOO_MUCH_AUTO_TIMESTAMP_COLS [sic], can't happen anymore. There's no limit to how many columns with CURRENT_TIMESTAMP you can have. Knock yourself out.

When is NOW()?

In MySQL, CURRENT_TIMESTAMP is defined as the query start time, the time when the query arrives at the server. This means that the value is constant during the execution of the query. So don't be surprised if you update a million rows of type DATETIME(6) ON UPDATE NOW(6) - microsecond precision - and they all get the same update time.

23 kommentarer:

  1. Congratulations Martin on delivering this long-desired feature!

    As the QA engineer for this project, I can honestly say you did an *excellent* job.

    Many attempts at finding feature-specific bugs only resulted in a resounding "all clear!".

    Some reference links for people to use:

    Roel Van de Paar,
    Senior MySQL QA Engineer

    Opinions are my own and may not reflect Oracle's views.

  2. Yay! Nice work, and nice write-up.

  3. Thank you Martin!

  4. Sara Silavi7 maj 2012 09:11

    It took a long time but Thank you so much.

  5. Bra jobbat Martin - stötte just på denna vid konvertering från sqlite :)

  6. "the first column in a table that is declared simply "TIMESTAMP" will implicitly be promoted to TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" - any reason to still keep this?

  7. Very nice. Thanks, I've come across that bug report every couple years when I go to try to use a NOW() and wonder why it doesn't work, but have forgotten the whole soap opera bug report.

    Now I just need to upgrade my server.

    Thanks for fixing it!

  8. Thanks. It was a good solution

  9. Thanks for that.
    But it sounds like you've just added new exclusion for DATETIME data type.
    And what about general solution - let use function as DEFAULT value for any data type?

  10. Hej där! Har du någonsin upplevt en sådan situation när en fullständig främling har rånat dig på nätet och tog din immateriella rättigheter? Tack ett gäng på förhand för ditt svar.

  11. wonderful information, I had come to know about your blog from my friend nandu , hyderabad,i have read atleast 7 posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a ton once again, Regards, MySql online trainingamong the MySql in Hyderabad. Classroom Training in Hyderabad India

  12. Cooling function to be trusted one fan with a diameter of 12 Cm has led blue looks so interesting while working. Performance of the PSU is good enough, it can be seen from the fluctuation of power between the ideal position with the position of full load does not change significantly.
    sic code
    Standard Industrial Classification


  13. Nice Article !

    Really this will help to people of MySQL Community.
    I have also prepared small note on this, How to set default value for a datatime column in MySQL.


  14. Nice information !!!

    W3 D Technologies is a pioneer in Dell Boomi Training sector, providing the Dell Boomi Online Training course we have experienced expert faculty for Dell Boomi

    Dell Boomi Online Training | Dell Boomi Certification Course

  15. We are offering Dell Boomi Online Training with latest techniques. Our experts have more than 8 years experienced and certified.
    Dell Boomi Online Training

  16. This is good course to learn.Very informative and very
    good course.in this we have to learn something from
    this article.

  17. This is good course to learn.Very informative and very
    good course.in this we have to learn something from
    this article.

    oracle fusion Procurement Online Training

  18. Hi,
    It's a very nice article,Surely this will help to people of SQL Community. I Appreciate for your work,Keep move on with new articles providing useful information.
    Thank you.
    oracle R12 training