Lalo Santos Lalo Santos -4 years ago 144
MySQL Question

MYSQL Cursor running endlessly

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download