My Oracle Support Banner

Timestamp - Microseconds are getting trimmed When replicating from Oracle to MySQL with OGG 11.2.1 (Doc ID 1595655.1)

Last updated on JUNE 05, 2019

Applies to:

Oracle GoldenGate - Version 11.2.1.0.10 and later
Information in this document applies to any platform.

Goal

GoldenGate trimming the microseconds in the timestamp column.

Source: Oracle
Target: MySQL

In MySQL:
CREATE TABLE `<user_name>`.`<table_name>` (
  `<column_name>` datetime DEFAULT NULL,
  `<column_name>` datetime(6) DEFAULT NULL,
  `<column_name>` datetime(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO <table_name>VALUES ('<column_value>', '<column_value>', '<column_value>');

SELECT * FROM test;

The values shows correctly with microseconds.

For data populated by goldengate, all datetime(6) values show microseconds as 000000

In MySQL:
CREATE TABLE `<user_name>`.`<table_name>` (
  `<column_name>` bigint(20) NOT NULL,
  `<column_name>` varchar(64) DEFAULT NULL,
  `<column_name>` bigint(20) DEFAULT NULL,
  `<column_name>` varchar(64) DEFAULT NULL,
  `<column_name>` varchar(64) DEFAULT NULL,
  `<column_name>` datetime(6) DEFAULT CURRENT_TIMESTAMP(6),
  `<column_name>` datetime(6) DEFAULT CURRENT_TIMESTAMP(6),
  UNIQUE KEY `<unique_key_name>` (`<column_name>`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SELECT * FROM <table_name>;
creation_date: <column_value>
(In WHS: microseconds are showing values)
 

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.