MySQL Bugs: #78153: Workbench SQL Editor DATETIME Millisecond Precision Inconsistent.

Bug #78153	Workbench SQL Editor DATETIME Millisecond Precision Inconsistent.
Submitted:	20 Aug 2015 14:13	Modified:	17 May 2016 23:10
Reporter:	Ben Ernest-Jones	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	6.3.4	OS:	MacOS
Assigned to:		CPU Architecture:	Any
Tags:	DATETIME milliseconds

Description:
I've seen the bug reports on Workbench handling DATETIME millisecond precision in the modeler, and that appears to have been fixed as long as you configure your modeler settings to use MySQL 5.6 or above. I have not, however, been able to find any issues related to the same problem when using the SQL Editor, which does not appear to be affected by the modeler version number. When editing tables through the grid-view (as opposed to typing SQL directly into the editor), you can create millisecond precision DATETIME fields (e.g. DATETIME(3)) but it does not reload the DATETIME columns with the saved precision, but instead simply reloads those column types as DATETIME without any precision value. This normally does not cause a problem if you make future changes to the table that do not involve the DATETIME column(s), but if you do change a DATETIME column (such as renaming the column name, or changing it's order, etc), the generated SQL script will not use the original precision unless you do the "workaround" of explicitly remembering to add the precision back into the type (ie changing DATETIME back to DATETIME(3)). This can be very dangerous if it is somehow missed when updating a table then rolling the changes out to production, as the update will cause all millisecond precision DATETIME data to be unrecoverably lost (unless of course reloading from a previous backup).

How to repeat:
1) Open a schema in MySQL Workbench SQL Editor.
2) Create a new table
3) In the grid-view of the table editor, add an INT primary key that is auto-incremented just for good measure.
3) Now add a field named TEST with a type of DATETIME(3).
4) Apply the changes to actually create the table. Close the table editor tab.
6) Query the table rows so that you can manually enter a new row. Enter a new row with "2012-12-15 15:55:25.345" in the TEST column. Apply changes.
7) Reopen the table editor for the new table, and see that the TEST column type has now reverted to DATETIME.
8) Without fixing the type, change the TEST column name to TEST1. Apply changes.
9) Re-query the table rows and notice that the new row you previously entered now has the value "2012-12-15 15:55:25" in the TEST1 column, with the milliseconds missing.

Suggested fix:
Have the MySQL Workbench SQL Table Editor recognize and load the DATETIME and TIMESTAMP precisions correctly.

2 WB instances showing the bug

Attachment: sql_editor_and_datetime(x).png (image/png, text), 339.21 KiB.

Thank you for the bug report.

This issue is still there! 
Creating table script with DateTime(3) works for me.

But it still shows DateTime only if you ALTER table through designer but if you get script of table it's showing DateTime(3).

Posted by developer:
 
Fixed as of the upcoming MySQL Workbench 6.3.7 release, and here's the changelog entry:

Adding a DATETIME with precision (such as DATETIME(3)) from the Table
Editor would eventually get reverted to DATETIME (without precision).

Thank you for the bug report.

This appears to be broken again in 8.0.

Confirmed rolling back to 6.3.10 and it possible to create DATETIME(3) columns again.  OS X.

Are you seriously? For 3 years not to fix?

Please do not add any content to closed bugs. They are processed and will not be checked anymore. Instead open a new bug report.

The issue you see is not a regression. The original bug was indeed fixed, but only for older server versions (< 5.6). You can fix the issue you see by yourself pretty easily. Locate the file mysql_rdbms_info.xml in your Workbench installation (Mac users: look in the application bundle). That file contains an entry "com.mysql.rdbms.mysql.datatype.datetime_f", which has a sub element named "numericPrecision". This should be 0 for DATETIME. Change that, save + restart Workbench. The DATETIME field should now behave as intended, also for newer MySQL server versions.

This fix will be publicly available in a forthcoming Workbench release.