MySQL Bugs: #68831: HANDLER FOR SQLEXCEPTION not catching Error 1452 (FK constraint) for UPDATE

Bug #68831	HANDLER FOR SQLEXCEPTION not catching Error 1452 (FK constraint) for UPDATE
Submitted:	2 Apr 2013 6:25	Modified:	7 Aug 2013 18:57
Reporter:	Ben Heath	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.6.10	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	condition handler, error handling, innodb, SqlException

Description:
Condition handler does not seem to handle foreign key violation (error code 1452) where an attempt is made to UPDATE a child row with a value for a foreign key not matched in the parent table.  Defining the handler for that error specifically (1452), for SQLSTATE '23000' or the generic SQLEXCEPTION all fail.  Defining the handler to EXIT or CONTINUE makes no difference.  Also, does not make a difference if FK field allows NULLs.

Interestingly, the same foreign key violation (ie value not matched in parent table) when attempting to INSERT a new child record is handled by the same handler definition (error code is the same 1452).

Similarly, other errors are captured by the handler as expected (eg. use of NULL when NULL not allowed, using a value beyond the range of column datatype, etc)

Obviously I'm using InnoDB tables here.

How to repeat:
Fairly simple, just create two tables, with an FK to the PK of the other.  Then, create a stored procedure to update the child table with a handler declared, something like below:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
			GET DIAGNOSTICS condition 1
			@SQLState = RETURNED_SQLSTATE, @SQLMessage = MESSAGE_TEXT; 
			SELECT CONCAT('Database error occurred, state - ',@SQLState, '; error msg - ', @SQLMessage) As errorString; 
        END;

Then make sure you attempt to update the FK field of the child with a value which is not matched in the parent table.

Here is a complete test case:

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -uroot -proot -P3314 test
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table m(id int primary key, c1 int) engine=InnoDB;
Query OK, 0 rows affected (3.65 sec)

mysql> create table s(id int primary key, fk int, foreign key(fk) references m(id)) engine=InnoDB;
Query OK, 0 rows affected (1.62 sec)

mysql> insert into m values(1,1);
Query OK, 1 row affected (0.31 sec)

mysql> insert into s values(1,1);
Query OK, 1 row affected (0.22 sec)

mysql> insert into s values(1,2);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into s values(2,2);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`test`.`s`, CONSTRAINT `s_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `m` (`id`)
)
mysql> delimiter //
mysql> create procedure pr1()
    -> begin
    -> declare exit handler for sqlexception
    -> begin
    ->   get diagnostics condition 1
    ->   @SQLState = RETURNED_SQLSTATE, @SQLMessage = MESSAGE_TEXT;
    ->   select @SQLState state, @SQLMessage message;
    -> end;
    ->
    -> insert into s values(3,3);
    -> end;
    -> //
Query OK, 0 rows affected (0.44 sec)

mysql> call pr1()//
+-------+-----------------------------------------------------------------------
------------------------------------------------------------------------+
| state | message
                                                                        |
+-------+-----------------------------------------------------------------------
------------------------------------------------------------------------+
| 23000 | Cannot add or update a child row: a foreign key constraint fails (`tes
t`.`s`, CONSTRAINT `s_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `m` (`id`)) |
+-------+-----------------------------------------------------------------------
------------------------------------------------------------------------+
1 row in set (0.19 sec)

Query OK, 0 rows affected (0.23 sec)

mysql> create procedure pr2()
    -> begin
    -> declare exit handler for sqlexception
    -> begin
    ->   get diagnostics condition 1
    ->   @SQLState = RETURNED_SQLSTATE, @SQLMessage = MESSAGE_TEXT;
    ->   select @SQLState state, @SQLMessage message;
    -> end;
    -> update s set fk = 3;
    -> end;
    -> //
Query OK, 0 rows affected (0.03 sec)

mysql> call pr2()//
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`test`.`s`, CONSTRAINT `s_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `m` (`id`)
)

Now, copy/paste version to simplify verification:

create table m(id int primary key, c1 int) engine=InnoDB;
create table s(id int primary key, fk int, foreign key(fk) references m(id)) engine=InnoDB;
insert into m values(1,1);
insert into s values(1,1);
delimiter //
create procedure pr1()
begin
declare exit handler for sqlexception
begin
  get diagnostics condition 1
  @SQLState = RETURNED_SQLSTATE, @SQLMessage = MESSAGE_TEXT;
  select @SQLState state, @SQLMessage message;
end;
  insert into s values(3,3);
end;
//
create procedure pr2()
begin
declare exit handler for sqlexception
begin
  get diagnostics condition 1
  @SQLState = RETURNED_SQLSTATE, @SQLMessage = MESSAGE_TEXT;
  select @SQLState state, @SQLMessage message;
end;
  update s set fk = 3;
end;
//

delimiter ;

# this works as expected
call pr1();

# this does not work - no results from handler
call pr2();

Have fun.

Hello!

Thanks for the bug report. Verified as described.

Thanks for the prompt responses and indeed to Valeriy Kravchuk for posting code to verify the issue (I left that out myself because a) I was certain it wasn't just some flaw with my own code, and b) it is such a straightforward case anyway).

I'm not super anxious for this to be resolved (although that would obviously be nice), just glad that it has been independently verified more than anything.

My main problem now is this - what other cases might exist where certain exceptions cannot be handled, particularly one as common as an FK constraint violation?  To all who come across this post, please add any further cases you come across.  Indeed, are there any known cases where condition handlers will not catch certain exceptions, from previous versions of MySQL perhaps?

Cheers

The problem here is not really condition handlers, but how errors are reported 
internally. Errors can be tagged as fatal, which among other things means that 
condition handlers won't catch them. Fatal errors can be e.g. out of memory, 
disk error etc. The bug here is that the FK error was tagged as fatal for UPDATE 
(but not for INSERT).

This is in itself an easy thing to fix. But as you say, we need to
investigate if which other errors reported during UPDATE (or even in general) 
are by mistake tagged as fatal and thus not caught by condition handlers.

Thanks for the update Jon.  Is there by any chance a list of these fatal errors which handlers do not catch?

Thanks

No, because a given error might not always be fatal. It depends on the situation
where it is reported. E.g. the same FK error was fatal for UPDATE but not for INSERT.

Noted in 5.7.2 changelog.

Error handling could treat some errors differently in different
contexts. For example, ER_NO_REFERENCED_ROW_2 foreign-key errors
could be treated differently in condition handlers than otherwise.

Why was this closed? There is no indication here that it was resolved or even addressed. 

This is affecting me right now in MySQL 5.6.

Hi John,

This bug was closed when we fixed this in 5.7.2. You are experiencing this problems since you have a 5.6 installation.

Hi

As a MySQL novice, the post helped to exactly match the situation I encountered.  Thanks to Ben, Valeriy Kravchuk(for scenario) and all contributors.
Also appreciate Ben Heath  invite other to share cases for community.

My stored proc. situation was not simple.  
Nearly 3 tables gets rec. inserted/updated before final update to transaction tabel.
All these are in a start trans and commit.  Expection handler has rollback statement based on transaction open/started mode.

Becuase of this bug - which I din't know until came across this page,  
All first 3 tables got affected except the last table-update due to fk violation.

So the atomicity was lost.

I am trying with MySQL Installer 5.7.6 m16 as in http://dev.mysql.com/downloads/windows/installer/
Hope the problem is rectified with least migration 5.6.92 to 5.7.6 related issues.

Thanks 
- ursri