MySQL Forums
Forum List  »  General

Re: Random data loss, less than 0.1% of the time - how to prevent it
Posted by: Peter Brawley
Date: March 27, 2014 11:29AM

> Is this long list of possibilities documented anywhere (concisely, so I don't have to wade through thousands of pages of text in order to know how to proceed)?

A basic fact of error control is that an exhaustive list of such error possibilities is impossible in principle.

Here's a simple example of what can go wrong:

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);
commit;
select * from t;
+---+---+
| i | j |
+---+---+
| 1 | 2 |
| 2 | 3 |
+---+---+

You might have expected the whole transaction to roll back. It didn't. For that you would need something like ...

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;
  select * from t;
end ;
go
delimiter ;
call t();
Empty set (0.11 sec)

Now everything in the transaction rolls back

> Do you have, or can you point me to, a document on the web that describes such a beefed up error control

Read about ...

Error handlers
Signal and Resignal
Get Diagnostics

I'd first apply transaction blocks and error handlers.



Edited 1 time(s). Last edit at 03/27/2014 11:31AM by Peter Brawley.

Options: ReplyQuote


Subject
Written By
Posted
Re: Random data loss, less than 0.1% of the time - how to prevent it
March 27, 2014 11:29AM


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.