java.sql.SQLException: Statement violates GTID consistency: CREATE TEMPORARY TABLE 解决

Richard-Han

于 2018-09-17 16:32:01 发布

阅读量6.8k

点赞数 2

CC 4.0 BY-SA版权

分类专栏：日常检查处理

本文链接：https://blog.youkuaiyun.com/hanbowu/article/details/82744699

日常检查处理专栏收录该内容

5 篇文章

订阅专栏

本文探讨了在MySQL 5.7中使用GTID时，CREATETEMPORARYTABLE和DROPTEMPORARYTABLE语句导致的一致性错误。通过调整autocommit设置，解决了这些语句在存储过程中无法正常执行的问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

开发反馈存储过程程序报错

报错信息：java.sql.SQLException: Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context. These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.

mysql5.7

官方解释：CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements are not supported inside transactions, procedures, functions, and triggers when using GTIDs (that is, when the enforce_gtid_consistency system variable is set to ON). It is possible to use these statements with GTIDs enabled, but only outside of any transaction, and only with autocommit=1.

测试发现客户端call可以成功，于是思考和jdbc的区别

解决：存储过程增加set @@autocommit=1;