The THL is stored on disk in a specific format that combines the information about the SQL and row data, metadata about the environment in which the row changes and SQL changes were made (metadata), and the log specific information, including the source, database, and timestamp of the information.
A sample of the output is shown below, the information is taken from the output of the thl command:
SEQ# = 0 / FRAG# = 0 (last frag) - TIME = 2013-03-21 18:47:39.0 - EPOCH# = 0 - EVENTID = mysql-bin.000010:0000000000000439;0 - SOURCEID = host1 - METADATA = [mysql_server_id=10;dbms_type=mysql;is_metadata=true;service=dsone;» shard=tungsten_firstcluster;heartbeat=MASTER_ONLINE] - TYPE = com.continuent.tungsten.replicator.event.ReplDBMSEvent - OPTIONS = [##charset = ISO8859_1, autocommit = 1, sql_auto_is_null = 0, » foreign_key_checks = 1, unique_checks = 1, sql_mode = '', character_set_client = 8, » collation_connection = 8, collation_server = 8] - SCHEMA = tungsten_dsone - SQL(0) = UPDATE tungsten_dsone.heartbeat SET source_tstamp= '2013-03-21 18:47:39', salt= 1, » name= 'MASTER_ONLINE' WHERE id= 1 /* ___SERVICE___ = [firstcluster] */
The sample above shows the information for the SQL executed on a MySQL
server. The EVENTID
shows the MySQL binary log from
which the statement has been read. The MySQL server has stored the
information in the binary log using
STATEMENT
or
MIXED
mode; log events written in
ROW
mode store the individual row
differences. A summary of the THL stored format information, including
both hidden values and the information included in the
thl command output is provided in
Table E.1, “THL Event Format”.
Table E.1. THL Event Format
Displayed Field | Internal Name | Data type | Size | Description |
---|---|---|---|---|
- |
record_length
| Integer | 4 bytes | Length of the full record information, including this field |
- |
record_type
| Byte | 1 byte | Event record type identifier |
- |
header_length
| Unsigned int | 4 bytes | Length of the header information |
SEQ#
|
seqno
| Unsigned long | 8 bytes | Log sequence number, a sequential value given to each log entry |
FRAG#
|
fragno
| Unsigned short | 2 bytes | Event fragment number. An event can consist of multiple fragments of SQL or row log data |
- |
last_frag
| Byte | 1 byte | Indicates whether the fragment is the last fragment in the sequence |
EPOCH#
|
epoch_number
| Unsigned long | 8 bytes | Event epoch number. Used to identify log sections within the Primary THL |
SOURCEID
|
source_id
| UTF-8 String | Variable (null terminated) | Event source ID, the hostname or identity of the dataserver that generated the event |
EVENTID
|
event_id
| UTF-8 String | Variable (null terminated) | Event ID; in MySQL, for example, the binlog filename and position that contained the original event |
SHARDID
|
shard_id
| UTF-8 String | Variable (null terminated) | Shard ID to which the event belongs |
TIME
|
tstamp
| Unsigned long | 8 bytes | Time of the commit that triggered the event |
FILE
| - | String | - | Filename of the THL file containing the event |
- |
data_length
| Unsigned int | 4 bytes | Length of the included event data |
- |
event
| Binary | Variable | Serialized Java object containing the SQL or ROW data |
METADATA
|
Part of event
| - | - | Metadata about the event |
TYPE
|
Part of event
| - | - | Internal storage type of the event |
OPTIONS
|
Part of event
| - | - | Options about the event operation |
SCHEMA
|
Part of event
| - | - | Schema used in the event |
SQL
|
Part of event
| - | - | SQL statement or row data |
- |
crc_method
| Byte | 1 byte | Method used to compute the CRC for the event. |
- |
crc
| Unsigned int | 4 bytes | CRC of the event record (not including the CRC value) |
Individual events within the log are identified by a sequential
SEQUENCE
number. Events are further divided
into individual fragments. Fragments are numbered from 0 within a
given sequence number. Events are applied to the database wholesale,
fragments are used to divide up the size of the statement or row
information within the log file. The fragments are stored internally
in memory before being applied to the database and therefore memory
usage is directly affected by the size and number of fragments held in
memory.
The sequence number as generated during this process is unique and therefore acts as a global transaction ID across a cluster. It can be used to determine whether the Replicas and Primary are in sync, and can be used to identify individual transactions within the replication stream.
The EPOCH
value is used a check to ensure that
the logs on the Replica and the Primary match. The
EPOCH
is stored in the THL, and a new
EPOCH
is generated each time a Primary goes
online. The EPOCH
value is then written and
stored in the THL alongside each individual event. The
EPOCH
acts as an additional check, beyond the
sequence number, to validate the information between the Replica and the
Primary. The EPOCH
value is used to prevent the
following situations:
In the event of a failover where there are events stored in the
Primary log, but which did not make it to a Replica, the
EPOCH
acts as a check so that when the
Primary rejoins as the Replica, the EPOCH
numbers will not match the Replica and the new Primary. The trapped
transactions be identified by examining the THL output.
When a Replica joins a Primary, the existence of the
EPOCH
prevents the Replica from accepting
events that happen to match only the sequence number, but not the
corresponding EPOCH
.
Each time a Tungsten Replicator Primary goes online, the
EPOCH
number is incremented. When the Replica
connects, it requests the SEQUENCE
and
EPOCH
, and the Primary confirms that the
requested SEQUENCE
has the requested
EPOCH
. If not, the request is rejected and the
Replica gets a validation error:
pendingExceptionMessage: Client handshake failure: Client response validation failed: » Log epoch numbers do not match: client source ID=west-db2 seqno=408129 » server epoch number=408128 client epoch number=189069
When this error occurs, the THL should be examined and compared between the Primary and Replica to determine if there really is a mismatch between the two databases. For more information, see Section 7.5, “Managing Transaction Failures”.
The SOURCEID
is a string
identifying the source of the event stored in the THL. Typically it is
the hostname or host identifier.
The EVENTID
is a string identifying the source
of the event information in the log. Within a MySQL installed, the
EVENTID
contains the binary log name and
position which provided the original statement or row data.
The event ID shown is the end of the corresponding event stored in the THL, not the beginning. When examining the mysqlbinlog for an sequence ID in the THL, you should check the EVENTID of the previous THL sequence number to determine where to start looking within the binary log.
When the source information is committed to the database, that information is stored into the corresponding binary log (MySQL) or CDC (Oracle). That information is stored in the THL. The time recorded in the THL is the time the data was committed, not the time the data was recorded into the log file.
The TIME
value as stored in the THL is used to
compute latency information when reading and applying data on a Replica.
Part of the binary EVENT
payload stored within
the event fragment, the metadata is collected and stored in the
fragment based on information generated by the replicator. The
information is stored as a series of key/value pairs. Examples of the
information stored include:
MySQL server ID
Source database type
Name of the Replicator service that generated the THL
Any 'heartbeat' operations sent through the replicator service, including those automatically generated by the service, such as when the Primary goes online
The name of the shard to which the event belongs
Whether the contained data is safe to be applied through a block commit operation
The stored event type. Replicator has the potential to use a number of
different stored formats for the THL data. The default type is based
on the
com.continuent.tungsten.replicator.event.ReplDBMSEvent
.
Part of the EVENT
binary payload, the
OPTIONS
include information about the
individual event that have been extracted from the database. These
include settings such as the autocommit status, character set and
other information, which is used when the information is applied to
the database.
There will be one OPTIONS
block for each
SQL
statement stored in the event.
Part of the EVENT
structure, the
SCHEMA
provides the database or schema name in
which the statement or row data was applied.
When using parallel apply, provides the generated shard ID for the event when it is applied by the parallel applier thread. data.
For statement based events, the SQL of the statement that was recorded. Multiple individual SQL statements as part of a transaction can be contained within a single event fragment.
For example, the MySQL statement:
mysql> INSERT INTO user VALUES (null, 'Charles', now());
Query OK, 1 row affected (0.01 sec)
Stores the following into the THL:
SEQ# = 3583 / FRAG# = 0 (last frag) - TIME = 2013-05-27 11:49:45.0 - EPOCH# = 2500 - EVENTID = mysql-bin.000007:0000000625753960;0 - SOURCEID = host1 - METADATA = [mysql_server_id=1687011;dbms_type=mysql;service=firstrep;shard=test] - TYPE = com.continuent.tungsten.replicator.event.ReplDBMSEvent - SQL(0) = SET INSERT_ID = 3 - OPTIONS = [##charset = ISO8859_1, autocommit = 1, sql_auto_is_null = 0, » foreign_key_checks = 1, unique_checks = 1, sql_mode = '', character_set_client = 8, » collation_connection = 8, collation_server = 8] - SCHEMA = test - SQL(1) = INSERT INTO user VALUES (null, 'Charles', now()) /* ___SERVICE___ = [firstrep] */
For row based events, the information is further defined by the
individual row data, including the action type
(UPDATE
,
INSERT
or
DELETE
),
SCHEMA
, TABLE
and
individual ROW data. For each ROW, there may be one or more
COL
(column) and identifying
KEY
event to identify the row on which the
action is to be performed.
The same statement when recorded in ROW
format:
SEQ# = 3582 / FRAG# = 0 (last frag) - TIME = 2013-05-27 11:45:19.0 - EPOCH# = 2500 - EVENTID = mysql-bin.000007:0000000625753710;0 - SOURCEID = host1 - METADATA = [mysql_server_id=1687011;dbms_type=mysql;service=firstrep;shard=test] - TYPE = com.continuent.tungsten.replicator.event.ReplDBMSEvent - SQL(0) = - ACTION = INSERT - SCHEMA = test - TABLE = user - ROW# = 0 - COL(1: ) = 2 - COL(2: ) = Charles - COL(3: ) = 2013-05-27 11:45:19.0