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)

[20 Oct 2008 20:16] Greg Thomas
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.
[20 Oct 2008 20:17] Greg Thomas
Screen shot showing table definition and incorrect SQL

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

[20 Oct 2008 20:21] Greg Thomas
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,
[20 Oct 2008 20:24] Greg Thomas
It's also worth noting that the "NULL" is also lost when editing that column, too.
[21 Oct 2008 0:05] Miguel Solorzano
Thank you for the bug report. Could you please try the latest release 1.2.13 of MySL Administrator. Thanks in advance.
[25 Oct 2008 18:09] Greg Thomas
Have retried with version 1.2.14 of MySQL Administrator, and this exhibits identical behaviour.
[31 Oct 2008 9:50] Sveta Smirnova
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.
[20 Oct 2009 6:25] Susanne Ebrecht
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/