MySQL Bugs: #12886: generation create table with foreign key

Bug #12886	generation create table with foreign key
Submitted:	30 Aug 2005 16:11	Modified:	26 Oct 2005 21:54
Reporter:	bocquet denis	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S3 (Non-critical)
Version:	1.0.14	OS:
Assigned to:		CPU Architecture:	Any

Description:
CREATE TABLE `infos_bloc` (
  `infb_code_partenaire` INT(2) NOT NULL,
  `infb_reference` VARCHAR(30) NOT NULL,
  `infb_numero` INT(3) NOT NULL,
  `infb_type` VARCHAR(30) NULL,
  `infb_actif` INT(1) NULL,
  PRIMARY KEY (`infb_code_partenaire`, `infb_reference`, `infb_numero`),
  CONSTRAINT `fk_infos_bl_ref_65296_infos` FOREIGN KEY `fk_infos_bl_ref_65296_infos` (`infb_code_partenaire`)
    REFERENCES `infos` (`inf_code_partenaire`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,

)
ENGINE = INNODB;
An error occured while executing the SQL statement.
Can't create table './devu/infos_bloc.frm' (errno: 150)

How to repeat:
use the sql command on description

Suggested fix:
if you remove the comma at the end of "ON UPDATE NO ACTION,"
it's ok

DDL of oracle table (from oem)
CREATE TABLE "DEV"."INFOS_BLOC" ("INFB_CODE_PARTENAIRE" NUMBER(2)
    NOT NULL, "INFB_REFERENCE" VARCHAR2(30 byte) NOT NULL, 
    "INFB_NUMERO" NUMBER(3) NOT NULL, 
    "INFB_TYPE" VARCHAR2(30  byte), 
    "INFB_ACTIF" NUMBER(1), 
    CONSTRAINT "FK_INFOS_BL_REF_65296_INFOS" FOREIGN 
    KEY("INFB_CODE_PARTENAIRE", "INFB_REFERENCE") 
    REFERENCES "DEV"."INFOS"("INF_CODE_PARTENAIRE", 
    "INF_REFERENCE"), 
    CONSTRAINT "PK_INFOS_BLOC" PRIMARY KEY("INFB_CODE_PARTENAIRE",
    "INFB_REFERENCE", "INFB_NUMERO") 
    USING INDEX  
    TABLESPACE "DATA1" 
    STORAGE ( INITIAL 10240K NEXT 0K MINEXTENTS 1 MAXEXTENTS 
    2147483645 PCTINCREASE 0) PCTFREE 10 INITRANS 2 MAXTRANS 255)
    LOGGING

Thanks for your bug report.  This indeed happens in 1.0.15

Jorge, can you test if this is still an issue in 1.0.18 and if so, please paste the complete CREATE TABLE script with both master and detail tables.

Thanks,
Mike

I was unable to reproduce this problem using 10.0.20

i haven't downloaded the 1.018 release so i don't try it.
i tested the 1.0.20rc and it's OK

The severity may be "non-critical" but this is affecting our decision to migrate some of our apps to mySQL from Oracle.  If we have to chase after hundreds of stupid little bugs in the migration, we'll be generating huge labor costs for the licensing cost savings, making it a wash, so, why bother?