E.1.1. THL Format

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 master 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
- 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)

  • SEQ# and FRAG#

    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 slaves and master are in sync, and can be used to identify individual transactions within the replication stream.

  • EPOCH#

    The EPOCH value is used a check to ensure that the logs on the slave and the master match. The EPOCH is stored in the THL, and a new EPOCH is generated each time a master 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 slave and the master. The EPOCH value is used to prevent the following situations:

    • In the event of a failover where there are events stored in the master log, but which did not make it to a slave, the EPOCH acts as a check so that when the master rejoins as the slave, the EPOCH numbers will not match the slave and the new master. The trapped transactions be identified by examining the THL output.

    • When a slave joins a master, the existence of the EPOCH prevents the slave from accepting events that happen to match only the sequence number, but not the corresponding EPOCH.

    Each time a Tungsten Replicator master goes online, the EPOCH number is incremented. When the slave connects, it requests the SEQUENCE and EPOCH, and the master confirms that the requested SEQUENCE has the requested EPOCH. If not, the request is rejected and the slave 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 master and slave to determine if there really is a mismatch between the two databases. For more information, see Section 5.8, “Managing Transaction Failures”.

  • SOURCEID

    The SOURCEID is a string identifying the source of the event stored in the THL. Typically it is the hostname or host identifier.

  • EVENTID

    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.

    Note

    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.

  • TIME

    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 slave.

  • METADATA

    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 master 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

  • TYPE

    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.

  • OPTIONS

    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.

  • SCHEMA

    Part of the EVENT structure, the SCHEMA provides the database or schema name in which the statement or row data was applied.

  • SHARDID

    When using parallel apply, provides the generated shard ID for the event when it is applied by the parallel applier thread. data.

  • SQL

    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