MySQL Bugs: #27645: Datetime field does not accept default NOW()

Bug #27645	Datetime field does not accept default NOW()
Submitted:	4 Apr 2007 12:46	Modified:	10 Apr 2012 14:51
Reporter:	Ari Arantes	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S4 (Feature request)
Version:	5.0,5.1,5.5	OS:	Any
Assigned to:	Martin Hansson	CPU Architecture:	Any

Description:
Datetime field does not accept default NOW()

How to repeat:
Table structure:

mysql> desc conta;
+--------------+------------------+------+-----+------------+----------------+
| Field        | Type             | Null | Key | Default    | Extra          |
+--------------+------------------+------+-----+------------+----------------+
| co_cod       | int(10) unsigned | NO   | PRI | NULL       | auto_increment |
| co_dt_cad    | datetime         | YES  |     | NULL       |                |
| co_dt_venc   | date             | NO   |     | 0000-00-00 |                |
| co_cm_cod    | int(11)          | YES  |     | NULL       |                |
| co_tc_cod    | varchar(5)       | NO   |     |            |                |
| co_dt_prog   | date             | YES  |     | NULL       |                |
| co_fpc_cod   | varchar(5)       | NO   |     |            |                |
| co_desc      | varchar(50)      | NO   |     |            |                |
| co_status    | char(1)          | NO   |     |            |                |
| co_vlr_conta | double           | NO   |     | 0          |                |
| co_vlr_pago  | double           | YES  |     | NULL       |                |
| co_dt_pgto   | date             | YES  |     | NULL       |                |
| co_emp_cod   | varchar(5)       | NO   |     |            |                |
| co_contabil  | varchar(1)       | NO   |     |            |                |
| co_usr_cod   | varchar(10)      | NO   |     |            |                |
| co_obs_prog  | varchar(100)     | YES  |     | NULL       |                |
| co_obs_pgto  | varchar(100)     | YES  |     | NULL       |                |
+--------------+------------------+------+-----+------------+----------------+
17 rows in set (0.00 sec)

mysql> alter table conta modify co_dt_cad datetime default now();
ERROR 1067 (42000): Invalid default value for 'co_dt_cad'

mysql> alter table conta modify co_dt_cad datetime default CURRENT_TIMESTAMP;
ERROR 1067 (42000): Invalid default value for 'co_dt_cad'

The same errors occurs when I tried to modify table using MySQL Query Browser (Table Editor).

ALTER TABLE `admin`.`conta` MODIFY COLUMN `co_dt_cad` DATETIME DEFAULT now();

Same problem:

mysql> create table a (b datetime default now());
ERROR 1067 (42000): Invalid default value for 'b'

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html:

"The DEFAULT value  clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW()  or CURRENT_DATE."

So, this is explicitely documented limitation.

This "documented limitation" is even more annoying to me than a bug. 

Let's change the documentation so that it allows functions as defaults. People commonly want the ability to make a datetime's default value to be now() (the time the record is inserted) as default.

When people realize that they can't do this it deters some of them from using mysql. Every little thing mysql can't do contributes to the amount of people deterred form using mysql. I want as many people as possible to use mysql, because I believe mysql will evolve faster as more people who use it. It causes more ideas, more feature request, more bug reports, etc....

So, let's not let a "documented limitation" prevent us from treating a limitation like a bug.

For those who might suggest using timestamp instead of datetime, consider that the year range for timestamp is 1970 to 2037! That's not to far away, man, and I don't want to have to remember until then that I've got to change my database schema in 2037. So, yes, timestamp will default inherently to now() "until 2037"!

I'd like to choke who ever made these types the way they are. I'm not even satisfied with the fact that datetime has a year range of 1000 to 9999. Sure I'll be dead in 9999, but I like making things "overkill flexible" ... I think I'm just going to use a bigint to store my date values. If you're going to be myopic jackass, be big myopic jackass. Even bigint has limitations, but it's way more ambitious than timestamp and datetime.

Why should I have to write more code to something so simple to work?

Datetime should accept the default NOW() I agree.

You can use a TIMESTAMP column (works almost like DATETIME)...

"The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column."

That works for me :)

But what if you have the need  for 2 "timestamp" column in the same table. MySQL won't allow this either...

i.e I have a column created_date as DATETIME (to store the date time when the record was created) and also a TIMESTAMP last_update to store the time when the record was last updated. 

The later can be automatically updated by mysql with current_timestamp but the created_date cannot be set to using now() as the default value (so one less thing I need to set the value of in the app code).

I know, one year after placing, but so recognizable. It has been so many times that I have to figure out how it works for MySQL. We work a lot with other databases and it is so much more convenient being able to use Now() as a default. It is probably a difficult one to facilitate but will be highly appreciated.

Has no one done anyhing about this in the last year+?!

* PostgreSQL
* MSSQL
* Oracle

All these database servers allow this, so what's the hangup?!  

Can we think outside of the box regarding this small yet VERY annoying 'limitation' (that is still a 'limitation' all the way through version 6!!!).  Is it so difficult to go the way of the status-quot and listen to user demand?  Is it so necessary to through developers a curve ball like this?  You can defend this 'limitation' however you wish, but to me, and I'm sure, many many others, it won't justify it.  

To do this strictly on the database server, the only other work around I can think of would be to create a trigger manually for the insert event on said table that sets the datetime column to the value to the value returned by now() or current_timestamp(). 

Coming from the camp that says "We should make things easier for our users", I think that now or current_timestamp should be allowed as default values for datetime fields.  Creating a trigger like I stated previously could be done automatically while processing create and alter statements as a convenience for those that use MySQL.

I am surprised that even after so many users asking for it, it still hasn't been done; even in 5.1 and after a year.

I do like MySQL and am glad that it's free. I have been using it for 5 years now and plan to keep on using it.

But, does it (being free) mean users won't be listened to?

ping.

Is this lack of a feature going to be addressed? This may seem a small limitation, but enough small limitations and tiny work-arounds become a large problem.

I have to add my concern that this bug continues to go unswatted. Put up a PayPal contribute button and I'll chip in a buck. Or give me access to code repository and I'll fix it myself.

Thanks,

---rd

I agree. MySQL should do this. This is such a basic thing. Let's not call it a bug but a feature request. However I doubt any developer would be reading what we're writing here. :(

ping - this feature request / bug still exists, and still blocks people.

I just change back to MySQL. I am very experienced in MySQL and .NET, last I design in MySQL in a couple of years. Then I forced to change to MSSQL because of Visual Studio integrated features.

I got no chance to use MySQL because I just maintaining industrial system that using MSSQL and Oracle until now. Now, I have a chance to develop a project and I decide to use MySQL because of my experience before think that MySQL is the great.

After few days studying the current MySQL engine and tools and also designing a new database, I found a few silly problems in MySQL. I googling around to find out why this happened. Just like ondelete/onupdate dropped when the table altered to add new field and also this issue - running a new() (or else) function on default datetime field.

I found the issue for my own in just a few days using MySQL. It means that other developer also found and know this thing in earlier. It should not be a problem in the current years because MySQL team should learn from the past to alert and response even in a 'small' thing like this. the current version 5.1 should be stable and feature rich. If you not enough team members. Just let me in. I don't want to let MySQL popularity become reduced.

Another bigger things that I want to share here: After I testing the datetime, timestamp type and date time related function, I found a few bugs in MySQL engine. You can try to forward your system time to more than 2037 (year). I expect type/function related to timestamp only (that is limited to 2037) return an unexpected result, however, function that return datetime also the same including utc_date(). It will make the engine terminated unexpectedly (not the query browser). While the CURRENT_DATE() return 1900-01-00. It is differ from documentation state that the datetime limit is between 0001-01-01 to 9999-12-31. I know 9999 is impossible, but it is fine to make datetime functioning properly until 2999 or 2099. Now it can't even in 2040.

All this small and big issue shows that MySQL team need more effort to design a batter algorithm.

I hope so MySQL consider this things and solve it in the next release.

Guys,

I need 2 columns in my database:
CREATE_TIME
UPDATE_TIME

How can I support this case with this bug still around?

Can it be fixed?

I have the same issue.  I realize that this isn't technically a bug, but I'd love to have the ability to use functions as defaults.  In addition to NOW() I'd also like to be able to use USER().

I'd like to add my voice to the above.

I just want a created date and an updated date.  It really should not have taken me 30 mins to find out that MySQL won't do this.

Guys, I understand your problems and will ask for progress on this. There is, however, an open worklog task for this.

+1

I know, 2 years later but where there is life, there is hope :-)

Datetime should accept the default NOW() like other database do.

A Trigger was mentioned by a previous post and was the only solution that I could find.  Here is what I ended up with in a table called "event" and two fields called statdate and lastdate.  It seems to work pretty well.  

CREATE TRIGGER event_insert BEFORE INSERT ON `event`
    FOR EACH ROW SET NEW.startdate = NOW(), NEW.lastdate = NOW();

I just ran into this.  This is such a basic feature; it should have been implemented a long time ago.  Someone previously mentioned that this is supported on PostgreSQL, MSSQL, and Oracle.  I would like to add that DB2 also supports it.  Go on and add it already!

Oh, I completely agree with all of this. I have the same need as another stated. 2 separate columns. 1: DateTimeCreated; 2: DateTimeUpdated. To have to write a trigger for something as basic as this is really silly. I suppose you could create a 2 column child table that stores a list of the PK of the parent table and a column that is datetimestamp so that you always have a record of when updates were made but that's overkill for many folks. I just went through the migration toolkit to try to move a db over but all my tables have 2 date/time fields as stated above. None of them will move over.

I'm stunned that such a simple, well requested feature is not part of MySQL.

I'm in disbelief that I can't use 'now()' as a default value for a datetime data type.  I've been using this feature heavily in mssql and am using mysql on a personal project.  Thank god I have a dbi layer that will take care of this on an automatic basis but I'd much prefer it to occur at the database level where it well should be.  Default value should simply accept expression and returns errors when they don't provide a useful value.

Me too. On the login page it says "The world's most popular open source database". Well, let's keep it that way. Unfortunately, it actually doesn't matter to me in the short term because my ISP's on 5.0 and unlikely to upgrade any time soon.

BUT - this just goes to show that it will take *years* after this is eventually fixed until the feature is generally available. It must be fixed urgently to minimise that time lag.

This is a 'victim of your own success' problem. MySQL is very popular and widely available now in a version that excludes this basic feature, so even if it's fixed now, only when 'mass market' ISPs eventually adopt the fixed version will the masses be able to use it. Look at Microsoft's efforts to get rid of IE6 - it's rubbish, IE7 and IE8 are out, but IE6 just won't go away because it shipped with XP, which is _everywhere_.

Thanks for the trigger code, that'll have to do for now (and for several years to come!).

One last point - I notice that these Bug records have a 'Submitted' date and a 'Modified' date. How's that done then? Not with DEFAULT NOW(); !! ;)

Cheers, al.

throwing in my 2¢, too...

I can see how it could be daunting to add general support for functions as a default for a column. HOWEVER, let's at least be able to support this. Even if the 'Now()' in there is just some keyword constant as suggested by others.

I've wanted this for years and years (10+) of using MySQL pretty much exclusively. I'm not going anywhere (to any other db), and I'd code this in there myself if I had the time. Thanks guys for all your hard work!

I agree, it would be great if DATETIME would accept NOW(); ideally I would love to see something like this:

CREATE TABLE t (    
    created_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_date DATETIME DEFAULT ON UPDATE CURRENT_TIMESTAMP
);

As far as I know, this isn't possible even with TIMESTAMP because it's applying the CURRENT_TIMESTAMP to two columns:

#1293 - Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

@Tonci Grgin

Can you please update us with the open ticket status for this job?

I'm trying to migrate from Progress OpenEdge but unfortunately many of the tables (> 800) have fields with a default equivelant to `curdate()`.

This is just for those who need a creation date column and a update column:

CREATE TABLE `t1` (
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `col3` char(255) DEFAULT NULL
) 

INSERT INTO  t1  VALUES (NULL,NULL,"text");

SELECT * FROM t1;

UPDATE t1 SET col3="New value";

and the updated_at column will be updated with the current timestamp.

(taken from comments on http://dev.mysql.com/doc/refman/5.0/en/timestamp.html )

Guys, I understand the problem but I did whatever I could... Let me see if it's possible to do more.

2 years+... Can Someone get on this yet?

2010 and still no end in sight!

I think bug #12707 is both older and more generic feature request.

From 4 Apr 2007 through 26 July 2010 and the BUG BUG BUG still exists in MySQL that you can't default a date or datetime field to anything useful like NOW().

YES THIS IS A BUG!!!!

Just because you document it doesn't mean it is not a bug. A defect put into the code even by design is still a bug and a defect.

Documenting it and saying "we mean for it to work that way" comes right out of the Microsoft documentation hand book.

Now hurry up and fix this highly annoying BUG. Thank you very much.

CURRENT_TIMESTAMP would be the wrong type I imagine, but surely it'd be possible to define a new constant e.g. CURRENT_DATETIME with the right type? 

If it works for the timestamp type why not datetime? This is useful functionality that's missing from MySQL in my opinion.

I suppose there's an issue of not wanting to collide with people's existing objects that might be called "current_datetime"?

After reading this comment thread, I definitely support the new world order.  

Thanks to the user who suggested using a TIMESTAMP column... that will fix the issue in the majority of the cases.

Thanks to every user who is working hard to develop MySQL as free, open source software. Your work is amazing.. I appreciate every day I am able to use the software. 

Much love and peace!

David

I stumbled upon this page today while searching for why timestamp can accept a now() connection while datetime cannot. Now I find out that timestamp is an exception and expressions are not allowed as default values.

I tried out now() as default value with quite confidence as it seemed natural to me and I have found that whatever seems natural to me, MySQL usually supports that already. But this time there was disappointment. I told myself there must be a way MySQL supports this very basic feature of specifying a default value for datetime field. I tried date(now()) with much hope but that didn't work either.

The trigger solution, even though it works, to me is unacceptable. The code shouldn't branch out to stored functions for such a small task. I resorted to setting Date explicitly in my java code. I hope this feature gets added soon. MySQL should listen more to what community wants. MySQL 3 and 4 are still used in half the Linux hostings not because they had partitioning and replication but because they met the expectations of a typical user.

I'm more than certain that these half-wits are doing this for business reasons. They *don't want* their software to be compatible with anyone else's. They're simply taking MS's old policy of not playing well with others. That's why its been so long since they've bothered to fix such an obviously necessary feature. Considering this and other stupid recent "mistakes" it seems the open sourcers are trying to make mySQL something that Oracle doesn't actually want to buy. Sad that the team isn't quite smart enough to figure out that they just want to buy it and close it to get rid of the competition. IMHO, this is the only decent free & open-source platform out there. Ruin it...piss off the user base & Oracle/MSSQL will be king again. So...say goodbye folks. These guys are going to ruin this product.

The comments on this thread just demonstrate that if you give a geek a computer and some anonymity their manners go straight out the window.

Half-wits? Really? No appreciation shown for the volumes of hard work provided for them at no cost, charming. Surely someone of such towering contempt could simply patch the solution in themselves, MySQL is, after all, open source.

Sure this particular case might be frustrating, but it's not a bug (despite the powerful ITS IN CAPITALS THUS IT MUST BE argument displayed above) it's a clearly documented limitation.

Well, I have an application that I wrote using PostGreSql.  It is fairly large and because my current hosting facility now only supports mysql I have a choice.  I am in the process of porting from postgresql and there are many areas that are incompatable.  I have so many tables that mark their creation date using this method.  I am about at the stage where I would rather find a new host then to have the number of file changes to support both databases.

I don't see why it should work for a time stamp and not for current date.  Both are functions, one isn't any harder then the other.  Whats the issue?

Your right that we should be appreciative that we get to use these powerful tools for free, but that's only if we can make them work for our problem.  If not, it seems to me that we should be able to tell them what we need, and if they don't listen to their customers, the only avenue to get what we need is to move on.

This means users who upgrade their old applications to mysql 5.1 always have the problem of documented limitations instead of enjoying documented new features. What about backward compatibility... 

Put this feature back for god sake....

I also agree that this limitation should be added as a feature. It's been over two years since this plea was submitted. Can somebody do something about it?

Really, the NOW() function still does not work to create a default value for a DateTime field? Is this ever going to get fixed?!?

I agree, this is very annoying and I assume they implemented it this way in order to maintain speed. But I'll give up the speed of mysql for ease of use in postgres. In fact things like this about mysql are what keep me going back to postgres. I love to use postgres and I try to use mysql but then I run into things that mysql can't do but postgres can and I end up just going back to postgres.

This is the most ridiculous things I've ever run into as a DB developer. Agreed that mySQL is free, and it's opensource. But this is one such case about OpenSource that really bugs me and makes me run towards standard products such as SQL Server. With OpenSource, there is a "Yes way", a "No way", and then there is this "Mid way" where people need to come up with all kinds of workarounds to make things happen even if it means (otherwise) simple things like assigning a default value to a datetime column in a database. All this in the name of "free stuff"!

We don't seem to extend this "opensource" policy to other things in our lives  like the cars we drive. We all want them built by manufacturers with warranties and we're even ready to pay for them. No one seems to want to drive a free open source car put together by a group of backstreet mechanics! But when it comes to software, it looks like we gotta live with such ridiculous annoyances. Took me an hour to figure out that mySQL has no default value for a datetime column!!!!!!!!!!!!!!

Agreed - very nice to have. Can we pretty please have this implemented? Backport to all versions (esp 5.1) please.

Thanks in advance to the (normally very responsive) MySQL team!

Fix it please, it's the most ridiculous thing I'v ever seen in a DB.

MySQL, please change this. This is certainly not a desired application behavior. I hope this change will come soon.

There is no need to be rude people, lets keep it civil, eh? My definition of a limitation in software is a feature that should be there but isn't or is there but lacks the 'reach' that is normally required. Otherwise it would not be a limitation, but a feature request. I think when just about every other RDBMS in major use supports it (and many have for decades - DB2 for example) - it seems somewhat lacking that it is continually ignored (especially given the amount of comment about it). Someone mentioned patching it as it's open source - whilst this is true, I doubt many ISPs would allow us to patch their installs of MySQL. The trigger method is interesting, but not very efficient. For the guy that wanted Update Date and Time separately, this could be achieved with a view to split them from a single TimeStamp field in the base table. Other than that, the only viable efficient way I can see is to do it in code (maybe through a DAL).

I may have a look at the code tomorrow (if I get chance) and see how easy it would be to impliment just a new constant (as opposed to function support - like Now()). Being an old git, and having used database professionally since the days of IMS DB/DC and DB2 V1 (and through just about all the major RDBMSs since), I am stll impressed with, and like, MySQL - however, with SQL Express on one side (as a fairly powerful multiuser DB) and SQLLite (as an single user/app side DB)on the other, chomping at the bit, MySQL may need to pick up its game as the other freebie RDBMSs come up on the outside.

I hear my former co-worker's words in my head as I read this ticket: "You get what you pay for..."

I'm dumbfounded that this issue even exists -- let alone for such a long time...

Perhaps my former co-worker was right.

This is still not working?  Bang for the buck, fixing this would make a lot of people happy.  More time seems to have been spent in defending the limitation than the time it would have taken to fix it by now.

While testing migration from Oracle I encountered this issue. I can't believe this is still not fixed. So I am adding my vote.

This is silly. 

Could we get some progress update?

just ran into this problem today and found this thread, please lift this "limitation"...

Count me in. Thanks for the great tool and all the work on the new GUI Workbench. Nice stuff.

But I have been facing the same problem as others and would love to see a resolution for this. A documented limitation is still a limitation to an otherwise wonderful product.

Best regards!

please provice a solution for this issue asap. it's a blocker.

I'd like to see this feature added as well.

I'd also like to see this issue fixed.

I just came across this issue myself for a database I am working on. I too need an UPDATE_TIME and CREATE_TIME, with the former being a TIMESTAMP with default value of "CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" and the latter being a DATETIME which I would like to have a default value of NOW().

The trigger solution I think is the cleanest. That is, dont give a default value to CREATE_TIME, and in a "before insert" trigger set it to NOW(). That will work even though its kind of an ugly solution.

Personally, I dont understand why a default value cannot be a function. It seems to be that shouldnt be hard to implement, but I'm sure there has to be some technical reason that I am not privy too.

Can a developer at least weigh in on WHY this problem exists?  Why let the comments pile up with no explanation?

Come on, guys.

This is ridiculous.
This feature is a must.

2011 still no beans?  I would appreciate the addition of this feature, can't current_timestamp possibly be used for datetime, since it works for a timestamp field, I would guess it's just a matter of adding an if statement in the creation syntax ;(.

+1 to see this feature added

I'll put my vote in for this. The number of times I've wanted to use this has lead me to choose Postgres when I'd rather be using MySQL for broader support.

+1 to see this feature added

It's clear that Martin Hansson has his hands on something else and doesn't have time to develop this.

There are some comments where someone asked to get hands on source development. Last time I checked MySQL was open source. So those people really should download the source and start coding. Your patches can be submitted from files-section.

It was probably political issue to set this as "feature request", because everyone  understands it has already reached a bug level, when many people stop using product and ask for a fix for over two years, without any fixes being made (or brought to public).

But lets remember the history, I think it took to version 5.0 from MySQL, to get views into database. That for an example was a total disaster "missing feature". And first versions of views-functionality were even quite limited (what I recall).

So even as it seems ridiculous to us as users, it probably ain't to those who need to do it. So get more OpenSource style and apply a patch yourself!

PS. Example fix:
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
doesn't work in all conditions. It works in example case, but I just tested it in my db and no luck. Triggers are always good, but they add unnecessary extra work for a simple case, that should work out-of-the-box.

As we approach the fourth year of a total lack of standards compliance, I think that a statement can be made that this “feature” will not be added. Due to this bug, myself, several other programmers, and DBAs have wasted valuable time. Every other RDBMS in major use supports it. The time wasted on this and other SQL standard deviations by MySQL has made this product more expensive than even Microsoft SQL Server. After all, labor is not free and neither is any RDBMS. Developers and DBAs have made numerous pleas though all of the approved channels for YEARS about this lack of standards compliance and seemingly these pleas fallen upon deaf ears. Based on this alone, I would not recommend the use of MySQL to anyone for any purpose.

It is really ridiculous that this is still a 'feature'. To see how MySQL does not listen to their community is very frustrating to say the least.

This is one too many negative experiences with MySQL I've had. I'm astounded to why they insist on keeping their ground on simple matters like this.

I am struggling to think of a case in which you would want a datetime field with a constant default.  Instead of that virtually useless feature, it would be nice to have one that everybody would use.

@Bob Hoff: 'NOW()' is not a constant default (at least in my opinion), moreover constant values (eg. '2011-01-01 00:00') can be used as default for datetime fields.

I have recently started using MySQL in a new job after ~20 years of Rdb on OpenVMS.  In creating tables for a new deployment, I found the now() solution for the timestamp type with my "created_date" and "modified_date" fields.  However, the scripts were quickly sent back to me for rework because the fields HAVE to be datetime, which, as I found out through this thread, does not accept now() as a default.  Bummer!  So, being really new to all this, where can I find the open source code, as has been suggested several times?  I would like to take my hand at creating a fix, even if I just use it locally.  Thanks!

@Victoria: MySQL source code is available from here: http://dev.mysql.com/downloads/mysql/
At "Select Platform" select "Source Code" from the drop down.

Good luck! :)

If you've managed to fix this issue, please let us know. I think a lot of people who will find this bug thread in the future would like to know.

Thank you for the information!  If I'm able to come up with anything, I'll be sure and let you all know.  Hopefully, I can get enough fires put out that I have the time I want to work on it.

Thank you for the information!  If I'm able to come up with anything, I'll be sure and let you all know.  Hopefully, I can get enough fires put out that I have the time I want to work on it.

This is a rather pathetic issue to still have on what is a decent database.

This is forcing me to do a hack-around.  I am now defaulting the 2 columns timestamp columns to a static timestamp and then over-writting them in a update and insert trigger.

The larger issue is that you can't insert into a table that has a NOT NULL column without either providing a value in the insert or providing a default.  You should be able to use a BEFORE INSERT, emphasis on the "BEFORE", to populate the data.

Does anyone really think that it's in Oracle's interest to promote fixes to an OSS db platform that was chewing into their enterprise Oracle sales?  While I would love to see MySQL continue to be the top OSS db platform, there is just too much risk at present to trust my apps and data to the platform due to the unresponsive attitude to issues like these from Oracle.  I've rolled all my applications to postgresql over the last 3 months and am pleased with the result.

I think it's misleading to blame this bug on Oracle, as it was opened 4 years ago, even before Sun has acquired MySQL AB.

Please, add Now() or CURRENT_DATETIME or similar functionality for datetime default value.

Same story ......................
mysql 5.1.59

create table deductable_settlement_request (
deductable_settlement_request_id bigint not null auto_increment,
deductable_id bigint not null,                                  
settlement_issue_ts date default current_date not null,         
settlement_validity_indays int not null,                        
settlement_value double not null,                               
discount_value double not null,                                 
deductable_settlement_request_state_id int default 1 not null,  
constraint pk_deductable_settlement_request primary key (deductable_settlement_request_id),
constraint fk_dsr_deductable foreign key (deductable_id) references deductable (deductable_id),
constraint fk_dsr_settlementstate foreign key (deductable_settlement_request_state_id) references deductable_settlement_request_state (deductable_settlement_request_state_id)
) engine=innodb                                                                                                                                                               
--------------                                                                                                                                                                

ERROR 1064 (42000) at line 117: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'current_date not null,
settlement_validity_indays int not null,                                                                           
settlement_value' at line 4

Just tragic.

just to add my voice here.

it would be good to see such a 'feature' implemented.

has it not being addressed by MariaDB?
http://kb.askmonty.org/en/default-clause

alternatively, there is always options out there:
http://www.postgresql.org/docs/8.2/static/sql-createtable.html

similarly to other on this discussion, i have started a project to create a solution for a client - but some of the key requirements would involve working around these limitation in MySql.  So to save my client's resources and deliver quicker i will be looking elsewhere for a database product - since we can not stretch to far enough for an Oracle license..

thanks for keeping us informed.

with regards,

+1 you can count me in.

2 years of not fixing this, due to lack of time? Maybe .. though I doubt that this is the truth. But 4 years? Are you kidding?

We're all jumping through the same hoop since 4 years, now?

Since I don't know, I gotta ask:
Is this bug that complicated to fix, that nobody has submitted a patch, yet?
Or have all the patches, that might have been sent in, been rejected?

P.S:
I don't care, who owned MySQL 4 years ago.
The thing I care for, is that neither Sun nor Oracle seem to have done anything regarding this issue for as long as they could have. And within that kind of time, to me, that counts as a statement.
Sad, sad, sad.

All,
 
I had said several months ago I would start working on a fix for this as I found this issue WAY beyond anything excusable.  However, right after I said that, I was diagnosed with cancer and have been taking care of that full time ever since.
 
I am feeling much better now (and the doctors are using the "cure" word - YEAH!), so hopefully I can start to work on this at least part time over the holidays.
 
Victoria Allen

Hi folks, this is solved using INSERT trigger, below:

USE `yourDatabaseName`$$

DROP TRIGGER /*!50032 IF EXISTS */ `yourTable_ITrig`$$

CREATE
    TRIGGER `yourTable_ITrig` BEFORE INSERT ON `yourTable` 
    FOR EACH ROW BEGIN
      SET NEW.yourDateColumn = NOW();
END;
$$

DELIMITER ;

Going on 5 years. The community obviously has felt a need to add this feature. I personally have had to work around this limitation a number of times. 

Also, turns out triggers and Now() are inefficient in practice compared to the Timestamp datatype. 

Here is a benchmark
http://www.flupps.org/pics/timestamp_benchmark.015.jpg
and its thread:
http://lists.mysql.com/internals/34918

The work-around often isn't as simple as adding "Now()" to an SQL query. People need to stop pointing to that as a solution. Adding a trigger is an unnecessary, ugly band-aid. Wreaks of incompetence. Do it right.

Really? Is that what this has come to? a 5 year old feature requested by the community can't just get implemented without pulling teeth? We have to document and develop benchmarks and test scenarios? 

Can somebody explain why Open Source is better/faster/cheaper than closed source again? I think I need to be reminded. 

-Chris

Hi folks,
you might want to read this:

http://optimize-this.blogspot.com/2012/04/datetime-default-now-finally-available.html

Best Regards

Martin

Fixed in 5.6. Documented in the 5.6.5 changelog as follows:

"Previously, at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time. This restriction has been lifted. Any TIMESTAMP column definition can have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used with DATETIME column definitions. For more information, see Section 11.3.4, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”. "

Closed. Good job Martin.

Great stuff although there is still one problem (MySQL 5.6.5 m8) with altering column when you want to set new / update old DEFAULT value:

ALTER TABLE test_table ALTER COLUMN CreatedOn SET DEFAULT now();

I'm getting an error with above statement. Other statements like CREATE TABLE and ADD COLUMN works fine.

Pawel: right.
But:
- this is documented: http://dev.mysql.com/doc/refman/5.6/en/alter-table.html says
ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
a literal would be '2012-05-01', NOW is a function thus not a literal
- here is a workaround:
ALTER TABLE test_table MODIFY COLUMN CreatedOn <current type and other options of the column> DEFAULT now();
For example if CreatedOn is currently DATETIME NOT NULL:
ALTER TABLE test_table MODIFY COLUMN CreatedOn DATETIME NOT NULL DEFAULT now();

It is still happening of MySQL 5.7.18.