MySQL Bugs: #95424: 'Declare continue handler xxx' doesn't works for the error thrown from my engine

Bug #95424	'Declare continue handler xxx' doesn't works for the error thrown from my engine
Submitted:	20 May 2019 10:21	Modified:	30 May 2019 2:56
Reporter:	wu winnie	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7.25	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
hi, I wrote my own MySQL storage engine, and I was thrown a error from my engine, and the error would finally been wrapped by MySQL standard error - 1296

I have written mysql stored procedure like this:

DELIMITER |
DROP PROCEDURE IF EXISTS hub_migrate_20150302_1|
CREATE PROCEDURE hub_migrate_20150302_1()
  SQL SECURITY INVOKER
BEGIN
  -- Ignore error reading license to move
  DECLARE CONTINUE HANDLER FOR 1296 BEGIN END;

  INSERT INTO te_license SELECT * FROM te_license WHERE license_type != 'INVALID';
END|
CALL hub_migrate_20150302_1()|
DROP PROCEDURE IF EXISTS hub_migrate_20150302_1|
DELIMITER ; 

in mysql5.6, it works well, no error would happen.
but in mysql5.7, it would always show error like this:
ERROR 1296 (HY000): Got error 4200 '{1198} The license key has already been added' from HUB 

it's quite weird, I have already use this sql "DECLARE CONTINUE HANDLER FOR 1296 BEGIN END;" in the stored procedure, why it still give me 1296 error in mysql5.7?

then I tried to use this stored procedure:

DELIMITER |
DROP PROCEDURE IF EXISTS hub_migrate_20150302_1|
CREATE PROCEDURE hub_migrate_20150302_1()
  SQL SECURITY INVOKER
BEGIN
  DECLARE CONTINUE HANDLER FOR 1296 BEGIN END;
  SIGNAL SQLSTATE 'HY000'
            SET MYSQL_ERRNO = 1296;
END|
CALL hub_migrate_20150302_1()|
DROP PROCEDURE IF EXISTS hub_migrate_20150302_1|
DELIMITER ; 

it would not give me any error in mysql5.7

note:  hub_license is using my own storage engine, and the error is thrown from my own engine, so I am wondering if mysql5.7 has done some special implemantation to customerized engine error

How to repeat:
none

typo: hub_license -> te_license

I debug my source code, I modified my source code to be the simplest:
int ha_hub::end_bulk_insert()
{
    int my_err = 0;
    if (this->bulk_insert)
    {
        ......		
	my_err = 4200;
	set_my_errno(my_err);
    }

    ...	 	
}
    DBUG_RETURN(my_err);
}

this issue stil exists.

but in mysql5.6, no such issue.

by the way, I tried
DECLARE CONTINUE HANDLER FOR SQLSTATE 'HY000' BEGIN END;

still no help.

it's 5.7.25 MySQL server enterprise version (archive)

Hi,

Thank you for your bug report.

First, would you like to explain what do you mean by:

"
MySQL server enterprise version (archive)

"

What I am wondering about is what exactly is "archive" ???

Next, we can not possibly repeat your test case without your storage engine.

There were changes in the SE interface between 5.6 and 5.7. They are all described in our 5.7 changelogs, that you can find on dev.mysql.com.

modified ha_example plugin dll

Attachment: ha_example.dll (application/octet-stream, text), 35.00 KiB.

modified ha_example.cc

Attachment: ha_example.cc (text/plain), 29.56 KiB.

modified ha_example.h

Attachment: ha_example.h (text/plain), 8.93 KiB.

firstly, "archive" -means I installed MySQL Using a noinstall ZIP Archive, see https://dev.mysql.com/doc/refman/5.7/en/windows-install-archive.html.

secondly,
I found I can easily reproduce this issue by ha_example.dll plugin I uploaded.
reproduce steps:
1) in my.ini file
[mysqld]
…
plugin-load=ha_example.dll

2)start MySQL server, then execute the following queries:
create database test;
CREATE TABLE test_example (i INT) ENGINE = EXAMPLE;
INSERT INTO test.test_example VALUES(1);

then we would get this error:
ERROR 1296 (HY000): Got error 4200 'this is winnie testing error message.' from EXAMPLE

then run the following quries, which includes calling a store procedure:
use test;
DELIMITER |
DROP PROCEDURE IF EXISTS hub_migrate_20150302_1|
CREATE PROCEDURE hub_migrate_20150302_1()
  SQL SECURITY INVOKER
BEGIN
  DECLARE CONTINUE HANDLER FOR 1296 BEGIN END;
  INSERT INTO test.test_example VALUES(1);
END|
CALL hub_migrate_20150302_1()|
DROP PROCEDURE IF EXISTS hub_migrate_20150302_1|
DELIMITER ;

we can see the error still throws when calling the store procedure, although I've already "DECLARE CONTINUE HANDLER FOR 1296 BEGIN END;"

I also have uploaded ha_example.cc and ha_example.h, I just made some fix based on mysql 5.7.25 source code.
see:

int ha_example::write_row(uchar *buf)
{
  DBUG_ENTER("ha_example::write_row");
  /*
    Example of a successful write_row. We don't store the data
    anywhere; they are thrown away. A real implementation will
    probably need to do something with 'buf'. We report a success
    here, to pretend that the insert was successful.
  */
  int errnum = 4200;   <-- I add this line
  set_my_errno(errnum);  <-- I add this line

  DBUG_RETURN(errnum); <-- I modified this line
}

bool
ha_example::get_error_message(int error, String *buf)  <-- I add this function

{
	std::ostringstream s;
	s << "this is winnie testing error message.";
	buf->copy(s.str().c_str(), s.str().length(), system_charset_info);
	DBUG_RETURN(false);
}

error shows when call the store procedure

Attachment: error_info.png (image/png, text), 34.42 KiB.

Thank you for your considerable effort.

We do still need some confirmations from you.

First of all, ha_example.cc and .h is your entire source code for the storage engine ??? I guess you realise that storage engine has other necessary calls ....

Second, I could try your test case on macOS ... let me know if your problem is Windows-specific or not ...

Last, but not least, you wrote that you have MySQL Enterprise Edition. That one is available only to our subscribers. Hence, is it a community or Enterprise package that you are using ????

1) I downloaded MySQL source code 5.7.25 from MySQL official GitHub site. the ha_example.cc and .h  are source files from that. like I said above, I just did some modification for the two files - in order to reproduce this issue. 
I use visual studio 2015 to compile it(ha_example.dll).  and then replace the ha_example.dll located in MySQL non-install binaries(archive version).

2) I tested it on windows10

3) our company has license for MySQL SE version, for 5.7.25, EE and SE version use the same binary zip.

my own storage engine is quite complicated. I can't give you the source code. so I made the simplest demo one for you. It can show you my issue clearly. 
Thanks!

We will have to investigate whether your example storage engine is sufficient for this issue.

Hi Mr. Winnie,

Our Development team has studied your report and found a cause for your problems ... Explanation follows .....

In 5.7, error handling code is improved. Even handler function
is_fatal_error() is refactored.

Signature of is_fatal_error() in 5.7:

virtual bool is_fatal_error(int error);

If handler implementation (ha_example in this case) is not implementing
the is_fatal_error() then default implementation is invoked. In default
implementation all the errors considered as FATAL except

HA_ERR_FOUND_DUPP_KEY, HA_ERR_FOUND_DUPP_UNIQUE, HA_ERR_ROW_IS_REFERENCED, HA_ERR_NO_REFERENCED_ROW, HA_ERR_NULL_IN_SPATIAL

Due to this fact, error number 4200 is considered as FATAL error and it is not handled by the SP handlers 

We can suggest user to implement the handler function is_fatal_error().

We implemented is_fatal_error() in the ha_example, like this:

handler.h:

+   virtual bool is_fatal_error(int error) { return false; }

mysql> INSTALL PLUGIN example SONAME 'ha_example.so';
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE test_example (i INT) ENGINE = EXAMPLE;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO test.test_example VALUES(1);
ERROR 1296 (HY000): Got error 4200 'this is winnie testing error message.' from EXAMPLE

mysql> DELIMITER |
mysql> DROP PROCEDURE IF EXISTS hub_migrate_20150302_1|
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE hub_migrate_20150302_1()
   ->   SQL SECURITY INVOKER
   -> BEGIN
   ->   DECLARE CONTINUE HANDLER FOR 1296 BEGIN END;
   ->   INSERT INTO test.test_example VALUES(1);
   -> END|
Query OK, 0 rows affected (0.00 sec)

mysql> CALL hub_migrate_20150302_1()|
Query OK, 0 rows affected (0.01 sec)

Non-fatal error 4200 is handled by the SP continue handler here.

Hi Mr. Winne,

One more important info.

Since you are SE customer, you have a privilege and obligation to create a support ticket (SR) in our Support portal.

There you can freely share all your problems with storage engine and anything else. You can also freely disclose there your storage engine and all the problems that you have with it.

Thanks in advance.

thank you so much, Sinisa. The solution works for me, really appreciate your help