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: | |
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 |
[16 Jul 2008 8:01]
Frédéric Lehmann
[16 Jul 2008 9:20]
Susanne Ebrecht
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'
[16 Jul 2008 9:30]
Susanne Ebrecht
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.
[16 Jul 2008 10:14]
Frédéric Lehmann
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?
[8 Aug 2008 10:05]
Frédéric Lehmann
The same bug has been reproduced on MySQL 5.1.26-rc-1. Thanks in advance.