MySQL Bugs: #80211: Workbench wont create stored generated column / edit table w/that as primary key

Bug #80211	Workbench wont create stored generated column / edit table w/that as primary key
Submitted:	31 Jan 2016 23:32	Modified:	26 Mar 2018 21:04
Reporter:	Mark Steiglitz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	6.3.6	OS:	MacOS
Assigned to:		CPU Architecture:	Any
Tags:	stored generated column primary key, table structure editing

Description:
Neither using the EER diagram nor using the MySQL model is it possible to set the STORED attribute on a generated column.  Therefore forward engineering cannot be used to create such a generated column, and stored columns cannot be used as primary keys.  (Connection to a live database does allow such settings.)

How to repeat:
Try to create a generated column as a component of the primary key for a table.

Suggested fix:
Add relevant user-interface elements to the modeling component of MySQL Workbench.

Hi Mark,

Thank you for the report.
Imho defining a virtual generated column as primary key is not supported for generated columns. As of MySQL 5.7.8, InnoDB supports secondary indexes on virtual generated columns. Other index types are not supported. Could you please post exact error message or screenshot of the error that you are seeing?

From CLI:
mysql> CREATE TABLE test(id1 int not null,id INT GENERATED ALWAYS AS (id1*2) primary key)ENGINE = innodb;
ERROR 3106 (HY000): 'Defining a virtual generated column as primary key' is not supported for generated columns.
mysql>

Thanks,
Umesh

Workbench GUI for modeling, lacking option for STORED generated column

Attachment: ss1.png (image/png, text), 178.23 KiB.

Workbench editing live database, showing both columns in the sample table

Attachment: ss2.png (image/png, text), 135.40 KiB.

Workbench editing live database, missing one column in the editing mode, and displaying the stored generated column incorrectly

Attachment: ss3.png (image/png, text), 151.50 KiB.

As requested, I have combined bug #80212 into this report.

Although MySQL does not support a virtual generated columns as a primary key, it does support a STORED generated column as a primary key.

I have put your example as a MySQL model in Workbench, and Forward Engineered it, and got the same error; however, I then manually edited the SQL code to include the STORED keyword:

CREATE TABLE IF NOT EXISTS `mydb`.`test` (
  `id` INT GENERATED ALWAYS AS (id1*2) STORED,
  `id1` INT NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

and it worked.  But one of the problems as there is no place in the Workbench modeling GUI to put the STORED option, as seen in the attached screenshot (ss1.png).

Furthermore, Workbench won't edit the resulting table properly in the live database.

It does display the columns (2 columns) of the table properly, as indicated in the attached screenshot (ss2.png).

However, in the editing mode, it displays the STORED GENERATED column without the PK and NN flags checked, and it doesn't display any other columns following that column (so in this example, one column is missing), as shown in the attached screenshot (ss3.png).

Thank you for the feedback.
Verified as described.

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

A column with the STORED keyword could not be generated by using the EER
Diagram or MySQL Model tabs on macOS hosts. This fix updates all of the
relevant tabs to include the missing STORED option for generating a column
or for editing generated columns.

Thank you for the bug report.