业务场景报错:
日志报错信息:
java.sql.SQLException: When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1.
报错翻译:
java.sql。SQLException:当@全局时。ENFORCE_GTID_CONSISTENCY=1时,只能在非事务上下文中执行CREATE TEMPORATY TABLE和DROP TEMPORATE TABLE语句,并要求AUTOCOMMIT=1。
目标:获取考评得分的排名
实际上最理想的就是直接按得分倒序排列,获取行号:
select a.objectguid,score,(@rowNum:=@rowNum+1) as rank
from audit_performance_stardetail a,(select (@rowNum :=0)) b
WHERE starguid = '42d96ff4-6aad-4c6f-9e2e-4b999ec5c45c'
ORDER BY Score DESC
;
但实际因为使用了公司自己的封装框架,(@rowNum:=@rowNum+1)因为存在特殊字符会被截断字符串,从而报错,故此寻求另一种存储过程的方式:
-- 创建存储过程
CREATE PROCEDURE `getObjectRank`(IN in_starguid varchar(50), IN in_objectguid varchar(50))
BEGIN
-- DECLARE rowNum INT;
-- SET rowNum = 0;
CREATE TEMPORARY TABLE IF NOT EXISTS Temp_Star_Rank (
objectguid VARCHAR (50),
score DOUBLE,
rank INT
);
INSERT INTO Temp_Star_Rank
select a.objectguid,score,(@rowNum:=@rowNum+1) as rank
from audit_performance_stardetail a,(select (@rowNum :=0)) b
WHERE starguid = in_starguid
ORDER BY Score DESC
;
SELECT * FROM Temp_Star_Rank
WHERE objectguid = in_objectguid;
DROP table Temp_Star_Rank;
END
以上为最开始的存储过程,经过localhost应用调试,成功,没有报错!
本地mysql版本:
服务器mysql版本:
但更新至服务器后报错最开始的报错。
show variables like 'autocommit'
解决方法1:失败!
解决方法2:成功!
解决方法3:成功!