ENFORCE_GTID_CONSISTENCY与临时表的问题

简介:

业务从5.6切到5.7后,启用gtid,业务有临时表。出现:



修改 SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = off;


配置文件中

ENFORCE_GTID_CONSISTENCY = off;


###

Checkpoint 1. Ensure that your workload is compatible with GTIDs.

This checkpoint is needed because GTIDs do put (small) restrictions on the SQL you can execute. The following statements are disallowed: CREATE  TABLE … SELECT; CREATE TEMPORARY or DROP TEMPORARY inside a transaction context; and mixing transactional tables with nontransactional tables in the same statement or in the same transaction context. Such statements must be eliminated from the workload (this is a good practice anyways, because the non-transactional nature of these statements makes them unsuitable for replication).

We do not expect these to be severe limitations for most users. But, to be sure that enabling GTIDs does not cause any surprises, you can now pre-check your workload by enabling a mode that generates warnings for the GTID-violating statements.

To complete checkpoint 1, do the following:

1. On each server, execute: SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = WARN, and set enforce-gtid-consistency=WARN in my.cnf.

2. Let it run for a while with your normal workload. Any GTID-violating statement will execute correctly, but generate a warning in the server log, like:

or

or

Look in the log for these warnings. If there are any warnings , adjust your SQL so that it only uses GTID-compatible statements. E.g., replace CREATE TABLE … SELECT by a CREATE TABLE statement followed by an INSERT statement, move CREATE/DROP TEMPORARY out of transactional context, or switch to InnoDB for any table that needs to be updated in the same transaction or in the same statement as other InnoDB tables. Notice that these statements are risky to use together with replication, so all this is good to do anyways. Repeat until it does not generate any warning.

3. On each server, execute: SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = ON, and set enforce-gtid-consistency=ON in my.cnf so that the value is preserved next time you need to restart (you don’t need to restart due to this procedure, but there could always be some other reason).

目录
打赏
0
0
0
0
3
分享
相关文章
Oracle使用expdp/impdp实现全库导入导出的整体流程
Oracle的全库导入,首先一点必须先创建数据库,创建了数据库,才能往该数据库导入所有数据。相对来说,使用Oracle进行数据导入导出还很有些“麻烦”的,大多数资料上来就是......
11731 0
Oracle使用expdp/impdp实现全库导入导出的整体流程
Exception in thread “main“ java.lang.NoClassDefFoundError: freemarker/template/Configuration
Exception in thread “main“ java.lang.NoClassDefFoundError: freemarker/template/Configuration
340 0
SpringCloud Gateway鉴权和跨域解决方案
SpringCloud Gateway鉴权和跨域解决方案
1169 0
【Java】已解决java.lang.NoClassDefFoundError异常
【Java】已解决java.lang.NoClassDefFoundError异常
3352 0
Java一分钟之-JMX:Java管理扩展
【6月更文挑战第3天】Java Management Extensions (JMX) 允许创建、注册和管理MBeans以监控和控制Java应用。本文关注JMX的基本概念、常见问题和易错点。关键点包括:正确实现MBean和使用`StandardMBean`,确保MBean注册时名称唯一,引用平台MBean Server,配置安全管理,以及处理MBean操作异常。理解这些概念和最佳实践对于有效利用JMX至关重要。记得在实际应用中测试管理接口并加强生产环境的安全性。
338 8
echarts 提示框异步获取数据 —— formatter 异步回调函数的使用
echarts 提示框异步获取数据 —— formatter 异步回调函数的使用
351 0
MySQL启用GTID的限制
开启 GTID 之后,会由部分的限制,内容如下: 更新非事务引擎表GTID 同步复制是基于事务的,所以 MyISAM 存储引擎不支持,这可能导致多个 GTID 分配给同一个事务。 使用GTID时,使用非事务性存储引擎(如MyISAM)对表的更新不能在与使用事务性存储引擎(如InnoDB)的表的更新相同的语句或事务中进行。
3110 0
npm install 太慢?解决方法
npm install 太慢?解决方法
9555 0
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等