Transacciones en procedimientos almacenados

[alejandrolll]

Hola a todos, espero me puedan orientar con mi problema.

Tengo un procedimiento almacenado que duplica un registro dado un id, y se duplica en todas las tablas donde aparezca ese id, y en las tablas con las que esta relacionado.

Para esto tengo varios cursores que duplican el registros el las diferentes tablas, y ahora ahorita lo hace bien, el problema comienza cuando ocurre algún error en la inserción en alguno de los cursores, es decir, si ya había ejecutado 2 correctamente, y en el tercero se provoca el error, la ejecución se detiene, pero lo que se inserto en los dos primeros cursores, ya no se deshace, y quiero que se ejecuten todos los cursores o no se ejecute ninguno si es que ocurre algún error.
MI código esta así actualmente, lo pongo incompleto ya que lo importante es lo delas transacciones.

Código MySQL:

DELIMITER //
CREATE PROCEDURE clonar_materia(id_materia_clonar INT)
 
BEGIN
DECLARE done INT DEFAULT 0;
-- variables de para los cursores.
DECLARE ...;
 
DECLARE cursor2 CURSOR FOR SELECT * FROM tarea2_tmp;
DECLARE cursor3 CURSOR FOR SELECT * FROM tabla_relacionada_tmp;
 
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
 
DROP TABLE IF EXISTS tabla2_tmp;
CREATE TEMPORARY TABLE tabla2_tmp ...;
 
DROP TABLE IF EXISTS tabla_relacionada_tmp;
CREATE TEMPORARY TABLE tabla_relacionada_tmp...
 
INSERT INTO tabla SELECT ... WHERE id_=id_materia_clonar;
SELECT MAX(id_materia) from tabla INTO nuevo_id_materia;
 
OPEN cursor2;
REPEAT
   FETCH cursor2 INTO ... ; 
    IF NOT done THEN
            INSERT INTO tabla2 ...;
  UNTIL done END REPEAT;
SET done=0;
CLOSE cursor2;
 
OPEN cursor3;
 REPEAT
      FETCH cursor3 INTO ...; 
         IF NOT done THEN
             INSERT INTO tabla_relacionada VALUES ...
             SET SQL_SAFE_UPDATES=0;
             UPDATE tabla_relacionada SET ...
         END IF;
 UNTIL done END REPEAT;
 SET done=0;
CLOSE cursor3;
 
END
//
DELIMITER ;
 

[gnzsoloyo]

No veo en tu código el punto donde inicias la transacción, por lo que es posible que esté antes de ejecutarlo.
Si es así, si la transacción inicia y termina fuera del SP, te has olvidado de tener en cuenta que existen sentencias que provocan COMMIT implícito, como es el caso del CREATE y DROP. Y como estás borrando y creando tablas dentro del SP, esta acción puede estar generando ese COMMIT en la transacción, y por ende toda operación DML posterior está fuera de una transacción...

Si el tema es que supones que no debería funcionar, porque las tablas que creas son TEMPORARY, te aclaro que el DROP que haces no las está afectando, porque las tablas creadas con CREATE TEMPORARY TABLE se deben eliminar con DROP TEMPORARY TABLE (eso en el caso de que la conexión no se cierre), y eso no lo estás haciendo.
Además, el que sea TEMPORARY no afecta la restricción del DROP y el CREATE para una transacción.

[alejandrolll]

MUchas gracias por la informacion, la duda era de como implementar las transacciones dentro de procedimiento, ya lo pude hacer, solo basto con agregar unos manejador para ROLLBACK y listo, todo va al 100. Dejo el codigo para que lo vean.

Saludos
DELIMITER //
CREATE PROCEDURE clonar_materia(id_materia_clonar INT)

BEGIN
DECLARE done INT DEFAULT 0;
-- variables de para los cursores.
DECLARE ...;

DECLARE cursor2 CURSOR FOR SELECT * FROM tarea2_tmp;
DECLARE cursor3 CURSOR FOR SELECT * FROM tabla_relacionada_tmp;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;

DROP TABLE IF EXISTS tabla2_tmp;
CREATE TEMPORARY TABLE tabla2_tmp ...;

DROP TABLE IF EXISTS tabla_relacionada_tmp;
CREATE TEMPORARY TABLE tabla_relacionada_tmp...

SET autocommit = 0;
START TRANSACTION;

INSERT INTO tabla SELECT ... WHERE id_=id_materia_clonar;
SELECT MAX(id_materia) FROM tabla INTO nuevo_id_materia;

OPEN cursor2;
REPEAT
FETCH cursor2 INTO ... ;
IF NOT done THEN
INSERT INTO tabla2 ...;
UNTIL done END REPEAT;
SET done=0;
CLOSE cursor2;

OPEN cursor3;
REPEAT
FETCH cursor3 INTO ...;
IF NOT done THEN
INSERT INTO tabla_relacionada VALUES ...
SET SQL_SAFE_UPDATES=0;
UPDATE tabla_relacionada SET ...
END IF;
UNTIL done END REPEAT;
SET done=0;
CLOSE cursor3;
COMMIT;

END
//
DELIMITER ;

[gnzsoloyo]

Te recuerdo que no estas usando bien el DROP, porque así como lo escribes intenta borrar una tabla fija, y no una TEMPORARY.

[alejandrolll]

Ok gracias, es que copie el codigo de arriba y le agregue las nuevas lineas, pero si en mi codigo si lo puse. A proposito me sale un warning "mysql error 1329 no data - zero rows fetched selected or processed" A que se debera?, ya que el procedmiento funciona perfectamente.

Saludos

[gnzsoloyo]

A que si ya impactaron los cambios previstos y/o posibles en los datos, MySQL no hará modificaciones si ejecutas el mismo proceso con los mismos parámetros.
Tienes dos soluciones:
1) Agregar un handler para ese caso.
2) Agregarle la cláusula IGNORE a los UPDATE, REPLACE y DELETE que tengas el el SP, de modo que no generen ese warning.