MySQL Bugs: #38161: Triggers can't handle ERROR 1296 (HY000) when cluster is down

Bug #38161	Triggers can't handle ERROR 1296 (HY000) when cluster is down
Submitted:	16 Jul 2008 8:01	Modified:	8 Aug 2008 10:05
Reporter:	Frédéric Lehmann	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	mysql-5.1	OS:	Linux (Debian 2.6.18)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	5.1.25-rc-1, triggers cluster error handler

Description:
Hi
First of all, apologize for my english which isn't perfect yet!

I've encountred some stranges problems when using triggers and MySQL cluster storage engine.
I apply a trigger on a MyISAM table t1 which just made a copy of the row in another table in NDB engine. I also implement an error handling to prevent error to be fired when cluster is down.
All is working fine when all services are up.
But when I shutdown the cluster, even if I try to handle the error code, my trigger fire a "ERROR 1296 (HY000): Got error 157 'Unknown error code' from NDBCLUSTER"

So, I've put my trigger's code in a procedure and when I call it, the error is fully handled and no errors are fired.

How to repeat:
CREATE DATABASE dbtest;

USE dbtest;

CREATE TABLE t1 (`test` varchar(20) NOT NULL) ENGINE=ndbcluster;

CREATE TABLE t2 (`test` varchar(20) NOT NULL) TYPE=MyISAM;

CREATE TABLE t3 (`test` varchar(20) NOT NULL) TYPE=MyISAM;

DELIMITER |
DROP TRIGGER IF EXISTS trig_insert_t2|
CREATE TRIGGER trig_insert_t2 AFTER INSERT
   ON dbtest.t2 FOR EACH ROW
BEGIN
	DECLARE EXIT HANDLER FOR 1296 INSERT INTO `dbtest`.`t3`(`test`) VALUES (NEW.test);
        INSERT INTO `dbtest`.`t1` (`test`) VALUES (NEW.test);
END;
|

DELIMITER |
DROP PROCEDURE IF EXISTS proc_insert_t2|
CREATE PROCEDURE proc_insert_t2(v_test VARCHAR(20))
BEGIN
	DECLARE EXIT HANDLER FOR 1296 INSERT INTO `dbtest`.`t3`(`test`) VALUES (v_test);
        INSERT INTO `dbtest`.`t1`(`test`) VALUES (v_test);
END;
|

# When Cluster is running, I check that my routines are working as expected:

mysql> INSERT INTO t2(test) VALUE ('foo');
Query OK, 1 row affected (0,00 sec)

mysql> SELECT * FROM t1;
+------+
| test |
+------+
| foo  | 
+------+
1 row in set (0,00 sec)

mysql> call proc_insert_t2('foo2');
Query OK, 1 row affected (0,00 sec)

mysql> SELECT * FROM t1;
+------+
| test |
+------+
| foo  | 
| foo2 | 
+------+
2 rows in set (0,00 sec)

# so, when all services are up, it works well!
# Now I shutdown the cluster and I retry my trigger and my procedure:

shell# ndb_mgm -e "shutdown"

mysql> SELECT * FROM t1;
ERROR 1296 (HY000): Got error 157 'Unknown error code' from NDBCLUSTER

# so cluster is unavailable, it's ok.

mysql> INSERT INTO t2(test) VALUE ('foo3');
ERROR 1296 (HY000): Got error 157 'Unknown error code' from NDBCLUSTER
mysql> show errors;
+-------+------+----------------------------------------------------+
| Level | Code | Message                                            |
+-------+------+----------------------------------------------------+
| Error | 1296 | Got error 4009 'Cluster Failure' from NDB          | 
| Error | 1296 | Got error 157 'Unknown error code' from NDBCLUSTER | 
| Error | 1033 | Incorrect information in file: './dbtest/t1.frm'   | 
+-------+------+----------------------------------------------------+
3 rows in set (0,00 sec)

# it seem's that the error code isn't handled

mysql> call proc_insert_t2('foo4');
Query OK, 1 row affected (0,00 sec)

# no errors are fired, so it seem's that the error code has been handled

mysql> SELECT * FROM t3;
+------+
| test |
+------+
| foo4 | 
+------+
1 row in set (0,00 sec)

# the statement in the handler has been executed only for the procedure.

Suggested fix:
Handle error code in triggers as procedures when cluster in degraded mode.

Many thanks for writing a bug report.

$ perror 157
MySQL error code 157: Could not connect to storage engine

I made the same test with Innodb and skipped innodb:

SELECT * FROM t1;
ERROR 1286 (42000): Unknown table engine 'InnoDB'

It's not a bug that trigger can't handle this.

The bug here is the confusing error message.

I think the returning error should be the same for every storage engine. And "unknown error" is as bad as "unknown table" ... maybe something like: storage engine not reachable would be better.

Hi thanks for your answers.
So you mean that a trigger can't handle this error while procedure does?
I try to avoid this problem using a few tricks:

1) I get the state of the ndbcluster table using information_schema. I retrieve the amount of rows in the table ( if cluster down, amount = null else amount > 0)

2) If amount > 0, I insert into the ndbcluster table otherwise in the MyISAM table

DELIMITER |
DROP TRIGGER IF EXISTS trig_insert_t2|
CREATE TRIGGER trig_insert_t2 AFTER INSERT
   ON dbtest.t2 FOR EACH ROW
BEGIN
	SET @amount = 0;
	SELECT IFNULL(table_rows,0) INTO @amount FROM information_schema.tables WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='dbtest';
	IF @amount > 0 THEN
        	INSERT INTO `dbtest`.`t1`(`test`) VALUES (NEW.test);
	ELSE
		INSERT INTO `dbtest`.`t3`(`test`) VALUES (NEW.test);
	END IF;
END;
|

And the trigger fired the same error code too, even if I'm sure that @amount = 0
I go further with replacement of the INSERT code by a call to another procedure like this:

DELIMITER |
DROP TRIGGER IF EXISTS trig_insert_t2|
CREATE TRIGGER trig_insert_t2 AFTER INSERT
   ON dbtest.t2 FOR EACH ROW
BEGIN
	SET @amount = 0;
	SELECT IFNULL(table_rows,0) INTO @amount FROM information_schema.tables WHERE TABLE_NAME='t1' AND TABLE_SCHEMA='dbtest';
	IF @amount > 0 THEN
        	CALL proc_insert_t2(NEW.test);

	ELSE
		INSERT INTO `dbtest`.`t3`(`test`) VALUES (NEW.test);
	END IF;
END;
|

DELIMITER |
DROP PROCEDURE IF EXISTS proc_insert_t2|
CREATE PROCEDURE proc_insert_t2(v_test VARCHAR(20))
BEGIN
	DECLARE EXIT HANDLER FOR 1296 INSERT INTO `dbtest`.`t3`(`test`) VALUES (v_test);
        INSERT INTO `dbtest`.`t1`(`test`) VALUES (v_test);
END;
|

The result of the trigger is the same:
ERROR 1296 (HY000): Got error 157 'Unknown error code' from NDBCLUSTER

But if I call the procedure, all works fine.

How can I include, in a trigger, the case of an unavailable MySQL Cluster?

The same bug has been reproduced on MySQL 5.1.26-rc-1.

Thanks in advance.