mysql - MySQL 存储过程失败,错误代码 2013(查询期间与 MySQL 服务器的连接丢失)
问题描述
提前感谢您尝试帮助我!
我的问题如下:我有一个名为fill_enrollment_id() 的存储过程。此过程声明了一个游标,该游标填充了一些变量,这些变量稍后用于 SELECT INTO 语句以填充另一个变量 (v_enrollmentid)。然后,该过程检查 SELECT INTO 语句是否返回 NOT FOUND 异常。如果确实引发了 NOT FOUND 异常,则该过程使用默认值“0 - 0”对表“mdl_edulevel2_log”的“enrollmentid”列执行 UPDATE;然后更新 NOT FOUND 处理程序变量,以便游标可以迭代。否则,如果没有引发异常,则该过程使用通过 SELECT INTO 语句获得的值执行 UPDATE 语句。最后,设置关闭游标和离开循环的指令。该程序的代码如下:
CREATE DEFINER=`mutual`@`%` PROCEDURE `fill_enrollment_id`()
BEGIN
DECLARE v_id BIGINT DEFAULT 0;
DECLARE v_userid BIGINT DEFAULT 0;
DECLARE v_courseid BIGINT DEFAULT 0;
DECLARE v_timecreated BIGINT DEFAULT 0;
DECLARE v_enrollmentid VARCHAR(255) DEFAULT null;
DECLARE exit_loop BOOLEAN;
DECLARE edulog_cursor CURSOR FOR SELECT id, userid, courseid, timecreated FROM mdl_edulevel2_log WHERE userid not in (0, 2, 3);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;
OPEN edulog_cursor;
edulog_loop: LOOP
FETCH edulog_cursor INTO v_id, v_userid, v_courseid, v_timecreated;
SELECT ifnull(enrollmentid, "0 - 0")
INTO v_enrollmentid
FROM mdl_enrollments
WHERE m_relateduserid = v_userid
AND m_courseid = v_courseid
AND ((v_timecreated >= m_timecreated) AND (v_timecreated <= dm_timecreated OR dm_timecreated = 0));
IF exit_loop THEN
UPDATE mdl_edulevel2_log
SET enrollmentid = "0 - 0"
WHERE id = v_id;
SET exit_loop = FALSE;
ELSE
UPDATE mdl_edulevel2_log
SET enrollmentid = v_enrollmentid
WHERE id = v_id;
END IF;
IF exit_loop THEN
CLOSE edulog_cursor;
LEAVE edulog_loop;
END IF;
END LOOP edulog_loop;
END
在我看来,逻辑没问题,但过程失败,错误代码为 2013. Lost connection to MySQL server during query。我真的不知道为什么会这样。我的直觉说它与光标查询的大小(143.115 行)有关,而且我在笔记本电脑上本地运行它(Intel(R) Core(TM) i5 M250 2.4GHz CPU、8GB RAM 和 64bits Windows 10 专业版)。另一方面,超时间隔在 MySQL Workbench 中设置为 600(见下图)。我不确定增加这个值是否会解决问题或使问题变得更糟。
最后,我将与您分享过程中使用的两个表的定义(mdl_edulevel2_log 和 mdl_enrollments),因此您可以看到定义了正确的索引,因此过程运行得更快。他们来了:
CREATE TABLE `mdl_edulevel2_log` (
`id` bigint NOT NULL AUTO_INCREMENT,
`eventname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`component` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`action` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`target` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`objecttable` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`objectid` bigint DEFAULT NULL,
`crud` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`edulevel` tinyint(1) NOT NULL,
`contextid` bigint NOT NULL,
`contextlevel` bigint NOT NULL,
`contextinstanceid` bigint NOT NULL,
`userid` bigint NOT NULL,
`courseid` bigint DEFAULT NULL,
`relateduserid` bigint DEFAULT NULL,
`anonymous` tinyint(1) NOT NULL DEFAULT '0',
`other` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
`timecreated` bigint NOT NULL,
`origin` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`ip` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`realuserid` bigint DEFAULT NULL,
`enrollmentid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `mdl_edulevel2log_tim_ix` (`timecreated`),
KEY `mdl_edulevel2log_couanotim_ix` (`courseid`,`anonymous`,`timecreated`),
KEY `mdl_edulevel2log_useconconcr_ix` (`userid`,`contextlevel`,`contextinstanceid`,`crud`,`edulevel`,`timecreated`),
KEY `mdl_edulevel2log_con_ix` (`contextid`),
KEY `idx_mdl_edulevel2_log_userid` (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=4727019 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Standard log table';
CREATE TABLE `mdl_enrollments` (
`m_id` bigint NOT NULL,
`m_objectid` bigint DEFAULT NULL,
`m_userid` bigint NOT NULL,
`m_courseid` bigint DEFAULT NULL,
`m_relateduserid` bigint DEFAULT NULL,
`m_timecreated` bigint NOT NULL,
`m_ip` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`dm_id` bigint DEFAULT NULL,
`dm_objectid` bigint DEFAULT NULL,
`dm_userid` bigint DEFAULT NULL,
`dm_courseid` bigint DEFAULT NULL,
`dm_relateduserid` bigint DEFAULT NULL,
`dm_timecreated` bigint DEFAULT NULL,
`dm_ip` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`enrollmentid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`enrollmentid`),
KEY `idx_mdl_enrollments_m_relateduserid` (`m_relateduserid`),
KEY `idx_mdl_enrollments_m_courseid` (`m_courseid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Standard log table';
但是,老实说,我不太确定这些索引是否被使用。我不知道为存储过程调用执行 EXPLAIN 语句。但是当我单独为查询执行 EXPLAIN 语句时,结果如下:
因此,似乎可以使用索引,但正如我之前所说,我不太确定一旦调用了该过程,就会使用索引。
这就是我能说的。我希望你们能给我一些提示,线索或提示来解决这个问题或以其他方式实现它。如果我可能还没有提供,请不要犹豫,询问与手头主题相关的其他信息。
来自委内瑞拉的问候,如果我的英语不够清楚,我很抱歉。
解决方案
首先,您正在序列化不需要的查询。具有 143115 行的游标导致每行 1 次选择和 1 次更新,从而导致 143115*2 + 1 = 286231 次 SQL 操作。这将花费大量时间,以至于您的执行超时(错误 2013)。
其次,您的光标逻辑似乎有点不对劲。当游标循环结束时(没有更多行要处理),它会将exit_loop
变量转换为true
. 然而,您正在尝试进行最后一次更新并exit_loop
返回false
好像光标应该继续。
您可能可以对拥有所有逻辑的单个 SQL 操作执行相同操作:
UPDATE mdl_edulevel2_log l
INNER JOIN mdl_enrollments e ON e.m_relateduserid = l.userid
AND ((l.timecreated >= e.m_timecreated)
AND (l.timecreated <= e.dm_timecreated OR e.dm_timecreated = 0)
)
SET enrollmentid = ifnull(e.enrollmentid, "0 - 0")
WHERE l.userid not in (0, 2, 3);
推荐阅读
- firebase - 授予用户对特定子域的访问权限
- angular - 分页器始终设置在第一页primeng
- javascript - TypeError: undefined is not an object (evalating 'addTodo(title, description).then') in React Native
- r - 使用 ggplot 的信息性线性判别图
- reporting-services - SSRS:显示文本而不是数字
- list - 多个半径列表对象的圆区域不可调用
- powershell - Powershell 脚本根据文件的修改日期重命名文件。我对此真的很陌生
- bash - 尝试将 for /f "tokens=*" %%a in ... 转换为 SHELL
- transactions - signrawtransactionwithwallet 显示错误无法签署输入,无效堆栈大小(可能缺少密钥)
- storage - Google Cloud Storage API - StringToSign 返回了错误的存储桶名称