MySQL Bugs: #45110: Inconsistent results if alter table engine then forward engineer alter script

Bug #45110	Inconsistent results if alter table engine then forward engineer alter script
Submitted:	26 May 2009 22:59	Modified:	9 Feb 2010 16:20
Reporter:	John Pancoast	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.2.1, 5.1.12	OS:	Linux (Ubuntu 9.04, Mac OS X 10.5.6)
Assigned to:	Alexander Musienko	CPU Architecture:	Any
Tags:	alter, engine, export, forward, forward engineer, import, reverse, reverse engineer

Description:
If you reverse engineer a script (import) which has tables with no engine defined, change the engine in workbench, forward engineer an alter script... the alter syntax to change the engine for the table doesn't get output. 

If however, you're import script's tables do have an engine defined and you change a table's engine in workbench, then forward engineer an alter script, the alter syntax is there as expected.

How to repeat:
1. import -> reverse engineer MySQL create script
2. Import a script that contains the following sql
   note: only the second table has engine defined
------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb_test`;
USE `mydb_test`;

-- this table doesn't specify ENGINE
CREATE  TABLE IF NOT EXISTS `mydb_test`.`table_noengine` (
  `idtable_noengine` INT NOT NULL ,
  PRIMARY KEY (`idtable_noengine`) );

-- this table uses MyISAM engine
CREATE  TABLE IF NOT EXISTS `mydb_test`.`table_MyISAM` (
  `idtable_MyISAM` INT NOT NULL ,
  PRIMARY KEY (`idtable_MyISAM`) )
ENGINE = MyISAM;
------------------------------------------

3. In workbench, change the engine of both tables to InnoDB
4. export -> Forward Engineer SQL ALTER script
5. either export to a file or leave 'output file' blank to see the output.
6. output is the following

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 `mydb_test`.`table_MyISAM` ENGINE = InnoDB ;

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

#########################################################
Only the table 'table_MyISAM' with the engine specified
to begin with got an alter statement. 

Suggested fix:
Perhaps when importing a "default engine" should be set on tables with no engine defined.

Thank you for the bug report. Verified just as described with 5.1.12 on Mac OS X.

When importing tables from script which don't have a engine specified, WB will set the tables to "default engine". This "default engine" can be set in preferences -> MySQL. In case this preference is set to "innoDB" (which is the default for that specific setting), changing the table to InnoDB won't generate ALTER code for changing the engine of affected tables.

An entry has been added to the 5.2.16 changelog:

If a schema that contained tables with no engine defined was reverse engineered, and then the engine type was changed in MySQL Workbench, then when the model was exported the ALTER script did not contain code to change the engine of the table.