MYSQL 生產環境字段更改的failed的問題如何解決

這篇文章主要介紹“MYSQL 生產環境字段更改的failed的問題如何解決”，在日常操作中，相信很多人在MYSQL 生產環境字段更改的failed的問題如何解決問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MYSQL 生產環境字段更改的failed的問題如何解決”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

早上看到微信一個銀行的同學問了小問題，希望他不要背鍋，具體問題是MYSQL 一個50G的表要更改字段，將一個字段從varchar(3)  改成varchar(6).  MYSQL 5.7 官版。因為根據官方和在測試系統測試的結果來看，不應該是緩慢的，應該是很快完成的。

VARCHAR列所需的長度字節數必須保持相同。對于大小為0到255個字節的VARCHAR列，需要一個長度的字節來編碼該值。對于大小為256字節或更大的VARCHAR列，需要兩個長度的字節。結果，就地ALTER TABLE僅支持將VARCHAR列大小從0增大到255字節，或從256字節增大到更大的大小。就地ALTER TABLE不支持將VARCHAR列的大小從少于256個字節增加到等于或大于256個字節的大小。在這種情況下，所需的長度字節數從1更改為2，這僅由表副本支持（ALGORITHM = COPY）。

所以我們要理解一個事情首先要站在一個起跑線上，上面的東西都是官方文檔，并且在測試環境上測試基本上沒有太大問題。

可能原因如下

1  DDL ONLINE  不阻塞 DML 但并沒有說，不會不阻塞 DDL 的操作

2  測試環境比較單純，可能測試的時候，對表并沒有其他的復雜的操作

所以還是那句話，數據庫的問題，一定要想的復雜點，理論上很多事情說的很明白，解析的很明白，但到了實際當中，可能就會不一樣了。

我也做了一個測試

1  我弄了一個存儲過程，并且不斷往一個表里面插入數據

2  我將這表里面的某個字段從200 變化到 201 

3   我的語句嚴格按照官方的語句去撰寫，不給不嚴謹的操作留下半點口實

存儲過程就不展示了，主要是太簡單了，表就是下面的表，content字段是varchar(200)  -> varchar(201)

alter table test1 modify column content  varchar(201), algorithm=inplace,lock=none;

但實際上，這條語句一直在等待的狀態，根據官方文檔，如果他在執行的時候，應該是不會對DML 操作有影響。但如果他根本就在等待 metadata lock呢。所以修改字段的任務依然是失敗的。

到底是為什么，官方在文檔中明確了

為了確保事務的可串行性，服務器必須不允許一個會話對另一個會話中未完成的顯式或隱式啟動的事務中使用的表執行數據定義語言(DDL)語句。服務器通過獲取事務中使用的表的元數據鎖，并將這些鎖的釋放推遲到事務結束時，來實現這一點。表上的元數據鎖可以防止對表結構的更改。這種鎖定方法意味著一個會話內的事務正在使用的表，不能在DDL狀態下使用。

但讓我感到奇怪的事情是，當我停止了存儲過程不斷 對這個表進行操作，DDL的語句也未在執行，并且就卡在哪里。

而在kill 掉所有的有關線程后，再次做這個實驗，驚奇的是不在有MDL LOCK 來阻礙 alter 的操作，基本上都是瞬間在0.幾秒的時間就完成了。

總結一下

DB的工作本身是一件復雜的工作，他并沒有你在理解原理后，就一定會按照你認為的那樣，去工作，因為理論和實際遇到的情況不同，實際的情況太多種多樣。

有些公司操作ALTER 語句的并不是人工，而是通過購買（或開源）的一個所謂的 “自動化”工具來的，誰也不知道在故障發生的一刻，做了什么，同時不能復制的，就是當時的生產環境到底有沒有大事務，并且就對那張表進行了什么操作，那個表有幾個索引，這個字段有沒有索引，等等。

也注定 DB的工作，是一件需要小心小心小心的工作，因為生產環境一定有你不清楚的環境，而這些可能不清楚的環境，就會讓某次“信心滿滿”的Action Failed.

注：到目前為止MYSQL 在修改字段方面，對比其他數據庫還是要注意的地方多多，當然MYSQL 8 已經添加了 instant 讓修改字段變得更讓人放心。

但目前MYSQL5.X  PT-OSC  GH-OST等等的工具還是用起來，終歸是不希望出現意外的情況。

下面有一個查看metadatalock的存儲過程，（有點亂，可以拷貝出來，自己整理一下）

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.PROCESSLIST_STATE,']'), '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 ;

到此，關于“MYSQL 生產環境字段更改的failed的問題如何解決”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！