MYSQL Cursor running endlessly

Question

All right, first of all the disclaimer: I know using cursors is one of the worst practices for database programming, but I didn't find any other way of doing this.

I am creating a blog/chat like application so that our employees can communicate with the client, and have all company/client interactions saved and timestamped (there has been some response time comlaints).

Anyway, I am struggling to keep track of the unread messages, so I figured that I could make a simple math if I knew which messages have been read for each user, so whenever my app calls the messages table for the messages, it will update a separate table called "Read messages" where I will keep track of which messages have been read.

I think I have the logic, but whenever I try to run it, mysql just hangs thinking until it times out, however, if I check the 'messages read' table, the info was indeed updated.

Here's the code:

BEGIN 

DROP TEMPORARY TABLE IF EXISTS fetchedMessages;
CREATE TEMPORARY TABLE fetchedMessages AS (

SELECT 

blg.pkIdEntrada AS idMensaje,
blg.grlContenido AS Contenido,
blg.grlTimestamp AS FechaHora,
blg.grlReferencia AS Referencia,
usu.pkIdUsuario AS IdUsuario,
usu.gPrimerNombre AS PrimerNombre,
usu.gPrimerApellido AS PrimerApellido,
tml.pkIdRead AS idLectura


FROM blgEntries blg

LEFT JOIN tbl_usuarios      usu ON blg.fkIdUsuario = usu.pkIdUsuario
LEFT JOIN tbl_mensajesLeidos    tml ON blg.pkIdEntrada = tml.fkIdMensaje AND blg.fkIdUsuario = tml.fkIdUser

WHERE 

blg.grlReferencia = numReferencia

ORDER BY 
FechaHora DESC
);
    BEGIN
        DECLARE done INT DEFAULT FALSE;
        DECLARE c_idMess INT;
        DECLARE curs CURSOR 
            FOR SELECT idMensaje FROM fetchedMessages WHERE idLectura IS NULL AND idMensaje IS NOT NULL;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;


        OPEN curs;
            LOOP 
                FETCH curs INTO c_idMess;
                INSERT INTO tbl_mensajesLeidos (fkIdMensaje, fkIdUser) VALUES (c_idMess, idEmp) ON DUPLICATE KEY UPDATE fkIdMensaje = c_IdMess, fkIdUser = idEmp; 
                ##SELECT CONCAT(c_idMess, ' - ', idEmp);
            END LOOP;

        CLOSE curs;
    END;

SELECT * FROM fetchedMessages;

END

Any ideas??

Answer 1

Most of the examples i see relating to MySql cursors and loops explicitly check the found variable in the loop for its true condition, 1 in your case, and exit the loop if true. You seem to be missing that step.

I'm also curious why if you're declaring your found variable as an integer you're defaulting it to false instead of 0.