MySQL Bugs: #54680: Model Inserts of Binary Data Not Forward Engineered

Bug #54680	Model Inserts of Binary Data Not Forward Engineered
Submitted:	21 Jun 2010 22:29	Modified:	28 Jul 2010 16:33
Reporter:	Michael LaBrot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S2 (Serious)
Version:	5.2.24	OS:	Windows (XP Pro)
Assigned to:	Sergei Tkachenko	CPU Architecture:	Any

Description:
Now that I can get binary data into the model inserts (see Bug #54156) I find that this data does not get forward engineered into my database.

How to repeat:
1.) In the Inserts tab in a model, add binary data to a table with a VARBINARY column.
2.) Forward engineer the model.
3.) The binary data does not appear in the SQL Script nor does it appear in the forward engineered database.

Please paste an example of binary data you tried to insert.

You can use http://wb.mysql.com/favicon.ico as your binary file if you like.

To be specific, in the model Inserts tab, right-click on a VARBINARY field and choose "Load Value From File". Click the "Apply changes to data" button. Forward engineer the model to a database. The forward engineering script inserts a value of NULL into the field instead of the binary data.

Verified just as described. This is what WB generated:

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 ,
  `data` VARBINARY(64000) 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`) VALUES ('1');
INSERT INTO `mydb`.`table1` (`idtable1`) VALUES ('2');

COMMIT;

Although I've loaded file to the `data` column in the second row.

Still a problem in 5.2.25.

fix confirmed in repository

An entry has been added to the 5.2.26 changelog:

When a model was forward engineered to a database, insert statements associated with binary data were not present in the generated code. As a result, binary data was not forwarded to the database.

For me, in 5.2.47 Inserts with binary data are still not handled correctly in Forward Engineering: The binary data gets replaced by '...'.

Steps to reproduce:
> Create New EER model
> Add table, name `table1`, columns `idtable1` and `table1col`

CREATE  TABLE IF NOT EXISTS `mydb`.`table1` (
  `idtable1` INT NOT NULL ,
  `table1col` BINARY(16) NULL ,
  PRIMARY KEY (`idtable1`) )
ENGINE = InnoDB;

> On the Inserts tab
>> put '1' as idtable1
>> on table1col: right-click, Open Value in Editor and put 'aaaaaaaaaaaaaaaa', Apply, Close
>> Apply changes to data

> Database > Forward Engineer
> Next > Select "Generate INSERT Statements for Tables" > Next > Next

Here you can see that binary data is shown as '...'

INSERT INTO `mydb`.`table1` (`idtable1`, `table1col`) VALUES (1, ...);

This is not only in the preview but also in the code that is actually executed.

Issue persists. Is this working for anyone?

Just checked with latest 6.0.9.11421 build 1170. Issue persists.

I'm having the same issue as Risman Hidayat. I've created a new bug for it (#78786) since it differs from this one.