MySQL Bugs: #12948: Change of default value of (datetime) column to NULL not recognised

Bug #12948	Change of default value of (datetime) column to NULL not recognised
Submitted:	2 Sep 2005 11:37	Modified:	27 Sep 2005 16:48
Reporter:	Peter Velens	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Administrator	Severity:	S3 (Non-critical)
Version:	1.1.2	OS:	Windows (Win XP)
Assigned to:	Vladimir Kolesnikov	CPU Architecture:	Any

Description:
When trying to change the default value of a (datetime) column which is currently set to the default '0000-00-...' into a NULL default value, the Administrator just tells me nothing has changed. If I try to manually empty the field it just won't work either (same default value). 

My workaround is to simply fill in some default rubbish and capture the change, then paste it in Query Browser and replace the rubbish with NULL, but it makes changing columns aftwerwards quite annoying.

How to repeat:
Add a datetime column, forget the default value, try to null it afterwards (as described in the description)

Thanks for your bug report.  I was able to reproduce this bug in 1.1.2

How to set NULL value

Attachment: 12948.bmp (image/bmp, text), 201.76 KiB.

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

Hi,
I was unable to repro this bug. I've attached a screenshot showing how I setup default NULL value for a DATETIME column. If you use a different method, please describe.

Let me eleborate...

First I create a date field (it pretends to have a default value of 'null' but the create statement says the following)

ALTER TABLE `test`.`test` ADD COLUMN `aDate` DATETIME NOT NULL AFTER `ID`;

(mind you, I added it wrong, since i left 'nut null' checked, which is something I want to correct later on, along with the default value. if you add the column with the field unchecked (i.e. "you think before you act"), it does create a 'null' default)

Next I reopen the same table which gives me the following:
aDate DATETIME (notnull) 0000-00-00 00:00 (= the default)

First I disable the notnull (which is kinda useless when you want a null value defaulted). And Apply the changes, this time it tells me that it actually set the default value to the zero's.

Then I go to the tabsheet with column details (because just deleting/editing the default in the table won't allow me to make it null), and press the "null" button. It neatly fills in that the default is now null so apply the changes and get:

"The changes you made did not result in the need to alter the table"

Which is exactly my problem, since they do...

When I try to put a default of 'foo' in, it doesn't give an error, just resets again
to '0000-00-00 00:00' which then won't result in a change when I try to Null it.

Final note:
- i'm using MySQL 4.0.24_Debian-10 (on Linux), not sure if it's relevant.
- after 20 random-attempts I just noticed that if I switch the NotNull off and at the same time change the null value, it does work when I press apply (it then removes the default zero's), so this workaround works: notnull on, apply, notnull off + null, apply...

Hope this makes it clear (I tend to update parts that I look at, and later attempt to update the other parts, that should work just as well as updating them all at once of course). Thanks in advance...

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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Hi Peter, thanks for your feedback.
I went through your test-case and fixed (hopefully) all relevant issues. Now not-null check box default value == null should behave consistently and of course changing default value is now recognized as a change. 

Also notice that in MySQL server of version 5.0.2 or above setting 'foo' as default value for DATETIME values will cause server error (see documentation chapter 'TIMESTAMP Properties as of MySQL 4.1' for details).

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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html