8.17.1. thl list Command

The list command to the thl command outputs a list of the sequence number information from the THL. By default, the entire THL as stored on disk is output. Command-line options enable you to select individual sequence numbers, sequence number ranges, or all the sequence information from a single file.

thl list
[-seqno # ]
[-low # ] | [-high # ]
[-file filename ] [-no-checksum ] [-sql ] [-charset] [-headers] [-json] [-specs-] [-charset]

There are three selection mechanisms:

  • -seqno #

    Output the THL sequence for the specific sequence number. When reviewing or searching for a specific sequence number, for example when the application of a sequence on a slave has failed, the replication data for that sequence number can be individually viewed. For example:

    shell> thl list -seqno 15
    SEQ# = 15 / FRAG# = 0 (last frag)
    - TIME = 2013-05-02 11:37:00.0
    - EPOCH# = 7
    - EVENTID = mysql-bin.000004:0000000000003345;0
    - SOURCEID = host1
    - METADATA = [mysql_server_id=1687011;unsafe_for_block_commit;dbms_type=mysql;»
        service=firstrep;shard=cheffy]
    - TYPE = com.continuent.tungsten.replicator.event.ReplDBMSEvent
    - OPTIONS = [##charset = UTF-8, autocommit = 1, sql_auto_is_null = 0, foreign_key_checks = 0, »
        unique_checks = 0, sql_mode = 'NO_AUTO_VALUE_ON_ZERO', character_set_client = 33, »
        collation_connection = 33, collation_server = 8]
    - SCHEMA = cheffy
    - SQL(0) = CREATE TABLE `access_log` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `userid` int(10) unsigned DEFAULT NULL,
      `datetime` int(10) unsigned NOT NULL DEFAULT '0',
    ...

    If the sequence number selected contains multiple fragments, each fragment will be output. Depending on the content of the sequence number information, the information can be output containing only the header/metadata information or only the table data (row or SQL) that was contained within the fragment. See -headers and -sql for more information.

    Note

    Unsigned integers are displayed and stored in the THL as their negative equivalents, and translated to the correct unsigned type when the data is applied to the target database.

  • -low # and/or -high #

    Specify the start (-low) or end (-high) of the range of sequence numbers to be output. If only -low is specified, then all sequence numbers from that number to the end of the THL are output. If -high is specified, all sequence numbers from the start of the available log file to the specified sequence number are output. If both numbers are specified, output all the sequence numbers within the specified range. For example:

    shell> thl list -low 320

    Will output all the sequence number fragments from number 320.

    shell> thl list -high 540

    Will output all the sequence number fragments up to and including 540.

    shell> thl list -low 320 -high 540

    Will output all the sequence number fragments from number 320 up to, and including, sequence number 540.

  • -file filename

    Outputs all of the sequence number fragment information from the specified THL file. If the filename has been determined from the thl index command, or by examining the output of other fragments, the file-based output can be used to identify statements or row data within the THL.

  • -charset charset

    Specify the character set to be used to decode the character-based row data embedded within the THL event. Without this option, data is output as a hex value.

  • -hex

    For SQL that may be in different character sets, the information can be optionally output in hex format to determine the contents and context of the statement, even though the statement itself may be unreadable on the command-line.

  • -no-checksum

    Ignores checksums within the THL. In the event of a checksum failure, use of this option will enable checksums to be ignored when the THL is being read.

  • -sql

    Prints only the SQL for the selected sequence range. Use of this option can be useful if you want to extract the SQL and execute it directly by storing or piping the output.

  • -headers

    Generates only the header information for the selected sequence numbers from the THL. For THL that contains a lot of SQL, obtaining the headers can be used to get basic content and context information without having to manually filter out the SQL in each fragment.

    The information is output as a tab-delimited list:

    2047	1412	0	false	2013-05-03 20:58:14.0	mysql-bin.000005:0000000579721045;0	host3		
    2047	1412	1	true	2013-05-03 20:58:14.0	mysql-bin.000005:0000000579721116;0	host3		
    2048	1412	0	false	2013-05-03 20:58:14.0	mysql-bin.000005:0000000580759206;0	host3		
    2048	1412	1	true	2013-05-03 20:58:14.0	mysql-bin.000005:0000000580759277;0	host3		
    2049	1412	0	false	2013-05-03 20:58:16.0	mysql-bin.000005:0000000581791468;0	host3		
    2049	1412	1	true	2013-05-03 20:58:16.0	mysql-bin.000005:0000000581791539;0	host3		
    2050	1412	0	false	2013-05-03 20:58:18.0	mysql-bin.000005:0000000582812644;0	host3

    The format of the fields output is:

    Sequence No | Epoch | Fragment | Last | Fragment | Date/Time | EventID | SourceID | Comments

    For more information on the fields displayed, see Section D.1.1, “THL Format”.

  • -json

    Only valid with the -headers option, the header information is output for the selected sequence numbers from the THL in JSON format. The field contents are identical, with each fragment of each THL sequence being contained in a JSON object, with the output consisting of an array of the these sequence objects. For example:

    [
       {
          "lastFrag" : false,
          "epoch" : 7,
          "seqno" : 320,
          "time" : "2013-05-02 11:41:19.0",
          "frag" : 0,
          "comments" : "",
          "sourceId" : "host1",
          "eventId" : "mysql-bin.000004:0000000244490614;0"
       },
       {
          "lastFrag" : true,
          "epoch" : 7,
          "seqno" : 320,
          "time" : "2013-05-02 11:41:19.0",
          "frag" : 1,
          "comments" : "",
          "sourceId" : "host1",
          "eventId" : "mysql-bin.000004:0000000244490685;0"
       }
    ]

    For more information on the fields displayed, see THL SEQNO.

  • -specs

    Shows the column specifications, such as identified type, length, and additional settings, when viewing events within row-based replication. This can be helpful when examining THL data in heterogeneous replication deployments.

    For example:

    shell> thl list -low 5282 -specs
    SEQ# = 5282 / FRAG# = 0 (last frag)
    - TIME = 2014-01-30 05:46:26.0
    - EPOCH# = 5278
    - EVENTID = mysql-bin.000017:0000000000001117;0
    - SOURCEID = host1
    - METADATA = [mysql_server_id=1687011;dbms_type=mysql;is_metadata=true;» 
       service=firstrep;shard=tungsten_firstrep;heartbeat=MASTER_ONLINE]
    - TYPE = com.continuent.tungsten.replicator.event.ReplDBMSEvent
    - SQL(0) =
     - ACTION = UPDATE
     - SCHEMA = tungsten_firstrep
     - TABLE = heartbeat
     - ROW# = 0
      - COL(index=1 name= type=4 [INTEGER] length=8 unsigned=false blob=false desc=null) = 1
      - COL(index=2 name= type=4 [INTEGER] length=8 unsigned=false blob=false desc=null) = 1416
      - COL(index=3 name= type=12 [VARCHAR] length=0 unsigned=false blob=false desc=null) = [B@65b60280
      - COL(index=4 name= type=93 [TIMESTAMP] length=0 unsigned=false blob=false desc=null) = 2014-01-30 05:46:26.0
      - COL(index=5 name= type=93 [TIMESTAMP] length=0 unsigned=false blob=false desc=null) = 2013-05-03 12:05:47.0
      - COL(index=6 name= type=4 [INTEGER] length=8 unsigned=false blob=false desc=null) = 1015
      - COL(index=7 name= type=4 [INTEGER] length=8 unsigned=false blob=false desc=null) = 0
      - COL(index=8 name= type=12 [VARCHAR] length=0 unsigned=false blob=false desc=null) = [B@105e55ab
      - KEY(index=1 name= type=4 [INTEGER] length=8 unsigned=false blob=false desc=null) = 1

    When identifying the different data types, the following effects should be noted:

  • -timezone

    Specify the timezone to use when display date or time values. When not specified, times are displayed using UTC.