Description:
In general we have a small NDBD table (clustered table) that defined with trgger for INSERT to another INNODB table and to FEDERATED (remote) table.
Such sentence target is ignore a SQL exceptions in case of remote system access issues , like server crash .
After the upgrade to MySQL 5.0.41 version (clustered environment on Centos 4.4) such command doesn't work.
It seems like ignoring of whole "DECLARE CONTINUE HANDLER FOR" sentence in 5.0.41 version in case of remote system shutdown , the follow error has been received :
"Got error 10000 'Error on remote system: 2003: Can't connect to MySQL server on '10.10.10.10'(111)' from FEDERATED" with ErrorNumber 1296
How to repeat:
1. Create cluster table on server A - 10.10.10.11
CREATE TABLE `hingi_logs`.`hmlog_tmp` (
`DateAndTime` datetime NOT NULL,
`SubsystemType` varchar(32) NOT NULL,
`Status` varchar(32) NOT NULL,
`ChannelCode` varchar(128) NULL,
`IPAddress` varchar(32) NULL,
`Description` varchar(512) NULL
) ENGINE=NDBCLUSTER DEFAULT CHARSET=utf8;
2. Create table using INNODB engine on server A:
CREATE TABLE `hingi_logs`.`hmlog` (
`DateAndTime` datetime NOT NULL,
`SubsystemType` varchar(32) NOT NULL,
`Status` varchar(32) NOT NULL,
`ChannelCode` varchar(128) NULL,
`IPAddress` varchar(32) NULL,
`Description` varchar(512) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3. Create table using INNODB engine on server B:
CREATE TABLE `hingi_logs`.`hmlog` (
`DateAndTime` datetime NOT NULL,
`SubsystemType` varchar(32) NOT NULL,
`Status` varchar(32) NOT NULL,
`ChannelCode` varchar(128) NULL,
`IPAddress` varchar(32) NULL,
`Description` varchar(512) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
4. Create the federated table on server A:
CREATE TABLE `hingi_logs`.`hmlog_fed` (
`DateAndTime` datetime NOT NULL,
`SubsystemType` varchar(32) NOT NULL,
`Status` varchar(32) NOT NULL,
`ChannelCode` varchar(128) NULL,
`IPAddress` varchar(32) NULL,
`Description` varchar(512) NULL
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://username:password@10.10.10.10:3306/hingi_logs/hmlog';
5.Create trigger for INSERT of `hingi_logs`.`hmlog_tmp` table on server A:
CREATE TRIGGER `hingi_logs`.tr_InsertHMLog AFTER INSERT ON hingi_logs.hmlog_tmp
FOR EACH ROW
BEGIN
DECLARE CONTINUE HANDLER FOR 1296 BEGIN END;
INSERT INTO hingi_logs.hmlog values(NEW.DateAndTime, NEW.SubsystemType,NEW.Status,NEW.ChannelCode, NEW.IPAddress, NEW.Description);
INSERT INTO hingi_logs.hmlog_fed values(NEW.DateAndTime, NEW.SubsystemType,NEW.Status,NEW.ChannelCode, NEW.IPAddress, NEW.Description);
END
6.Execute the follow query on server A:
insert hingi_logs.hmlog_tmp VALUES (now(), 'REC', 'STATUP', '918', '127.0.0.0', 'RS restart');
Suggested fix:
During normal run of both MySQL nodes each table receive a new row after the INSERT execution, BUT if server B is down we supposed to ignore the MySQL exception and continue to INSERT into hingi_logs.hmlog table on server A.
In fact the server A return a follow exception :
Got error 10000 'Error on remote system: 2003: Can't connect to MySQL server on '10.10.10.10' (111)' from FEDERATED and affected rows wasn't ins
Suggested fix for this issue is perform and not ignore the follow query:
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
