MYSQL8.0兼容租户执行SQL报错

AntTech_65GRJG · 2025 年5 月 29 日 09:43

现在执行一个sql报错，sql如下：
update mon_agent SET car_count = car_count+1 where 1=2 or FIND_IN_SET(agent_id,getParAgentList(1));

说明：getParAgentList是个存储过程，里面会查询mon_agent表，具体定义如下：
CREATE DEFINER = gpsman@% FUNCTION tpss-release.getParAgentList(agentId int(11)) RETURNS varchar(21845)
READS SQL DATA
BEGIN

DECLARE fid varchar(100) default ‘’;

DECLARE str varchar(1000) default agentId;

WHILE agentId is not null do

SET fid =(SELECT parent_id FROM mon_agent where is_delete=0  and agent_id = agentId);   

IF fid is not null THEN   

    SET str = concat(str, ',', fid);   

    SET agentId = fid;   

ELSE   

    SET agentId = fid;   

END IF;

END WHILE;

return str;

END

报错如下：
SQL 错误 [1442] [HY000]: Can’t update table ‘mon_agent’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

但是我在原数据库（MYSQL8.0）执行是不报错的，现在创建的租户是指定兼容MYSQL8.0，sqlmode和原数据库也是一致的。