MySQL Forums
Forum List  »  Performance

Re: Values not inserting into MySQL Database table
Posted by: Peter Brawley
Date: November 11, 2015 12:28PM

In InnoDB, some errors don't rollback the whole transaction:

drop table if exists t;
create table t(i int primary key, j int not null) engine=innodb;

start transaction;
  insert into t values(1,1),(2,2);
  update t set j=j+1;
  insert into t values(2,null); -- ERROR 1048 (23000): Column j cannot be null
commit;                      
select * from t;                -- TRANSACTION DID NOT ROLL BACK 
+---+---+
| i | j |
+---+---+
| 1 | 2 |
| 2 | 3 |
+---+---+


-- ROLLBACK ON ALL ERRORS & WARNINGS:
truncate t;
drop procedure if exists t;
delimiter go
create procedure t()
begin
  start transaction;
  begin
    declare exit handler for sqlexception, sqlwarning, not found 
      rollback;
    insert into t values(1,1),(2,2);
    update t set j=j+1;
    insert into t values(3,null);
  end;
  commit;
end ;
go
delimiter ;

call t(); 
select * from t; -- Empty set


Is YOUR transaction coded to rollback on all errors & warnings?

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Values not inserting into MySQL Database table
2044
November 11, 2015 12:28PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.