How to undo all statements inside a MySQL transaction?

Question

I need to update a specific table column ( bigtable ) containing the identifiers of another table (FK restriction to oldsmalltable ) to point to identifiers in another table (FK restriction on newsmalltable ). This is basically what I am doing:

 DELIMITER // CREATE PROCEDURE updatebigtable () BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING ROLLBACK; START TRANSACTION; ALTER TABLE bigtable DROP FOREIGN KEY bigtable_ibfk_1, MODIFY smalltable_id SMALLINT ; UPDATE bigtable SET smalltable_id=CASE smalltable_id WHEN 1 THEN 1592 WHEN 2 THEN 1593 WHEN 3 THEN 1602 ... ELSE 0 END; ALTER TABLE bigtable ADD CONSTRAINT bigtable_ibfk_1 FOREIGN KEY(smalltable_id) REFERENCES newsmalltable(id); COMMIT; END// DELIMITER ; CALL updatebigtable(); DROP PROCEDURE updatebigtable; 

I need to make sure that if for some reason the new foreign key fails (for example, with columns with different types, an error will occur in the last alter table expression), UPDATE and the first alter table should also be rolled back, i.e. they must remain as they were originally.

According to the MySQL documentation , using START TRANSACTION , autocommit is disabled for this transaction, which will not allow:

as soon as you execute the statement that updates (modifies) the table, MySQL saves the update on disk to make it permanent.

I found this question as minimally related to mine:

How can I use transactions in my MySQL stored procedure?

If this error, which I mentioned, occurs inside a transaction, the previous statements were already executed and the updates were "constantly running on disk" ...

I also tried placing SET autocommit=0; before creating the procedure, but the behavior still remains unchanged ... Am I missing something? Or is this the expected MySQL transaction rollback behavior?

If that matters, I'm using MySQL v.5.6.17.

Answer 1

Operators

ALTER TABLE always cause implicit execution ( section 13.3.3 from the MySQL documentation , thanks wchiquito ), which means that even if they are inside the START TRANSACTION; block START TRANSACTION; ... COMMIT; , there will be as many commits as the number of changes made inside this block.

Locking a table is also not an option ( from problems with ALTER TABLE ):

If you use ALTER TABLE in a transactional table or use Windows, ALTER TABLE unlocks the table if you made LOCK TABLE on it. This is because InnoDB and these operating systems cannot delete the table that is being used.

The only option that avoids unwanted reads / writes during change and update statements emulates all ALTER TABLE steps:

• Create a new table named A-xxx with the requested structural changes.
• Copy all rows from the source table to A-xxx.
• Rename the source table to B-xxx.
• Rename A-xxx to the original table name.
• Delete B-xxx.

Thus, updates can be performed in the new table (after step 2), and the only time bigtable unavailable, steps 3 and 4 are performed (renaming).

Answer 2

Use the TRY CATCH block TO START TRAN before the BEGIN TRY and ROLLBACK TRAN inside the CATCH block.