12 Understanding What's Supported for MySQL
This chapter contains information on database and table features supported by Oracle GoldenGate.
Topics:
- Character Sets in MySQL
- Supported MySQL Data Types
- Non-Supported MySQL Data Types
- Supported Objects and Operations for MySQL
- Non-Supported Objects and Operations
- System Schemas
Parent topic: Using Oracle GoldenGate for MySQL
Character Sets in MySQL
MySQL provides a facility that 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
-
When you specify the character set of your database as utf8mb4/utf8, the default collation is
utf8mb4_unicode_ci
/utf8_general_ci
. If you specifycollation_server=utf8mb4_bin
, the database interprets the data as binary. For example, specifying theCHAR
column length as four means that the byte length returned is 16 (for utf8mb4) though when you try to insert data more than four bytes the target database warns that the data is too long. This is the limitation of database so Oracle GoldenGate does not support binary collation. To overcome this issue, specifycollation_server=utf8mb4_bin
when the character set is utf8mb4 andcollation_server=utf8_bin
for utf8. -
The following character sets are not supported:
armscii8
keybcs2
utf16le
geostd8
Parent topic: Understanding What's Supported for MySQL
Supported MySQL Data Types
MySQL supports the following data types:
-
CHAR
-
VARCHAR
-
INT
-
TINYINT
-
SMALL
INT
-
MEDIUM INT
-
BIG INT
-
DECIMAL
-
FLOAT
-
DOUBLE
-
DATE
-
TIME
-
YEAR
-
DATETIME
-
TIMESTAMP
-
BINARY
-
VARBINARY
-
TEXT
-
TINYTEXT
-
MEDIUMTEXT
-
LONGTEXT
-
BLOB
-
TINYBLOB
-
MEDIUMBLOB
-
LONGBLOB
-
ENUM
-
BIT(M)
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
orTEXT
types when used as a primary key. -
Oracle GoldenGate supports UTF8 and UCS2 character sets. UTF8 data is converted to UTF16 by Oracle GoldenGate before writing it to the trail.
-
UTF32 is not supported by Oracle GoldenGate.
-
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
to9999/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. -
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 Classic Architecture, create a session variable for Oracle GoldenGate, called
TZ
, and set it equal to the time zone value of the database. -
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-strictsql_mode
, the non-strictsql_mode
does not prevent you from entering an invalidENUM
value and an error will be returned. To avoid this situation, do one of the following:-
Use
sql_mode
asSTRICT
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 useENUM
data types. -
Continue using non-strict
sql_mode
and useENUM
data types with valid values in the database. If you specify invalid values, the database will silently accept them and Extract will abend.
-
-
To preserve transaction boundaries for a MySQL target, create or alter the target tables to the InnoDB transactional database engine instead of the MyISAM engine. MyISAM will cause Replicat records to be applied as they are received, which does not guarantee transaction integrity even with auto-commit turned off. You cannot roll back a transaction with MyISAM.
-
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 thebinlog
that it receives from the master , you need to start the replication slave with the log slave-updates option as 1 inmy.cnf
. This is in addition to the other binary logging parameters. After the master's transactions are in the slave'sbinlog
, you can then setup a regular capture on the slave to capture and process the slave'sbinlog
.
Parent topic: Supported MySQL Data Types
Non-Supported MySQL Data Types
Oracle GoldenGate for MySQL does not support the following data types:
All spatial types (Geometry and so on), JSON
,
SET
, XML
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.
-
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 inbinlog
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
, andoutgoing_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.
-
Parent topic: Understanding What's Supported 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
andFETCHMODCOLEXCEPT
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.
Create a session variable for Oracle GoldenGate, called
TZ
, and set it equal to the time zone value of the database. -
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 thebinlog
that it receives from the master , you need to start the replication slave with the log slave-updates option as 1 inmy.cnf
. This is in addition to the other binary logging parameters. After the master's transactions are in the slave'sbinlog
, you can then setup a regular capture on the slave to capture and process the slave'sbinlog
.
Parent topic: Understanding What's Supported for MySQL
System 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'
-
'sys'
Parent topic: Understanding What's Supported for MySQL