4.1.2. Data Type Differences and Limitations

When replicating from MySQL to Oracle there are a number of datatype differences that should be accommodated to ensure reliable replication of the information. The core differences are described in Table 4.1, “Data Type differences when replicating data from MySQL to Oracle”.

Table 4.1. Data Type differences when replicating data from MySQL to Oracle

MySQL Datatype Oracle Datatype Notes
INT NUMBER(10, 0)  
DECIMAL(x,y) NUMBER(x, y)  
CHAR(n) CHAR(n)  
VARCHAR(n) VARCHAR2(n) For sizes less than 2000 bytes data can be replicated. For lengths larger than 2000 bytes, the data will be truncated when written into Oracle
TEXT CLOB Replicator can transform TEXT into CLOB or VARCHAR(N). If you choose VARCHAR(N) on Oracle, the length of the data accepted by Oracle will be limited to 4000. This is limitation of Oracle. The size of CLOB columns within Oracle is calculated in terabytes. If TEXT fields on MySQL are known to be less than 4000 bytes (not characters) long, then VARCHAR(4000) can be used on Oracle. This may be faster than using CLOB.
ENUM(...) VARCHAR(255) Use the enumtostring filter
SET(...) VARCHAR(255) Use the settostring filter

When replicating between Oracle and other database types, the ddlscan command can be used to generate DDL appropriate for the supported data types in the target database. For example, in MySQL to Oracle deployments the DDL can be read from the MySQL server and generated for the Oracle server so that replication can begin without manually creating the Oracle specific DDL.

When replicating from Oracle to MySQL or Oracle, there are limitations on the data types that can be replicated due to the nature of the CDC, whether you are using Asynchronous or Synchronous CDC for replication. The details of data types not supported by each mechanism are detailed in Table 4.2, “Data Type Differences when Replicating from Oracle to MySQL or Oracle”.

Table 4.2. Data Type Differences when Replicating from Oracle to MySQL or Oracle

Data Type Asynchronous CDC (Oracle EE Only) Synchronous CDC (Oracle SE and EE)
BFILE Not Supported Not Supported
LONG Not Supported Not Supported
ROWID Not Supported Not Supported
UROWID Not Supported Not Supported
BLOB   Not Supported
CLOB   Not Supported
NCLOB   Not Supported
All Object Types Not Supported Not Supported


More information on Oracle CDC can be found within the Oracle documentation.

In addition, the following DDL differences and requirements exist:

  • Column orders on MySQL and Oracle must match, but column names do not have to match.

    Using the dropcolumn filter, columns can be dropped and ignored if required.

  • Each table within MySQL should have a Primary Key. Without a primary key, full-row based lookups are performed on the data when performing UPDATE or DELETE operations. With a primary key, the pkey filter can add metadata to the UPDATE/DELETE event, enabling faster application of events within Oracle.

  • Indexes on MySQL and Oracle do not have to match. This allows for different index types and tuning between the two systems according to application and dataserver performance requirements.

  • Keywords that are restricted on Oracle should not be used within MySQL as table, column or database names. For example, the keyword SESSION is not allowed within Oracle. Tungsten Cluster determines the column name from the target database metadata by position (column reference), not name, so replication will not fail, but applications may need to be adapted. For compatibility, try to avoid Oracle keywords.

For more information on differences between MySQL and Oracle, see Oracle and MySQL Compared.

To make the process of migration from MySQL to Oracle easier, Tungsten Cluster includes a tool called ddlscan which will read table definitions from MySQL and create appropriate Oracle table definitions to use during replication. For more information on using this tool in a MySQL to Oracle deployment, see Section, “Create the Destination Schema”.

For reference information on the ddlscan tool, see Section 8.6, “The ddlscan Command”.