The little thing about Binlog (iii) (MYSQL5.7.13)

This time we are going to explore finer binlog content, and the last discussion of Query_event and Rows_event certainly has questions that make you puzzled. What are the Status-vars environment variables in query_event, what are the data types of rows_event, and what is the meta-data, and today we will answer each one.

First, the puzzling status-vars in the query_event

Status-vars is said to be in order to be compatible with the lower version of the MySQL server to join the environment variable settings, to tell the lower version of the MySQL server SQL statement is executed under what circumstances

The specific format is the State enumeration value + state parameter

Here are some more important states with State enumeration values:

(1) 0x00 q_flags2_code 4 bytes

This is mainly for Sql_auto_is_null, Foreign_key_checks, Unique_checks, autocommit settings, Sql_auto_is_null is auto-populated Null,foreign_key_ Checks is a foreign key check, Unique_checks is a unique key index check, AUTOCOMMIT is automatically submitted, here Sql_auto_is_null = 1, the others are 0.

(2) 0x01 q_sql_mode_code 8 bytes

Here is the configuration to refer to SET sql_mode : http://dev.mysql.com/doc/refman/5.7/en/set-variable.html, there are a large number of SQL environment variables

The corresponding status list can be referenced Http://dev.mysql.com/doc/internals/en/query-event.html#q-sql-mode-code

You can refer to the following Chinese translation:

1) Single mode:

2. Allow_invalid_dates: If the item is set, MySQL checks for the month and day of the date. Any date that satisfies 0<month<13, the 0<day<32 integer can become the insertion value.
4. Ansi_quotes: If the item is set, MySQL treats the quotation mark "" "as a quotation mark instead of a string symbol.
6. Error_for_division_by_zero: If set, MySQL inserts any number divided by 0 after the number is null.
8. High_not_precedence: If the key is set, the priority value of the NOT operator is dropped, as MySQL resolves not a between B and C to not (a between B and C)
10. Ignore_space: If the item is set, MySQL will allow the function name and "(" can have spaces.)
12. No_auto_create_user: If set, MySQL prevents new users from being automatically generated when the GRANT statement executes.
14. No_auto_value_on_zero: If the item is set, MySQL only generates the next sequence number for NULL when it processes the Auto_increment column.
16. No_backslash_escapes: If the item is set, MySQL cannot use "\" as the escape character.
18. No_dir_in_create: If the item is set, MySQL ignores all index directory and data directory when the table is being built.
20. No_engine_substitution: If the item is set, MySQL will make an error on the engine that cannot be used when the CREATE TABLE or ALTER TABLE, but does not do the actual operation.
22. No_field_options is only valid for the Show CREATE Table command.
24. No_key_options Ibid.
26. No_table_options Ibid.
28. No_unsigned_subtraction: If this setting is set, MySQL does not limit the number of negative numbers that are updated to unsingned Int.
30. No_zero_date: If the item is set and strict SQL mode is set, MySQL will not be able to insert the update for the date 0000-00-00.
32. No_zero_in_dat: If the key is set and strict SQL mode is set, MySQL cannot insert updates for dates with a value of 0 (except 0000-00-00).
34. Only_full_group_by was added in 5.75 and has no impact on this development.
36. Pad_char_to_full_length: If the item is set, MySQL does not reclaim extra CHAR space in the retrieval.
38. Pipes_as_concat: If this setting, MySQL will put | | Regarded as concat ().
40. Real_as_float: If this setting is true, MySQL will treat real as a FLOAT.
42. Strict_all_tables: If the item is set, MySQL will enter the strict SQL mode.
44. Strict_trans_tables: If the item is set, MySQL will enter a strict SQL mode, only valid for the transaction.

2) Mixed mode:

1. ANSI: The item if set, and Real_as_float, Pipes_as_concat, Ansi_quotes, ignore_space equivalent, in

2. DB2: If the setting is set, the equivalent is set together with Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, No_field_options.

3. MAXDB: If the item is set, with Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, No_field_options, NO_ Auto_create_user set the equivalence together.

4. MSSQL: If the item is set, Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, No_field_options. Set the equivalence together.

5. Traditiona: If the item is set, with Strict_trans_tables, Strict_all_tables, No_zero_in_date, No_zero_date, Error_for_division_by_ ZERO, No_auto_create_user, no_engine_substitution

6. POSTGRESQL: If set, with Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, No_field_ Options to set the equivalence together.

7. ORACLE: If set, with Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, No_field_ Options to set the equivalence together.

8. MYSQL323: only affects show CREATE table.

9. MYSQL40: Ibid.

(3) 0x02 Q_catalog

The default record is "\x3std\x0", which is the directory used by the MySQL server. One-byte length +null string

(4) 0x03 q_auto_increment

Related to Auto_increment, 4 bytes, the first 2 bytes represent Auto_increment, and the last 2 bytes represent Auto_increment_offset

(5) 0x04 Q_charset_code

Character set at connection time (2 bytes) + Character set at connection proofing (2 bytes) + Character set at server proofreading (2 bytes)

(6) 0x05 Q_time_zone_code

Time zone One byte length + string, such as "\x6+08:00" is Beijing time, this is to be confirmed.

(7) 0x07 Q_lc_time_names_code

The names of the weeks and months, for example: in the English language of Monday is Mon, January is Jan, in other words is not so.

Ii. rows_event data types and meta-data

This is just about common data types

(1) Date and time type

1) metadata represents a value that can be accurate to microseconds N is typically 0-3

There is a corresponding definition in my_time.h with this type.

Mysql_type_datetime2, which is stored with Longlong data, is fixed as 4-bit +n, and the metadata represents the length of the extra data n

1 //from mysql5.7.13 source my_time.c2 //Convert longlong time into a string3 //longlong time Format:4 //Bitwise Calculation5 //1 bit sign (used if on disk)6 //$ Bits Year * + month (year 0-9999, month 0-12)7 //5 bits Day (0-31)8 //5 bits Hour (0-23)9 //6 bits minute (0-59)Ten //6 bits second (0-59) One //bits microseconds (0-999999) A // - //total:64 bits = 8 bytes - // the //syyyyyyy. Yyyyyyyy. YYDDDDDH.HHHHMMMM.MMSSSSSS.FFFFFFFF.FFFFFFFF.FFFFFFFF

View Code

Mysql_type_time2, which is also stored with Longlong data, is fixed as 3-bit +n, and the metadata represents the length of the extra data n

MYSQL_TYPE_TIMESTAMP2, is the timestamp, fixed is 4 bits, the metadata represents the length of the extra data n

Mysql_type_newdate is also stored in this way, but there is no meta-data

2) There is no metadata, yyyymmddhhmmss stored in decimal

Mysql_type_timestamp,mysql_type_datetime are stored in this way.

Mysql_type_time is a time stamp.

3) Mysql_type_year, there is no meta data.

This is a 1900-year-start.

(2) shaping, no meta-data

Mysql_type_tiny, Mysql_type_short, Mysql_type_int24,mysql_type_long,mysql_type_longlong and the way we understand it, it's just small-end data.

(3) Real type

Mysql_type_float and mysql_type_double are also stored by small-end data, but they have a byte of metadata whose metadata represents the length of their valid data.

Mysql_type_newdecimal This fractional storage method is not very clear, only in the decimal.h is defined, but his metadata first byte represents the length of his valid data, the second represents his precision is the length of the decimal point

(4) String type

Mysql_type_varchar his two-byte metadata is his longest length.

Mysql_type_string,mysql_type_var_string their metadata the first byte represents the true type, while the second byte represents the size of the byte number that stores the string length

(5) Bit type

Mysql_type_blob His metadata stores the byte size of bit type length

Mysql_type_bit His metadata store the first byte represents how many bits, while the second byte represents the size of the byte number that stores the bit type

To this end, we can conclude that metadata stores more of this type of length information.

For more details, please see the mysql5.7.13 source Log_event.cc:1953::log_event_print_value () for more information.

The little thing about Binlog (iii) (MYSQL5.7.13)