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

1 |preface

Recently, when solving problems for development partners, I collected two interesting problems for everyone to share. One is a Duplicate entry error reported when executing the ALTER TABLE ADD COLUMN statement; the other is a problem with correctly accessing emoji emojis in MySQL.

2 |ALTER TABLE ADD COLUMN REPORT Duplicate entry error 2.1 Description

One day, when the system went online, I received a call from my development partner saying that when I went online, I executed a DDL statement script with an added field, and an error was reported. The error is as follows:

ERROR 1062 (23000) at line 1: Duplicate entry 'UR000021426347' for key 'T_CAP_CUST_MIDDLE_INFO_UNIQ_INDEX'

According to the error prompt conditions to query the database can only find a record, and there is no duplicate record. DDL script could not be executed, affecting subsequent online steps. At that time, due to absence from the scene, the only information learned was:

There are only two DDL statements in the DDL statement script, and both are statements that add fields.

It takes only 40-70 seconds for the script to run properly.

It was not a closed window.

Considering that the database version is 5.6.34, adding fields does not block DML operations. Let the development partner try again. The result is successful this time, and no conflict error is reported. The online problem was solved smoothly, and the specific reasons had to be analyzed offline. Although solving the problem is the main contradiction, figuring out the cause of the problem has a deeper significance.

2.2 cause location

Here's why it's time to find out why the same DDL statement script reported a Duplicate entry error the first time it was executed, but ran smoothly the second time around. In fact, the cause of the problem is very easy to find, open Google, enter the keyword mysql alter table add column duplicate entry, search results in a lot of keywords exactly match the link, indicating that many people have encountered the same problem. MySQL Bugs:#76895:Adding new column OR Drop column causes duplicate PK error. Through the Bug link to understand that the problem is a limitation of Online DDL, the official believes that the problem is a limitation, not a Bug, so far has not been resolved.

When running an online DDL operation, the thread that runs the ALTER TABLE statement applies an online log of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the online log. This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction.

To explain, when performing Oline DDL operations, MySQL actually stores the DML cache (the cache size is controlled by the variable innodb_online_alter_log_max_size, the default is 128M), and then reapplies the DML cache to the table after DDL execution is complete. If another thread performs the DML operation, duplicate entry errors may occur when applying DML after DDL completion.

2.3 experimental verification

The above through Google found a theoretical explanation may explain the cause of the problem description, but has not been verified in practice, so the next step is the offline reproduction link. First go to the development colleagues asked offline report the wrong table only one operation insert into... on duplicate key... There is a unique index on the Duplicate entry field. Insert if there are no conflicting records, otherwise update. Then the verification test steps are also relatively simple, find a test table, execute the ALTER TABLE ADD COLUMN operation, and at the same time execute insert into... on duplicate key... Operation to see if DDL statements report errors.

2.3.1 Experimental environment

redhat-6.7/redhat-7.4

MySQL-5.6.34/MySQL-5.7.22

2.3.2 Operation steps

Prepare the test environment.

The value of col1 field is consistent with the value of id field. There are 1600W + records in test table.

Execute DDL while executing insert into... on duplicate key... Operation.

The problem on the line was successfully reproduced, which means that the line failed to execute DDL because DML updated the same unique attribute field key value, and an error was reported. In the process of testing think insert into... on duplicate key... No, so replace into will also cause problems, so the replace into statement is also tested.

Execute DDL and perform replace into operation simultaneously.

Later, UPDATE and INSERT operations were tested in the same way, and experiments proved that both of them affected the correct execution of DDL operations, and the same phenomenon was observed in version 5.7.22. For the sake of space, I won't give you the test procedure here. Interested readers can experiment with it and verify whether the problem still exists in version 8.0.

2.4 summary

Here are a few things to summarize about this problem:

Reason for the problem: The principle of Oline DDL is simple to understand. It is to cache DML operations and reapply the DML statements in the cache after DDL execution is completed. If the DML operation generates a Duplicate entry error during Oline DDL execution, it will not directly affect the DDL operation. Instead, it will report an error when DDL execution is completed and finally apply DML, resulting in DDL execution failure. The Oline DDL implementation steps can be found at www.example.com. https://yq.aliyun.com/articles/282290

On the MySQL Bug website, officials replied that the phenomenon was not a Bug, but a restriction. But personally I think there are some improvements that can be made because in the test insert into... on duplicate key... and update and insert statements, the client executing the DML operation has returned an error directly, but MySQL still places the DML statement reporting the error into the cache of Oline DDL. If the error statement is directly removed from the cache, the normal execution of DDL will not be affected. This is just a simple personal view, in-depth words need to be confirmed by code.

Google is a good teacher.

3 |MySQL correct posture for accessing emoji 3.1 problem description

Development partners in the test environment testing process, need to insert emoji expression into MySQL database, but found that a set of test environment can be inserted successfully, another set of test environment can not, and inserted successfully into the set of environment database to query the data is??? garbled. After confirmation with the development, we learned the following information:

There is only one field in the table that needs to store emoji, and the developer has set utf8mb4 character set separately for this field.

The test environment database version that can be successfully inserted is 5.6.34, and the test environment database version that cannot be inserted is 5.7.22.

The utf8 character set is used in the application connection code.

In fact, the problem that needs to be solved is actually two problems: 1, the problem of emoji accessing garbled code under version 5.6, and 2, the problem that emoji cannot be inserted in version 5.7.

3.2 Experiment verification 3.2.1 MySQL- 5.6 emoji access garbled code problem.

Let's first look at the problem of storing garbled characters in emoji under version 5.6. Theoretically, from the database point of view, the field character set has been set to utf8mb4, and the application uses utf8 character set connection. Inserting emoji requires utf8mb4 character set, mostly the problem of connecting character set setting. The bold hypothesis is complete, and the process of careful verification follows.

experimental environment

Database version: 5.6.44

sql_mode='NO_ENGINE_SUBSTITUTION'

default-character-set=utf8

Connect to MySQL using the utf8 character set and check the parameter settings.

Create tests.

Insert emoji.

The emoji can indeed be inserted into the database, but there are two warning records indicating invalid strings when inserting, and the data queried by select is also the same garbled.

Insert emoji emoji and query using utf8mb4 character set.

You can see that the connection uses utf8mb4 character set to insert and query emoji expressions are normal, and there is no warning prompt when inserting, which means that the problem of emoji garbled code is caused by unreasonable connection character set settings.

3.2.2 MySQL-5.7 emoji cannot be inserted

After locating the problem of garbled code, let's look at the problem that emoji cannot be inserted.

experimental environment

Database version: 5.7.22

sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

default-character-set=utf8

Connect to MySQL using the utf8 character set and check the parameter settings.

Create tests.

Insert emoji.

When inserting, it directly reported an error. For the same character set and the same sentence, emoji can be inserted under version 5.6, and 5.7 directly reported an error. 5.6->5.7 database version problem, if you step on more pits, it is easier to associate with the sql_mode parameter problem, because the default value of sql_mode parameter in 5.7 has many items, and the restrictions on statements have been strengthened a lot.

Check the default values for the sql_mode parameter. mysql> show variables like '%sql_mode%'\G*************************** 1. row ***************************Variable_name: sql_mode Value: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION1 row in set (0.00 sec)

Check the official documentation about the values of various sql_mode options, and find the suspect object STRICT_TRANS_TABLES, which means that strict mode is enabled. In strict mode, if the inserted data is not in range, an error interrupt statement will be reported.

Modify the sql_mode parameter value and insert the emoji emoji test again. mysql> set session sql_mode='ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';Query OK, 0 rows affected, 1 warning (0.00 sec

You can see that after changing the sql_mode value, the phenomenon is consistent with version 5.6.

Use utf8mb4 character set to insert emoji and query.

3.3 summary

Here's a summary of emoji problems:

Storing emoji in MySQL requires not only setting the field character set in the table structure to utf8mb4, but also considering the problem of connecting character sets.

It is recommended that the database use version 5.7.22 and above, and sql_mode is enabled in strict mode, so that when data anomalies occur, they can be discovered in time.

When sql_mode is turned on in strict mode, the application connection database also needs to be adjusted to utf8mb4 character set to insert emoji emoji normally.

| Shen Gang·Woqu database technology expert familiar with MySQL database operation mechanism, rich database and replication architecture fault diagnosis, performance tuning, database backup recovery and migration experience.

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.