Shulou(Shulou.com)06/01 Report--

X-Engine is a high-performance and low-cost storage engine developed by Alibaba. After years of efforts, we have supported many businesses within the group in the form of AliSQL (X-Engine) (AliSQL is Ali's MySQL branch), bringing significant costs and performance benefits to users.

Up to now, Alibaba database team has submitted many valuable bug and repair plans to MySQL officials. We inherit this fine tradition, encounter MySQL-related problems in production and testing, always actively think about solutions, and quickly communicate with the authorities to contribute to the development of the open source community.

Below we will introduce a MySQL problem and its fix that we have just discovered. Friends who encounter the same situation need to pay attention, perhaps the data that does not conform to the specification has been written into your database.

Background knowledge

If the MySQL parameter sql_mode contains the following 3 items:

NO_ZERO_DATE

NO_ZERO_IN_DATE

STRICT_TRANS_TABLES

Inserting '0000-00' or 0 in any part of the year / month / day 3 column into the DATE type column will fail.

Exception: '0000-0000' was inserted successfully

Execute the following statement in sequence on MySQL 8.0.16:

Set sql_mode=''

Create table test (mydate DATE NOT NULL DEFAULT '0000-0000')

Set sql_mode=default

Show variables like "sql_mode"

Insert into test values ()

Select * from test

The purpose of setting sql_mode to empty is to set the default value of mydate to '0000-00-00' when creating the table, otherwise the table will fail because the default value does not conform to NO_ZERO_DATE.

Set sql_mode back to default after successfully creating the table, including:

ONLY_FULL_GROUP_BY

NO_ZERO_DATE

NO_ZERO_IN_DATE

STRICT_TRANS_TABLES

ERROR_FOR_DIVISION_BY_ZERO

NO_ENGINE_SUBSTITUTION

However, at this time, a '0000-00' DATE was successfully inserted into the test library. Obviously, the semantics of NO_ZERO_DATE have been broken.

Peeling off the cocoon, this is the problem.

First, we navigate to the MySQL insertion path to check whether default value is legal.

This function is relatively simple to find out the columns that the user insert lists does not contain and have default value, and check whether their default value is legal. Write_set is a bitmap that identifies which columns are contained in the user's insert lists.

I added a breakpoint to the function with gdb to execute the above case, only to find that all bit in the write_set is set to 1. This is obviously an abnormal phenomenon, my insert SQL statement insert into test values (); insert list is clearly empty, write_set is all 0 is reasonable. It seems that some function has modified it incorrectly.

So I added a watchpoint to the address of write_set with gdb and re-executed the insert statement. This time it locates to the place where the write_set is modified:

This function is executed before checking whether default value is legal, and its effect is that when binlog_format is ROW and binlog_row_image is FULL, write_set is all set to 1.

The parameter binlog_format specifies the binlog format and has three options:

ROW represents synchronization between master and standby through log_event.

STATEMENT represents synchronization between master and standby through SQL statements.

MIXED is a mixed format, using STATEMENT by default and ROW in some special cases.

Because the master / slave is synchronized through STATEMENT (although it produces a small number of binlog), the results may be inconsistent due to different context information, environment, and other factors, so for security reasons, binlog_ format defaults to ROW.

The parameter binlog_row_image specifies what information to record in the ROW format binlog. It also has three options:

FULL indicates that binlog records all columns before and after changes

MINIMAL indicates that binlog only records unique identity columns and modification columns

NOBLOB indicates that BLOB is a modified column or a unique identification column before it is recorded, and all other columns are the same as FULL.

Binlog_row_image defaults to FULL.

When binlog_format is ROW and binlog_row_image is FULL, write_set is all set to 1 in order to ensure that all columns are written to binlog.

The write_set variable is originally used to identify the user to insert the column, and has been given the important task of controlling the writing of binlog. Multiple semantics are intertwined, and it is easy to produce bug. This also gives us a hint of coding: each variable should have an exact semantics.

Repair suggestion

The reason for this bug is that write_set is too useful. So you can create a new bitmap:binlog_write_set specifically for controlling write binlog.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.