Foros del Web » Programación para mayores de 30 ;) » Bases de Datos General » Mysql »

Transacciones en procedimientos almacenados

Estas en el tema de Transacciones en procedimientos almacenados en el foro de Mysql en Foros del Web. 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 ...
  #1 (permalink)  
Antiguo 26/10/2012, 11:30
 
Fecha de Ingreso: octubre-2012
Mensajes: 3
Antigüedad: 11 años, 5 meses
Puntos: 0
Transacciones en procedimientos almacenados

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:
Ver original
  1. DELIMITER //
  2. CREATE PROCEDURE clonar_materia(id_materia_clonar INT)
  3.  
  4. -- variables de para los cursores.
  5. DECLARE ...;
  6.  
  7. DECLARE cursor2 CURSOR FOR SELECT * FROM tarea2_tmp;
  8. DECLARE cursor3 CURSOR FOR SELECT * FROM tabla_relacionada_tmp;
  9.  
  10. DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  11.  
  12. DROP TABLE IF EXISTS tabla2_tmp;
  13. CREATE TEMPORARY TABLE tabla2_tmp ...;
  14.  
  15. DROP TABLE IF EXISTS tabla_relacionada_tmp;
  16. CREATE TEMPORARY TABLE tabla_relacionada_tmp...
  17.  
  18. INSERT INTO tabla SELECT ... WHERE id_=id_materia_clonar;
  19. SELECT MAX(id_materia) from tabla INTO nuevo_id_materia;
  20.  
  21. OPEN cursor2;
  22.    FETCH cursor2 INTO ... ;
  23.     IF NOT done THEN
  24.             INSERT INTO tabla2 ...;
  25.   UNTIL done END REPEAT;
  26. SET done=0;
  27. CLOSE cursor2;
  28.  
  29. OPEN cursor3;
  30.       FETCH cursor3 INTO ...;
  31.          IF NOT done THEN
  32.              INSERT INTO tabla_relacionada VALUES ...
  33.              SET SQL_SAFE_UPDATES=0;
  34.              UPDATE tabla_relacionada SET ...
  35.          END IF;
  36.  UNTIL done END REPEAT;
  37.  SET done=0;
  38. CLOSE cursor3;
  39.  
  40. //
  41. DELIMITER ;

Última edición por gnzsoloyo; 26/10/2012 a las 12:30 Razón: Codigo sin etiquetar.
  #2 (permalink)  
Antiguo 29/10/2012, 07:15
Avatar de gnzsoloyo
Moderador criollo
 
Fecha de Ingreso: noviembre-2007
Ubicación: Actualmente en Buenos Aires (el enemigo ancestral)
Mensajes: 23.324
Antigüedad: 16 años, 4 meses
Puntos: 2658
Respuesta: Transacciones en procedimientos almacenados

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.
__________________
¿A quién le enseñan sus aciertos?, si yo aprendo de mis errores constantemente...
"El problema es la interfase silla-teclado." (Gillermo Luque)

Última edición por gnzsoloyo; 29/10/2012 a las 07:21
  #3 (permalink)  
Antiguo 30/10/2012, 11:45
 
Fecha de Ingreso: octubre-2012
Mensajes: 3
Antigüedad: 11 años, 5 meses
Puntos: 0
Respuesta: Transacciones en procedimientos almacenados

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 ;
  #4 (permalink)  
Antiguo 30/10/2012, 11:51
Avatar de gnzsoloyo
Moderador criollo
 
Fecha de Ingreso: noviembre-2007
Ubicación: Actualmente en Buenos Aires (el enemigo ancestral)
Mensajes: 23.324
Antigüedad: 16 años, 4 meses
Puntos: 2658
Respuesta: Transacciones en procedimientos almacenados

Te recuerdo que no estas usando bien el DROP, porque así como lo escribes intenta borrar una tabla fija, y no una TEMPORARY.
__________________
¿A quién le enseñan sus aciertos?, si yo aprendo de mis errores constantemente...
"El problema es la interfase silla-teclado." (Gillermo Luque)
  #5 (permalink)  
Antiguo 30/10/2012, 12:15
 
Fecha de Ingreso: octubre-2012
Mensajes: 3
Antigüedad: 11 años, 5 meses
Puntos: 0
Respuesta: Transacciones en procedimientos almacenados

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
  #6 (permalink)  
Antiguo 30/10/2012, 12:22
Avatar de gnzsoloyo
Moderador criollo
 
Fecha de Ingreso: noviembre-2007
Ubicación: Actualmente en Buenos Aires (el enemigo ancestral)
Mensajes: 23.324
Antigüedad: 16 años, 4 meses
Puntos: 2658
Respuesta: Transacciones en procedimientos almacenados

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.
__________________
¿A quién le enseñan sus aciertos?, si yo aprendo de mis errores constantemente...
"El problema es la interfase silla-teclado." (Gillermo Luque)

Etiquetas: transacciones
Atención: Estás leyendo un tema que no tiene actividad desde hace más de 6 MESES, te recomendamos abrir un Nuevo tema en lugar de responder al actual.
Respuesta




La zona horaria es GMT -6. Ahora son las 07:08.