Bug #81183 datetime acuate to milliseconds, alter table can not show columns after it
Submitted: 23 Apr 2016 1:43 Modified: 25 Apr 2016 12:20
Reporter: 53064 zKF Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:6.3.6 OS:Windows
Assigned to: CPU Architecture:Any
Tags: datetime;

[23 Apr 2016 1:43] 53064 zKF
Description:
after datetime acuate to milliseconds, alter table can not show columns after it 

How to repeat:
first:
     1) create a datetime column without milliseconds precision
     2) then add other column after this datetime column

second:
     1) alter datetime column, acurate precision to millisencods

final:
    1) apply alter table function to this table, then the problem is come out
[25 Apr 2016 11:02] MySQL Verification Team
Please provide screenshot for second and final steps. Thanks.
[25 Apr 2016 11:38] 53064 zKF
bug's screenshot

Attachment: mysql-wb-bug.png (image/x-png, text), 34.50 KiB.

[25 Apr 2016 11:41] 53064 zKF
here post the test sql

CREATE TABLE `db_test`.`t_test` (
  `t_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `t_no` VARCHAR(50) NOT NULL,
  `t_create_time` DATETIME NOT NULL DEFAULT current_timestamp,
  `t_status` INT UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (`t_id`));

ALTER TABLE `db_test`.`t_test` 
CHANGE COLUMN `t_create_time` `t_create_time` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ;
[25 Apr 2016 12:20] MySQL Verification Team
Thank you for the feedback.
Observed this issue with WB 6.3.6 on Win7.

- Create schema - SQL Editor(WB)

DROP TABLE IF EXISTS `test`.`t_test`;
CREATE TABLE `test`.`t_test` (
  `t_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `t_no` VARCHAR(50) NOT NULL,
  `t_create_time` DATETIME NOT NULL DEFAULT current_timestamp,
  `t_status` INT UNSIGNED NOT NULL DEFAULT 1,
  PRIMARY KEY (`t_id`));
- Alter table
  ALTER TABLE `test`.`t_test` 
CHANGE COLUMN `t_create_time` `t_create_time` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ;

- Refresh Schema, right click on  t_test table and click ALTER TABLE..
- Observe that column t_status is missing
- from mysql CLI, confirm that all details are intact(only issue when checked through ALTER TABLE...)

mysql> DROP TABLE IF EXISTS `test`.`t_test`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `test`.`t_test` (
    ->   `t_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `t_no` VARCHAR(50) NOT NULL,
    ->   `t_create_time` DATETIME NOT NULL DEFAULT current_timestamp,
    ->   `t_status` INT UNSIGNED NOT NULL DEFAULT 1,
    ->   PRIMARY KEY (`t_id`));
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE `test`.`t_test`\G
*************************** 1. row ***************************
       Table: t_test
Create Table: CREATE TABLE `t_test` (
  `t_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `t_no` varchar(50) NOT NULL,
  `t_create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `t_status` int(10) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>   ALTER TABLE `test`.`t_test`
    -> CHANGE COLUMN `t_create_time` `t_create_time` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE `test`.`t_test`\G
*************************** 1. row ***************************
       Table: t_test
Create Table: CREATE TABLE `t_test` (
  `t_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `t_no` varchar(50) NOT NULL,
  `t_create_time` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `t_status` int(10) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE `test`.`t_test`\G
*************************** 1. row ***************************
       Table: t_test
Create Table: CREATE TABLE `t_test` (
  `t_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `t_no` varchar(50) NOT NULL,
  `t_create_time` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `t_status` int(10) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
[8 Sep 2017 15:38] Andy Schmidt
The storage of fractional seconds is an important new feature that requires to be supporting. REST APIs might rely on timestamping for deep caching, but also manage concurrent updates. A 1 second granularity is insufficient.

PHP's DateTime class has been improved in the recent year for consistent microsecond support, having the MySQL Workbench fail with this feature is used, is becoming critical for new application development!