专注,勤学,慎思。戒骄戒躁,谦虚谨慎

just do it

导航

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文本文件解析或者其他方式,这个过程并没有什么难点

 

posted on 2020-04-28 20:34  MSSQL123  阅读(567)  评论(0编辑  收藏  举报