0

I have multiple MySQL InnoDb tables, I am trying to alter/insert/modify many of them in a SQL transaction, I set autocommit to false, so that if some SQL statements fail, I do commit the rest of statements:

set autocommit = 0;
START TRANSACTION;
use database1;
update `table1` set tkey = '3' .....;
update `table1` set tkey = '4' .......;
update `table1` set tkey = '5' .......;
update `table1` set tkey = '6' .......;
ALTER TABLE `table2` ADD COLUMN `.....` bool NOT NULL; -- This fails
COMMIT;

In above transaction the alter against table2 fails, but still the updates to table 1 have happened, how can I avoid that? I want a rollback even if one statement fails.

2

In MySQL most DDL statements, including ALTER TABLE, implicitly issue a COMMIT before executing. It's described in the manual.

One way to do what you want is reorder your statements, placing updates after the alter.

  • This applies also to other DBMS – frlan Mar 3 '15 at 17:45
  • 1
    @frlan -- this applies to some other DBMSes. – mustaccio Mar 3 '15 at 17:46
  • What if the sql has insert/delete/update/alter statements all in one? – user60611 Mar 3 '15 at 18:23
  • @user60611 -- I'm afraid I don't understand the question. How can you have multiple statements "all in one"? – mustaccio Mar 3 '15 at 21:41
0

Certain statements trigger an implicit commit. ALTER TABLE is definitely one of them.

See my post row locking within ACID transaction innodb on all statements that do this.

With regard to your transaction, any of the updates that fail will rollback previous updates. You need to create a flag that checks whether or not the transaction went to completion. You need to setup Dynamic SQL to pull that off. Perhaps something like this:

SET @OK_TO_ALTER = 0;
START TRANSACTION;
use database1;
update `table1` set tkey = '3' .....;
update `table1` set tkey = '4' .......;
update `table1` set tkey = '5' .......;
update `table1` set tkey = '6' .......;
SET @OK_TO_ALTER = 1;
COMMIT;
SET @sql = 'SELECT 1 DoNothing';
SET @sql = IF(@OK_TO_ALTER=1,'ALTER TABLE `table2` ADD COLUMN `.....` bool NOT NULL',@sql);
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

BTW you do not need set autocommit = 0; for a START TRANSACTION; ... COMMIT; block.

  • Looks like it will work in this case, but I have a sql script which has many such statements, with insert/update/delete sql statements in it, The above approach will add a lot of IF conditions. are there any other ways to make it ACID? – user60611 Mar 3 '15 at 18:22
  • Please look over my post dba.stackexchange.com/questions/58709/…. Any of your transactions with those statements must have those statement placed somewhere else in your code. – RolandoMySQLDBA Mar 3 '15 at 18:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.