1. Using Oracle GoldenGate Classic Architecture for Non-Oracle Databases
  2. Using Oracle GoldenGate for MySQL
  3. Understanding What's Supported for MySQL

12 Understanding What's Supported for MySQL

This chapter contains information on database and table features supported by Oracle GoldenGate.

Topics:

Parent topic: Using Oracle GoldenGate for MySQL

Character Sets in MySQL

MySQL allows users to specify different character sets at different levels.

Level Example

Database

 
create database test charset utf8;

Table

 
create table test( id int, name char(100)) charset utf8;

Column

 
create table test ( id int, name1 char(100) charset gbk, name2 char(100) charset utf8));

Limitations of Support

Oracle GoldenGate supports mixed character sets per listed objects, with the following limitations.

  • Binary collations are not supported for multi-byte character sets. For example, do not set the collation_server variable equal to utf8mb4_bin when the character set is utf8mb4.

  • The following character sets are not supported:

    • armscii8
    • utf8mb3
    • keybcs2
    • utf16le
    • geostd8

Parent topic: Understanding What's Supported for MySQL

Oracle GoldenGate for MySQL Supported Data Types

Oracle GoldenGate for MySQL supports the following data types:

  • BLOB

  • BIGINT

  • BINARY

  • BIT(M)

  • CHAR

  • DATE

  • DATETIME

  • DECIMAL

  • DOUBLE

  • ENUM

  • FLOAT

  • INT

  • JSON

  • LONGBLOB

  • LONGTEXT

  • MEDIUMBLOB

  • MEDIUMINT

  • MEDIUMTEXT

  • SMALLINT

  • TEXT

  • TIME

  • TIMESTAMP

  • TINYBLOB

  • TINYINT

  • TINYTEXT

  • VARBINARY

  • VARCHAR

  • YEAR

Parent topic: Understanding What's Supported for MySQL

Limitations and Clarifications

When running Oracle GoldenGate for MySQL, be aware of the following:

  • Functional indexes are not supported for Capture or Delivery.

  • Oracle GoldenGate does not support BLOB or TEXT types when used as a primary key.

  • Oracle GoldenGate supports a TIME type range from 00:00:00 to 23:59:59.

  • Oracle GoldenGate supports timestamp data from 0001/01/03:00:00:00 to 9999/12/31:23:59:59. If a timestamp is converted from GMT to local time, these limits also apply to the resulting timestamp. Depending on the time zone, conversion may add or subtract hours, which can cause the timestamp to exceed the lower or upper supported limit.

  • Oracle GoldenGate does not support negative dates.

  • The support of range and precision for floating-point numbers depends on the host machine. In general, the precision is accurate to 16 significant digits, but you should review the database documentation to determine the expected approximations. Oracle GoldenGate rounds or truncates values that exceed the supported precision.

  • When you use ENUM type in non-strict sql_mode, the non-strict sql_mode does not prevent you from entering an invalid ENUM value and an error will be returned. To avoid this situation, do one of the following:

    • Use sql_mode as STRICT and restart Extract. This prevents users from entering invalid values for any of the data types. An IE user can only enter valid values for those data types.

    • Continue using non-strict sql_mode, but do not use ENUM data types.

    • Continue using non-strict sql_mode and use ENUM data types with valid values in the database. If you specify invalid values, the database will silently accept them and Extract will abend.

  • Table with single column is not supported for JSON datatype. Extract will abend in case it is configured for a table which has a single column of JSON datatype.

  • JSON datatype does not support CDR. The following message gets logged in the report file if GETBEFORECOLS is configured and the table has columns of JSON datatypes:
    INFO OGG-06556 The following columns will not be considered for CDR

    The limtations for CDR applies to cases where the GETBEFORECOLS and COMPARECOLS are used.

Parent topic: Oracle GoldenGate for MySQL Supported Data Types

Non-Supported MySQL Data Types

Oracle GoldenGate for MySQL does not support the following data types:

XML, SET, all spatial types (Geometry and so on).

Note:

Extract abends if it is configured to capture from tables that contain any of the unsupported data types, so ensure that Extract is not configured to capture from tables containing columns of unsupported data types.

Parent topic: Understanding What's Supported for MySQL

Supported Objects and Operations for MySQL

Oracle GoldenGate for MySQL supports the following objects and operations:

  • Oracle GoldenGate supports the following DML operations on source and target database transactional tables:

    • Insert operation

    • Update operation (compressed included)

    • Delete operation (compressed included)

    • Truncate operation

  • Oracle GoldenGate supports the extraction and replication of DDL (data definition language) operations.

  • Oracle GoldenGate supports transactional tables up to the full row size and maximum number of columns that are supported by MySQL and the database storage engine that is being used. InnoDB supports up to 1017 columns.

  • Generated columns are supported and captured.

  • Oracle GoldenGate supports the AUTO_INCREMENT column attribute. The increment value is captured from the binary log by Extract and applied to the target table in a Replicat insert operation.

  • Oracle GoldenGate can operate concurrently with MySQL native replication.

  • Oracle GoldenGate supports the DYNSQL feature for MySQL.

    Note:

    XA transactions are not supported for capture and any XA transactions logged in binlog cause Extract to abend. So, you must not use XA transactions against a database that Extract is configured to capture.

    If XA transactions are being used for databases that are not configured for Oracle GoldenGate capture, then exclude those databases from logging into MySQL binary logs by using the parameter binlog-ignore-db in the MySQL server configuration file.

    Limitations on Automatic Heartbeat Table support are as follows:

    • Ensure that the database in which the heartbeat table is to be created already exists to avoid errors when adding the heartbeat table.

    • In the heartbeat history lag view, the information in fields like heartbeat_received_ts, incoming_heartbeat_age, and outgoing_heartbeat_age are shown with respect to the system time. You should ensure that the operating system time is setup with the correct and current time zone information.

  • Position by End of File (EOF) is supported in MySQL. Oracle GoldenGate Extract for MySQL finds the position corresponding to the end of the file and starts reading transactions from there. The EOF position is not exact, if data is continuously written to the binary log.

    The Extract is added and altered using:
    ADD EXTRACT group_name, TRANLOG, EOF
    ALTER EXTRACT group_name, EOF

Parent topic: Understanding What's Supported for MySQL

Details of Support for Objects and Operations in MySQL DDL

Here's a list of the MySQL objects and operation types that Oracle GoldenGate supports for the capture and replication of DDL operations.

  • DDL replication for MySQL is only supported between MySQL databases as sources and targets.

  • Basic extraction and replication of DDL operations are supported for MySQL 5.7.10 and higher.

  • For MySQL 5.7.10, only local DDL capture is supported.

  • For MySQL 8.0, local and remote DDL capture is supported.

  • Only the CREATE TABLE, ALTER TABLE, and DROP TABLE operations are supported.

  • TRUNCATE operations are supported as DML through the GETTRUNCATES Extract and Replicat parameter and do not require configuring Oracle GoldenGate for MySQL DDL support.

  • DDL replication is not supported in a Oracle GoldenGate bi-directional configuration.

  • DDL replication is not supported for cloud based database services where the binlog_row_metadata database setting cannot be set to FULL.

Parent topic: Supported Objects and Operations for MySQL

Non-Supported Objects and Operations

Oracle GoldenGate for MySQL does not support the following objects and operations:

  • Invisible columns

  • The Oracle GoldenGate BATCHSQL feature.

  • Array fetching during initial load.

  • The following character sets are not supported:

    • ULIB_CS_ARMSCII8, /* American National 166-9 */
    • ULIB_CS_GEOSTD8, /* Geogian Standard */
    • ULIB_CS_KEYBCS2, /* Kemennicky MS-DOS

  • Capturing NLS LOB data using the FETCHMOCOLS and FETCHMODCOLEXCEPT TABLE options is not supported when DDL is enabled.

  • Renaming tables.

  • DDL statements inside stored procedures is not supported.

  • When the time zone of the Oracle GoldenGate installation server does not match the time zone of the source database server, then the TIMESTAMP data sent to the target database will differ from the source database. For Oracle GoldenGate Microservices installations, regardless of the time zones being the same, Extract will resolve the time zone to UTC. Determine the source database time zone by running the following query: 

    select @@system_time_zone;

    This will return a time zone value, such as PDT.

    For Classic Architecture, create a session variable for Oracle GoldenGate, called TZ, and set it equal to the time zone value of the database.

    For MA, create a variable in the deployment that contains the source Extract, called TZ and set it to the value of the source database time zone. After this, stop any running Oracle GoldenGate processes and restart the Administration Service, and then start the Extracts and Replicats.

  • Extraction and replication from and to views is not supported.

  • Transactions applied by the slave are logged into the relay logs and not into the slave's binlog. If you want a slave to write transactions the binlog that it receives from the master , you need to start the replication slave with the log slave-updates option as 1 in my.cnf. This is in addition to the other binary logging parameters. After the master's transactions are in the slave's binlog, you can then setup a regular capture on the slave to capture and process the slave's binlog.

Limitations of SingleStoreDB Objects and Operations

Oracle GoldenGate for MySQL now supports SingleStoreDB and SingleStoreDB Cloud, but with the following limitations:

  • Replication to views within SingleStoreDB are not supported, as only the base tables are writeable.

  • Updates to columns that are part of the SHARD key are not supported.

  • Primary key updates on tables with no explicit SHARD key are not supported. This is because SingleStoreDB assigns the primary key as the SHARD key in this situation, and updates to SHARD key columns are not allowed.

  • The DBOPTIONS LIMITROWS behavior of Replicat for SingleStoreDB tables without a primary or unique key that are spread across multiple partitions, is not supported.

  • The DBOPTIONS LIMITROWS and NOLIMITROWS parameter options are not supported for SingleStoreDB.

  • SingleStoreDB does not support cross-database transactions, which means that a Replicat can only support mapping to a single schema/database. This includes mappings for checkpoint and heartbeat tables, so these objects must be created under the same schema/database as the user tables to be replicated.

Parent topic: Understanding What's Supported for MySQL

Systems Schemas

The following schemas or objects are not automatically replicated by Oracle GoldenGate unless they are explicitly specified without a wildcard.

  • 'information_schema'

  • 'performance_schema'

  • 'mysql'

Parent topic: Understanding What's Supported for MySQL