Description:
The results of "Synchronize with Any Source" will present wrong results when the table contains a JSON field and the source and/or destination is a script file .sql
Two scenario are presented.
Scenario 1. When the source is the "Model Schemata" and the destination is a script file, the synchronization result is a "create table" instead the alter query
Scenario 2. When the source is the a script file and the destination is either a script file, the synchronization result is a empty instead the alter query
It has been verified in
- Windows 10 64bit, Workbench version 8.0.23
- Oracle Linux 7 64bit, Workbenc version 8.0.22
How to repeat:
Scenario 1.
- File > New Model
- File > Import > Reverse Engineer SQL Script. Select "schema1.sql" file and place objects on diagram
- Amend something on the table in the diagram e.g. change the "name" field from VARCHAR(100) to VARCHAR(150)
- Database > Synchronize with Any Source
- Select "Model Schemata" as source and original "schema1.sql" script file as destination
The result of the next steps is the whole "CREATE TABLE..." statement.
The expected result is the "ALTER" statement of the changed field VARCHAR type
Scenario 2.
- File > New Model
- Database > Synchronize with Any Source
- Select "schema1.sql" script file as source and "schema1.sql" script file as destination (the 2 files differences only for the name of a varchar field, both have a JSON field)
The result of the next steps is empty.
The expected result is the right statement to change the field name
-- --------- TEST CASE SQL -----------
-- schema1.sql
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
-- ----------------------------------------------------------------------------
-- Schema main
-- ----------------------------------------------------------------------------
DROP SCHEMA IF EXISTS `main` ;
CREATE SCHEMA IF NOT EXISTS `main` DEFAULT CHARACTER SET utf8 ;
-- ----------------------------------------------------------------------------
-- Table main.dummytest
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `main`.`dummytest` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NULL DEFAULT NULL,
`test` JSON NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `name_UNIQUE` (`name` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
-- --------- TEST CASE SQL -----------
-- schema2.sql
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
-- ----------------------------------------------------------------------------
-- Schema main
-- ----------------------------------------------------------------------------
DROP SCHEMA IF EXISTS `main` ;
CREATE SCHEMA IF NOT EXISTS `main` DEFAULT CHARACTER SET utf8 ;
-- ----------------------------------------------------------------------------
-- Table main.dummytest
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `main`.`dummytest` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NULL DEFAULT NULL,
`test2` JSON NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `name_UNIQUE` (`name` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
