Bug #56835 setting deafault value with DATE_ADD()
Submitted: 17 Sep 2010 7:50 Modified: 17 Sep 2010 8:34
Reporter: vaishali dolas Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: Query Cache Severity:S2 (Serious)
Version:mysql Ver 14.12 Distrib 5.0.45, for red OS:Linux (redhat-linux-gnu)
Assigned to: CPU Architecture:Any
Tags: setting deafault value with DATE_ADD()

[17 Sep 2010 7:50] vaishali dolas
I want to alert my table.I have fired following command.
alter table customer_user modify  endDate timestamp default CURRENT_TIMESTAMP 
Its working perfectly fine
But if i want to set my default value using DATE_ADD gunction its giving me error.

alter table customer_user modify  endDate DateTime default DATE_ADD(NOW(), INTERVAL 1 YEAR);

Error Messgae is as follows: 
ERROR 1064 (42000): 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 'DATE_ADD(NOW(), INTERVAL 1 YEAR)' at line 1

Please suggest me to fire command correctly if im wrong.
Or else suggest me solution

How to repeat:
alter table customer_user modify  endDate DateTime default DATE_ADD(NOW(), INTERVAL 1 YEAR);
[17 Sep 2010 8:01] Peter Laursen
This is a limitation with MySQL.  You cannot use functions and expressions as default-specifier


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. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column

(not a MySQL person)
[17 Sep 2010 8:04] vaishali dolas
Hi Peter,
Thanks for quick reply.
But can you suggest me solution for such problem?
[17 Sep 2010 8:08] Peter Laursen
Now - I do not know what environment you are using except for the server.

But one solution could be to do the calculation of "DATE_ADD(NOW(), INTERVAL 1 YEAR)" in a client (a script for instance) to produce a constant and next fire the ALTER statement with the constant.
[17 Sep 2010 8:19] Peter Laursen
Even this does (unfortunately) not work (also returns 1064 syntax error):

  `txt` varchar(10) DEFAULT NULL,
  `when` date DEFAULT NULL,
  `num` int(11) DEFAULT '55',
  PRIMARY KEY (`id`),
  KEY `NewIndex1` (`txt`)
set @peter = 99; -- here we could do a calculation in a Stored Function for instnace
ALTER table `test`.`tttt` CHANGE `num` `num` int(11) DEFAULT @peter NULL; 

So user variables are not accepted either - what would be nice really!
I can only see the solution to calculate a constant client-side.
[17 Sep 2010 8:34] Valeriy Kravchuk
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.