MySQL Bugs: #57665: Inconsitent Default Timestamp Context Menu

Bug #57665	Inconsitent Default Timestamp Context Menu
Submitted:	22 Oct 2010 17:13	Modified:	5 Jan 2011 12:21
Reporter:	Dityo Nurasto	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.29 CE	OS:	Any
Assigned to:	Alfredo Kojima	CPU Architecture:	Any
Tags:	columns, context menu, timestamp

Description:
An error occured when trying to forward enginering ERR Diagram to database. Timestamp datatype column on 7th position and upward, makes context menu for the default CURRENT_TIMESTAMP and CURRENT_TIMESTAMP ON UPDATE CURRENT_STAMP are grey out.

How to repeat:
1. Create ERR Diagram
2. Add New Table
3. Add 8 columns
4. Defined as TIMESTAMP at column 6 and 7
5. Left click and the default action for TIMESTAMP are gray out at column 7 and upward
6. Try to forward enginering with not null checked on 7th column, wizard will show error.

Suggested fix:
fix the context menu for default timestamp

it should be right click not left click.

Please, read the manual (http://dev.mysql.com/doc/refman/5.5/en/timestamp.html) about TIMESTAMP columns properties. Workbench is correct when it does NOT allow to set that defaults to second TIMESTAMP column in the table.

It would be nice if it will prevent setting NOT NULL without default, but it is hard to decide if user had finished editing column properties or not...

Thank you for reference. I try the following:

1. set column 6 (last_login) as TIMESTAMP NULL with no default value.
2. set column 7 (created_date) as TIMESTAMP NULL with CURRENT_TIMESTAMP default value.

I try different combination with NULL and default value 0 or NULL for column 6.

as I see in documentation, those situation are allowed. Only one column that is required to have default value by using CURRENT_TIMESTAMP and the context menu / properties grey out on column 7.

As far as I remember documentation clearly says that explicit CURRENT_TIMESTAMP default is allowed only for the first TIMESTAMP column in the table, column 6 in your case. Please, check.

In documentation i found that we could supress automatic default for first TIMESTAMP column by assigning constant DEFAULT value such as 0.

In my case, I want to assigned default CURRENT_TIMESTAMP value to second TIMESTAMP column which is column 7, but make the column 6 as constant DEFAULT value. 

The desired behaviour that I was expected was while in column 7 the properties menu for default CURRENT_TIMESTAMP value still available to be choosen.

I see now. You mean this:

"To specify automatic default or updating for a TIMESTAMP column other than the first one, you must suppress the automatic initialization and update behaviors for the first TIMESTAMP column by explicitly assigning it a constant DEFAULT value (for example, DEFAULT 0 or DEFAULT '2003-01-01 00:00:00'). Then, for the other TIMESTAMP column, the rules are the same as for the first TIMESTAMP column, except that if you omit both of the DEFAULT and ON UPDATE clauses, no automatic initialization or updating occurs."

Indeed, this is a bug that Workbench does not allow to set this via context menu.

fix confirmed in repository

An entry has been added to the 5.2.31 changelog:

For a column of type TIMESTAMP, the menu items Default CURRENT_TIMESTAMP and Default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP in the context-sensitive menu of the Columns tab of the Table Editor were grayed out. This occurred where a preceding column of type TIMESTAMP was allocated an explicit default value. MySQL Workbench should not have prevented setting a value via the context-sensitive menu for the second defined column in this case.