MYSQL 生產(chǎn)環(huán)境字段更改的failed的問(wèn)題如何解決

這篇文章主要介紹“MYSQL 生產(chǎn)環(huán)境字段更改的failed的問(wèn)題如何解決”，在日常操作中，相信很多人在MYSQL 生產(chǎn)環(huán)境字段更改的failed的問(wèn)題如何解決問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”MYSQL 生產(chǎn)環(huán)境字段更改的failed的問(wèn)題如何解決”的疑惑有所幫助！接下來(lái)，請(qǐng)跟著小編一起來(lái)學(xué)習(xí)吧！

早上看到微信一個(gè)銀行的同學(xué)問(wèn)了小問(wèn)題，希望他不要背鍋，具體問(wèn)題是MYSQL 一個(gè)50G的表要更改字段，將一個(gè)字段從varchar(3)  改成varchar(6).  MYSQL 5.7 官版。因?yàn)楦鶕?jù)官方和在測(cè)試系統(tǒng)測(cè)試的結(jié)果來(lái)看，不應(yīng)該是緩慢的，應(yīng)該是很快完成的。

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

所以我們要理解一個(gè)事情首先要站在一個(gè)起跑線上，上面的東西都是官方文檔，并且在測(cè)試環(huán)境上測(cè)試基本上沒(méi)有太大問(wèn)題。

可能原因如下

1  DDL ONLINE  不阻塞 DML 但并沒(méi)有說(shuō)，不會(huì)不阻塞 DDL 的操作

2  測(cè)試環(huán)境比較單純，可能測(cè)試的時(shí)候，對(duì)表并沒(méi)有其他的復(fù)雜的操作

所以還是那句話，數(shù)據(jù)庫(kù)的問(wèn)題，一定要想的復(fù)雜點(diǎn)，理論上很多事情說(shuō)的很明白，解析的很明白，但到了實(shí)際當(dāng)中，可能就會(huì)不一樣了。

我也做了一個(gè)測(cè)試

1  我弄了一個(gè)存儲(chǔ)過(guò)程，并且不斷往一個(gè)表里面插入數(shù)據(jù)

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

3   我的語(yǔ)句嚴(yán)格按照官方的語(yǔ)句去撰寫，不給不嚴(yán)謹(jǐn)?shù)牟僮髁粝掳朦c(diǎn)口實(shí)

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

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

但實(shí)際上，這條語(yǔ)句一直在等待的狀態(tài)，根據(jù)官方文檔，如果他在執(zhí)行的時(shí)候，應(yīng)該是不會(huì)對(duì)DML 操作有影響。但如果他根本就在等待 metadata lock呢。所以修改字段的任務(wù)依然是失敗的。

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

為了確保事務(wù)的可串行性，服務(wù)器必須不允許一個(gè)會(huì)話對(duì)另一個(gè)會(huì)話中未完成的顯式或隱式啟動(dòng)的事務(wù)中使用的表執(zhí)行數(shù)據(jù)定義語(yǔ)言(DDL)語(yǔ)句。服務(wù)器通過(guò)獲取事務(wù)中使用的表的元數(shù)據(jù)鎖，并將這些鎖的釋放推遲到事務(wù)結(jié)束時(shí)，來(lái)實(shí)現(xiàn)這一點(diǎn)。表上的元數(shù)據(jù)鎖可以防止對(duì)表結(jié)構(gòu)的更改。這種鎖定方法意味著一個(gè)會(huì)話內(nèi)的事務(wù)正在使用的表，不能在DDL狀態(tài)下使用。

但讓我感到奇怪的事情是，當(dāng)我停止了存儲(chǔ)過(guò)程不斷 對(duì)這個(gè)表進(jìn)行操作，DDL的語(yǔ)句也未在執(zhí)行，并且就卡在哪里。

而在kill 掉所有的有關(guān)線程后，再次做這個(gè)實(shí)驗(yàn)，驚奇的是不在有MDL LOCK 來(lái)阻礙 alter 的操作，基本上都是瞬間在0.幾秒的時(shí)間就完成了。

總結(jié)一下

DB的工作本身是一件復(fù)雜的工作，他并沒(méi)有你在理解原理后，就一定會(huì)按照你認(rèn)為的那樣，去工作，因?yàn)槔碚摵蛯?shí)際遇到的情況不同，實(shí)際的情況太多種多樣。

有些公司操作ALTER 語(yǔ)句的并不是人工，而是通過(guò)購(gòu)買（或開(kāi)源）的一個(gè)所謂的 “自動(dòng)化”工具來(lái)的，誰(shuí)也不知道在故障發(fā)生的一刻，做了什么，同時(shí)不能復(fù)制的，就是當(dāng)時(shí)的生產(chǎn)環(huán)境到底有沒(méi)有大事務(wù)，并且就對(duì)那張表進(jìn)行了什么操作，那個(gè)表有幾個(gè)索引，這個(gè)字段有沒(méi)有索引，等等。

也注定 DB的工作，是一件需要小心小心小心的工作，因?yàn)樯a(chǎn)環(huán)境一定有你不清楚的環(huán)境，而這些可能不清楚的環(huán)境，就會(huì)讓某次“信心滿滿”的Action Failed.

注：到目前為止MYSQL 在修改字段方面，對(duì)比其他數(shù)據(jù)庫(kù)還是要注意的地方多多，當(dāng)然MYSQL 8 已經(jīng)添加了 instant 讓修改字段變得更讓人放心。

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

下面有一個(gè)查看metadatalock的存儲(chǔ)過(guò)程，（有點(diǎn)亂，可以拷貝出來(lái)，自己整理一下）

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 ;

到此，關(guān)于“MYSQL 生產(chǎn)環(huán)境字段更改的failed的問(wèn)題如何解決”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)?lái)更多實(shí)用的文章！