MySQL 8.0的原子DDL非事務性DDL，絕大多數情況下，原子DDL仍舊是一個然并卵的特性-有解無憂

首先宣告一下：MySQL 8.0之后，依舊不支持DDL事務，原子性DDL與其說是一個MySQL8.0下的新特性，倒是不如說是修復了MySQL5.7 DDL 執行失敗造成的bug，

什么是原子DDL：
當執行DDL時，資料字典更新、存盤引擎操作和二進制日志中的寫操作被合并到一個原子事務中，該事務要么完全執行，要么完全不執行，
這提供了更好的可靠性，未完成的ddl不會留下任何不完整的資料，

MySQL 8.0 前后表的物理檔案差異
在mysql中，對于表的資料檔案，在mysql 8.0之前，是分為一個ifm的表結構文本檔案和一個idb資料檔案
再執行表結構變更的DDL的時候實際上是一個寫文本檔案的程序，因此不支持事務
到了8.0之后，表結構存盤進去了共享表空間檔案的資料字典中Data Dictionary Storage Engine（即 InnoDB表中），已經無法直接看到.frm檔案了

這樣看來，DDL的創建好似乎也是一個事務性的DML（修改元資料），但是MySQL 8.0依舊不支持事務性的DDL，

原子DDL
最多的示例就是這個：DROP TABLE t1,t2；drop VIEW v1,v2，執行時候t1,t2出錯之后會自動回滾，
原子DDL相對MySQL8.0之前的DDL，稍微進步了一點點：alter table 操作，在server crash的情況下，不會遺留.frm，.ibd臨時檔案
但是仍舊不支持事務性DDL，最多的示例就是這個，誰特么天天 DROP TABLE t1,t2；drop VIEW v1,v2 ？？？
實際對于DB的變更，更多的時候，是基于不同的表加欄位/修改欄位型別/加索引/刪索引，而這種陳述句，無法書寫成原子性DDL支持的陳述句，當然MySQL 8.0下也是不支持事務性DDL的，

原子DDL的實作程序

以下原子DDL的實作程序摘自于網路：
不管引數innodb_flush_log_at_trx_commit的值，只要是對mysql.innodb_ddl_log表做的更改的重做日志都會立即重繪到磁盤，
立即重繪重做日志可以避免DDL操作修改資料檔案的情況，但是由這些操作產生的對mysql.innodb_ddl_log表的更改的重做日志不會持久保存到磁盤，
這種情況可能會在回滾或恢復期間導致錯誤，“老大要重點保護”

InnoDB存盤引擎分以下幾個階段執行DDL操作：
1)準備：創建所需物件并將DDL日志寫入mysql.innodb_ddl_log表， DDL日志定義了如何前滾和回滾DDL操作，
2)執行：執行DDL操作，例如，為CREATE TABLE操作執行create例程，
3)提交：更新資料字典并提交資料字典事務，
4)DDL后收尾作業：從mysql.innodb_ddl_log表中重播并洗掉DDL日志，為了確保可以安全地執行回滾而不引入不一致性，在最后階段執行檔案操作，例如重命名或洗掉資料檔案，此階段還從mysql.innodb_dynamic_metadata資料字典表中洗掉DROP TABLE，TRUNCATE TABLE和其他重建表的DDL操作的動態元資料，

無論事務是提交還是回滾，DDL日志都會在Post-DDL階段重放并從mysql.innodb_ddl_log表中洗掉，如果服務器在DDL操作期間暫停，則DDL日志應僅保留在mysql.innodb_ddl_log表中，在這種情況下，DDL日志將在恢復后重放并洗掉，
在恢復情況下，可以在重新啟動服務器時提交或回滾DDL事務，如果在重做日志和二進制日志中存在在DDL操作的提交階段期間執行的資料字典事務，則該操作被視為成功并且前滾，否則，當InnoDB重放資料字典重做日志并回滾DDL事務時，將回滾未完成的資料字典事務，

實作批量DDL的可重復性執行

因此，這里上可以認為，除了表結構變更時候的臨時檔案清理，與DDL事物沒有什么瓜葛，這個MySQL8.0的原子DDL是然并卵的，
如果真的需要執行一系列DDL陳述句，如何避免中間發生了錯誤，再次執行時某些陳述句無法重復執行造成的錯誤？
其實還是一樣的思路，將執行過的DDL存起來，如果判斷已經執行過了，就跳過，繼續判斷下一個DDL，花了一小會驗證了一下這個思路，

#!/usr/bin/python3
import pymysql
import zlib
'''
-- dll record log
CREATE TABLE ddl_execute_record
(
    id                     INT AUTO_INCREMENT PRIMARY KEY,
    db_name                 VARCHAR(50),
    ddl_statement         LONGTEXT,
    ddl_hash                BIGINT,
    execute_status     TINYINT COMMENT '0:init;1:successful;-1:fail',
    create_datetime     DATETIME(6),
    last_update            DATETIME(6)
);
'''

class mysql_release(object):
    def __init__(self):
        return

    def __init__(self, host=None, port=None,user=None,password=None,db=None,charset=None):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.db = db
        self.charset = charset

    def get_conn(self):
        if not self.db:
            raise(NameError,"no database info")
        conn = pymysql.connect(host=self.host,  port=self.port,  user=self.user,password=self.password, db=self.db, charset=self.charset)
        if not conn:
            raise(NameError,"conn database fail")
        else:
            return conn

    def execute_noquery(self,str_sql):
        try:
            conn = self.get_conn()
            cursor =conn.cursor()
            cursor.execute(str_sql)
            last_rowid = cursor.lastrowid
            conn.commit()
        except  Exception as err:
            raise
        return last_rowid

    def execute_query(self,str_sql):
        list = None
        try:
            conn = self.get_conn()
            cursor =conn.cursor()
            cursor.execute(str_sql)
            list = cursor.fetchall()
        except  Exception as err:
            print(err)
        return list

    def execute_statement(self,sql_statement):
        #尼瑪這個hash不好使，忽略先，與記憶體地址有關，這里目的是對sql陳述句的字串進行固定hash運算，然后基于hash值作比較
        ddl_hash = hash(sql_statement)
        ddl_log_search = '''select * from db_release_audit.ddl_execute_record where ddl_statement = '{0}' and execute_status=1 '''.format(sql_statement.strip())
        ddl_log_insert = '''insert into db_release_audit.ddl_execute_record(db_name,ddl_statement,ddl_hash,execute_status,create_datetime,last_update) 
                    values ('{0}','{1}',{2},{3},now(6),now(6))'''.format('master',sql_statement.strip(),ddl_hash,0)
        ddl_log = self.execute_query(ddl_log_search)
        if not ddl_log:
            last_rowid = self.execute_noquery(ddl_log_insert)
            if last_rowid:
                update_statment = ""
                try:
                    self.execute_noquery(sql_statement)
                    # update sql statement execute successful
                    update_statment = '''update db_release_audit.ddl_execute_record set execute_status = 1,last_update = now(6) where id = {0}'''.format(last_rowid)
                except Exception as err:
                    # update sql statement execute fail
                    update_statment = '''update db_release_audit.ddl_execute_record set execute_status = -1,last_update = now(6) where id = {0}'''.format(last_rowid)
                    print(err)
                finally:
                    self.execute_noquery(update_statment)
            else:
                print('record log fail')
        else:
            print('the statment executed info: '+str(ddl_log[0]))


if __name__ == '__main__':
    dict_conn = {'host': '***，***，***，***', 'port': 3306, 'user': 'root', 'password': '***', 'db': '***', 'charset':'utf8mb4'}
    # parse db name and sql statement from sql file or anywhere
    db_name = 'user_database_name'
    sql_statement = ''' 
    create table t_1 (id int primary key);
    '''
    release_obj = mysql_release(host=dict_conn['host'], port=dict_conn['port'], user=dict_conn['user'],password=dict_conn['password'], db=dict_conn['db'], charset=dict_conn['charset'])
    try:
        release_obj.execute_statement(sql_statement)
    except Exception as err:
        print(err)
        raise

1，這里的DDL和DML一樣無法實作一個事務性操作，除非DDL執行完，記錄日志的程序中發生了錯誤，造成實際DDL與日志不一致的情況
2，這里不允許重復執行同一條DDL陳述句：如果真的存在1加欄位 ,2 刪欄位,3 繼續加同樣的欄位，這種情況可以通過增加時間條件來規避錯誤的判斷，因為不太可出現上述的連續三個步驟
3，被執行的多個DDL或者是DML陳述句，可能基于sql文本檔案決議或者其他方式，這個程序并沒有什么難點

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/50673.html

標籤：MySQL

上一篇：windows啟動 MySQL出錯

下一篇：織夢dedecms如何讓內容頁顯示不同的內容,但是每次更新都不變