MySQL Bugs: #101981: Reverse Engineer/Synchronize fails (silently) for external generated columns

Bug #101981	Reverse Engineer/Synchronize fails (silently) for external generated columns
Submitted:	12 Dec 2020 15:00	Modified:	12 Dec 2020 21:40
Reporter:	Jan Hieronymus	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S1 (Critical)
Version:	8.0.22	OS:	Windows (10 v2004)
Assigned to:		CPU Architecture:	x86

Description:
The problem appears with generated columns on a database server to compare with.

Using Database -> Reverse Engineer...
you will get an error message like:

>>>
ERROR: (3, 23) "AS" is not valid at this position for this server version, expecting ASCII, AUTO_INCREMENT, BINARY, BYTE, CHARSET, CHAR, ...
ERROR: (3, 43) ")" is not valid at this position, expecting BIGINT, BINARY, BIT, BLOB, BOOLEAN, BOOL, ...
ERROR: (3, 44) ")" is not valid at this position, expecting EOF, ';'
>>>

My database server is "10.1.21-MariaDB - mariadb.org binary distribution" from an XAMPP installation; and it does support generated columns.

Using Database -> Synchronize Model:

If there is no table in the model yet, nothing will happen. Especially, you will not see that there is a table on the database server.

If there is already a table of the same name in the model, you will see no changes on that table, regardless which/how many columns have been changed in this table on the server.

Especially there is no error message indicating that something is going wrong.

Side note: The forward engineering of generated columns works well.

How to repeat:
Create a the following environment on a server

CREATE DATABASE IF NOT EXISTS `bugs` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `bugs`;

CREATE TABLE `some_table` (
  `id` int(11) NOT NULL COMMENT 'col_comment-bla',
  `generated_col` text AS (concat('ID: ',id)) VIRTUAL COMMENT 'gen_col_comment-bla'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='table_comment-blabla';

Then use reverse engineering or synchronize features to see the errors appear.

Suggested fix:
make the importer able to understand generated columns

Thank you for the bug report. The server MariaDB isn't supported by this tool which is for MySQL Oracle server.

Similar situation using to reported bug but on MySQL:

Data Workbench v.8.0.31 build 2235049 CE (64 bits)
MySQL Server v.8.0.31 (MySQL Community Server-GPL)
Windows 10 Pro v.22H2 (OS build: 19045.2728) 

-----------------------------------
DROP DATABASE IF EXISTS travel;
 
CREATE DATABASE travel;
 
USE travel;
 
CREATE TABLE manufacturers (
  manufacturer_id INT UNSIGNED NOT NULL,
  manufacturer VARCHAR(50) NOT NULL,
  create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_update TIMESTAMP NOT NULL 
    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (manufacturer_id) );
 
CREATE TABLE airplanes (
  plane_id INT UNSIGNED NOT NULL,
  plane VARCHAR(50) NOT NULL,
  manufacturer_id INT UNSIGNED NOT NULL,
  engine_type VARCHAR(50) NOT NULL,
  engine_count TINYINT NOT NULL,
  max_weight MEDIUMINT UNSIGNED NOT NULL,
  wingspan DECIMAL(5,2) NOT NULL,
  plane_length DECIMAL(5,2) NOT NULL,
  parking_area INT GENERATED ALWAYS AS ((wingspan * plane_length)) STORED,
  icao_code CHAR(4) NOT NULL,
  create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_update TIMESTAMP NOT NULL 
    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (plane_id),
  CONSTRAINT fk_manufacturer_id FOREIGN KEY (manufacturer_id) 
    REFERENCES manufacturers (manufacturer_id) );
----------------------------

Data Workbench:

Reverse Engineer Database

ERROR: (3, 21) "GENERATED" is not valid at this position, expecting ')'

[Note: believe it is on column 'parking area' in table 'airplanes'.]

[Note: attempted to change INT to INT(11) for 'parking area' and received following warning:
1 warning(s): 1681 Integer display width is deprecated and will be removed in a future release.
and table is created with just INT.]