Bug #15035 Unable to enter column defaults - error 1067
Submitted: 17 Nov 2005 21:56 Modified: 24 Jul 2006 14:56
Reporter: David Wybo Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Administrator Severity:S3 (Non-critical)
Version:5.0.15 OS:Any (*)
Assigned to: Michael G. Zinner CPU Architecture:Any
Tags: Object Editors

[17 Nov 2005 21:56] David Wybo 
Description:
Using MySQL administrator 1.1.5 and entering for example "CURRENT_TIMESTAMP" into the default value field creates the following query:

ALTER TABLE `mysql_weis`.`table_language` MODIFY COLUMN `column_modified_at` TIMESTAMP DEFAULT 'CURRENT_TIMESTAMP';

This results in an MySQL error message 1067 (invalid default value)

How to repeat:
Using MySQL administrator 1.1.5 and entering for example "CURRENT_TIMESTAMP" into the default value field creates the following query:

ALTER TABLE `mysql_weis`.`table_language` MODIFY COLUMN `column_modified_at` TIMESTAMP DEFAULT 'CURRENT_TIMESTAMP';

This results in an MySQL error message 1067 (invalid default value)
[29 Nov 2005 0:23] MySQL Verification Team 
Could you please show the output of:

win5017>select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

and if your table have already records when doing the
alter table action.

Thanks in advance.
[5 Dec 2005 18:53] David Wybo 
Hello Miguel,

Please find the following @@sql_mode query result:
'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

The error occurs with empty and non-empty tables.

Greetings,
David
[6 Dec 2005 6:27] Aleksey Kishkin 
david, if you put ' around the default value, mysql assumes it's string (or date) literal. Adn of course the string 'current_timestamp' cannot be default value for timestamp field.  look:

mysql> create table test (i timestamp default 'CURRENT_TIMESTAMP');
ERROR 1067 (42000): Invalid default value for 'i'
mysql> create table test (i timestamp default CURRENT_TIMESTAMP);
Query OK, 0 rows affected (0.03 sec)
[6 Dec 2005 17:57] David Wybo 
It seems that the MySQL Administrator application adds the quotes automatically. When I insert CURRENT_TIMESTAMP (without quotes) into the Colums detail tab - Default value textbox and execute APPLY CHANGES, I get a text box with the following query:

ALTER TABLE `mysql_weis`.`table_language` MODIFY COLUMN `column_modified_at` TIMESTAMP DEFAULT 'CURRENT_TIMESTAMP';

The quotes are added automatically by the MySQL Administrator application. Is there a way to avoid this?

Kind regards,
David
[6 Dec 2005 18:58] Aleksey Kishkin 
looks very close to http://bugs.mysql.com/bug.php?id=15511
[8 Dec 2005 13:10] Aleksey Kishkin 
verified against mysql-administrator 1.1.4/mysql 5.0.15
[6 Apr 2006 9:53] Vignesh Guttikar 
This is happening is  Ver 14.12 Distrib 5.0.18 as well. This problem occurs for both create and alter sql commands. Is there a patch for this in any version?
[21 Jul 2006 19:23] Adam Marzi 
This bug also occurs with the TIMESTAMP data type.  See example ALTER statement below, which produces the same error 1067.  It can be resolved as suggested in http://bugs.mysql.com/bug.php?id=15511 by dropping the table and re-creating the table from a script with the changes.  

ALTER TABLE `mydatabase`.`mytable` MODIFY COLUMN `CommitTimeStamp` TIMESTAMP NOT NULL DEFAULT 'CURRENT_TIMESTAMP';
[24 Jul 2006 14:56] Michael G. Zinner 
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://www.mysql.com/doc/en/Installing_source_tree.html