Bug #84078 Created table in MySQL Workbech with generated columns, is not quite editable

• Submitted: 6 Dec 2016 13:28
• Modified: 30 May 2018 15:22
• Reporter: Mike Klemin
• Status: Closed
• Category: MySQL Workbench
• Severity: S3 (Non-critical)
• Version: 6.3.8
• OS: Fedora
• CPU Architecture: Any
• Tags: MySQL, workbench

[6 Dec 2016 13:28] Mike Klemin

Description:
The Server version is 5.7.16

I just creating table like this in MySQL WorkBench by executing SQL query (but it is also possible to do in WorkBench create table GUI with same result):

CREATE TABLE `raw_data2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rw` json DEFAULT NULL,
  `Invoice` decimal(10,2) DEFAULT '0.00',
  `Curtailment` decimal(10,2) DEFAULT '0.00',
  `ModifiedAfter` timestamp NULL DEFAULT NULL,
  `AccountNumber` varchar(45) GENERATED ALWAYS AS (json_unquote(json_extract(`rw`,'$.AccountNumber'))) VIRTUAL,
  `TransactionDate` timestamp GENERATED ALWAYS AS (json_unquote(json_extract(`rw`,'$.TransactionDate'))) VIRTUAL,
  `Description` varchar(128) GENERATED ALWAYS AS (json_unquote(json_extract(`rw`,'$.Description'))) VIRTUAL,
  `ControlNumber` varchar(15) GENERATED ALWAYS AS (json_unquote(json_extract(`rw`,'$.ControlNumber'))) VIRTUAL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now if you navigate to newly created table in MySQL WorkBench, it will not show the column Description (however actually it is there), and it will show that column TransactionDate is set to NN (which is never did)

Now if I "ALTER TABLE raw_data2 DROP COLUMN Description;" It will says error because DEFAULT VALUE for TransactionDate is not correct. 

How to repeat:
The repeat procedure described in Description.

Suggested fix:
Sorry can not make suggestion...

[6 Dec 2016 15:00] MySQL Verification Team

Showing description column

Attachment: virtual.png (image/png, text), 505.75 KiB.

[6 Dec 2016 15:03] MySQL Verification Team

Thank you for the bug report. I couldn't repeat on Fedora:

[miguel@f24 ~]$ uname -a
Linux f24.vb 4.6.3-300.fc24.x86_64 #1 SMP Fri Jun 24 20:52:41 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
[miguel@f24 ~]$ 

See prior attached picture. Thanks.

[6 Dec 2016 15:09] Mike Klemin

Alter Table

Attachment: Screenshot from 2016-12-06 18-06-51.png (image/png, text), 34.47 KiB.

[6 Dec 2016 15:11] Mike Klemin

You have to right click in MySQL WorkBench on the table, then click "alter table", and then there will be what I just attached, attaching files is tad confusing here.

[6 Dec 2016 15:13] MySQL Verification Team

Alter table description column missed

Attachment: viertual_alter_table.png (image/png, text), 297.83 KiB.

[6 Dec 2016 15:14] MySQL Verification Team

Thank you for the feedback.

[31 Jan 2018 2:00] Neil Dong

Hi guys , this issue reported at a yearago, it seems still not fixed. Any further?

[30 May 2018 15:22] Christine Cole

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

The table editor did not display generated columns within a user-defined
table and the Alter Table operation returned an error message when an
attempt was made to modify any of the nonvisible columns.

Thank you for the bug report.