MySQL Bugs: #56835: setting deafault value with DATE

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:	None
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()

Description:
Hello,
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);

This is a limitation with MySQL.  You cannot use functions and expressions as default-specifier

http://dev.mysql.com/doc/refman/5.1/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. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column

Peter
(not a MySQL person)

Hi Peter,
Thanks for quick reply.
But can you suggest me solution for such problem?

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.

Even this does (unfortunately) not work (also returns 1064 syntax error):

CREATE TABLE `tttt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `txt` varchar(10) DEFAULT NULL,
  `when` date DEFAULT NULL,
  `num` int(11) DEFAULT '55',
  PRIMARY KEY (`id`),
  KEY `NewIndex1` (`txt`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
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.

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.