mysql - Mirror tables: triggers, deadlock and implicit commits -

mysql - Mirror tables: triggers, deadlock and implicit commits -

i'm have 2 similar tables, illustration , b. i want replicate insertions in b, , insertions in b integrate 2 users systems . configured "after insert triggers" on each one. example:

delimiter $$ create definer = `root`@`localhost` trigger `after_a_insert` after insert on `a` each row begin insert `b` set `id` = new.`id`,`name` = new.`name`; end$$ delimiter ; delimiter $$ create definer = `root`@`localhost` trigger `after_b_insert` after insert on `b` each row begin insert `a` set `id` = new.`id`,`name` = new.`name`; end$$ delimiter ;

if insert in a, triggers calls insert in b, insert executes trigger in b , deadlock occurs avoiding infinite loop.

i've tried edit triggers drop table trigger before insert , create 1 time again after it. example:

delimiter $$ create definer = `root`@`localhost` trigger `after_b_insert` after insert on `b` each row begin drop trigger if exists `after_a_insert`; insert `a` set `id` = new.`id`, `name` = new.`name`; /* , create 1 time again here */ end$$ delimiter ;

however create info definition language (ddl) statement makes implicit commit. thus, can't done.

i've tried phone call procedure drop within handle explicitly commit, isn't possible too.

any suggestion mirror 2 tables?

update: using bill karwin suggestion, added origin field each table respective default vale a or b. then, alter (drop , recreate) triggers follows:

trigger in a:

... begin if new.`origin`='a' insert `b` set `id` = new.`id`, `name` = new.`name`, `origin` = new.`origin`; end if; end

trigger in b:

... begin if new.`origin`='b' insert `a` set `id` = new.`id`, `name` = new.`name`, `origin` = new.`origin`; end if; end

you need way avoid creating cycle.

i'd suggest adding column origin in both tables. in table a, create default 'a'. in table b, create default 'b'.

when inserting either table in application, omit origin column, allowing take default value.

in both triggers, replicate other table if new.origin equal respective table's default.

re comment , new error:

sorry, forgot mention in trigger when inserting other table, must re-create value of new.origin. in application when original insert omit origin.

trigger in a:

... begin if new.`origin`='a' insert `b` set `id` = new.`id`, `name` = new.`name`, `origin` = new.`origin`; end if; end

trigger in b:

... begin if new.`origin`='b' insert `a` set `id` = new.`id`, `name` = new.`name`, `origin` = new.`origin`; end if; end

i created these triggers , tested:

mysql> insert set name = 'bill'; query ok, 1 row affected (0.00 sec) mysql> insert b set name = 'john'; query ok, 1 row affected (0.01 sec) mysql> select * a; +----+------+--------+ | id | name | origin | +----+------+--------+ | 1 | bill | | | 2 | john | b | +----+------+--------+ 2 rows in set (0.00 sec) mysql> select * b; +----+------+--------+ | id | name | origin | +----+------+--------+ | 1 | bill | | | 2 | john | b | +----+------+--------+

mysql stored-procedures triggers deadlock autocommit