Bug #28484 Improper DEFAULT values for BLOB fields when mapping from Oracle
Submitted: 17 May 2007 4:06 Modified: 6 Feb 2009 12:38
Reporter: Raymond DeRoo Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Migration Toolkit Severity:S3 (Non-critical)
Version:1.1.12 OS:Any
Assigned to: CPU Architecture:Any

[17 May 2007 4:06] Raymond DeRoo 
Description:
The EMPTY_BLOB keyword from Oracle gets mapped to, but is not supported by MySQL.

How to repeat:
Oracle Table Description

>>> 

Name Null? Type

----------------------------------------- -------- ----------------------------

CONTENT_ID NOT NULL VARCHAR2(256)
ANNOTATION NOT NULL VARCHAR2(256)
CONTENT_TYPE NOT NULL VARCHAR2(256)
DEFAULT_MEDIA_TYPE NUMBER(2)
CONTENT BLOB

>>> 

Generated MySQL Create

>>> 

DROP TABLE IF EXISTS `agwms`.`ms_fixmedia_tbl`;
CREATE TABLE `agwms`.`ms_fixmedia_tbl` (
`content_id` VARCHAR(256) BINARY NOT NULL,
`annotation` VARCHAR(256) BINARY NOT NULL,
`content_type` VARCHAR(256) BINARY NOT NULL,
`default_media_type` INT(2) NULL,
`content` LONGBLOB NULL DEFAULT EMPTY_BLOB(),
PRIMARY KEY (`content_id`),
INDEX `ms_fixmedia_annotation_idx` (`annotation`(256))
) ENGINE = INNODB;

Suggested fix:
Remove the EMPTY_BLOB() function as a DEFAULT.
[6 Aug 2007 11:43] Valeriy Kravchuk 
Thank you for a bug report. Verified just as described while migrating from Oracle XE to MySQL 5.0.44. The following column definition was generated:

...
  `cb` LONGBLOB NULL DEFAULT empty_blob()
...

This is a bug.
[20 Aug 2007 16:25] Valeriy Kravchuk 
Bug #30507 was marked as a duplicate of this one.
[6 Feb 2009 12:38] Susanne Ebrecht 
This is fixed in MySQL Migration Toolkit 1.1.16.