Bug #30343 Migration Toolkit does not properly convert some numeric types from Sybase

• Submitted: 9 Aug 2007 19:46
• Modified: 3 Sep 2007 13:05
• Reporter: Matthew Montgomery
• Status: Closed
• Category: MySQL Migration Toolkit
• Severity: S3 (Non-critical)
• Version: 1.1.12
• OS: Windows
• CPU Architecture: Any

[9 Aug 2007 19:46] Matthew Montgomery

Description:
Migration Toolkit does not properly convert FLOAT, MONEY and SMALLMONEY from Sybase.

Currently it maps to "DECIMAL" which defaults to a precision and scale of (10,0)

The correct mapping should be MONEY => DECIMAL(19,4) and SMALLMONEY(10,4).

Also FLOAT and REAL have the same mappings to FLOAT but are defined separately in the MigrationSybase.java file.

The Sybase manual at http://manuals.sybase.com/onlinebooks/group-iq/iqg1250e/iqref/@Generic__BookTextView/23134... states:

If precision is not supplied, the FLOAT data type is the same as the REAL data type. If precision supplied, then the FLOAT data type is the same as the REAL or DOUBLE data type, depending on the value of the precision. The cutoff between REAL and DOUBLE is platform dependent, and it is the number of bits used in the mantissa of single-precision floating point number on the platform.

How to repeat:
Attempt migration of MONEY types from Sybase to MySQL

Suggested fix:
Patch for MONEY, SMALLMONEY mappings.

--- MigrationSybase.java-original        2007-05-02 10:45:37.000000000 -0500
+++ MigrationSybase.java       2007-08-09 14:16:50.000000000 -0500
@@ -260,17 +260,21 @@
                        }
                        // numeric types
                        else if (sourceDatatypeName.equalsIgnoreCase("DECIMAL")
-                                       || sourceDatatypeName.equalsIgnoreCase("NUMERIC")
-                                       || sourceDatatypeName.equalsIgnoreCase("MONEY")
-                                       || sourceDatatypeName.equalsIgnoreCase("SMALLMONEY")) {
+                                       || sourceDatatypeName.equalsIgnoreCase("NUMERIC")) {
+                               targetColumn.setDatatypeName("DECIMAL"));
+                       } else if (sourceDatatypeName.equalsIgnoreCase("MONEY")) {
                                targetColumn.setDatatypeName("DECIMAL");
+                               targetColumn.setPrecision(19);
+                               targetColumn.setScale(4);
+                       } else if (sourceDatatypeName.equalsIgnoreCase("SMALLMONEY")) {
+                               targetColumn.setDatatypeName("DECIMAL");
+                               targetColumn.setPrecision(10);
+                               targetColumn.setScale(4);
                        } else if (sourceDatatypeName.equalsIgnoreCase("DOUBLE PRECISION")) {
                                targetColumn.setDatatypeName("DOUBLE");
                                targetColumn.setScale(-1);

[9 Aug 2007 23:50] MySQL Verification Team

Thank you for the bug report.

[3 Sep 2007 13:05] Michael G. Zinner

Patch applied as proposed.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

[3 Sep 2007 16:28] Michael G. Zinner

Please find a patch here.
ftp://ftp.mysql.com/pub/mysql/download/gui-tools/mysql-migration-toolkit-1.1.12-patch-1.zi...
ftp://ftp.mysql.com/pub/mysql/download/gui-tools/mysql-migration-toolkit-1.1.12-patch-1.zi...

Installation Instructions
-------------------------

The zip file contains a MySQLMigrationTool.exe which needs to replace the existing one in C:\Program Files\MySQL\MySQL GUI Tools for 5.0

The modules.zip file needs to be extracted to 
C:\Program Files\MySQL\MySQL GUI Tools for 5.0\java\com\mysql\grt\modules and replace the .java and .class files there.