Re: Values not inserting into MySQL Database table

[Peter Brawley]

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?