Skip navigation links
MySQL Internals Manual :: 20 The Binary Log :: 20.9 Event Data for Specific Event Types
20.10 Row-Based Binary Logging »
Section Navigation      [Toggle]

20.9. Event Data for Specific Event Types

20.9.1. LOAD DATA INFILE Events

The following sections provide details about what appears in the fixed and variable parts of the event data for each event type.

LOAD DATA INFILE statements have been associated over time with several different events. The event contents are detailed in this section, and LOAD DATA INFILE Events provides a historical perspective on which events were used when.

Start_log_event_v3/START_EVENT_V3

This event occurs at the beginning of v1 or v3 binary log files. See Binary Log Versions for how it is used.

In MySQL 4.0 and 4.1, such events are written only to the first binary log file that mysqld creates after startup. Log files created subsequently (when someone issues a FLUSH LOGS statement or the current binary log file becomes too large) do not contain this event. In MySQL 5.0 and up, all binary log files start with a FORMAT_DESCRIPTION_EVENT.

Fixed data part:

  • 2 bytes. The binary log format version. This is 1 in MySQL 3.23 and 3 in MySQL 4.0 and 4.1. (In MySQL 5.0 and up, FORMAT_DESCRIPTION_EVENT is used instead of START_EVENT_V3.)

  • 50 bytes. The MySQL server's version (example: 4.0.14-debug-log), padded with 0x00 bytes on the right.

  • 4 bytes. Timestamp in seconds when this event was created (this is the moment when the binary log was created). This value is redundant; the same value occurs in the timestamp header field.

Variable data part:

  • Empty

Query_log_event/QUERY_EVENT

Fixed data part:

  • 4 bytes. The ID of the thread that issued this statement. Needed for temporary tables. This is also useful for a DBA for knowing who did what on the master.

  • 4 bytes. The time in seconds that the statement took to execute. Only useful for inspection by the DBA.

  • 1 byte. The length of the name of the database which was the default database when the statement was executed. This name appears later, in the variable data part. It is necessary for statements such as INSERT INTO t VALUES(1) that don't specify the database and rely on the default database previously selected by USE.

  • 2 bytes. The error code resulting from execution of the statement on the master. Error codes are defined in include/mysqld_error.h. 0 means no error. How come statements with a nonzero error code can exist in the binary log? This is mainly due to the use of nontransactional tables within transactions. For example, if an INSERT ... SELECT fails after inserting 1000 rows into a MyISAM table (for example, with a duplicate-key violation), we have to write this statement to the binary log, because it truly modified the MyISAM table. For transactional tables, there should be no event with a nonzero error code (though it can happen, for example if the connection was interrupted (Control-C)). The slave checks the error code: After executing the statement itself, it compares the error code it got with the error code in the event, and if they are different it stops replicating (unless --slave-skip-errors was used to ignore the error).

  • 2 bytes (not present in v1, v3). The length of the status variable block.

Variable part:

  • Zero or more status variables (not present in v1, v3). Each status variable consists of one byte code identifying the variable stored, followed by the value of the variable. The format of the value is variable-specific, as described later.

  • The default database name (null-terminated).

  • The SQL statement. The slave knows the size of the other fields in the variable part (the sizes are given in the fixed data part), so by subtraction it can know the size of the statement.

Each entry in the status variable block has a code and a value, where the value format is as indicated in the following list. The list provides basic information about each variable. For additional details, see log_event.h.

  • Q_FLAGS2_CODE = 0. Value is a 4-byte bit-field. This variable is written only as of MySQL 5.0.

  • Q_SQL_MODE_CODE = 1. Value is an 8-byte SQL mode value.

  • Q_CATALOG_CODE = 2. Value is the catalog name: a length byte followed by that many bytes, plus a terminating null byte. This variable is present only in MySQL 5.0.0 to 5.0.3. It was replaced with Q_CATALOG_NZ_CODE in MySQL 5.0.4 because the terminating null is unnecessary.

  • Q_AUTO_INCREMENT = 3. Value is two 2-byte unsigned integers representing the auto_increment_increment and auto_increment_offset system variables. This variable is present only if auto_increment is greater than 1.

  • Q_CHARSET_CODE = 4. Value is three 2-byte unsigned integers representing the character_set_client, collation_connection, and collation_server system variables.

  • Q_TIME_ZONE_CODE = 5. Value is the time zone name: a length byte followed by that many bytes. This variable is present only if the time zone string is non-empty.

  • Q_CATALOG_NZ_CODE = 6. Value is the catalog name: a length byte followed by that many bytes. Value is always std. This variable is present only if the catalog name is non-empty.

  • Q_LC_TIME_NAMES_CODE = 7. Value is a 2-byte unsigned integer representing the lc_time_names number. This variable is present only if the value is not 0 (that is, not en_US).

  • Q_CHARSET_DATABASE_CODE = 8. Value is a 2-byte unsigned integer representing the collation_database system variable.

  • Q_TABLE_MAP_FOR_UPDATE_CODE = 9. Value is 8 bytes representing the table map to be updated by a multiple-table update statement. Each bit of this variable represents a table, and is set to 1 if the corresponding table is to be updated by the statement.

Table_map_for_update is used to evaluate the filter rules specified by --replicate-do-table / --replicate-ignore-table.

Stop_log_event/STOP_EVENT

A Stop_log_event is written under these circumstances:

  • A master writes the event to the binary log when it shuts down

  • A slave writes the event to the relay log when it shuts down or when a RESET SLAVE statement is executed

Fixed data part:

  • Empty

Variable data part:

  • Empty

Rotate_log_event/ROTATE_EVENT

When a binary log file exceeds a size limit, a ROTATE_EVENT is written at the end of the file that points to the next file in the squence. This event is information for the slave to know the name of the next binary log it is going to receive.

ROTATE_EVENT is generated locally and written to the binary log on the master. It is written to the relay log on the slave when FLUSH LOGS occurs, and when receiving a ROTATE_EVENT from the master. In the latter case, there will be two rotate events in total originating on different servers.

There are conditions under which the terminating log-rotation event does not occur. For example, the server might crash.

Fixed data part:

  • 8 bytes. The position of the first event in the next log file. Always contains the number 4 (meaning the next event starts at position 4 in the next binary log). This field is not present in v1; presumably the value is assumed to be 4.

Variable data part:

  • The name of the next binary log. The filename is not null-terminated. Its length is the event size minus the size of the fixed parts.

Intvar_log_event/INTVAR_EVENT

Fixed data part:

  • Empty

Variable data part:

  • 1 byte. A value indicating the variable type: LAST_INSERT_ID_EVENT = 1 or INSERT_ID_EVENT = 2.

  • 8 bytes. An unsigned integer indicating the value to be used for the LAST_INSERT_ID() invocation or AUTO_INCREMENT column.

Load_log_event/LOAD_EVENT

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

LOAD_EVENT shares the Load_log_event class with NEW_LOAD_EVENT. The primary difference is that LOAD_EVENT allows only single-character field and line option values, whereas NEW_LOAD_EVENT allows multiple-character values. Also, LOAD_EVENT has no file ID or data block because with this event, the slave asks the master to transfer the data file at event execution time.

The format for this event is more complicated than for others, because it contains information about many LOAD DATA INFILE statement clauses.

Fixed data part:

  • 4 bytes. The ID of the thread on the master that issued this LOAD DATA INFILE statement. Needed for temporary tables. This is also useful for a DBA for knowing who did what on the master.

  • 4 bytes. The time in seconds which the LOAD DATA INFILE took for execution. Only useful for inspection by the DBA.

  • 4 bytes. The number of lines to skip at the beginning of the file (corresponds to the IGNORE N LINES clause of LOAD DATA INFILE).

  • 1 byte. The length of the name of the table to load.

  • 1 byte. The length of the name of the database containing the table.

  • 4 bytes. The number of columns to load ((col_name,...) clause). Will be nonzero only if the columns to load were explicitly mentioned in the statement.

Variable data part:

  • 1 byte. The field-terminating character (FIELDS TERMINATED BY option).

  • 1 byte. The field-enclosing character (FIELDS ENCLOSED BY option).

  • 1 byte. The line-terminating character (LINES TERMINATED BY option).

  • 1 byte. The line-starting character (LINES STARTING BY option).

  • 1 byte. The escaping character (FIELDS ESCAPED BY option).

  • 1 byte. Flags that indicate whether certain keywords are present in the statement:

    • DUMPFILE_FLAG =0x1 (unused; this flag appears to be a botch because it would apply to SELECT ... INTO OUTFILE, not LOAD DATA INFILE)

    • OPT_ENCLOSED_FLAG = 0x2 (FIELD OPTIONALLY ENCLOSED BY option)

    • REPLACE_FLAG = 0x4 (LOAD DATA INFILE REPLACE)

    • IGNORE_FLAG = 0x8 (LOAD DATA INFILE IGNORE)

  • 1 byte. Flags that indicate whether each of the field and line options are empty. The low-order five bits are 1 to indicate an empty option (has a length of 0) or 0 to indicate a non-empty option (has a length of 1).

    • FIELD_TERM_EMPTY = 0x1

    • ENCLOSED_EMPTY = 0x2

    • LINE_TERM_EMPTY = 0x4

    • LINE_START_EMPTY = 0x8

    • ESCAPED_EMPTY = 0x10

  • 1 byte. The length of the name of the first column to load.

  • ...

  • 1 byte. The length of the name of the last column to load.

  • Variable-sized. The name of the first column to load (null-terminated).

  • ...

  • Variable-sized. The name of the last column to load (null-terminated).

  • Variable-sized. The name of the table to load (null-terminated).

  • Variable-sized. The name of the database that contains the table (null-terminated).

  • Variable-sized. The name of the file that was loaded (the original name on the master, not the name of the temporary file created on the slave). The length of the data filename is the event size minus the size of all other parts.

Note: Because this event allows only single-character field and line option values, presumably LOAD DATA INFILE statements will not replicate correctly if any such option contains multiple characters.

Slave_log_event/SLAVE_EVENT

This event is never written, so it cannot exist in a binary log file. It was meant for failsafe replication, which has never been implemented.

Create_file_log_event/CREATE_FILE_EVENT

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

This event tells the slave to create a temporary file and fill it with a first data block. Later, zero or more APPEND_BLOCK_EVENT events append blocks to this temporary file. EXEC_LOAD_EVENT tells the slave to load the temporary file into the table, or DELETE_FILE_EVENT tells the slave not to do the load and to delete the temporary file. DELETE_FILE_EVENT occurs when the LOAD DATA failed on the master: On the master we start to write loaded blocks to the binary log before the end of the statement. If for some reason there is an error, we must tell the slave to abort the load.

The format for this event is more complicated than for others, because it contains information about many LOAD DATA INFILE statement clauses.

Fixed data part:

  • 4 bytes. The ID of the thread on the master that issued this LOAD DATA INFILE statement. Needed for temporary tables. This is also useful for a DBA for knowing who did what on the master.

Variable data part:

  • 4 bytes. The time in seconds which the LOAD DATA INFILE took for execution. Only useful for inspection by the DBA.

  • 4 bytes. The number of lines to skip at the beginning of the file (corresponds to the IGNORE N LINES clause of LOAD DATA INFILE).

  • 1 byte. The length of the name of the table to load.

  • 1 byte. The length of the name of the database containing the table.

  • 4 bytes. The number of columns to load ((col_name,...) clause). Will be nonzero only if the columns to load were explicitly mentioned in the statement.

  • 4 bytes. An ID for the data file. This is necessary in case several LOAD DATA INFILE statements occur in parallel on the master. In that case, the binary log may contain intermixed events for the statements. The ID resolves which file the blocks in each APPEND_BLOCK_EVENT must be appended, and the file that must be loaded by the EXEC_LOAD_EVENT or deleted by the DELETE_FILE_EVENT.

  • 1 byte. The length of the field-terminating string (FIELDS TERMINATED BY option).

  • Variable-sized. The field-terminating string.

  • 1 byte. The length of the field-enclosing string (FIELDS ENCLOSED BY option).

  • Variable-sized. The field-enclosing string.

  • 1 byte. The length of the line-terminating string (LINES TERMINATED BY option).

  • Variable-sized. The line-terminating string.

  • 1 byte. The length of the line-starting string (LINES STARTING BY option).

  • Variable-sized. The line-starting string.

  • 1 byte. The length of the escaping string (FIELDS ESCAPED BY option).

  • Variable-sized. The escaping string.

  • 1 byte. Flags that indicate whether certain keywords are present in the statement:

    • DUMPFILE_FLAG =0x1 (unused; this flag appears to be a botch because it would apply to SELECT ... INTO OUTFILE, not LOAD DATA INFILE)

    • OPT_ENCLOSED_FLAG = 0x2 (FIELD OPTIONALLY ENCLOSED BY option)

    • REPLACE_FLAG = 0x4 (LOAD DATA INFILE REPLACE)

    • IGNORE_FLAG = 0x8 (LOAD DATA INFILE IGNORE)

  • 1 byte. The length of the name of the first column to load.

  • ...

  • 1 byte. The length of the name of the last column to load.

  • Variable-sized. The name of the first column to load (null-terminated).

  • ...

  • Variable-sized. The name of the last column to load (null-terminated).

  • Variable-sized. The name of the table to load (null-terminated).

  • Variable-sized. The name of the database that contains the table (null-terminated).

  • Variable-sized. The name of the file that was loaded (the original name on the master, not the name of the temporary file created on the slave) (null-terminated). The length of the data filename is not explicit in the event. However, it is null-terminated, so the length can be determined by reading to the null byte.

  • Variable-sized. The block of raw data to load. If the file size exceeds a threshold, additional APPEND_BLOCK_EVENT instances will follow, each containing a data block. The size of the raw data is the event size minus the size of all other parts.

Append_block_log_event/APPEND_BLOCK_EVENT

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

Contains data to be written to the data file for a LOAD DATA INFILE statement.

Fixed data part:

  • 4 bytes. The ID of the file to append this block to.

Variable data part:

  • The raw data to load. The raw data size is the event size minus the size of all the fixed event parts.

Execute_log_event/EXEC_LOAD_EVENT

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

Indicates the end of a successful LOAD DATA INFILE statement and that the data file should be loaded.

Fixed data part:

  • 4 bytes. The ID of the file to load.

Variable data part:

  • Empty

Delete_file_log_event/DELETE_FILE_EVENT

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

Indicates the end of an unsuccessful LOAD DATA INFILE statement and that the data file should not be loaded.

Fixed data part:

  • 4 bytes. The ID of the file to be deleted.

Variable data part:

  • Empty

Load_log_event/NEW_LOAD_EVENT

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

The format for this event is more complicated than for others, because it contains information about many LOAD DATA INFILE statement clauses.

LOAD_EVENT shares the Load_log_event class with NEW_LOAD_EVENT. The primary difference is that LOAD_EVENT allows only single-character field and line option values, whereas NEW_LOAD_EVENT allows multiple-character values. Each of these is encoded as a length followed by a string rather than as a single character. Because of that, NEW_LOAD_DATA does not have the flags byte that indicates whether each option is empty.

Fixed data part:

  • 4 bytes. The ID of the thread on the master that issued this LOAD DATA INFILE statement. Needed for temporary tables. This is also useful for a DBA for knowing who did what on the master.

  • 4 bytes. The time in seconds which the LOAD DATA INFILE took for execution. Only useful for inspection by the DBA.

  • 4 bytes. The number of lines to skip at the beginning of the file (corresponds to the IGNORE N LINES clause of LOAD DATA INFILE).

  • 1 byte. The length of the name of the table to load.

  • 1 byte. The length of the name of the database containing the table.

  • 4 bytes. The number of columns to load ((col_name,...) clause). Will be nonzero only if the columns to load were explicitly mentioned in the statement.

Variable data part:

  • 1 byte. The length of the field-terminating string (FIELDS TERMINATED BY option).

  • Variable-sized. The field-terminating string.

  • 1 byte. The length of the field-enclosing string (FIELDS ENCLOSED BY option).

  • Variable-sized. The field-enclosing string.

  • 1 byte. The length of the line-terminating string (LINES TERMINATED BY option).

  • Variable-sized. The line-terminating string.

  • 1 byte. The length of the line-starting string (LINES STARTING BY option).

  • Variable-sized. The line-starting string.

  • 1 byte. The length of the escaping string (FIELDS ESCAPED BY option).

  • Variable-sized. The escaping string.

  • 1 byte. Flags that indicate whether certain keywords are present in the statement:

    • DUMPFILE_FLAG =0x1 (unused; this flag appears to be a botch because it would apply to SELECT ... INTO OUTFILE, not LOAD DATA INFILE)

    • OPT_ENCLOSED_FLAG = 0x2 (FIELD OPTIONALLY ENCLOSED BY option)

    • REPLACE_FLAG = 0x4 (LOAD DATA INFILE REPLACE)

    • IGNORE_FLAG = 0x8 (LOAD DATA INFILE IGNORE)

  • 1 byte. The length of the name of the first column to load.

  • ...

  • 1 byte. The length of the name of the last column to load.

  • Variable-sized. The name of the first column to load (null-terminated).

  • ...

  • Variable-sized. The name of the last column to load (null-terminated).

  • Variable-sized. The name of the table to load (null-terminated).

  • Variable-sized. The name of the database that contains the table (null-terminated).

  • Variable-sized. The name of the file that was loaded (the original name on the master, not the name of the temporary file created on the slave). The length of the data filename is not explicit in the event. It is determined as the remaining characters to the end of the event.

There is no file ID or data block in the variable data part. The slave is supposed to request the file from the master in a separate connection.

Rand_log_event/RAND_EVENT

RAND() in MySQL generates a random number. A RAND_EVENT contains two seed values that set the rand_seed1 and rand_seed2 system variables that are used to compute the random number.

Fixed data part:

  • Empty

Variable data part:

  • 8 bytes. The value for the first seed.

  • 8 bytes. The value for the second seed.

User_var_log_event/USER_VAR_EVENT

Fixed data part:

  • Empty

Variable data part:

  • 4 bytes. the size of the user variable name.

  • The user variable name.

  • 1 byte. Non-zero if the variable value is the SQL NULL value, 0 otherwise. If this byte is 0, the following parts exist in the event.

  • 1 byte. The user variable type. The value corresponds to elements of enum Item_result defined in include/mysql_com.h (STRING_RESULT=0, REAL_RESULT=1, INT_RESULT=2, ROW_RESULT=3, DECIMAL_RESULT=4).

  • 4 bytes. The number of the character set for the user variable (needed for a string variable). The character set number is really a collation number that indicates a character set/collation pair.

  • 4 bytes. The size of the user variable value (corresponds to member val_len of class Item_string).

  • Variable-sized. For a string variable, this is the string. For a float or integer variable, this is its value in 8 bytes. For a decimal this value is a packed value - 1 byte for the precision, 1 byte for the scale, and $size - 2 bytes for the actual value. See the decimal2bin function in strings/decimal.c for the format of this packed value.

Format_description_log_event/FORMAT_DESCRIPTION_EVENT

This event occurs at the beginning of v4 binary log files. See Binary Log Versions for how it is used.

In MySQL 5.0 and up, all binary log files start with a FORMAT_DESCRIPTION_EVENT, but there will be a way to distinguish between a FORMAT_DESCRIPTION_EVENT created at mysqld startup and other FORMAT_DESCRIPTION_EVENT instances; such a distinction is needed because the first category of FORMAT_DESCRIPTION_EVENT (which means the master has started) should trigger some cleaning tasks on the slave. (Suppose the master died brutally and restarted: the slave must delete old replicated temporary tables.)

Fixed data part:

  • 2 bytes. The binary log format version. This is 4 in MySQL 5.0 and up.

  • 50 bytes. The MySQL server's version (example: 5.0.14-debug-log), padded with 0x00 bytes on the right.

  • 4 bytes. Timestamp in seconds when this event was created (this is the moment when the binary log was created). This value is redundant; the same value occurs in the timestamp header field.

  • 1 byte. The header length. This length - 19 gives the size of the extra headers field at the end of the header for other events.

  • Variable-sized. An array that indicates the post-header lengths for all event types. There is one byte per event type that the server knows about.

Variable data part:

  • Empty

Xid_log_event/XID_EVENT

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

CREATE TABLE t1 (a INT) ENGINE = INNODB;
START TRANSACTION;
INSERT INTO t1 VALUES (1);
COMMIT;

Fixed data part:

  • Empty

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

Begin_load_query_log_event/BEGIN_LOAD_QUERY_EVENT

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

Fixed data part:

  • 4 bytes. An ID for the data file. This is necessary in case several LOAD DATA INFILE statements occur in parallel on the master. In that case, the binary log may contain intermixed events for the statements. The ID resolves which file the blocks in each APPEND_BLOCK_EVENT must be appended to, and the file that must be loaded by the EXEC_LOAD_QUERY_EVENT or deleted by the DELETE_FILE_EVENT.

Variable data part:

  • Variable-sized. The first block of data to load. The size is the event size minus the size of all other fields in the event. If the file size exceeds a threshold, additional APPEND_BLOCK_EVENT instances will follow, each containing a data block.

Execute_load_query_log_event/EXECUTE_LOAD_QUERY_EVENT

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

Indicates the end of a successful LOAD DATA INFILE statement and that the data file should be loaded. It is similar to QUERY_EVENT, but before executing the statement, it substitutes the original filename in the statement with the name of the slave-side temporary file. The first 13 bytes of the fixed data part are the same as for QUERY_EVENT, as is the initial status variable block in the variable data part. See the description of that event type for additional information.

Fixed data part:

  • 4 bytes. The ID of the thread that issued this statement.

  • 4 bytes. The time in seconds that the statement took to execute.

  • 1 byte. The length of the name of the database which was the default database when the statement was executed.

  • 2 bytes. The error code resulting from execution of the statement on the master.

  • 2 bytes. The length of the status variable block.

  • 4 bytes. The ID of the file to load.

  • 4 bytes. The start position within the statement for filename substitution.

  • 4 bytes. The end position within the statement for filename substitution.

  • 1 byte. How to handle duplicates: LOAD_DUP_ERROR = 0, LOAD_DUP_IGNORE = 1, LOAD_DUP_REPLACE = 2

Variable data part:

  • Zero or more status variables. Each status variable consists of one byte identifying the variable stored, followed by the value of the variable.

  • Variable-sized. The database name (null-terminated).

  • The LOAD DATA INFILE statement. The length is the event size minus the size of all other fields.

Table_map_log_event/TABLE_MAP_EVENT

Used for row-based binary logging beginning with MySQL 5.1.5.

Fixed data part:

  • 6 bytes. The table ID.

  • 2 bytes. Reserved for future use.

Variable data part:

  • 1 byte. The length of the database name.

  • Variable-sized. The database name (null-terminated).

  • 1 byte. The length of the table name.

  • Variable-sized. The table name (null-terminated).

  • Packed integer. The number of columns in the table.

  • Variable-sized. An array of column types, one byte per column.

  • Packed integer. The length of the metadata block.

  • Variable-sized. The metadata block; see log_event.h for contents and format.

  • Variable-sized. Bit-field indicating whether each column can be NULL, one bit per column. For this field, the amount of storage required for N columns is INT((N+7)/8) bytes.

Write_rows_log_event_old/PRE_GA_WRITE_ROWS_EVENT

Used for row-based binary logging from MySQL 5.1.5 to 5.1.17, using the old implementation of Write_rows_log_event/WRITE_ROWS_EVENT. The structure is similar to that for the newer event.

Update_rows_log_event_old/PRE_GA_UPDATE_ROWS_EVENT

Used for row-based binary logging from MySQL 5.1.5 to 5.1.17, using the old implementation of Update_rows_log_event/UPDATE_ROWS_EVENT. The structure is similar to that for the newer event.

Delete_rows_log_event_old/PRE_GA_DELETE_ROWS_EVENT

Used for row-based binary logging from MySQL 5.1.5 to 5.1.17, using the old implementation of Delete_rows_log_event/DELETE_ROWS_EVENT. The structure is similar to that for the newer event.

Write_rows_log_event/WRITE_ROWS_EVENT

Used for row-based binary logging beginning with MySQL 5.1.18.

[TODO: following needs verification; it's guesswork]

Fixed data part:

  • 6 bytes. The table ID.

  • 2 bytes. Reserved for future use.

Variable data part:

  • Packed integer. The number of columns in the table.

  • Variable-sized. Bit-field indicating whether each column is used, one bit per column. For this field, the amount of storage required for N columns is INT((N+7)/8) bytes.

  • Variable-sized (for UPDATE_ROWS_LOG_EVENT only). Bit-field indicating whether each column is used in the UPDATE_ROWS_LOG_EVENT after-image; one bit per column. For this field, the amount of storage required for N columns is INT((N+7)/8) bytes.

  • Variable-sized. A sequence of zero or more rows. The end is determined by the size of the event. Each row has the following format:

    • Variable-sized. Bit-field indicating whether each field in the row is NULL. Only columns that are "used" according to the second field in the variable data part are listed here. If the second field in the variable data part has N one-bits, the amount of storage required for this field is INT((N+7)/8) bytes.

    • Variable-sized. The row-image, containing values of all table fields. This only lists table fields that are used (according to the second field of the variable data part) and non-NULL (according to the previous field). In other words, the number of values listed here is equal to the number of zero bits in the previous field (not counting padding bits in the last byte).

      The format of each value is described in the log_event_print_value() function in log_event.cc.

    • (for UPDATE_ROWS_EVENT only) the previous two fields are repeated, representing a second table row.

For each row, the following is done:

  • For WRITE_ROWS_LOG_EVENT, the row described by the row-image is inserted.

  • For DELETE_ROWS_LOG_EVENT, a row matching the given row-image is deleted.

  • For UPDATE_ROWS_LOG_EVENT, a row matching the first row-image is removed, and the row described by the second row-image is inserted.

Update_rows_log_event/UPDATE_ROWS_EVENT

Used for row-based binary logging beginning with MySQL 5.1.18.

See the description for WRITE_ROWS_EVENT.

Delete_rows_log_event/DELETE_ROWS_EVENT

Used for row-based binary logging beginning with MySQL 5.1.18.

See the description for WRITE_ROWS_EVENT.

Incident_log_event/INCIDENT_EVENT

Fixed data part:

  • 1 byte. The incident number.

  • 1 byte. The message length.

Variable data part:

  • The incident message, if present.

Incident number codes are listed in rpl_constant.h. The only code currently used is INCIDENT_LOST_EVENTS, which indicates that there may be lost events (a "gap") in the replication stream that requires databases to be resynchronized.

Heartbeat_log_event/HEARTBEAT_LOG_EVENT

A Heartbeat_log_event is sent by a master to a slave to let the slave know that the master is still alive. Events of this type do not appear in the binary or relay logs. They are generated on a master server by the thread that dumps events and sent straight to the slave without ever being written to the binary log. The slave examines the event contents and then discards it without writing it to the relay log.

Fixed data part:

  • Empty

Variable data part:

  • Empty

Muted_query_log_event

This is a subclass of Query_log_event that is not written to the log. It is used as a means of flushing a transaction without logging an event.

This event class was added in MySQL 5.0.23 and removed in 6.0.4. It was a solution to Bug#16206 that became unnecessary with the fix for Bug#29020.

20.9.1. LOAD DATA INFILE Events

LOAD DATA INFILE is not written to the binary log like other statements. It is written as one or more events in a packed format, not as a cleartext statement in the binary log. The events indicate what options are present in the statement and how to process the data file.

Historically, there seem to have been at least four event sequences for representing LOAD DATA INFILE operations.

1) In MySQL 3.23, there was only one event: Load_log_event (type code LOAD_EVENT = 6). Load_log_event only contains the filename, not the file itself. When the slave sees a Load_log_event, it requests that the master send the file in a separate connection. This has the drawback that the binary log is not self-contained: If the file has been removed on the master or the slave cannot access the master, the file transfer fails.

2) In MySQL 4.0.0, the file contents were included in the binary log. Several new event types were introduced: Create_file_log_event (type code CREATE_FILE_EVENT = 8), Append_block_log_event (type code APPEND_BLOCK_EVENT = 9), Execute_load_log_event (type code EXEC_LOAD_EVENT = 10), and Delete_file_log_event (type code DELETE_FILE_EVENT = 11). The event sequence is:

  • Create_file_log_event : 1 instance

  • Append_block_log_event : 0 or more instances

  • Execute_load_log_event (success) or Delete_file_log_event (failure): 1 instance

The Create_file_log_event contains the options to LOAD DATA INFILE. This was a design flaw since the file cannot be loaded until the Exec_load_log_event is seen. To work around this, the slave, when executing the Create_file_log_event, wrote the Create_file_log_event to a temporary file. When the Execute_load_log_event was seen, this temporary file was read back so that the LOAD DATA INFILE statement could be constructed.

Append_block_log_event is used for files larger than a threshold. In this case, the file is split and the pieces are sent in separate events. The threshold is around 2^17 = 131072 bytes.

CREATE_FILE_EVENT tells the slave to create a temporary file and fill it with a first data block. Later, zero or more APPEND_BLOCK_EVENT events append blocks to this temporary file. EXEC_LOAD_EVENT tells the slave to load the temporary file into the table, or DELETE_FILE_EVENT tells the slave not to do the load and to delete the temporary file. DELETE_FILE_EVENT occurs when the LOAD DATA failed on the master: On the master we start to write loaded blocks to the binary log before the end of the statement. If for some reason there is an error, we must tell the slave to abort the load.

MySQL 4.0.0 also introduced the NEW_LOAD_EVENT = 12 type code.

If a slave reads a NEW_LOAD_EVENT from a binlog, it will use it as a LOAD_EVENT (but allowing longer separator names). Lost in the mysteries of time is the knowledge of whether there was ever a server version capable of writing NEW_LOAD_EVENT.

3) The original Load_log_event used one character for each of the delimiters (FIELDS TERMINATED BY, and so forth). At an unknown point in the version history, the format was modified to allow multiple-character strings as separators. This uses the same class, Load_log_event, but has the type code NEW_LOAD_EVENT = 12. This affects Create_file_log_event, since that inherits from <code class="literal">Load_log_event. So the new feature in Load_log_event allows Create_file_log_event to use multiple-character delimiters.

[A guess for when this occurred would be MySQL 4.0.0: That is when the sql_ex structure that holds the single-character field/line options was renamed to old_sql_ex and a new sql_ex structure was created that allows multiple-character values.]

4) In 5.0.3, the event sequence was changed again with the addition of two new event types: Begin_load_query_log_event (type code BEGIN_LOAD_QUERY_EVENT = 17) and Execute_load_query_log_event (type code EXECUTE_LOAD_QUERY_EVENT = 18). The event sequence is:

  • Begin_load_query_log_event : 1 instance

  • Append_block_log_event : 0 or more instances

  • Execute_load_query_log_event (success) or Delete_file_log_event (failure): 1 instance

With the new sequence, information about the options to LOAD DATA INFILE is moved from the first event to the last event. Consequently, Begin_load_query_log_event is almost the same as Append_file_log_event (it contains only file data), whereas Execute_load_query_log_event contains the text of the LOAD DATA INFILE statement. The revised event sequence fixes the design flaw in the 4.0 format.

Also, the temp file that stores the parameters to LOAD DATA INFILE is not needed anymore. There is still a temp file containing all the data to be loaded.

Here is a concrete example (it applies to MySQL 4.0 and 4.1):

On the master we have a file named /m/tmp/u.txt that contains these lines: 

>1,2,3
>4,5,6
>7,8,9
>10,11,12

And we issue this statement on the master while the default database is test: 

load data infile '/m/tmp/u.txt' replace into table x fields
terminated by ',' optionally enclosed by '"' escaped by '\\'
lines starting by '>' terminated by '\n' ignore 2 lines (a,b,c);

Then in the master's binary log we have this CREATE_FILE_EVENT (hexadecimal dump): 

00000180:                     db4f 153f 0801 0000  .........O.?....
00000190: 006f 0000 0088 0100 0000 0004 0000 0000  .o..............
000001a0: 0000 0002 0000 0001 0403 0000 0003 0000  ................
000001b0: 0001 2c01 2201 0a01 3e01 5c06 0101 0161  ..,."...>.\....a
000001c0: 0062 0063 0078 0074 6573 7400 2f6d 2f74  .b.c.x.test./m/t
000001d0: 6d70 2f75 2e74 7874 003e 312c 322c 330a  mp/u.txt.>1,2,3.
000001e0: 3e34 2c35 2c36 0a3e 372c 382c 390a 3e31  >4,5,6.>7,8,9.>1
000001f0: 302c 3131 2c31 32db 4f15 3f0a 0100 0000  0,11,12.O.?.....
00000200: 1700 0000 f701 0000 0000 0300 0000       ..............

  • Line 180:

    • Timestamp (db4f 153f)

    • Event type (08)

    • Server ID (01 0000 00)

  • Line 190:

    • Event size (6f 0000 00)

    • Position in the binary log (88 0100 00) (that's 392 in decimal base)

    • Flags (00 00)

    • Thread ID (04 0000 00)

    • Time it took (00 0000 00)

  • Line 1a0:

    • Number of lines to skip at the beginning of the file (02 0000 00)

    • Length of the table name (01)

    • Length of the database name (04)

    • Number of columns to load (03 0000 00)

    • The file ID (03 0000 00)

  • Line 1b0:

    • Length of the field terminating string (01)

    • Field terminating string (2c = ,)

    • Length of the field enclosing string (01)

    • Field enclosing string (22 = ")

    • Length of the line terminating string (01)

    • Line terminating string (0a = newline)

    • Length of the line starting string (01)

    • Line starting string (3e = >)

    • Length of the escaping string (01)

    • Escaping string (5c = \)

    • Flags (06) (that's OPT_ENCLOSED_FLAG | REPLACE_FLAG)

    • Length of the name of the first column to load (01)

    • Length of the name of the second column to load (01)

    • Length of the name of the third column to load (01)

    • Name of the first column to load (61 00 = "a")

  • Line 1c0:

    • Name of the second column to load (62 00 = "b")

    • Name of the third column to load (63 00 = "c")

    • Name of the table to load (78 00 = "x"), name of the database that contains the table (74 6573 7400 = "test")

    • Name of the file on the master (2f6d 2f74 6d70 2f75 2e74 7874 00 = "/m/tmp/u.txt")

  • Line 1d0 and following:

    • Raw data to load (3e 312c 322c 330a 3e34 2c35 2c36 0a3e 372c 382c 390a 3e31 302c 3131 2c31 32)

The next byte in the file is the beginning of the EXEC_LOAD_EVENT event.

Copyright © 1997, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices