MySQL Bugs: #40189: Table Editor - creating nullable TIMESTAMP columns

Bug #40189	Table Editor - creating nullable TIMESTAMP columns
Submitted:	20 Oct 2008 20:16	Modified:	20 Oct 2009 6:25
Reporter:	Greg Thomas	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL GUI Common	Severity:	S3 (Non-critical)
Version:	1.2.14	OS:	Microsoft Windows
Assigned to:		CPU Architecture:	Any
Tags:	null, timestamp
Triage:	Triaged: D3 (Medium) / R3 (Medium) / E3 (Medium)

Description:
You cannot create tables using the MySQL Table Editor that contains TIMESTAMP columns that contain NULL values. 

This is because of the well documented feature of TIMESTAMP columns that require the "NULL" to be made explicit (http://dev.mysql.com/doc/refman/5.0/en/timestamp.html - "However, a TIMESTAMP column can be allowed to contain NULL by declaring it with the NULL attribute."), yet the Table Editor does not add the explicit NULL, instead generating, for example ...

CREATE TABLE `wtest`.`foo` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `bar` TIMESTAMP,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

The column 'bar' should be defined as 

  `bar` NULL TIMESTAMP,

How to repeat:
1. Click "Create Table" from MySQL Administrator.
2. Add a TIMESTAMP column, clear the "NOT NULL" check box.
3. Click "Apply Changes"
4. Examine the SQL that the Table Editor indicates it will execute; note that the TIMESTAMP column does not include the required "NULL" qualifier, as described at 

Suggested fix:
I can see no harm in applying the "NULL" clause to all columns regardless of datatype where the "NOT NULL" check box is cleared.

If that is not appropriate for some reason that I can't fathom at the moment, then at least columns with TIMESTAMP datatypes should have the "NULL" qualifier included.

Screen shot showing table definition and incorrect SQL

Attachment: tableeditor.png (image/png, text), 38.92 KiB.

Doh! I said

>The column 'bar' should be defined as 
>
>  `bar` NULL TIMESTAMP,

I of course meant 

>The column 'bar' should be defined as 
>
>  `bar` TIMESTAMP NULL,

It's also worth noting that the "NULL" is also lost when editing that column, too.

Thank you for the bug report. Could you please try the latest release 1.2.13 of MySL Administrator. Thanks in advance.

Have retried with version 1.2.14 of MySQL Administrator, and this exhibits identical behaviour.

Thank you for the report.

Verified as described. Workaround: edit table, then check "not null" again. Please note TIMESTAMP field will have default CURRENT_TIMESTAMP in this case which is correct.

Many thanks for writing a bug report. We are on the way to implement full functionality of
MySQL GUI tools into MySQL Workbench. We won't fix this anymore.

More informations about MySQL Workbench you will find here:

http://dev.mysql.com/workbench/