MySQL Stored Procedure fails with Error Code 2013 (Lost connection to MySQL server during query)

Question

Thanks in advance for trying to help me!

My problem is as follows: I have a stored procedure called fill_enrollment_id(). This procedure declares a cursor, which data fills some variables that later are used for a SELECT INTO statement to fill another variable (v_enrollmentid). Then, the procedure checks if the SELECT INTO statement returns a NOT FOUND exception. If, indeed, a NOT FOUND exception is raised then the procedure does an UPDATE on column "enrollmentid" of table "mdl_edulevel2_log" with the default value "0 - 0"; and then updates NOT FOUND handler variable so the cursor can iterate. Otherwise, if no exception is raised, then the procedure does the UPDATE sentence with the value obtained with the SELECT INTO statement. Finally, the instructions for closing the cursor and leaving the loop are set. The code of the procedure is as follows:

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

It seems to me that the logic is OK, but the procedure fails with Error Code 2013. Lost connection to MySQL server during query. I don't really know why is this happening. My intuition says that it's related to the size of the cursor's query (143.115 rows) and the fact taht I'm running this locally on my laptop (Intel(R) Core(TM) i5 M250 2.4GHz CPU, 8GB RAM and 64bits Windows 10 Pro). On the other hand, the timeout interval is set to 600 in MySQL Workbench (see image below). I'm not sure if increasing this value will fix the problem or make it worse.

Finally, I'm sharing with you the definition of both tables used in the procedure (mdl_edulevel2_log and mdl_enrollments), so you can see that proper indexes are defined so the procedure runs faster. Here they are:

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';

But, to be honest, I'm not quite sure that these indexes are being used. I don't know to do the EXPLAIN statement for a stored procedure call. But when I did the EXPLAIN statement for the queries individually, these where the results:

So, it seems that the indexes could be being used, but as I said before, I'm not quite sure that once the procedure is called, the indexes are used.

This is all I can say. I hope you guys can give me some tips, clues or hints to solve this problem or to achieve it in other way. Don't hesitate to ask for other information related to the subject at hand if perhaps I'm not already giving.

Greetings from Venezuela and sorry if my english isn't clear enough.

Answer 1

Firstly, you are serializing a query that you do not need to. The cursor with 143115 rows causes 1 select and 1 update for each row resulting 143115*2 + 1 = 286231 SQL operations. This will take so much time that your execution times out (error 2013).

Secondly, your cursor logic seems to be bit off. When the cursor loop ends (no more rows to handle), it will turn the exit_loop variable to true. Yet you are trying to make last update and turn the exit_loop back to false as if the cursor should continue.

You probably can do the same with single SQL operation where you have all the logic:

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);