Bug #78786 BINARY column inserts can't be forward engineered
Submitted: 9 Oct 2015 13:13 Modified: 9 Oct 2015 15:57
Reporter: Jonas Stendahl Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S2 (Serious)
Version:6.34.0 OS:Mac OS X
Assigned to: CPU Architecture:Any
Tags: BINARY, forward engineer

[9 Oct 2015 13:13] Jonas Stendahl
Description:
When forward engineering a model with inserts into table with BINARY columns those columns ends up with the value ...

Like this:

CREATE TABLE IF NOT EXISTS `example` (
  `id` BINARY(16) NOT NULL COMMENT '',
  `name` VARCHAR(255) NULL COMMENT '',
  PRIMARY KEY (`id`)  COMMENT '')
ENGINE = InnoDB;

INSERT INTO `example` (`id`, `name`) VALUES (..., 7200);

The type BLOB seems to be forward engineered just fine (as seen below if I change the name column from VARCHAR to BLOB), not sure what Workbench is doing differently with them.

CREATE TABLE IF NOT EXISTS `example` (
  `id` BINARY(16) NOT NULL COMMENT '',
  `name` BLOB NULL COMMENT '',
  PRIMARY KEY (`id`)  COMMENT '')
ENGINE = InnoDB;

INSERT INTO `example` (`id`, `name`) VALUES (..., 0x37323030);

I've tested if non-key BINARY columns can be forward engineered and they can't, so the issue is with BINARY itself and not the fact that it's a key.

How to repeat:
1. Create a table with a BINARY column.
2. Add an insert to that table.
3. Forward engineer a SQL script that include INSERT statements.
[9 Oct 2015 13:17] MySQL Verification Team
Thank you for the bug report. Are you able to attach a model project file as test case?. Thanks.
[9 Oct 2015 13:23] Jonas Stendahl
Example Model

Attachment: 78786.mwb (application/octet-stream, text), 5.34 KiB.

[9 Oct 2015 13:24] Jonas Stendahl
Example Forward Engineered SQL

Attachment: 78786.sql (application/octet-stream, text), 1.11 KiB.

[9 Oct 2015 13:25] Jonas Stendahl
I've added an example model file as well as the SQL it exports.
[9 Oct 2015 15:57] MySQL Verification Team
Thank you for the feedback. Verified on Windows too.

-- -----------------------------------------------------
-- Data for table `mydb`.`example`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`example` (`id`, `name`) VALUES (..., 'foobar');

COMMIT;

See https://bugs.mysql.com/bug.php?id=54680.