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.



73 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:
    http://bugs.mysql.com/bug.php?id=27645
    http://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

    Roel Van de Paar,
    Senior MySQL QA Engineer

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

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

    SvaraRadera
  3. Thank you Martin!

    SvaraRadera
  4. Sara Silavi7 maj 2012 09:11

    It took a long time but Thank you so much.

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

    SvaraRadera
  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?

    SvaraRadera
  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!

    SvaraRadera
  8. Thanks. It was a good solution

    SvaraRadera
  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?

    SvaraRadera
  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.

    SvaraRadera
  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

    SvaraRadera
  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

    SvaraRadera


  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.

    http://www.dbrnd.com/2015/10/mysql-set-default-value-for-a-datetime-column/

    SvaraRadera
  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

    SvaraRadera
  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

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

    SvaraRadera
  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

    SvaraRadera
  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

    SvaraRadera
  19. CALFRE is a local search engine for online and classroom training institute. we have online training for the course oracle fusion financials.our oracle fusion financials online training institute Hyderabad, Bangalore, Delhi, Chennai, Kolkata, Pune, Mumbai, Ahmedabad, Gurgon, Noida, India, Dubai, UAE, USA, Kuwait, UK, Singapore, Saudi Arabia, Canada


    Oracle fusion Financials Online Training

    Oracle Fusion Financials online Training

    SvaraRadera
  20. Such a great articles in my carrier, It's wonderful commands like easiest understand words of knowledge in information's.
    PEGA Training in Chennai

    SvaraRadera
  21. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    Android Training in Chennai
    Ios Training in Chennai

    SvaraRadera
  22. Thank you for taking the time and sharing this information with us. It was indeed very helpful and insightful while being straight forward and to the point.
    www.mcdonaldsgutscheine.net/ | www.startlr.com/ | www.saludlimpia.com/

    SvaraRadera
  23. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    Android Training in Chennai
    Ios Training in Chennai

    SvaraRadera
  24. It takes a long time to read but Thank you so much.
    and more information visit my website

    http://www.igofusion.com/courses/oracle-fusion-financials-online-training


    SvaraRadera
  25. You're so interesting and Fantastic; so nice to find someone with some original thoughts on this subject seriously. Many thanks for starting this up.......visit our website about ERP Tree Oracle Fusion Cloud Financials Training Centre

    SvaraRadera
  26. that's great to have blog like this it may clear the many thought from this Like
    Teradata
    Oracle DBA

    SvaraRadera
  27. How to Solve Time Data Type in MySQL? Contact to MySQL Server 5.0 Support
    Unable to solve your time data type issue in MySQL? Then check your issue with reliable support who can troubleshoot your all problem. Here we talk about Cognegic’s MySQL Technical Support or MySQL Enterprise Backup to solve your date time related issues. Our all professional experts effectively increased the availability and performance of your MySQL environment and protect you from any kind of outages and failures.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    SvaraRadera
  28. The most effective method to Solve MySQL Time Zone Problem through MySQL Technical Support
    MySQL Time Zone issue is a standout amongst the most widely recognized issues which are looked by a great many clients on everyday schedule. The primary bugs with respect to this issue might be your database, your working framework, Meta construct facilitating condition thus in light of. With this issue you are not ready to work with your MySQL. Anyway to troubleshoot this issue by claim isn't a simple assignment since it requires some awesome aptitudes that is the reason you need to connect with MySQL Remote Service and MySQL Remote Support.
    For More Info: https://cognegicsystems.com/
    Contact Number: 1-800-450-8670
    Email Address- info@cognegicsystems.com
    Company’s Address- 507 Copper Square Drive Bethel Connecticut (USA) 06801

    SvaraRadera
  29. Really Thanks For Posting Such an Useful Information...Thanks For Sharing.....
    Salesforce Training

    SvaraRadera
  30. Great Post. Keep sharing such kind of noteworthy information.

    IoT Training in Chennai | IoT Courses in Chennai

    SvaraRadera
  31. Thanks for posting this blog. This was very interesting blog, I felt comfortable while reading this post, thank you...............................For More details about best training Center details please Click Here.

    SvaraRadera
  32. It was really helpful very nice article to understand 3 tire best thank you so much for providing this article.It really helped me a lot.
    Oracle Project Accounting Training Institutes in Hyderabad

    SvaraRadera
  33. good
    http://spunksoft.com/course/sap-s4-hana-simple-finance-training-in-hyderabad/

    SvaraRadera
  34. I am sure this post has helped me save many hours of browsing other related posts just to find what I was looking for. Many thanks!
    Click here:
    angularjs training in chennai
    Click here:
    angularjs2 training in chennai
    Click here:
    angularjs4 Training in Chennai
    Click here:
    angularjs5 Training in Chennai

    SvaraRadera
  35. Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this.
    Click here:
    Microsoft azure training in annanagar
    Click here:
    Microsoft azure training in velarchery
    Click here:
    Microsoft azure training in sollinganallur
    Click here:
    Microsoft azure training in btm
    Click here:
    Microsoft azure training in rajajinagar

    SvaraRadera
  36. Thanks for sharing such an useful and nice information coming to us we are the 24Layouts provides the best of the best Vizag Real Estate deals and services,Thanks for the nice information...

    SvaraRadera
  37. Thank you for an additional great post. Exactly where else could anybody get that kind of facts in this kind of a ideal way of writing? I have a presentation next week, and I’m around the appear for this kind of data.

    click here

    Selenium Training in Bangalore|
    Selenium Training in Chennai

    SvaraRadera
  38. Thanks for posting this info. I just want to let you know that I just check out your site and I find it very interesting and informative. I can't wait to read lots of your posts


    click here

    Selenium Training in Bangalore|
    Selenium Training in Chennai

    SvaraRadera
  39. This is a nice article here with some useful tips for those who are not used-to comment that frequently. Thanks for this helpful information I agree with all points you have given to us. I will follow all of them.
    Blueprism training in Chennai

    Blueprism training in Bangalore

    Blueprism training in Pune

    Blueprism online training

    Blueprism training in tambaram

    SvaraRadera
  40. Thanks For Such an useful and informative stuff as always provided by your blog PartsBBQ is the leading provider for the Restaurant Parts at the best whole sale prices.Thanks For Sharing...

    SvaraRadera
  41. Den här kommentaren har tagits bort av skribenten.

    SvaraRadera

  42. Nice blog..! I really loved reading through this article. Thanks for sharing such a amazing post with us and keep blogging...

    ABiNitio online training in Hyderabad

    ABiNitio training in Hyderabad

    online ABiNitio training in Hyderabad

    SvaraRadera
  43. I wondered upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon.
    AWS Training in Chennai
    AWS Course in Bangalore
    AWS Training in Ambattur
    AWS Training in Guindy

    SvaraRadera
  44. Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end.

    best rpa training in chennai |
    rpa training in chennai |
    rpa training in bangalore
    rpa training in pune | rpa online training

    SvaraRadera
  45. Thank you for providing useful information and this is the best article blog for the students.learn Oracle Fusion Financials Online Training.
    Oracle Fusion Financials Online Training

    SvaraRadera
  46. Thank you for sharing such a valuable article with good information containing in this blog.students can learn Oracle Fusion Technical Online Training.
    Oracle Fusion Technical Online Training

    SvaraRadera
  47. Thanks for sharing valuable information in the article.students can make good career by learning Oracle Fusion SCM Online Training.
    Oracle Fusion SCM Online Training

    SvaraRadera
  48. Thanks for providing such a great information in the blog and also very helpful to all.learn best Oracle Fusion HCM Online Training.
    Oracle Fusion HCM Online Training

    SvaraRadera
  49. Thanks for Sharing,

    We are offering Classroom and Online Training Programs with latest Technology. Our Trainer are Real time Expert have 6+ Years Experience in IT Industries.
    https://bitaacademy.com/

    SvaraRadera
  50. Thank you so much for a well written, easy to understand article on this. It can get really confusing when trying to explain it – but you did a great job. Thank you!

    Data Science training in rajaji nagar | Data Science with Python training in chenni
    Data Science training in electronic city | Data Science training in USA
    Data science training in pune | Data science training in kalyan nagar

    SvaraRadera
  51. Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your blog.

    java training in chennai | java training in bangalore

    java interview questions and answers | core java interview questions and answers

    SvaraRadera
  52. Thank you so much for a well written, easy to understand article on this. It can get really confusing when trying to explain it – but you did a great job. Thank you!
    Scom2012 Training
    Office365 Training

    SvaraRadera
  53. Thank you so much for a well written, easy to understand article on this. It can get really confusing when trying to explain it – but you did a great job. Thank you!
    Oracle Rac Classes

    Oracle SOA Classes

    SvaraRadera
  54. Great and nice blog thanks sharing..I just want to say that all the information you have given here is awesome...Thank you very much for this one.
    DataScience Online Training

    DataStage Online Training

    Dell boomi Online Training

    Golden gate Online Training

    SvaraRadera
  55. I’m planning to start my blog soon, but I’m a little lost on everything. Would you suggest starting with a free platform like Word Press or go for a paid option? There are so many choices out there that I’m completely confused. Any suggestions? Thanks a lot.
    Best AWS Training in Marathahalli | AWS Training in Marathahalli
    Amazon Web Services Training in Anna Nagar, Chennai |Best AWS Training in Anna Nagar, Chennai
    AWS Training in Velachery | Best AWS Course in Velachery,Chennai
    Best AWS Training in Chennai | AWS Training Institutes |Chennai,Velachery

    SvaraRadera
  56. Inspiring writings and I greatly admired what you have to say , I hope you continue to provide new ideas for us all and greetings success always for you..Keep update more information..

    angularjs Training in chennai
    angularjs Training in chennai

    angularjs-Training in tambaram

    angularjs-Training in sholinganallur

    angularjs-Training in velachery

    SvaraRadera
  57. Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this.

    advanced excel training in bangalore

    SvaraRadera
  58. Thanks for your great and helpful presentation I like your good service.I always appreciate your post.That is very interesting I love reading and I am always searching for informative information like this.Please keep Sharing For More info on DATETIME DEFAULT NOW() finally available please follow our article.android development guide | future of android developer in india

    SvaraRadera
  59. Everyone wants to get unique place in the IT industries for that you need to upgrade your skills, your blog helps me improvise my skill set to get good career, keep sharing your thoughts with us.
    fire and safety course in chennai

    SvaraRadera
  60. Needed to compose you a very little word to thank you yet again regarding the nice suggestions you’ve contributed here.
    nebosh course in chennai

    SvaraRadera
  61. Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us and I never get bored while reading your article because, they are becomes a more and more interesting from the starting lines until the end.
    ibm datapower online training

    SvaraRadera
  62. Thanks for your great and helpful presentation I like your good service.I always appreciate your post. That is very interesting I love reading and I am always searching for informative information like this. Well written article.Thank You for Sharing with Us Please keep Sharing angular 7 training in chennai | angular 7 training in velachery | Best angular training institute in chennai

    SvaraRadera
  63. Nice tutorial. Thanks for sharing the valuable information. it’s really helpful. Who want to learn this blog most helpful. Keep sharing on updated tutorials…
    Devops Training courses
    Devops Training in Bangalore
    Best Devops Training in pune
    Devops interview questions and answers

    SvaraRadera
  64. Really you have done great job,There are may person searching about that now they will find enough resources by your post
    excel advanced excel training in bangalore
    Devops Training in Chennai

    SvaraRadera