ROWS_EVENT_V1

You are viewing an old version of this article. View the current version here.

A ROWS_EVENT_V1 is written for row based replication if data is inserted, deleted or updated:

Event types

WRITE_ROWS_EVENT_V1Insert new row
UPDATE_ROWS_EVENT_V1Update existing row
DELETE_ROWS_EVENT_V1Delete existing row
  • 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

0x0001End of statement
0x0002No foreign key checks
0x0004No 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_NULLBit set in null bitmap, no value in row data
MYSQL_TYPE_TINY1 byte integer
MYSQL_TYPE_SHORT2 byte integer
MYSQL_TYPE_YEAR2 byte integer
MYSQL_TYPE_INT243 byte integer
MYSQL_TYPE_LONG4 byte integer
MYSQL_TYPE_LONGLONG8 byte integer
MYSQL_TYPE_FLOAT4 byte floating point value (stored as a C float type)
MYSQL_TYPE_DOUBLE8 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.

MYSQL_TYPE_DATETIME

Stored as a 8 byte value with the values stored as multiples of 100. This means that the stored value is in the format YYYYMMDDHHMMSS and can be easily extracted by repeatedly calculating the remainder of dividing the value by 100 and dividing the value by 100. The following pseudo-code demonstrates extracting the value.

value = read_8_byte_value(row_data)
seconds = value % 100
value = value / 100
minutes = value % 100
value = value / 100
hours = value % 100
value = value / 100
days = value % 100
value = value / 100
months = value % 100
value = value / 100
years = value

MYSQL_TYPE_TIME

Stored as a 3 byte value with the values stored as multiples of 100. This means that the stored value is in the format HHMMSS and can be easily extracted the same way a MYSQL_TYPE_DATETIME is extracted. The following pseudo-code demonstrates extracting the value.

value = read_3_byte_value(row_data)
seconds = value % 100
value = value / 100
minutes = value % 100
value = value / 100
hours = value

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

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.