MySQL Bugs: #36489: Table editor Columns tab Default section has problems.

Bug #36489	Table editor Columns tab Default section has problems.
Submitted:	3 May 2008 21:05	Modified:	8 Oct 2008 19:08
Reporter:	Justin Quick	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench	Severity:	S1 (Critical)
Version:	5.0.21 SE	OS:	Windows (XP Pro SP2)
Assigned to:		CPU Architecture:	Any

Description:
The Default column section under the Table Editor has problematic behavior for nullable columns, timestamps, and boolean values (true/false keywords).  TIMESTAMP default value CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP gets truncated to CURRENT_TIMESTAMP upon import and always fails validation from the Model > Validation menu.  

NULL seems to be added in as default value upon schema export.  

TRUE and FALSE fail validation as keywords when in the default column.  

*No documentation on how to use the Table Editor default values column for Booleans, timestamps, and null values.

How to repeat:
1) Create a DDL script tests.sql

CREATE TABLE `tests` (
	`test_id` INT unsigned NOT NULL auto_increment,
	`field1` VARCHAR(30) NOT NULL,
	`field2` BOOLEAN NOT NULL DEFAULT FALSE,
	`field3` VARCHAR(30) NULL DEFAULT NULL,
	`updated_ts` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	`created_ts` TIMESTAMP,
	PRIMARY KEY (`test_id`),
	KEY (`updated_ts`),
	KEY (`created_ts`)
) TYPE=InnoDB;

2) Import the DDL create table script using:
File > Import > Reverse MySQL Create Script 

3) Edit the script using the table editor.  

4) Validate the table using Model > Validation (MySQL) > Validate All

5) Notice the weirdness regarding the default values.

Suggested fix:
1) Use drop down options as allowable values for BOOLEAN, TIMESTAMP, and other appropriate columns under the Table Editor Default column field.  

2) Add documentation (Help Chapter 7.3.3) for BOOLEAN, TIMESTAMP, and other appropriate columns under the Default values section.
Create a sub-section just for default values under Help Chapter 7.3.3.  

3) Improve the validator's ability to make suggestions for default values.

4) Generally improve/debug the Table Editor Default Column section of the professional application.

Thank you for a bug report. Verified just as described.

At least the TIMESTAMP Stuff regarding CURRENT_TIMESTAMP ON UPDATE CURRENT TIMESTAMP is not fixed in 5.0.24 (OSS) as stated in this Bug:
http://bugs.mysql.com/bug.php?id=37071

:-(

Can you please attach a screenshot of the problem?

please double check if it's not only the column width that's obscuring the whole String (exspecially because it begins and ends with the same word (so it might look like theres only "CURRENT_TIMESTAMP" in the column)

I confirm that this bug is still present in 5.0.24.
How to duplicate:
Create model with a timestamp column.  Set its default value to DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.
Export->Forward engineer.  It creates a script like this:

CREATE  TABLE IF NOT EXISTS `mydb`.`timestamptest` (
  `timestamp` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )
ENGINE = InnoDB;

Notice how it adds "NULL".  The same thing happens when you use Copy SQL to Clipboard on that table: It adds "NULL".

Then import->reverse engineer the script you just created.  The importer will fail, saying there is a syntax error near "NULL".  Here is the error message:

Line 12: SQL syntax error near 'NULL ,
  `timestamp` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_'. Statement skipped.

Also, as the original bug mentions, the table editor only shows "CURRENT_TIMESTAMP" in the default field, even when you click in that field and scroll the cursor left and right.  The "ON UPDATE CURRENT_TIMESTAMP" does not appear at all in the default field when viewed in the table editor.

Never mind the very last paragraph of the last comment, about the ON UPDATE not appearing in the default field.  Johannes Taxacher was right about that: It is appearing.

But the export problem remains.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".