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

建议与反馈 社区建设
#1

现在执行一个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和原数据库也是一致的。

3 个赞
#2

调用了存储函数或触发器，并且该函数/触发器试图更新与外层 SQL 所操作的相同表 mon_agent，这在 OceanBase 中是被禁止的行为。

这是为了防止 递归更新 或 数据不一致 的情况发生。具体来说，在存储函数或触发器中尝试修改当前正在被外部语句访问的表，会引发“表正在变异（mutating）”的问题。

3 个赞
#3

@论坛小助手

1 个赞
#4

根据您的回复，我的理解是oceanbase 虽然兼容mysql8.0，但是对这种同一表的同时读/写操作的场景是无法兼容的是吗？

1 个赞
#5

需要@论坛小助手，才能回你消息

1 个赞
#6

@辞霜 老师根据您的回复，我的理解是oceanbase 虽然兼容mysql8.0，但是对这种同一表的同时读/写操作的场景是无法兼容的是吗？

1 个赞
#8

可能兼容设计还是存在问题吧

#9

一步步来，可能后面版本就支持了。可以给 OB 提需求，提需求的官方路径是：GitHub - oceanbase/oceanbase: OceanBase is an enterprise distributed relational database with high availability, high performance, horizontal scalability, and compatibility with SQL standards. 里 提 issue 。
论坛里提需求也就看看 :slight_smile:

#10

从 5.7 开始就严格限制了这个行为，不允许“mutating table”操作（即在触发器/函数里更新、删除、插入当前正在被修改的表）。但部分云厂商（或者某些 MySQL 发行版）会在兼容性设置、触发器/函数安全级别（TRUSTED、SQL SECURITY INVOKER/DEFINER）等方面做了改动或者放宽限制。

如果你在新的租户数据库中显式指定兼容 MySQL 8.0 且 sql_mode 一致，但仍然报错，可能的原因是：

:white_check_mark: 1. 触发器/函数安全模式不同
查看 SHOW CREATE TRIGGER 或 SHOW CREATE FUNCTION，看下是否 SQL SECURITY 设定不一致。
在部分 MySQL 兼容数据库（比如 TiDB、PolarDB）中，如果 SQL SECURITY 设置不同，会导致安全上下文执行权限不同，从而触发限制。

:white_check_mark: 2. 兼容性配置仍有细微差别
虽然 sql_mode 一致，但云厂商可能在内部实现（比如分布式存储层）限制了对“同表更新”的支持。可以尝试查下：

SHOW VARIABLES LIKE ‘version_compile_os’;
SHOW VARIABLES LIKE ‘version_comment’;

以及云数据库提供的文档是否有类似“触发器行为限制”。

:white_check_mark: 3. 触发器里是否递归调用
如果触发器里又调用了函数/存储过程，而这些过程里又操作了同一个表，也可能导致此报错。可以排查下调用链。
:gear: 解决方案建议

:heavy_check_mark: 避免在触发器/函数里直接更新当前操作的表
通常可通过以下方式绕开限制：

改为异步处理
将“更新表”的逻辑放到触发器外部，由应用层或异步任务完成。

使用中间表
触发器写入中间表，后续再更新原表，避免在触发器内操作相同表。

改写触发器逻辑
如果是简单自更新（比如 UPDATE mon_agent SET col=val WHERE id=NEW.id），很多场景可以直接在 BEFORE UPDATE 中用 SET NEW.col=val 实现，避免显式 UPDATE 语句。
#11

感谢大家的大力支持，后来我用物化视图来解决了。Oceanbase的物化视图还是很好用的。