Bug #38650 'Invalid default value' when trying to rename a column
Submitted: 8 Aug 2008 2:07 Modified: 11 Feb 2009 15:08
Reporter: Aleksey Grichenko Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Administrator Severity:S3 (Non-critical)
Version:1.2.12 OS:Microsoft Windows
Assigned to: Mike Lischke CPU Architecture:Any
Triage: D2 (Serious)

[8 Aug 2008 2:07] Aleksey Grichenko
Description:
When renaming a column (and probably in some other situations) saving the changes
fails with error 1067 - 'Invalid default value'. The SQL causing the problem
looks like: "ALTER TABLE `t` CHANGE COLUMN `c1` `c2` ... NOT NULL DEFAULT NULL".
The error does not appear for auto-increment columns or if 'NOT NULL' flag is not set.

The current workaround is to clear 'NOT NULL' flag, rename the column, then enable the flag again.

How to repeat:
In SQL Administrator, any database:

1. Create a new table, add columns `a` and `b` (`b` should not have auto-inc flag).
2. Apply changes. Default values for both columns are now NULL.
3. Rename column `b` to `b2`.
4. Apply changes. Enjoy the error.
[8 Aug 2008 5:17] Valeriy Kravchuk
Thank you for a problem report.
[11 Feb 2009 15:08] Mike Lischke
Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/