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