Turns out the log shipping configuration has reverted to NORECOVERY instead of STANDBY.
I think the is that there must have been an open session on the database, SSMS or alike and after a set period the log shipping is configured to revert back to NORECOVERY. The original configuration was did not have the 'disconnect users currently connected' option enabled.
To resolve the issue I simply went back into the original database and selected ship transaction logs and then selected the radio button for STANDBY instead of NORECOVERY
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
You are not going to be able to create a server-wide trigger, because it has to exist in the same database that the DML changes are occurring in.
Having self-deleting code sounds a bit problematic as well.
I think you are going to have to have a process in place that creates the triggers when a new database is attached, and removes them when you are finished with them. This is likely going to be a manual process.