I concur with @NathanJolly, this is a potential data nightmare and seems ill-advised... but there is a way to implement this that is, at least conceptually, fairly straightforward.
You need not worry about the possibility of recursion in triggers, because this won't happen... MySQL doesn't support it.
I built this fiddle that creates tables t1 and t2, each of which has an AFTER INSERT
trigger that tries to insert the record into the other table... then tries to do an insert. Theoretically, there's a potential for recursion, but MySQL prevents this:
ERROR 1442 (HY000): Can't update table 't1' in stored function/trigger because it is already
used by statement which invoked this stored function/trigger.
So it won't run in a loop, but it still throws an exception. Technically, it's not trigger recursion causing the exception, because we haven't actually gotten that far... it's the fact that the trigger in t2 tries to modify table t1, which was involved in the currently-executing statement.
Then again, technically, it's not just the recursion of the triggers we need to avoid -- we have to stop one step before that -- if the client modifies t1, the t1 trigger modifies t2, then t2's trigger needs to not even try to do anything to t1 (including, but not limited to, firing the trigger again on t1).
Implementing this is actually fairly straightforward... in each trigger, we toggle the value of a session to tell the other trigger not to run.
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW BEGIN
IF @__disable_trigger_t1t2 = 1 THEN
SET @__disable_trigger_t1t2 = NULL;
ELSE
SET @__disable_trigger_t1t2 = 1;
-- trigger logic goes in here
INSERT INTO t2 (id,things) VALUES (NEW.id,NEW.stuff);
END IF;
END //
CREATE TRIGGER t2_ai AFTER INSERT ON t2 FOR EACH ROW BEGIN
IF @__disable_trigger_t1t2 = 1 THEN
SET @__disable_trigger_t1t2 = NULL;
ELSE
SET @__disable_trigger_t1t2 = 1;
-- trigger logic goes in here
INSERT INTO t1 (id,stuff) VALUES (NEW.id,NEW.things);
END IF;
END //
A test case for this logic working can be seen in this fiddle.
Essentially, when a trigger fires, it checks the value of the @__disable_trigger_t1t2
variable (this variable name is, of course, arbitrary). If '1', it sets it back to NULL
(the default for session variables) and does no further processing, because the '1' means this row is being manipulated by the trigger on the other table. If already NULL
, then it sets the value to 1 and executes the trigger logic -- which will update the other table, whose trigger will see the '1', will not execute its own logic, and will reset the variable back to NULL
, so that the next execution of either trigger -- whether for the next row in the same statement, or for a subsequent statement -- will see the NULL
and execute normally.
In MySQL, triggers are only supported FOR EACH ROW
, meaning that we can define a MySQL trigger as "a stored program that is executed before or after each row is inserted, updated, or deleted"... which means that no matter how many rows your query affects, the trigger will run once for each row. Actions against other tables will cause the triggers on those tables to fire, and the execution of the trigger on t1 for the first row will not complete until all of the triggers it caused to fire have also completed... and this is why we can use this variable like this -- only one row is actually being handled at a time, so we set and clear this variable for each row, always leaving it NULL
after each statement terminates.
A significant caveat is that this approach fails if you need to trigger on cascading deletes or updates related to foreign keys, because:
"Currently, cascaded foreign key actions do not activate triggers."
-- http://dev.mysql.com/doc/refman/5.6/en/create-trigger.html
So, while I have misgivings about the long-term viability of maintaining database synchronization with this approach because of the number of things that could go wrong with two applications manipulating each other's data or logical errors in the trigger definitions... it seems technically possible.
You also mentioned:
views cannot have triggers in MySQL
No, but updating an updatable view will fire the triggers on the underlying base table as if the table had been directly manipulated.
Ok, after some research, here's the answers I found.
Q1: Is it possible to detect the user that executed the query in MySQL?
It is possible to detect with which user by using: select user();
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_user
Beware: In triggers and events current_user()
returns the user who defined the object (in SP and views unless if defined with the SQL SECURITY INVOKER
). You really need to use the user()
function to return the invoker in those contexts.
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_current-user
Q2: Is it possible to fire more than one action (INSERT, UPDATE, DELETE) on the same trigger?
Most unfortunately, at the current time (2016/02/01), it is not possible to fire two actions on the same time in MySQL. The best we can do is to regroup the common coding within a stored procedure.
The following procedure is one possible way to work around this problem. It expects in input:
callingTable
: The name of the table for which we want to record the query (must be in the white list).
action
: Either 'DELETE', 'INSERT' or 'UPDATE'.
fieldsValues
: a string containing a valid pair of field/value in XML. By example: <field>ID</field><value>1616</value>
CREATE PROCEDURE `dataLinkRecorder` (
IN callingTable VARCHAR (25),
IN action ENUM('DELETE', 'INSERT', 'UPDATE'),
IN fieldsValues TEXT
)
BEGIN
DECLARE callingUser VARCHAR(25) DEFAULT '';
DECLARE fieldCount SMALLINT UNSIGNED DEFAULT 0;
DECLARE fieldCCount SMALLINT UNSIGNED DEFAULT 0;
DECLARE valueCount SMALLINT UNSIGNED DEFAULT 0;
DECLARE valueCCount SMALLINT UNSIGNED DEFAULT 0;
IF (TRIM(fieldsValues) <> '') THEN
/* the calling table must be one in the white list */
SELECT CASE
WHEN UPPER(callingTable) IN (
'CATEGORY',
'CUSTOMER',
'MAKE',
'MODEL',
'PRODUCT',
'VEHICLE'
)
THEN TRIM(UPPER(callingTable))
ELSE ''
END AS myTable
INTO callingTable;
SELECT TRIM(
UPPER(
IFNULL(USER(), 'CLIENT')
)
) AS myUser
INTO callingUser;
/* do not record modifications from tier software */
IF (callingTable <> '' AND INSTR(callingUser, 'CLIENT') < 1) THEN
SELECT IFNULL(
((CHAR_LENGTH(fieldsValues) - CHAR_LENGTH(REPLACE(fieldsValues, '<field>', ''))) / 7)
, 0) AS fCount,
IFNULL(
((CHAR_LENGTH(fieldsValues) - CHAR_LENGTH(REPLACE(fieldsValues, '</field>', ''))) / 8)
, 0) AS fCCount,
IFNULL(
((CHAR_LENGTH(fieldsValues) - CHAR_LENGTH(REPLACE(fieldsValues, '<value>', ''))) / 7)
, 0) AS vCount,
IFNULL(
((CHAR_LENGTH(fieldsValues) - CHAR_LENGTH(REPLACE(fieldsValues, '</value>', ''))) / 8)
, 0) AS vCCount
INTO fieldCount, fieldCCount, valueCount, valueCCount;
/* validate the field/value XML pair */
IF (fieldCount > 0 AND
valueCount > 0 AND
fieldCount = fieldCCount AND
valueCount = valueCCount AND
fieldCount = valueCCount
) THEN
INSERT INTO `pc_datalink`
(`table_name`, `query_xml`)
VALUES(
callingTable,
CONCAT(
'<query>',
'<table>', callingTable, '</table>',
'<action>', action, '</action>',
fieldsValues,
'</query>'
)
);
END IF;
END IF;
END IF;
END //
Will insert values in the following table:
CREATE TABLE `pc_datalink` (
`id` int(10) UNSIGNED NOT NULL,
`table_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`query_xml` text COLLATE utf8_unicode_ci NOT NULL,
`date_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `AK_DATE_ADDED` (`date_added`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
By example, a very simple trigger like this one:
CREATE TRIGGER `dataLinkDeleteVehicle` AFTER DELETE ON `pc_vehicle`
FOR EACH ROW
BEGIN
IF (OLD.original_id <> '') THEN
CALL dataLinkRecorder(
'VEHICLE',
'DELETE',
CONCAT(
'<field>ID</field><value>',
OLD.original_id,
'</value>'
)
);
END IF;
END //
Will generate a line in the table like this one:
Don't hesitate to ask for details if you still have questions,
Good success everyone
Best Answer
To accomplish what you are wanting to do, it is possible to use the
FEDERATED
storage engine on both servers, in conjunction with triggers, to allow each server to update the other server's database.This is not exactly a simple out-of-the-box solution, because it requires additional precautions and requires you to decide whether consistency or isolation tolerance is more important and allow the queries to fail when the other server isn't available (more consistency) or use a
CONTINUE HANDLER
to suppress errors (isolation tolerance).But here is an extremely simplified example.
Each server would have the identical configuration.
The local user table:
A local table that is federated to the user table on the other server.
Selecting from remote_user on one server will retrieve the records from the other server, and insert/update/delete on that table will change data on the other server.
So, we create triggers do accomplish the purpose of updating the distance server. They are written as
BEFORE
triggers, with the idea being that we don't want to do something to ourselves that we can't do to the other server -- for example, if a username already exists on the other server, but not here, we want the insert on the other server to throw an error that prevents us from creating the user here... as opposed to creating a user here with what would be a conflicting username. This is, of course, one of the tradeoff decisions you'll need to make.This is not a perfect solution and is not a high-availability solution, because it relies on solid connectivity between the two systems and even if you are using InnoDB and transactions, the actions you take against the target table are not part of your local transaction and cannot be rolled back.
I use the
FEDERATED
engine quite a bit; it comes in handy for a number of creative purposes in my environment, including one situation where I used a federated query launched by a trigger to impose foreign key constraints against a foreign data source; however, I restrict its use to back-end processes where unexpected issues such as timeouts, coding errors, or server-to-server network/outage/isolation events cannot result in the end user on one of our web sites experiencing any kind of problem. Your ability to tolerate such a situation would be a major determining factor into whether this is an appropriate solution.An alternative would be to configure your two servers in master/master replication. For this, you would need to use different database names on each server, so that for most events that replicate, the two servers could not possibly conflict with each other. In the worst-case scenario, if you lose connectivity or encounter a replication error, the two sites would still be running independently and you could resynchronize and recover. Configuration would look something like this:
Then, in database_a and database_b:
MySQL will treat database_a.user and database_b.user as aliases for the "real" user table, database_c.user, so you would not have to change your application other than to use its designated database (i.e, you wouldn't have to configure it to understand that the user table was actually in a different schema, because the view will function pretty much transparently with this configuration). If the schemas have foreign keys against the user table, you would declare those against the true base table database_c.user.
Configure the two servers to replicate everything, but set
auto_increment_increment
andauto_increment_offset
appropriately so you do not have conflicting auto-increment values on the shared table(s), if your tables use auto-increment. (Note, the documentation says that these variables are forNDB
tables only, but that's not accurate).An extra advantage of this setup is that your two servers would then have a complete duplicate of the other site's data that you could potentially use to your advantage for recovery from hardware failure in one of the servers.