共计 2104 个字符,预计需要花费 6 分钟才能阅读完成。
作者:刘开洋
爱可生交付服务团队北京 DBA,对数据库及周边技术有浓重的学习趣味,喜爱看书,谋求技术。
本文起源:原创投稿
* 爱可生开源社区出品,原创内容未经受权不得随便应用,转载请分割小编并注明起源。
问题
共事在客户那里解决问题时发现一个报错,出于趣味就进行了钻研:
Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions,and never in the same statement as updates to transactional tables.
报错的意思是指业务端下发的事务违反了 GTID 的一致性。
起因是,非事务表和事务表在一条事务里进行了更新,MySQL 是不容许咱们这么做的,但业务非要胳膊掰大腿,怎么办?
官网阐明:
Only statements that can be logged using GTID safe statements can be logged when enforce_gtid_consistency is set to ON,so the operations listed here cannot be used with this option:
- Transactions or statements that update both transactional and nontransactional tables.
跟客户沟通确定了该报错事务波及到的两张表别离是 innodb 表和 myisam 表。咱们在本人环境上复现一下。
复现
应用 mysql 5.7.25,开启 gtid,
并别离创立一张 innodb 表和 myisam 表。
在同一个事务中对两张表进行更新:
没有报错呀,怎么和客户那里的报错解释不符?
咱们将两张表的提交程序换一下呢?
果然复现了客户环境中的报错。可是为什么先更新 myisam 引擎表能够更新 innodb 引擎表,然而反之则不行呢?
因为 myisam 引擎表不反对事务,在咱们下发 begin 之后,就曾经隐式提交了,这是个运维教训,无奈直接判断,专门求教了行业大牛失去了确认;
而持续更新 myisam 表时,MySQL 不认这个 insert myisam 表的操作是一个事务操作,只是给予一个记录 binlog 地位的 gtid,之后再更新 innodb 表的操作时,mysql 就认定这是在更新一条事务,在 commit 中就将这条事务提交了。
解决
解决办法有三种:
- 在业务层面批改事务逻辑,将对 myisam 表和 innodb 表的更新语句拆分到不同事务中。
- 将 myisam 存储引擎改为 innodb 存储引擎。
- 敞开事务一致性参数 ENFORCE_GTID_CONSISTENCY。
接下来别离对三种解决方案进行验证:
1)批改业务逻辑,将 myisam 表的更新语句独自放在一个事务里执行。
这是最为稳当的一个办法。
2)批改 myisam 表的存储引擎为 innodb
- 首先确认如果数据量比拟大,能够应用 mysqldump 导出数据,而后批改 SQL 文件中的存储引擎的类型和表名,删掉 drop table 的操作,之后保留 SQL 文件导入到数据库中。
- percona 公司也有一款工具实用于这样的场景,有趣味的同学能够钻研应用下,以备生产环境的应用:pt-online-schema-change。
- 如果表数据量很小,就能够应用惯例的 alter 语句间接批改:alert table sanguo engine = InnoDB。
留神:
myisam 存储引擎表不反对事务,innodb 表绝对于 myisam 表来说能反对更多的数据库高级操作,例如:事务、外键。随着 MySQL 版本的不断更新迭代,innodb 的劣势越来越大,倡议将所有 MySQL 表的存储引擎逐步替换为 innodb,两种存储引擎其余方面的优劣比照参考网上文档。
3)敞开事务一致性参数 ENFORCE_GTID_CONSISTENCY
OFF_PERMISSIVE: New transactions are anonymous. Replicated transactions can be either anonymous or GTID transactions.
- 匿名事务依赖于二进制日志文件和地位来辨认特定的事务。
- 从 MySQL 5.7.6 开始,增加的 OFF_PERMISSIVE 和 ON_PERMISSIVE 模式容许在拓扑中混合应用这些事务类型。
对表的更新操作实现。
咱们去看看此时 binlog 中的写入:
敞开 ENFORCE_GTID_CONSISTENCY 参数后新事务的提交都是匿名的,且提交程序被打乱,数据库不再进行一致性查看;但此时复制会中断,数据库应用的 HA 将生效。权衡利弊,对于业务来说代价较高,不倡议应用。
附:
如果在一个生产环境中,不倡议同时更新 innodb 和 myisam 存储引擎表,对于 myisam 引擎表的更改是无奈进行回滚的,倡议业务将 SQL 上线前肯定做好查看。
参考
https://dev.mysql.com/doc/ref…
https://www.percona.com/doc/p…