Web lists-archives.com

Re: understanding the binlog

----- On May 20, 2016, at 8:38 PM, Bernd Lentes bernd.lentes@xxxxxxxxxxxxxxxxxxxxx wrote:

> Hi,
> i'm currently creating a replication and got in contact with the binlog. I use
> row-based logging. I read already the doc but i still have some questions.
> Maybe you can help me.
> An excerp from a relay-log:
> # at 329
> #160520 17:00:01 server id 1  end_log_pos 259   Query   thread_id=94
> exec_time=3     error_code=0
> SET TIMESTAMP=1463756401/*!*/;
> SET @@session.sql_mode=524288/*!*/;
> /*!\C latin1 *//*!*/;
> @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33/*!*/;
> /*!*/;
> # at 405
> #160520 17:00:01 server id 1  end_log_pos 410   Query   thread_id=94
> exec_time=3     error_code=0
> use `percona`/*!*/;
> SET TIMESTAMP=1463756401/*!*/;
> DELETE FROM `percona`.`checksums` WHERE db = 'mausdb' AND tbl = 'GTAS_line_info'
> /*!*/;
> # at 556
> #160520 17:00:01 server id 1  end_log_pos 437   Xid = 785606
> COMMIT/*!*/;
> # at 583
> #160520 17:00:04 server id 1  end_log_pos 512   Query   thread_id=94
> exec_time=0     error_code=0
> SET TIMESTAMP=1463756404/*!*/;
> Let's begin with "at 329". Ok, that's the byte offset of the event. Next line:
> Why is here the end_log_pos 259 before the Byte-Offset one line above ? Same
> happen later on again.
> Exec_time is 3. Milliseconds, seconds ... ?
> SET TIMESTAMP=1463756401/*!*/; How can i interpret "1463756401" ? Is there a way
> to calculate to get a sensefull timestamp ?
> Xid = 785606. What is Xid ? An id of the event ?

I found out what Xid is:
The doc says:

An XID event is generated for a commit of a transaction that modifies one or more tables of an XA-capable storage engine. Strictly speaking, Xid_log_event is used if thd->transaction.xid_state.xid.get_my_xid() returns nonzero.

Here is an example of how to generate an XID event (it occurs whether or not innodb_support_xa is enabled):

Fixed data part:


Variable data part:

8 bytes. The XID transaction number.

Note: Contrary to all other numeric fields, the XID transaction number is not always written in little-endian format. The bytes are copied unmodified from memory to disk, so the format is machine-dependent. Hence, when replicating from a little-endian to a big-endian machine (or vice versa), the numeric value of transaction numbers will differ. In particular, the output of mysqlbinlog differs. This should does not cause inconsistencies in replication because the only important property of transaction numbers is that different transactions have different numbers (relative order does not matter).


This event is used for LOAD DATA INFILE statements. See also LOAD DATA INFILE Events."



Helmholtz Zentrum Muenchen
Deutsches Forschungszentrum fuer Gesundheit und Umwelt (GmbH)
Ingolstaedter Landstr. 1
85764 Neuherberg
Aufsichtsratsvorsitzende: MinDir'in Baerbel Brumme-Bothe
Geschaeftsfuehrer: Prof. Dr. Guenther Wess, Dr. Alfons Enhsen, Renate Schlusen (komm.)
Registergericht: Amtsgericht Muenchen HRB 6466
USt-IdNr: DE 129521671

MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql