Bug #12721 Oracle CLOB data not converted
Submitted: 22 Aug 2005 11:09 Modified: 22 Aug 2005 13:09
Reporter: Sadao Hiratsuka (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Migration Toolkit Severity:S1 (Critical)
Version:1.0.13 OS:Microsoft Windows (Windows XP)
Assigned to: Michael G. Zinner CPU Architecture:Any

[22 Aug 2005 11:09] Sadao Hiratsuka
Description:
MySQL Migration Toolkit can't convert Oracle CLOB data.

CLOB data are not converted,
although BLOB data are converted correctly.

How to repeat:
-- Table definition in Oracle
create table test005_clob (
col1 number,
col2 clob
)
/

-- Generated creates.sql
CREATE TABLE `mi`.`test005_clob` (
`col1` INT NULL,
`col2` VARCHAR(4000) NULL
)
ENGINE = INNODB;

-- Generated inserts.sql
INSERT INTO `mi`.`test005_clob`(`col1`, `col2`)
VALUES (1, "");

Suggested fix:
1) Col2's datatype should be longtext.
2) It is necessary to store the CLOB value in the INSERT statement.

It seems that the CLOB process is not implemented
in doDataBulkTransferTableToMysql method
of com.mysql.grt.modules.MigrationGeneric class.
[22 Aug 2005 12:14] Michael G. Zinner
I have uploaded a fix for this issue to 
 ftp://ftp.mysql.com/pub/mysql/download/mysql-migration-toolkit-patch3-1.0.13-rc-win32.zip

Please download the file and extract it into [INSTALLDIR]\java\com\mysql\grt\modules overwriting the existing files there.

Please report if this fix addresses your issues.

Thanks,
Mike
[22 Aug 2005 12:50] Sadao Hiratsuka
I confirmed this issue fixed.
Thank you very much!

BLOB has 4MB size limit (in getEscapedHexString method).
Does CLOB have such limit, too?
[22 Aug 2005 13:09] Michael G. Zinner
Thanks for the verification. 

No, I have not added such a limit to CLOB. But it might be good to add it since one might run into memory issues when using very large CLOBs.

You can easily increase the BLOB max size by changing 

			if (currentSize > 1048576 * 4) {
				throw new RuntimeException("BLOB is larger than 4MB.");
			}
to
			if (currentSize > 1048576 * 8) {
				throw new RuntimeException("BLOB is larger than 8MB.");
			}
or similar and compile it. (An Eclipse project is shipping with the tool in [installdir]/java. There you simply change the source, save and it will compile the files into the correct directory. Please see the forum at http://forums.mysql.com/list.php?104 for more details).