All Tables cant' perform INSERT OR UPDATE

Question

I used a stored procedure that uses a cursor to loop through and process an attendance data table on Mariadb 10.1 database after calling the procedure the first time all the tables on the database lost the ability to perform INSERT INTO or UPDATE statements unless the targeted table is truncated first, can any one tell me what went wrong and how to fix it the procedure that caused the problem:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `settle_attendance`()
    MODIFIES SQL DATA
BEGIN
DECLARE trans_done BOOLEAN DEFAULT FALSE;
DECLARE punchid BIGINT(20);
DECLARE timein DATETIME;
DECLARE utctimein DATETIME;
DECLARE timeout DATETIME;
DECLARE utctimeout DATETIME;
DECLARE inday DATE;
DECLARE outday DATE;
DECLARE todaysdate DATE;

DECLARE attendcur CURSOR FOR
     SELECT id, punch_in_utc_time, punch_in_user_time,
                punch_out_utc_time, punch_out_user_time
         FROM ohrm_attendance_record
         ORDER BY id ASC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET trans_done = TRUE;

OPEN attendcur;
edit_loop: LOOP
SET todaysdate = CURRENT_DATE();
FETCH attendcur INTO punchid, utctimein, timein, utctimeout, timeout;

IF trans_done THEN
        CLOSE attendcur;
        LEAVE edit_loop;
END IF;

SET inday = DATE(timein);
SET outday = DATE(timeout);
SET todaysdate = CURRENT_DATE();


IF (inday < todaysdate) OR (outday < todaysdate) THEN
    CASE 
        WHEN (timein IS NULL OR timein = '')
          OR (utctimein IS NULL OR utctimein = '') THEN
            UPDATE ohrm_attendance_record
               SET punch_in_utc_time = utctimeout,
                   punch_in_user_time = timeout,
                   state = 'PUNCHED OUT'
               WHERE punchid = id;
        ELSE BEGIN END;
    END CASE;

    CASE 
        WHEN (timeout IS NULL OR timeout = '')
          OR (utctimeout IS NULL OR utctimeout = '') THEN
            UPDATE ohrm_attendance_record
               SET punch_out_utc_time = utctimein,
                   punch_out_user_time = timein,
                   state = 'PUNCHED OUT'
               WHERE punchid = id;
        ELSE BEGIN END;
    END CASE;
END IF;

END LOOP edit_loop;
END $$
DELIMITER ;

Answer 1

I choose to avoid the question you asked. Instead, let's try to do the query 10 times as fast by getting rid of the pesky CURSOR. The entire Stored Procedure can be done in 2 UPDATEs, no loop:

UPDATE  ohrm_attendance_record
    SET punch_in_utc_time = utctimeout,
        punch_in_user_time = timeout,
        state = 'PUNCHED OUT'
    WHERE  ( timein < CURDATE()  OR  timeout < CURDATE() )
      AND  ( ( timein IS NULL     OR  timein = '' )
          OR ( utctimein IS NULL  OR  utctimein = '' )
           );

UPDATE  ohrm_attendance_record
    SET punch_out_utc_time = utctimein,
        punch_out_user_time = timein,
        state = 'PUNCHED OUT'
    WHERE  ( timein < CURDATE()  OR  timeout < CURDATE() )
      AND  ( ( timeout IS NULL    OR  timeout = '' )
          OR ( utctimeout IS NULL OR  utctimeout = '' )
           );

I am, however, suspicious of your tests against timein and timeout.

The queries would be easier to read if you settled on either NULL or '' for missing times.

If you store only UTC values in a TIMESTAMP, you can let the user's timezone take care of coverting to local time -- this would eliminate quite a few columns and simplify the UPDATEs.

I'll make a stab at the question... Do SHOW CREATE PROCEDURE settle_attendance;, you may find that the CHARACTER SET or COLLATION is inconsistent with what you think it should be.