MySQL Bugs: #70125: Timestamp and datetime with microseconds still not supported in MWB 6.0

Bug #70125	Timestamp and datetime with microseconds still not supported in MWB 6.0
Submitted:	22 Aug 2013 19:50	Modified:	26 Sep 2013 21:48
Reporter:	Karl Nicoletti	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	6.0.6	OS:	Windows (Windows 7)
Assigned to:	Alfredo Kojima	CPU Architecture:	Any
Tags:	DATETIME(6), microseconds, TIMESTAMP(6)

Description:
DATETIME(6), TIMESTAMP(6), CURRENT_TIMESTAMP(6), etc. still produce errors when I attempt to use them in the MWB modeler.

I am filing this bug as essentially a duplicate of Bug #68374 which was filed against MWB 5.2.

While in the query builder window I am allowed to execute the following to create the table:

  create table kk
  (
    pk TIMESTAMP(6) default CURRENT_TIMESTAMP(6)
  )
  ENGINE=InnoDB;

However, the '(6)' is underlined in red and the red "X" icon shows up in the line number column.

How to repeat:
1) In the modeler, create a table.
2) Create a column named "foo".
3) Manually enter the datatype as DATETIME(6)
4) Press the TAB or RETURN key.
5) Get the error dialog:

 "Could not set new data type

  The given data type

  DATETIME(6)

  contains errors and cannot be accepted. The previous
  value is kept instead."

Can we PLEASE get some action on this? My work around is that I have to forward engineer the model to create the schema *.sql, then run a sed script to change the definitions from DATETIME to DATETIME(6). After that I can check in the schema *.sql file to version control. Clearly, the model and schema file are now out of sync.

You must set the target mysql version of your model in Model -> Model Options to 5.6

Well, that works if I build a new model from scratch in MWB 6.0.6. HOWEVER, the model I am working with was generated way back in MWB 5.2.12. And it refuses to allow the DATETIME(6) entry in the datatype - even if I set the target version to 5.6, "Save As" under a different name, shut down  MWB, and restart MWB only opening the new copy of my database.
Any ideas on how to regenerate a fresh model file without reverse engineering the schema output and having to reconstruct all the diagrams?
I'm attaching the model file for private use by the development staff.

I can repeat the issue with existing models. We'll fix it in an upcoming release. As a workaround, you can upgrade your models with the following script. 

Copy/paste the code below to upgrade_model.py and execute it from Scripting -> Run Workbench Script File... after opening the model you want to upgrade. Make a backup of your model first, just in case.

full=grt.root.wb.rdbmsMgmt.rdbms[0].simpleDatatypes
model=grt.root.wb.doc.physicalModels[0].catalog.simpleDatatypes
all_types=[x.__id__ for x in model]
for d in full:
    if d.__id__ not in all_types:
        print "merging", d.__id__
        model.append(d)

Thanks and appreciation for the upgrade script but it still didn't fix the model. Here's what I've done:

I. CONVERT FROM MODEL HOME SCREEN (kkn_01.mwb)
 1) Open the model.
 2) Close all diagrams.
 3) Run the script.
 4) Open the "MDV" diagram, double-click on the "mdv_policies" table.
 5) Change the "last_modified" column to DATETIME(6). Press the TAB key.
 6) Get the "Could not set new data type" error (see attached BUG#70124_ErrDialog.doc).

II. CONVERT FROM DIAGRAM SCREEN (kkn_02.mwb)
 1) Open the model.
 2) Close all diagrams. Open the "STA DB OVERVIEW" diagram
 3) Run the script.
 4) Double-click on the "cap_summmaries" table.
 5) Change the "created" column to TIMESTAMP(6). Press the TAB key.
 6) Get the "Could not set new data type" error (see attached BUG#70124_ErrDialog.doc).

YIPPEE! I figured it out. Simply setting the model's target MySQL version from the Model Home->Preferences... screen does NOT get propagated throughout the model's diagrams. You have to set the target version from within a diagram window.

Here is how to enable old MWB models to accept the datatypes for microseconds.

After opening your old MWB 6.0.6 model and running the conversion script noted above, you have to change the model attributes FROM WITHIN A DIAGRAM SCREEN!

1) Open any diagram.
2) Select Model -> Model Options.
3) At the lower left corner UNCHECK the “Use default from global settings” box.
4) Click on the “Model: MySQL”  tab.
5) Change the “Target MySQL Version:” to "5.6", and then click the OK button in the lower right hand corner of the dialog.
6) Now the DATETIME and TIMESTAMP columns can be set to DATETIME(6) and TIMESTAMP(6)!
7) This is now set for the entire model and all of its diagrams.

NOTE: "Model Home->Edit->Preferences...->Model:MySQL 'Default target MySQL Version'" does not seem to get propagated throughout the model's diagram views. Is this the real bug here?

Direction on how to (re)set the model to accept microseconds datatypes.

Attachment: HowToEnableMicroseconds.doc (application/msword, text), 80.00 KiB.

Fixed as of MySQL Workbench 6.0.7, and here's the changelog entry:

Changing "Target MySQL Version" to 5.6 on an older Model will now allow
configurable date related values, such as DATETIME(6), TIMESTAMP(6), and
CURRENT_TIMESTAMP(6).

Thank you for the bug report.

I had the same problem on v.6 of Work Bench

so i decided to have a small script to run every time i want to update a column with Time stamp.

ALTER TABLE users
ADD Updated TIMESTAMP(6) default CURRENT TIMESTAMP(6)

just Replace 'Users' as Table name and 'Updated' as column to be added

hope it helps