我被困在某个地方,需要你的帮助。
场景
我有两个数据库,即test_db1
和test_db2
,它们上都有users
表。两个数据库最初都是空的(0行)。
以下是users
表模式:
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` int(11) AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
之后,我在test_db1.users
表上为INSERT
、UPDATE
和DELETE
事件编写了一些触发器。下面是每个触发器的作用:
- 在
test_db1.users
中的INSERT
上,在test_db2.users
中插入同一行 - 在
test_db1.users
中的UPDATE
上,更新test_db2.users
中的同一行 - 在
test_db1.users
中的DELETE
上,删除test_db2.users
中的同一行
下面是触发器代码片段。
DELIMITER //
-- TRIGGER FOR INSERT
DROP TRIGGER IF EXISTS `test_db1_users_bi`;
CREATE TRIGGER `test_db1_users_bi` BEFORE INSERT ON `users` FOR EACH ROW
BEGIN
INSERT INTO `test_db2`.`users` (id, name, age) VALUES (NEW.id, NEW.name, NEW.age);
END; //
-- TRIGGER FOR UPDATE
DROP TRIGGER IF EXISTS `test_db1_users_bu`;
CREATE TRIGGER `test_db1_users_bu` BEFORE UPDATE ON `users` FOR EACH ROW
BEGIN
UPDATE `test_db2`.`users`
SET name = NEW.name,
age = NEW.age
WHERE id = NEW.id;
END; //
-- TRIGGER FOR DELETE
DROP TRIGGER IF EXISTS `test_db1_users_bd`;
CREATE TRIGGER `test_db1_users_bd` BEFORE DELETE ON `users` FOR EACH ROW
BEGIN
DELETE FROM `test_db2`.`users`
WHERE id = OLD.id;
END; //
-- DELIMITER;
现在,问题来了!
目前,在触发器中没有定义任何错误/异常处理程序。。所以,我也想处理这个问题,但我不知道该怎么做。我只是不知道我该如何获得异常,以及它的属性,比如异常——错误代码、错误消息?
我只想将捕获的异常/错误存储到test_db1
中。来自每个触发器的errors
表(如果失败):
下面是errors
表模式,它看起来像:
DROP TABLE IF EXISTS `errors`;
CREATE TABLE `errors` (
`id` int(11) AUTO_INCREMENT,
`error_code` varchar(30) DEFAULT NULL,
`error_message` TEXT DEFAULT NULL,
`emailed` TINYINT DEFAULT 0,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
仅供参考:以下是可能的故障:
- 如果
test_db2
不存在或无法连接 - 如果
INSERT
触发器失败,即,原因是什么 - 如果
UPDATE
触发器失败,即,原因是什么 - 如果
DELETE
触发器失败,即,原因是什么
如果有人能告诉我如何获取异常及其属性,如错误代码、错误消息,并将其存储到触发器内部的变量中,这样我就可以执行插入操作将它们存储到表中?
我正在运行MySQL版本:5.5+
谢谢!
我通过DECLARE CONTINUE HANDLER
和GET DIAGNOSTICS CONDITION
发现了一个破解,所以我想,我必须在这里分享它。
这是我完整的最后一个脚本,它为两个数据库users
保持备份(同步)。这意味着test_db1
(我们可以称之为productionDB)上的任何更新都将发生在test_db2
(我们可以将其称为stagingDB)上:
/*
Create two databases:
1. `test_db1`
2. `test_db2`
and execute below create *Table script* on both databases.
after that execute *Triggers Script* on `test_db1` only..
*/
/*
TABLE STRUCTURE FOR `users`
*/
DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) AUTO_INCREMENT NOT NULL,
`name` varchar(30) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*
TABLE STRUCTURE FOR `errors`
*/
DROP TABLE IF EXISTS `errors`;
CREATE TABLE IF NOT EXISTS `errors` (
`id` int(11) AUTO_INCREMENT NOT NULL,
`code` varchar(30) NOT NULL,
`message` TEXT NOT NULL,
`query_type` varchar(50) NOT NULL,
`record_id` int(11) NOT NULL,
`on_db` varchar(50) NOT NULL,
`on_table` varchar(50) NOT NULL,
`emailed` TINYINT DEFAULT 0,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*
TRIGGERS SCRIPTS FOR INSERT, UPDATE AND DELETE OPERATIONS
*/
DELIMITER //
-- TRIGGER FOR INSERT
DROP TRIGGER IF EXISTS `test_db1_users_ai`;
CREATE TRIGGER `test_db1_users_ai` AFTER INSERT ON `users` FOR EACH ROW
BEGIN
-- Declare variables to hold diagnostics area information
DECLARE errorCode CHAR(5) DEFAULT '00000';
DECLARE errorMessage TEXT DEFAULT '';
-- Declare exception handler for failed insert
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
errorCode = RETURNED_SQLSTATE, errorMessage = MESSAGE_TEXT;
END;
-- Perform the insert
INSERT INTO `test_db2`.`users` (id, name, age) VALUES (NEW.id, NEW.name, NEW.age);
-- Check whether the insert was successful
IF errorCode != '00000' THEN
INSERT INTO `errors` (code, message, query_type, record_id, on_db, on_table) VALUES (errorCode, errorMessage, 'insert', NEW.id, 'test_db2', 'users');
END IF;
END; //
-- TRIGGER FOR UPDATE
DROP TRIGGER IF EXISTS `test_db1_users_au`;
CREATE TRIGGER `test_db1_users_au` AFTER UPDATE ON `users` FOR EACH ROW
BEGIN
-- Declare variables to hold diagnostics area information
DECLARE errorCode CHAR(5) DEFAULT '00000';
DECLARE errorMessage TEXT DEFAULT '';
-- Declare exception handler for failed insert
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
errorCode = RETURNED_SQLSTATE, errorMessage = MESSAGE_TEXT;
END;
-- Perform the update
UPDATE `test_db2`.`users`
SET name = NEW.name,
age = NEW.age
WHERE id = NEW.id;
-- Check whether the update was successful
IF errorCode != '00000' THEN
INSERT INTO `errors` (code, message, query_type, record_id, on_db, on_table) VALUES (errorCode, errorMessage, 'update', NEW.id, 'test_db2', 'users');
END IF;
END; //
-- TRIGGER FOR DELETE
DROP TRIGGER IF EXISTS `test_db1_users_ad`;
CREATE TRIGGER `test_db1_users_ad` AFTER DELETE ON `users` FOR EACH ROW
BEGIN
-- Declare variables to hold diagnostics area information
DECLARE errorCode CHAR(5) DEFAULT '00000';
DECLARE errorMessage TEXT DEFAULT '';
-- Declare exception handler for failed insert
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
errorCode = RETURNED_SQLSTATE, errorMessage = MESSAGE_TEXT;
END;
-- Perform the delete
DELETE FROM `test_db2`.`users`
WHERE id = OLD.id;
-- Check whether the insert was successful
IF errorCode != '00000' THEN
INSERT INTO `errors` (code, message, query_type, record_id, on_db, on_table) VALUES (errorCode, errorMessage, 'delete', OLD.id, 'test_db2', 'users');
END IF;
END; //
-- DELIMITER;
希望这对其他人来这里有所帮助。
干杯,
查看DECLARE HANDLER 的语法
http://dev.mysql.com/doc/refman/5.1/en/declare-handler.html
此外,如果您正在尝试调试SP,这可能对您有所帮助:
http://www.bluegecko.net/mysql/debugging-stored-procedures/
触发后,您可以使用以下内容:
CREATE TRIGGER `my_table_AINS` AFTER INSERT ON `my_table` FOR EACH ROW
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
RESIGNAL;
DECLARE EXIT HANDLER FOR SQLWARNING
RESIGNAL;
DECLARE EXIT HANDLER FOR NOT FOUND
RESIGNAL;
-- Do the work of the trigger.
END
希望这能对你有所帮助。
这看起来像主服务器和从服务器/DB场景。您可以查看二进制日志。所以,主服务器中的任何操作都将被记录为一个事件,同样的操作也将应用于从属服务器。Checkout MySQL官方参考