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

This article mainly introduces "how to solve the failed problem of field changes in MYSQL production environment". In daily operation, I believe that many people have doubts about how to solve the problem of failed field changes in MYSQL production environment. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the problem of "how to solve the failed problem of field changes in MYSQL production environment". Next, please follow the editor to study!

In the morning, I saw a classmate from a bank in Wechat asking a small question, hoping that he wouldn't take it back. the specific problem was that a 50g table in MYSQL wanted to change a field from varchar (3) to varchar (6). MYSQL 5.7official edition. Because according to the official and test results of the test system, it should not be slow, but should be completed quickly.

The number of bytes of length required for the VARCHAR column must remain the same. For VARCHAR columns with sizes from 0 to 255 bytes, a byte of length is required to encode the value. For VARCHAR columns with a size of 256 bytes or more, two bytes of length are required. As a result, in-place ALTER TABLE only supports increasing the VARCHAR column size from 0 to 255bytes, or from 256bytes to a larger size. In-place ALTER TABLE does not support increasing the size of VARCHAR columns from less than 256 bytes to a size equal to or greater than 256 bytes. In this case, the required number of length bytes is changed from 1 to 2, which is only supported by the table copy (ALGORITHM = COPY).

So we have to understand that the first thing to do is to stand on a starting line, everything above is official documentation, and there is basically no problem with testing in a test environment.

The possible reasons are as follows

1 DDL ONLINE does not block DML but does not say that it does not block the operation of DDL

2 the test environment is relatively simple, and there may be no other complex operations on the table when testing.

So still the same sentence, the issue of the database, we must think about the complexity, in theory, a lot of things are very clear, the analysis is very clear, but in practice, it may be different.

I took a test, too.

I got a stored procedure and kept inserting data into a table

I changed a field in this table from 200 to 201

3 my sentence is written in strict accordance with the official sentence, leaving no excuse for imprecise operation

The stored procedure is not shown, mainly because it is too simple. The table is the following table, and the content field is varchar-> varchar.

Alter table test1 modify column content varchar, algorithm=inplace,lock=none

But in fact, the statement has been waiting for the state, according to the official document, if it is executed, it should not affect the DML operation. But what if he's waiting for metadata lock. So the task of modifying the field is still a failure.

Why on earth is it? officials have made it clear in the document.

To ensure that transactions are serializable, the server must not allow one session to execute data definition language (DDL) statements on tables used in outstanding explicitly or implicitly started transactions in another session. The server does this by acquiring metadata locks for tables used in the transaction and deferring the release of those locks until the end of the transaction. The metadata lock on the table prevents changes to the table structure. This locking method means that the table being used by a transaction within a session cannot be used in the DDL state.

But what surprised me was that when I stopped the stored procedure from constantly manipulating the table, the DDL statement wasn't executing either, and it got stuck there.

After kill dropped all the related threads, do this experiment again, it is surprising that there is no longer MDL LOCK to hinder the operation of alter, basically at 0. 5%. It was finished in a few seconds.

To sum up.

DB's work itself is a complex task, he is not sure to work as you think after you understand the principle, because the situation encountered in theory and practice is different, and the actual situation is too varied.

Some companies operate ALTER statements not manually, but through the purchase (or open source) of a so-called "automation" tool. No one knows what was done at the moment of the failure, and what cannot be copied at the same time is whether there were any major transactions in the production environment at that time, and what operations were carried out on that table, how many indexes there are in that table, whether there is an index in this field, and so on.

It is also destined that the work of DB is a work that needs to be careful, because the production environment must have an environment that you do not know, and these may not be clear environment, will make a "full of confidence" Action Failed.

Note: so far, MYSQL has a lot to pay attention to when it comes to modifying fields compared to other databases. Of course, MYSQL 8 has added instant to make modifying fields more reassuring.

But at present, the tools of MYSQL5.X PT-OSC GH-OST and so on are still used, and we don't want anything unexpected after all.

Here is a stored procedure to view metadatalock. (it's a bit messy. You can copy it and sort it out by yourself.)

CREATE PROCEDURE procShowMetadataLockSummary ()

BEGIN

DECLARE table_schema VARCHAR (64)

DECLARE table_name VARCHAR (64)

DECLARE id bigint

DECLARE time bigint

DECLARE info longtext

DECLARE curMdlCount INT DEFAULT 0

DECLARE curMdlCtr INT DEFAULT 0

DECLARE curMdl CURSOR FOR SELECT * FROM tmp_blocked_metadata

DROP TEMPORARY TABLE IF EXISTS tmp_blocked_metadata

CREATE TEMPORARY TABLE IF NOT EXISTS tmp_blocked_metadata (

Table_schema varchar (64)

Table_name varchar (64)

Id bigint

Time bigint

Info longtext

PRIMARY KEY (table_schema, table_name)

);

REPLACE tmp_blocked_metadata (table_schema,table_name,id,time,info) SELECT mdl.OBJECT_SCHEMA,mdl.OBJECT_NAME,t.PROCESSLIST_ ID, t.PROCESSLIST_TIME, t.PROCESSLIST_INFO FROM performance_schema.metadata_locks mdl JOIN performance_schema.threads t ON mdl.OWNER_THREAD_ID = t.THREAD_ID WHERE mdl.LOCK_STATUS='PENDING' and mdl.LOCK_TYPE='EXCLUSIVE' ORDER BY mdl.OBJECT_SCHEMA,mdl.OBJECT_NAME,t.PROCESSLIST_TIME ASC

OPEN curMdl

SET curMdlCount = (SELECT FOUND_ROWS ())

WHILE (curMdlCtr < curMdlCount)

DO

FETCH curMdl INTO table_schema, table_name, id, time, info

SELECT CONCAT_WS ('', 'PID',t.PROCESSLIST_ID,'has metadata lock on', CONCAT (mdl.OBJECT_SCHEMA,'.',mdl.OBJECT_NAME),' with current state', CONCAT_WS ('','[', t.PROCESSLISTISTSTATEN']), 'for', t.PROCESSLIST_TIME,' seconds and is currently running', CONCAT_WS ('', "[", t.PROCESSLIST_INFO) "]") AS 'Process (es) that have the metadata lock' FROM performance_schema.metadata_locks mdl JOIN performance_schema.threads t ON t.THREAD_ID = mdl.OWNER_THREAD_ID WHERE mdl.LOCK_STATUS='GRANTED' AND mdl.OBJECT_SCHEMA = table_schema and mdl.OBJECT_NAME = table_name AND mdl.OWNER_THREAD_ID NOT IN (SELECT mdl2.OWNER_THREAD_ID FROM performance_schema.metadata_locks mdl2 WHERE mdl2.LOCK_STATUS='PENDING' AND mdl.OBJECT_SCHEMA = Mdl2.OBJECT_SCHEMA and mdl.OBJECT_NAME = mdl2.OBJECT_NAME)

SELECT CONCAT_WS (', 'PID', id,' has been waiting for metadata lock on',CONCAT (table_schema,'.', table_name), 'for', time,' seconds to execute', CONCAT_WS (', [, info,']')) AS 'Oldest process waiting for metadata lock'

SET curMdlCtr = curMdlCtr + 1

SELECT CONCAT_WS (', 'PID', t.PROCESSLIST_ID,' has been waiting for metadata lock on',CONCAT (table_schema,'.', table_name), 'for', t.PROCESSLIST_TIME,' seconds to execute', CONCAT_WS (', [', t.PROCESSLIST_INFO) ']) AS' Other queries waiting for metadata lock' FROM performance_schema.metadata_locks mdl JOIN performance_schema.threads t ON t.THREAD_ID = mdl.OWNER_THREAD_ID WHERE mdl.LOCK_STATUS='PENDING' AND mdl.OBJECT_SCHEMA = table_schema and mdl.OBJECT_NAME = table_name AND mdl.OWNER_THREAD_ID AND t.PROCESSLIST_ID id

END WHILE

CLOSE curMdl

END//

Delimiter

At this point, the study on "how to solve the failed problem of field changes in the MYSQL production environment" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.