MySQL Bugs: #54639: MySQL Workbench does not preserve inserted data from 5.1 model

Bug #54639	MySQL Workbench does not preserve inserted data from 5.1 model
Submitted:	20 Jun 2010 2:12	Modified:	29 Jun 2010 10:50
Reporter:	Dan Cook	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S1 (Critical)
Version:	5.2.22 rc 2	OS:	Windows
Assigned to:	Sergei Tkachenko	CPU Architecture:	Any
Tags:	incompatibility, insert import

Description:
Models created with Workbench 5.1.18 and opened in 5.2.22 (rc2) do not preserve the "Inserted" data from the 5.1 model.

The only work around is re-entering all the data in the new model.  This is not an acceptable work around and thus this is a S1 bug.

How to repeat:
Open Workbench 5.1.18:
1) Create a model in Workbench 5.1.18 (I am using SE).  
2) Create a table with an arbitrary number of columns.
3) Insert data into the table using using the "Insert" tab in 5.1
4) Save the workspace. (Can export the table to verify the data is there - it is.)

Open Workbench 5.2.22 (rc2):
1) Open the 5.1 model 
2) Export the Model using Forward Engineer "SQL Create Script"... making sure to check the "Generate Insert..." check box.
3) Notice the Inserts are all inserting "NULL" data.
   Alternatively open the table editor and notice all rows are "Null" data.

Here is the output from a test.  The first is 5.1.18 (SE), the second is 5.2.22 (rc2).

<<< 5.1.18 (SE) >>
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';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb`;

-- -----------------------------------------------------
-- Table `mydb`.`table1`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`table1` (
  `idtable1` INT NOT NULL ,
  `col1` VARCHAR(45) NOT NULL ,
  `col2` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`idtable1`) )
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Data for table `mydb`.`table1`
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
USE `mydb`;
INSERT INTO `table1` (`idtable1`, `col1`, `col2`) VALUES (1, 'hello', 100);
INSERT INTO `table1` (`idtable1`, `col1`, `col2`) VALUES (2, 'world', 200);

COMMIT;

<<< 5.2.22 (rc2) >>>
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';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`table1`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`table1` (
  `idtable1` INT NOT NULL ,
  `col1` VARCHAR(45) NOT NULL ,
  `col2` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`idtable1`) )
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Data for table `mydb`.`table1`
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
USE `mydb`;
INSERT INTO `mydb`.`table1` (`idtable1`, `col1`, `col2`) VALUES ('1', NULL, NULL);
INSERT INTO `mydb`.`table1` (`idtable1`, `col1`, `col2`) VALUES ('2', NULL, NULL);

COMMIT;

Suggested fix:
Preserve the inserted data.

Thank you for the bug report. Could you please try version 5.2.24. Thanks in advance.

I have reproduced this issue with the latest RC candidate 5.2.24 (rc) also.

<< Output from 5.2.24 (rc) >>
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';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`table1`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`table1` (
  `id` INT NOT NULL ,
  `col1` VARCHAR(45) NOT NULL ,
  `col2` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Data for table `mydb`.`table1`
-- -----------------------------------------------------
SET AUTOCOMMIT=0;
USE `mydb`;
INSERT INTO `mydb`.`table1` (`id`, `col1`, `col2`) VALUES ('1', NULL, '100');
INSERT INTO `mydb`.`table1` (`id`, `col1`, `col2`) VALUES ('2', NULL, '200');

COMMIT;

Notice that in 5.2.24 (rc) the VARCHAR columns are now NULL.

Test 5.1 Model

Attachment: test51.mwb (application/octet-stream, text), 3.85 KiB.

Many thanks for writing a bug report. I was able to reproduce this.

Fixed.

Fix confirmed in repository.

An entry has been added to the 5.2.25 changelog:

Inserts data did not appear to be preserved between MySQL Workbench versions.

A model was created with MySQL Workbench 5.1.18 and data inserted via the Insert tab. If the model was then saved and loaded into MySQL Workbench 5.2.22, and the model forward engineered to a SQL script, the generated script contained INSERT statements that inserted NULL instead of the data originally typed in.