ROWS_EVENT_V1
A ROWS_EVENT_V1 is written for row based replication if data is inserted, deleted or updated:
Event types
WRITE_ROWS_EVENT_V1 | Insert new row |
UPDATE_ROWS_EVENT_V1 | Update existing row |
DELETE_ROWS_EVENT_V1 | Delete existing row |
Header
- WRITE_ROWS_EVENT_V1: Event Type is 23 (0x17)
- UPDATE_ROWS_EVENT_V1: Event Type is 24 (0x18)
- DELETE_ROWS_EVENT_V1: Event Type is 25 (0x19)
Fields
- uint<6> The table id
- uint<2> Flags
- uint<lenenc> Number of columns
- byte<n>Columns used. n = (number_of_columns + 7)/8
- if (event_type == UPDATE_ROWS_EVENT_v1
- byte<n> Columns used (Update). n = (number_of_columns + 7)/8
- byte<n> Null Bitmap (n = (number_of_columns + 7)/8)
- string<len> Column data. The length needs to be calculated by checking the column types from referring TABLE_MAP_EVENT.
- if (event_type == UPDATE_ROWS_EVENT_v1
- byte<n> Null Bitmap_Update. n = (number_of_columns + 7)/8
- string<len> Update Column data. The length needs to be calculated by checking the used colums bitmap and column types from referring TABLE_MAP_EVENT.
Flags
0x0001 | End of statement |
0x0002 | No foreign key checks |
0x0004 | No unique key checks |
Column Data Formats
The row data is stored in a packed format where each field is encoded in a particular format. The encoding is almost identical to the binary protocol but there are a few differences.
Simple Types
MYSQL_TYPE_NULL | Bit set in null bitmap, no value in row data |
MYSQL_TYPE_TINY | 1 byte integer |
MYSQL_TYPE_SHORT | 2 byte integer |
MYSQL_TYPE_YEAR | 2 byte integer |
MYSQL_TYPE_INT24 | 3 byte integer |
MYSQL_TYPE_LONG | 4 byte integer |
MYSQL_TYPE_LONGLONG | 8 byte integer |
MYSQL_TYPE_FLOAT | 4 byte floating point value (stored as a C float type) |
MYSQL_TYPE_DOUBLE | 8 byte floating point value (stored as a C double type) |
MYSQL_TYPE_BLOB
and other blob types
Stored as a length-encoded string where the string is preceded by a variable-sized integer that stores the length of the blob. The size of the preceding integer in bytes is stored as a one byte integer in the table metadata that is a part of the table map event.
For example if the value 4 is stored in the table metadata the length is stored as a 4 byte integer (e.g. uint32_t
) followed by the data.
MYSQL_TYPE_VARCHAR
and other string types
Stored as a length-encoded string where the string is preceded by a variable-sized integer that stores the length of the string. The field length is stored as a two byte integer in the table metadata.
If the field length is larger than 255, the string length is stored as a two byte integer. If the value is equal to or less than 255, the string length is stored as a one byte integer.
Example From mysqlbinlog Utility, CRC32
# at 1680 #180611 9:50:51 server id 1 end_log_pos 1754 CRC32 0x5415a8fb Write_rows: table id 23 flags: STMT_END_F BINLOG ' 2ykeWxMBAAAAPgAAAJAGAAAAABcAAAAAAAEABHRlc3QACWJ1bGtfbnVsbAAFDwMFE/YGFAAIAAMB H1bULg8= 2ykeWxcBAAAASgAAANoGAAAAABcAAAAAAAEABf/gATMDAAAAAAAAAAAACECAAACDAP/gATMDAAAA AAAAAAAACECAAACDAPuoFVQ= '/*!*/;
Example Event As It's Written In The Binlog File
db 29 1e 5b 17 01 00 00 00 4a 00 .).[.....J. 00 00 da 06 00 00 00 00 17 00 00 00 00 00 01 00 ................ 05 ff e0 01 33 03 00 00 00 00 00 00 00 00 00 08 ....3........... 40 80 00 00 83 00 ff e0 01 33 03 00 00 00 00 00 @........3...... 00 00 00 00 08 40 80 00 00 83 00 fb a8 15 54 .....@........T ....