Bug #54174 Synchronize Model does much more updates than necessary
Submitted: 2 Jun 2010 8:41 Modified: 5 Jul 2010 2:33
Reporter: Markus Warg Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:5.2.20 OSS Beta OS:Windows
Assigned to: CPU Architecture:Any

[2 Jun 2010 8:41] Markus Warg
Description:
When synchronizing a model into mysql, workbench often does much more updates than necessary, i.e.

* drop index, create index
* change fields

Even if the model was synched, no changes were made, the next synch just does the same alterations again and again.

Target mysql is a standard Debian mysql server package, listed as mysql-server 5.0.51a-24+lenny3.

How to repeat:
Create a table in workbench, add some fields with some data types and variations (PK, AI, UN, NN, ...), add comments, use enums and sets.

Forward engineer to create model in mysql. Then synchronize some 2 or 3 times and look into the sql workbench wants to apply.

I.e. show create table from mysql:

CREATE TABLE `crm_checkin_idlog` (
  `id` bigint(19) unsigned NOT NULL auto_increment,
  `crm_system_config_branch_id` bigint(19) unsigned NOT NULL,
  `crm_checkin_id` varchar(45) collate utf8_unicode_ci NOT NULL COMMENT 'scanned id',
  `crm_checkin_device` bigint(19) unsigned NOT NULL COMMENT 'crm_system_config_hw.id of the scanner device',
  `crm_scanned` datetime default NULL COMMENT 'timestamp from when the id was scanned',
  `crm_id_type` enum('UNKNOWN','CUSTOMER','STAFF') collate utf8_unicode_ci NOT NULL default 'UNKNOWN' COMMENT 'unknown id or known staff id (has precedence over customer) or customer id',
  `synched_to_master` datetime default NULL COMMENT 'date/time when record was successfully sent to master db',
  PRIMARY KEY  (`id`,`crm_system_config_branch_id`)
) ENGINE=MyISAM AUTO_INCREMENT=62 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Workbench tries to appy this script:

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';

ALTER TABLE `crm_branch_writeback`.`crm_checkin_idlog` CHANGE COLUMN `crm_checkin_id` `crm_checkin_id` VARCHAR(45) NOT NULL COMMENT 'scanned id'  , CHANGE COLUMN `crm_id_type` `crm_id_type` ENUM('UNKNOWN','CUSTOMER','STAFF') NOT NULL DEFAULT 'UNKNOWN' COMMENT 'unknown id or known staff id (has precedence over customer) or customer id'  ;

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

Suggested fix:
Make model parser more robust, add more code to detect unexpected results. I suspect that the culprit here is the charset/collate stuff that is used (by the way, the table was initially created by workbench! See the difference between the create script that was created by workbench (below) and the show create table output (pasted above).

Here is the forward engineer script for the above db/table:

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 `crm_branch_writeback` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `crm_branch_writeback`.`crm_checkin_idlog`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `crm_branch_writeback`.`crm_checkin_idlog` ;
SHOW WARNINGS;

CREATE  TABLE IF NOT EXISTS `crm_branch_writeback`.`crm_checkin_idlog` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `crm_system_config_branch_id` BIGINT UNSIGNED NOT NULL ,
  `crm_checkin_id` VARCHAR(45) NOT NULL COMMENT 'scanned id' ,
  `crm_checkin_device` BIGINT UNSIGNED NOT NULL COMMENT 'crm_system_config_hw.id of the scanner device' ,
  `crm_scanned` DATETIME NULL COMMENT 'timestamp from when the id was scanned' ,
  `crm_id_type` ENUM('UNKNOWN','CUSTOMER','STAFF') NOT NULL DEFAULT 'UNKNOWN' COMMENT 'unknown id or known staff id (has precedence over customer) or customer id' ,
  `synched_to_master` DATETIME NULL COMMENT 'date/time when record was successfully sent to master db' ,
  PRIMARY KEY (`id`, `crm_system_config_branch_id`) )
ENGINE = MyISAM;
SHOW WARNINGS;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
[5 Jun 2010 2:33] MySQL Verification Team
Could you please try version 5.2.22.Thanks in advance.
[5 Jul 2010 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".