CODEWITHSUNDEEP
mysql
Dreamer
Dreamer

Reputation: 646

What is correct way to handle exception and rollback a transaction in mysql on error?

Background :

I'm just a beginner in MYSQL. And trying to do transaction operations in MYSQL.

Query:

I am trying to alter table in mysql, I am just changing data type of a column. To do this operation, using a tansaction. What is the good way to rollback a transaction on error ?

SQL Sever Example to Handle The Exception:

USE XYDB

 BEGIN TRY

 BEGIN TRANSACTION;

    ALTER TABLE <table> ALTER COLUMN <column> MEDIUMTEXT;
 COMMIT TRANSACTION;  

END TRY 

BEGIN CATCH  

IF @@TRANCOUNT > 0  

    ROLLBACK TRANSACTION;  

    SELECT   

    ERROR_NUMBER() AS ErrorNumber  

,ERROR_SEVERITY() AS ErrorSeverity  

    ,ERROR_STATE() AS ErrorState  

    ,ERROR_PROCEDURE() AS ErrorProcedure  

    ,ERROR_LINE() AS ErrorLine  

    ,ERROR_MESSAGE() AS ErrorMessage;  

    END CATCH;  
GO

Upvotes: 0

Views: 1457

Answers (3)

0x49D1
0x49D1

Reputation: 8704

The previous commenters answered you about whats wrong with your posted code. About the exception handling procedure itself: here is the example of rollback code block with ability to log the error to some other table without loosing the exception details in MySQL and throwing the error again after logging it.

# CREATE PROCEDURE AND OTHER DECLARE STATEMENTS HERE
# ....

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;

    ROLLBACK;

    SET @full_error = CONCAT('ERR:', @errno, '(', @sqlstate, '):', @text);

    CALL sp_logaction(@full_error); # Some logging procedure

    RESIGNAL;
END;
# PROCEDURE BODY WITH START TRANSACTION & COMMIT HERE
# .....

Upvotes: 0

Suresh
Suresh

Reputation: 489

Alter Table is a DDL command, you can use same in MySQL as below:

ALTER TABLE <table> CHANGE COLUMN <column> Parchar(100);

The above query is auto commit , you can never rollback the same.

If you want to rollback ,again run alter table command with desired data type of column.

Upvotes: 1

Vatev
Vatev

Reputation: 7590

It is not possible to use DDL (create/alter table) statements in a transaction in MySQL.

These statements have their own "transaction" (separate for each statement), which is committed automatically.

If you run an ALTER TABLE ... in a transaction, the transaction will be committed before the alter.

If it is only one statement, there is no need to do anything, it will either fail (and the table will remain unchanged), or succeed (and the table will be altered correctly).

If you need to do multiple ALTER ..'s, there is no way to group them into 1 transaction.

Upvotes: 1

Related Questions