Как научить MySQL заглядывать в прошлое / forpes.ru

Главная
Как научить MySQL заглядывать в прошлое

Как научить MySQL заглядывать в прошлое +11

09.10.2018 12:39

asmm 17 2100 Источник

В статье речь пойдёт о протоколировании изменений в MySQL. Хочу показать реализацию протоколирования на триггерах и то, какие удивительные вещи можно будет с этим делать.

Почему на триггерах? Потому что нет доступа к бинарному логу. Реализация с бинарным логом потенциально более производительная, хотя и более сложная в разработке, т.к. требуется парсить лог.

Сразу хочу предупредить, что данный метод создаст дополнительную нагрузку на сервер. И если у Вас активно изменяющиеся данные, то данное решение может не подойти Вам или будет требовать некоторых корректировок и доработок.

В целом же решение является законченным и комплексным. Может быть внедрено «как есть» и прекрасно справляться со своей задачей.

Всё, что приведено ниже, реализовано на версии MariaDB 10.0.32
Протоколируются столбцы с типами: числа, строки, даты. Протоколируемая таблица должна иметь уникальное NOT NULL числовое поле id.

Для начала создадим таблицу с конфигом протоколирования:

DROP TABLE IF EXISTS protocol_config;
CREATE TABLE protocol_config (
id int(11) NOT NULL PRIMARY KEY auto_increment
, command VARCHAR(50) NOT NULL -- Команда
, table_name VARCHAR(50) -- Имя таблицы
, column_name VARCHAR(50) -- Имя колонки
, denormalize_column VARCHAR(50) -- Денормализованная колонка в таблице protocol
, UNIQUE (command, table_name, column_name, denormalize_column)
) DEFAULT CHARSET=utf8 COMMENT='Настройка протоколирования';

Все опции применяются во время генерации триггера на протоколирование. Т.е. при изменении настроек необходимо перегенерировать триггеры.

Поле command — опция настройки протокола:

disable_protocol — выключает протоколирование.
exclude_table — указывается таблица которую нужно исключить из протоколирования. По умолчанию в протоколировании участвуют все BASE TABLE ENGINE=InnoDB.
Например,
exclude_table protocol
exclude_table protocol_pos
exclude_column — указывается поле которое необходимо исключить из протоколирования. Например, денормализованное поле, поддерживаемое триггерами.

Например,
exclude_column docs sum
denormalize_column — указывается столбец который необходимо дополнительно денормализовать в протокол (таблица protocol). По умолчанию все поля протоколируются в таблицу protocol_pos.

Например,
denormalize_column docs id doc_id
из таблицы docs будет протоколироваться поле id в таблицу protocol в колонку doc_id. Поле doc_id в таблице protocol необходимо создать руками.
denormalize_column doc_pos doc_id doc_id
из таблицы doc_pos будет протоколироваться поле doc_id в таблицу protocol в колонку doc_id.

Таблица protocol:

DROP TABLE IF EXISTS protocol_pos;
DROP TABLE IF EXISTS protocol;
CREATE TABLE protocol (
id BIGINT NOT NULL PRIMARY KEY auto_increment
, date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP -- Дата время операции
, oper VARCHAR(1) NOT NULL -- операция I, U, D
, table_name VARCHAR(50) NOT NULL -- Имя таблица
, table_id BIGINT NOT NULL -- значение поля id в протоколируемой таблице
, username VARCHAR(50) NOT NULL -- имя пользователя приложения или БД
, ip varchar(45) -- IP адрес пользователя
, user_agent varchar(256) -- Браузер
, KEY (table_name, date)
) DEFAULT CHARSET=utf8 COMMENT='Протокол изменений';

Таблица protocol_pos:

DROP TABLE IF EXISTS protocol_pos;
CREATE TABLE protocol_pos (
prot_id BIGINT NOT NULL -- ссылка на protocol.id
, column_name VARCHAR(50) NOT NULL -- имя столбца из протоколируемой таблицы
, old_val VARCHAR(2000) -- старое значение столбца
, new_val VARCHAR(2000) -- новое значение столбца
, PRIMARY KEY (prot_id, column_name)
, FOREIGN KEY (prot_id) REFERENCES protocol(id)
) DEFAULT CHARSET=utf8 COMMENT='Поля протокола изменений';

В таблице protocol мы фиксируем операцию, а в таблицу protocol_pos заносим изменившиеся поля.

Сейчас возьмём за основу генератор триггеров из моей предыдущей статьи «Реализация бизнес-логики в MySQL» и на его основе напишем генератор для протоколирования.

Функция gen_bl_trigger генерации триггера бизнес-логики смотрит на наличие процедуры <имя_таблицы>_trg_proc

gen_bl_trigger

DELIMITER $
DROP FUNCTION IF EXISTS gen_bl_trigger$
CREATE FUNCTION gen_bl_trigger(table_name VARCHAR(200), trigger_time VARCHAR(200), trigger_type VARCHAR(200)) RETURNS TEXT BEGIN
    DECLARE text TEXT;
    DECLARE f_proc INT;
    SET group_concat_max_len = 9000000;
    SET f_proc := (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES
        WHERE ROUTINE_NAME = CONCAT(table_name, '_trg_proc')
         AND ROUTINE_TYPE = 'PROCEDURE'
         AND ROUTINE_SCHEMA = DATABASE()
    );
    IF IFNULL(f_proc, 0) = 0 THEN
        RETURN '';
    END IF;
    SET text := CONCAT('\nbl_proc: BEGIN
IF @disable_', table_name, '_bl_trg = 1 OR @disable_all_bl_trg = 1 THEN
LEAVE bl_proc;
END IF;');
    IF trigger_time = 'BEFORE' THEN
        -- Создаём временную таблицу
        SET text := CONCAT(text, '\nCREATE TEMPORARY TABLE ');
        -- Временная таблица уже может быть создана конструкцией INSERT INTO ... ON DUPLICATE KEY UPDATE поэтому добавляем IF NOT EXISTS
        -- для INSERT IGNORE не сработает AFTER TRIGGER, поэтому тоже добавляем
        IF trigger_type IN ('INSERT', 'UPDATE') THEN
            SET text := CONCAT(text, 'IF NOT EXISTS ');
        END IF;
        SET text := CONCAT(text, table_name, '_tmp_trg (');
        SET text := CONCAT(text, '\ntime VARCHAR(1)');
        SET text := CONCAT(text, '\n, type VARCHAR(1)');
        SET text := CONCAT(text, '\n, col_changed VARCHAR(1000)');
        SET text := CONCAT(text, (SELECT GROUP_CONCAT('\n, new_', COLUMN_NAME, ' ', COLUMN_TYPE
            , '\n, old_', COLUMN_NAME, ' ', COLUMN_TYPE SEPARATOR '') text
            FROM INFORMATION_SCHEMA.COLUMNS C
            WHERE C.TABLE_NAME = table_name
            AND C.TABLE_SCHEMA = DATABASE()
            AND C.COLUMN_TYPE != 'text'
            ));
        SET text := CONCAT(text, ') ENGINE=MEMORY;');
        -- Создаём переменные
        SET text := CONCAT(text, (SELECT GROUP_CONCAT('\nSET @new_', COLUMN_NAME, ' := '
            , IF(trigger_type = 'DELETE', 'NULL', CONCAT('NEW.', COLUMN_NAME)), ';'
            , '\nSET @old_', COLUMN_NAME, ' := '
            , IF(trigger_type = 'INSERT', 'NULL', CONCAT('OLD.', COLUMN_NAME)), ';'
            SEPARATOR '') text
            FROM INFORMATION_SCHEMA.COLUMNS C
            WHERE C.TABLE_NAME = table_name
            AND C.TABLE_SCHEMA = DATABASE()
            AND C.COLUMN_TYPE != 'text'
            ));
    END IF;
    SET text := CONCAT(text, '\nINSERT INTO ', table_name, '_tmp_trg VALUES ("', SUBSTR(trigger_time, 1, 1), '", "', SUBSTR(trigger_type, 1, 1), '", ');
    -- заполним col_changed для UPDATE
    IF trigger_type = 'UPDATE' THEN
        SET text := CONCAT(text, 'CONCAT('
            , (SELECT GROUP_CONCAT(CONCAT('IF(IFNULL(NEW.'
                   , COLUMN_NAME, ', "-ЪъЪ") != IFNULL(OLD.', COLUMN_NAME, ', "-ЪъЪ"), "|', COLUMN_NAME, '|", "")'
               ) SEPARATOR ', ') text
               FROM INFORMATION_SCHEMA.COLUMNS C
               WHERE C.TABLE_NAME = table_name
               AND C.TABLE_SCHEMA = DATABASE()
               AND C.COLUMN_TYPE != 'text'
              )
            , '), ');
    ELSE
        SET text := CONCAT(text, 'NULL, ');
    END IF;
    -- остальные поля
    SET text := CONCAT(text, (SELECT GROUP_CONCAT(
        CASE
            WHEN trigger_time = 'BEFORE' THEN CONCAT('@new_', COLUMN_NAME)
            WHEN trigger_type = 'DELETE' THEN 'NULL'
            ELSE CONCAT('NEW.', COLUMN_NAME)
        END
        , ', '
        , CASE
            WHEN trigger_time = 'BEFORE' THEN CONCAT('@old_', COLUMN_NAME)
            WHEN trigger_type = 'INSERT' THEN 'NULL'
            ELSE CONCAT('OLD.', COLUMN_NAME)
        END
        SEPARATOR ', ') text
        FROM INFORMATION_SCHEMA.COLUMNS C
        WHERE C.TABLE_NAME = table_name
        AND C.TABLE_SCHEMA = DATABASE()
        AND C.COLUMN_TYPE != 'text'
        ));
    SET text := CONCAT(text, ');');
    SET text := CONCAT(text, '\nCALL ', table_name, '_trg_proc;');
    IF trigger_time = 'BEFORE' THEN
        SET text := CONCAT(text
        , IF(trigger_type = 'DELETE'
          , ''
          , (SELECT CONCAT('\nSELECT '
             , GROUP_CONCAT('new_', COLUMN_NAME SEPARATOR ', ')
             , '\nINTO ', GROUP_CONCAT('@new_', COLUMN_NAME SEPARATOR ', ')
             , '\nFROM ', table_name, '_tmp_trg;'
             , GROUP_CONCAT('\nSET NEW.', COLUMN_NAME, ' := @new_', COLUMN_NAME, ';' SEPARATOR '')
             ) text
             FROM INFORMATION_SCHEMA.COLUMNS C
             WHERE C.TABLE_NAME = table_name
              AND C.TABLE_SCHEMA = DATABASE()
              AND C.COLUMN_TYPE != 'text'
            )
          )
        );
        SET text := CONCAT(text, '\nDELETE FROM ', table_name, '_tmp_trg;');
    ELSE
        SET text := CONCAT(text, '\nDROP TEMPORARY TABLE ', table_name, '_tmp_trg;');
    END IF;
    SET text := CONCAT(text, '\nEND;');
    RETURN text;
END$

Функция gen_prot_trigger генерации триггера протоколирования:

gen_prot_trigger

DELIMITER $
DROP FUNCTION IF EXISTS gen_prot_trigger$
CREATE FUNCTION gen_prot_trigger(table_name VARCHAR(200), trigger_time VARCHAR(200), trigger_type VARCHAR(200)) RETURNS TEXT BEGIN
    DECLARE text TEXT;
    DECLARE denormalize_columns TEXT;
    DECLARE denormalize_values TEXT;
    DECLARE f_exclude_table INT;
    SET group_concat_max_len = 9000000;
    -- исключим таблицу если её не надо протоколировать, в ней нет поля id или протоколирование выключено
    SET f_exclude_table := (
        SELECT CASE WHEN pd.id IS NOT NULL THEN 1
          WHEN pc.id IS NOT NULL THEN 1
          WHEN C.COLUMN_NAME IS NULL THEN 1
         END
        FROM (SELECT NULL FROM dual) d
        LEFT JOIN protocol_config pd ON pd.command = 'disable_protocol'
        LEFT JOIN protocol_config pc ON pc.command = 'exclude_table' AND pc.table_name = table_name
        LEFT JOIN INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_SCHEMA = DATABASE()
         AND C.TABLE_NAME = table_name
         AND C.COLUMN_NAME = 'id'
    );
    IF trigger_time = 'BEFORE' OR f_exclude_table = 1 OR table_name IN ('protocol', 'protocol_pos') THEN
        RETURN '';
    END IF;
    SET text := CONCAT('\nprot_proc: BEGIN
DECLARE prot_id INT;
IF @disable_', table_name, '_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
END IF;');
    -- Проверяем что изменилось хоть 1 поле, иначе выходим из протоколирования
    IF trigger_type = 'UPDATE' THEN
        SET text := CONCAT(text
        , '\nIF '
        , (SELECT GROUP_CONCAT('IFNULL(NEW.'
            , C.COLUMN_NAME, ', "-ЪъЪ") = IFNULL(OLD.', C.COLUMN_NAME, ', "-ЪъЪ")'
            SEPARATOR ' AND '
            ) text
            FROM INFORMATION_SCHEMA.COLUMNS C
            LEFT JOIN protocol_config ec ON ec.command = 'exclude_column'
             AND ec.table_name = C.TABLE_NAME
             AND ec.column_name = C.COLUMN_NAME
            WHERE C.TABLE_NAME = table_name
             AND C.TABLE_SCHEMA = DATABASE()
             AND C.COLUMN_TYPE != 'text'
             AND ec.id IS NULL)
        , ' THEN
LEAVE prot_proc;
END IF;'
        );
    END IF;
    -- денормализованные поля для таблицы protocol
    SELECT IFNULL(GROUP_CONCAT(', ', dc.denormalize_column ORDER BY dc.id SEPARATOR ''), '') denormalize_columns
    , IFNULL(GROUP_CONCAT(', '
        , CASE trigger_type WHEN 'DELETE' THEN 'OLD' ELSE 'NEW' END
        , dc.column_name
        ORDER BY dc.id SEPARATOR ', '
      )
      , '') denormalize_values
    INTO denormalize_columns, denormalize_values
    FROM INFORMATION_SCHEMA.COLUMNS C
    INNER JOIN protocol_config dc ON dc.command = 'denormalize_column'
     AND dc.table_name = C.TABLE_NAME
     AND dc.column_name = C.COLUMN_NAME
    WHERE C.TABLE_NAME = table_name
     AND C.TABLE_SCHEMA = DATABASE()
    ;
    -- Вставляем строчку в протокол
    SET text := CONCAT(text, '\nINSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent'
, denormalize_columns, ')
SELECT IFNULL(u.email, USER()) username, "', SUBSTR(trigger_type, 1, 1), '", "', table_name, '"'
, ', ', CASE trigger_type WHEN 'DELETE' THEN 'OLD' ELSE 'NEW' END, '.id'
, ', au.ip, au.user_agent'
, denormalize_values, '
FROM (SELECT NULL FROM dual) d
LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFT JOIN users u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();');
    -- Генерим строчки для вставки полей в позиции протокола
    SET text := CONCAT(text
    , '\nINSERT INTO protocol_pos (prot_id, column_name, '
    , CASE trigger_type WHEN 'INSERT' THEN 'new_val'
        WHEN 'UPDATE' THEN 'old_val, new_val'
        WHEN 'DELETE' THEN 'old_val'
      END
    , ')\n'
    , (SELECT GROUP_CONCAT('SELECT prot_id, "', C.COLUMN_NAME, '", '
        , CASE WHEN trigger_type = 'UPDATE'
           THEN CONCAT('OLD.', C.COLUMN_NAME, ', NEW.', C.COLUMN_NAME, ' FROM dual WHERE IFNULL(NEW.', C.COLUMN_NAME, ', "-ЪъЪ") != IFNULL(OLD.', C.COLUMN_NAME, ', "-ЪъЪ")')
           WHEN trigger_type = 'INSERT'
           THEN CONCAT('NEW.', C.COLUMN_NAME)
           WHEN trigger_type = 'DELETE'
           THEN CONCAT('OLD.', C.COLUMN_NAME)
          END
        SEPARATOR '\nUNION ALL '
        ) text
        FROM INFORMATION_SCHEMA.COLUMNS C
        LEFT JOIN protocol_config ec ON ec.command = 'exclude_column'
         AND ec.table_name = C.TABLE_NAME
         AND ec.column_name = C.COLUMN_NAME
        WHERE C.TABLE_NAME = table_name
         AND C.TABLE_SCHEMA = DATABASE()
         AND C.COLUMN_TYPE != 'text'
         AND ec.id IS NULL
      )
    , ';\nEND;'
    );
    RETURN text;
END$

Функция generate_trigger — бизнес-логика + протоколирование:

generate_trigger

DELIMITER $
DROP FUNCTION IF EXISTS generate_trigger$
CREATE FUNCTION generate_trigger(table_name VARCHAR(200), trigger_time VARCHAR(200), trigger_type VARCHAR(200)) RETURNS TEXT
BEGIN
    DECLARE text TEXT;
    DECLARE bl_text TEXT;
    DECLARE prot_text TEXT;
    DECLARE trigger_time_short VARCHAR(3);
    DECLARE trigger_type_short VARCHAR(3);
    SET group_concat_max_len = 9000000;
    SET trigger_time_short := LOWER(SUBSTR(trigger_time, 1, 3));
    SET trigger_type_short := LOWER(SUBSTR(trigger_type, 1, 3));
    SET text := '';
    SET text := CONCAT(text, 'DROP TRIGGER IF EXISTS ', table_name, '_', trigger_time_short, '_', trigger_type_short, '_trg$');
    SET bl_text := gen_bl_trigger(table_name, trigger_time, trigger_type);
    SET prot_text := gen_prot_trigger(table_name, trigger_time, trigger_type);
    IF bl_text = '' AND prot_text = '' THEN
        RETURN text;
    END IF;
    SET text := CONCAT(text, '\nCREATE TRIGGER ', table_name, '_', trigger_time_short, '_', trigger_type_short, '_trg ', trigger_time, ' ', trigger_type, ' ON ', table_name,' FOR EACH ROW
trg_proc:BEGIN
IF @disable_', table_name, '_trg = 1 OR @disable_all_trg = 1 THEN
LEAVE trg_proc;
END IF;'
    , bl_text
    , prot_text
    , '\nEND$\n'
    );
    RETURN text;
END$

Функция generate_triggers для генерации текста всех триггеров по таблице:

generate_triggers

DELIMITER $
DROP FUNCTION IF EXISTS generate_triggers$
CREATE FUNCTION generate_triggers(p_table_name VARCHAR(200)) RETURNS TEXT BEGIN
    DECLARE table_name VARCHAR(200);
    DECLARE text TEXT;
    SET group_concat_max_len = 9000000;
    SET table_name := p_table_name;
    SET text := '';
    SET text := (SELECT GROUP_CONCAT(generate_trigger(table_name, trigger_time, trigger_type) SEPARATOR '\n')
    FROM (SELECT 'BEFORE' trigger_time
    UNION ALL SELECT 'AFTER' trigger_time) trigger_time
    , (SELECT 'INSERT' trigger_type
    UNION ALL SELECT 'UPDATE' trigger_type
    UNION ALL SELECT 'DELETE' trigger_type
    ) trigger_type);
    RETURN text;
END$

Авторизация описана в статье «Реализация Row Level Security на MySQL»

DELIMITER ;
DROP TABLE IF EXISTS users;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(100) NOT NULL,
  `pass` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) DEFAULT CHARSET=utf8 COMMENT='Пользователи системы';

DROP TABLE IF EXISTS auth_users;
CREATE TABLE `auth_users` (
  `conn_id` bigint(20) NOT NULL,
  `user_id` int(11) NOT NULL,
  `login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ip` varchar(45) DEFAULT NULL,
  `user_agent` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`conn_id`)
  -- , FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=MEMORY DEFAULT CHARSET=utf8 COMMENT='Авторизованные пользователи';

Теперь создадим пару тестовых таблиц:

DROP TABLE IF EXISTS doc_pos;
DROP TABLE IF EXISTS docs;
CREATE TABLE `docs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` VARCHAR(20) NOT NULL,
  `date` DATE NOT NULL,
  `warehouse` VARCHAR(100) NOT NULL,
  `partner` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8 COMMENT='Документы';
DROP TABLE IF EXISTS doc_pos;
CREATE TABLE `doc_pos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `doc_id` int(11) NOT NULL,
  `material` VARCHAR(100) NOT NULL,
  `amount` int(11) NOT NULL,
  `price` int(11) NOT NULL,
  PRIMARY KEY (`id`)
  , FOREIGN KEY (doc_id) REFERENCES docs(id)
) DEFAULT CHARSET=utf8 COMMENT='Позиции документов';

Выполним запрос для контроля корректности триггеров в БД:

Запрос для контроля корректности триггеров в БД

SELECT table_name, comment, rows_cn, data_len_mb
, MAX(need_bl_trg) need_bl_trg
, MAX(exclude_prot) exclude_prot
, MAX(CASE
    WHEN action_statement != gen_trg THEN CONCAT('SELECT generate_triggers("', table_name, '")')
    WHEN gen_trg IS NULL AND (exclude_prot IS NULL OR need_bl_trg = 1) THEN CONCAT('SELECT generate_triggers("', table_name, '")')
  END
  ) create_trg
FROM (
SELECT table_name, comment, rows_cn, data_len_mb, need_bl_trg, exclude_prot, action_statement, gen_trg
FROM (
SELECT table_name, comment, rows_cn, data_len_mb, need_bl_trg, exclude_prot
, CONCAT(SUBSTRING_INDEX(gen_trg, '\n', 2), '\n', action_statement, '$', '\n') action_statement
, gen_trg
FROM (
SELECT t.TABLE_NAME table_name
, t.TABLE_COMMENT comment
, t.TABLE_ROWS rows_cn
, ROUND(t.DATA_LENGTH / 1024 / 1024, 2) data_len_mb
, CASE WHEN r.ROUTINE_NAME IS NOT NULL THEN 1 END need_bl_trg
, CASE WHEN pd.id IS NOT NULL THEN 'Протокол выключен'
   WHEN pc.id IS NOT NULL THEN 'Исключено настройкой'
   WHEN t.TABLE_NAME IN ('protocol', 'protocol_pos') THEN 'Не протоколируется'
   WHEN C.COLUMN_NAME IS NULL THEN 'Нет поля id'
  END exclude_prot
, tr.ACTION_STATEMENT action_statement
, generate_trigger(tr.EVENT_OBJECT_TABLE, tr.ACTION_TIMING, tr.EVENT_MANIPULATION) gen_trg
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.ROUTINES r ON r.ROUTINE_NAME = CONCAT(t.TABLE_NAME, '_trg_proc')
 AND r.ROUTINE_TYPE = 'PROCEDURE' AND r.ROUTINE_SCHEMA = t.TABLE_SCHEMA
LEFT JOIN protocol_config pd ON pd.command = 'disable_protocol'
LEFT JOIN protocol_config pc ON pc.command = 'exclude_table' AND pc.table_name = t.TABLE_NAME
LEFT JOIN INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_SCHEMA = t.TABLE_SCHEMA
 AND C.TABLE_NAME = t.TABLE_NAME AND C.COLUMN_NAME = 'id'
LEFT JOIN INFORMATION_SCHEMA.TRIGGERS tr ON tr.TRIGGER_SCHEMA = t.TABLE_SCHEMA
 AND tr.EVENT_OBJECT_TABLE = t.TABLE_NAME
WHERE t.TABLE_SCHEMA = DATABASE()
 AND t.TABLE_TYPE = 'BASE TABLE'
 AND t.ENGINE = 'InnoDB'
) d) d) d
GROUP BY table_name, comment, rows_cn, data_len_mb
ORDER BY table_name
;

+---------------+--------------------------+-------+-----------+-----------+------------------+-------------------------------------------+
|table_name     |comment                   |rows_cn|data_len_mb|need_bl_trg|exclude_prot      |create_trg                                 |
+---------------+--------------------------+-------+-----------+-----------+------------------+-------------------------------------------+
|docs           |Документы                 |      0|       0.02|       NULL|NULL              |SELECT generate_triggers("docs")           |
|doc_pos        |Позиции документов        |      0|       0.02|       NULL|NULL              |SELECT generate_triggers("doc_pos")        |
|protocol       |Протокол изменений        |      0|       0.02|       NULL|Не протоколируется|NULL                                       |
|protocol_config|Настройка протоколирования|      0|       0.02|       NULL|NULL              |SELECT generate_triggers("protocol_config")|
|protocol_pos   |Поля протокола изменений  |      0|       0.02|       NULL|Не протоколируется|NULL                                       |
|users          |Пользователи системы      |      0|       0.02|       NULL|NULL              |SELECT generate_triggers("users")          |
+---------------+--------------------------+-------+-----------+-----------+------------------+-------------------------------------------+

Система предлагает нам создать триггеры протоколирования на таблицы docs, doc_pos, protocol_config и users

Обернём предыдущий запрос SELECT-ом и выполним ещё раз:

Запрос для контроля корректности триггеров в БД

SELECT GROUP_CONCAT(create_trg SEPARATOR '\nUNION ALL ') sql_text
FROM (
SELECT table_name, comment, rows_cn, data_len_mb
, MAX(need_bl_trg) need_bl_trg
, MAX(exclude_prot) exclude_prot
, MAX(CASE
    WHEN action_statement != gen_trg THEN CONCAT('SELECT generate_triggers("', table_name, '")')
    WHEN gen_trg IS NULL AND (exclude_prot IS NULL OR need_bl_trg = 1) THEN CONCAT('SELECT generate_triggers("', table_name, '")')
  END
  ) create_trg
FROM (
SELECT table_name, comment, rows_cn, data_len_mb, need_bl_trg, exclude_prot, action_statement, gen_trg
FROM (
SELECT table_name, comment, rows_cn, data_len_mb, need_bl_trg, exclude_prot
, CONCAT(SUBSTRING_INDEX(gen_trg, '\n', 2), '\n', action_statement, '$', '\n') action_statement
, gen_trg
FROM (
SELECT t.TABLE_NAME table_name
, t.TABLE_COMMENT comment
, t.TABLE_ROWS rows_cn
, ROUND(t.DATA_LENGTH / 1024 / 1024, 2) data_len_mb
, CASE WHEN r.ROUTINE_NAME IS NOT NULL THEN 1 END need_bl_trg
, CASE WHEN pd.id IS NOT NULL THEN 'Протокол выключен'
   WHEN pc.id IS NOT NULL THEN 'Исключено настройкой'
   WHEN t.TABLE_NAME IN ('protocol', 'protocol_pos') THEN 'Не протоколируется'
   WHEN C.COLUMN_NAME IS NULL THEN 'Нет поля id'
  END exclude_prot
, tr.ACTION_STATEMENT action_statement
, generate_trigger(tr.EVENT_OBJECT_TABLE, tr.ACTION_TIMING, tr.EVENT_MANIPULATION) gen_trg
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.ROUTINES r ON r.ROUTINE_NAME = CONCAT(t.TABLE_NAME, '_trg_proc')
 AND r.ROUTINE_TYPE = 'PROCEDURE' AND r.ROUTINE_SCHEMA = t.TABLE_SCHEMA
LEFT JOIN protocol_config pd ON pd.command = 'disable_protocol'
LEFT JOIN protocol_config pc ON pc.command = 'exclude_table' AND pc.table_name = t.TABLE_NAME
LEFT JOIN INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_SCHEMA = t.TABLE_SCHEMA
 AND C.TABLE_NAME = t.TABLE_NAME AND C.COLUMN_NAME = 'id'
LEFT JOIN INFORMATION_SCHEMA.TRIGGERS tr ON tr.TRIGGER_SCHEMA = t.TABLE_SCHEMA
 AND tr.EVENT_OBJECT_TABLE = t.TABLE_NAME
WHERE t.TABLE_SCHEMA = DATABASE()
 AND t.TABLE_TYPE = 'BASE TABLE'
 AND t.ENGINE = 'InnoDB'
) d) d) d
GROUP BY table_name, comment, rows_cn, data_len_mb
ORDER BY table_name
) d
;

Результат:

SELECT generate_triggers("docs")
UNION ALL SELECT generate_triggers("doc_pos")
UNION ALL SELECT generate_triggers("protocol_config")
UNION ALL SELECT generate_triggers("users")
;

Выполним сейчас этот запрос:

SELECT generate_triggers(docs) UNION ALL SELECT ....

DROP TRIGGER IF EXISTS docs_bef_ins_trg$
DROP TRIGGER IF EXISTS docs_aft_ins_trg$
CREATE TRIGGER docs_aft_ins_trg AFTER INSERT ON docs FOR EACH ROW
trg_proc:BEGIN
IF @disable_docs_trg = 1 OR @disable_all_trg = 1 THEN
LEAVE trg_proc;
END IF;
prot_proc: BEGIN
DECLARE prot_id INT;
IF @disable_docs_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
END IF;
INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent)
SELECT IFNULL(u.email, USER()) username, "I", "docs", NEW.id, au.ip, au.user_agent
FROM (SELECT NULL FROM dual) d
LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFT JOIN users u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();
INSERT INTO protocol_pos (prot_id, column_name, new_val)
SELECT prot_id, "id", NEW.id
UNION ALL SELECT prot_id, "num", NEW.num
UNION ALL SELECT prot_id, "date", NEW.date
UNION ALL SELECT prot_id, "warehouse", NEW.warehouse
UNION ALL SELECT prot_id, "partner", NEW.partner;
END;
END$

DROP TRIGGER IF EXISTS docs_bef_upd_trg$
DROP TRIGGER IF EXISTS docs_aft_upd_trg$
CREATE TRIGGER docs_aft_upd_trg AFTER UPDATE ON docs FOR EACH ROW
trg_proc:BEGIN
IF @disable_docs_trg = 1 OR @disable_all_trg = 1 THEN
LEAVE trg_proc;
END IF;
prot_proc: BEGIN
DECLARE prot_id INT;
IF @disable_docs_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
END IF;
IF IFNULL(NEW.id, "-ЪъЪ") = IFNULL(OLD.id, "-ЪъЪ") AND IFNULL(NEW.num, "-ЪъЪ") = IFNULL(OLD.num, "-ЪъЪ") AND IFNULL(NEW.date, "-ЪъЪ") = IFNULL(OLD.date, "-ЪъЪ") AND IFNULL(NEW.warehouse, "-ЪъЪ") = IFNULL(OLD.warehouse, "-ЪъЪ") AND IFNULL(NEW.partner, "-ЪъЪ") = IFNULL(OLD.partner, "-ЪъЪ") THEN
LEAVE prot_proc;
END IF;
INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent)
SELECT IFNULL(u.email, USER()) username, "U", "docs", NEW.id, au.ip, au.user_agent
FROM (SELECT NULL FROM dual) d
LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFT JOIN users u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();
INSERT INTO protocol_pos (prot_id, column_name, old_val, new_val)
SELECT prot_id, "id", OLD.id, NEW.id FROM dual WHERE IFNULL(NEW.id, "-ЪъЪ") != IFNULL(OLD.id, "-ЪъЪ")
UNION ALL SELECT prot_id, "num", OLD.num, NEW.num FROM dual WHERE IFNULL(NEW.num, "-ЪъЪ") != IFNULL(OLD.num, "-ЪъЪ")
UNION ALL SELECT prot_id, "date", OLD.date, NEW.date FROM dual WHERE IFNULL(NEW.date, "-ЪъЪ") != IFNULL(OLD.date, "-ЪъЪ")
UNION ALL SELECT prot_id, "warehouse", OLD.warehouse, NEW.warehouse FROM dual WHERE IFNULL(NEW.warehouse, "-ЪъЪ") != IFNULL(OLD.warehouse, "-ЪъЪ")
UNION ALL SELECT prot_id, "partner", OLD.partner, NEW.partner FROM dual WHERE IFNULL(NEW.partner, "-ЪъЪ") != IFNULL(OLD.partner, "-ЪъЪ");
END;
END$

DROP TRIGGER IF EXISTS docs_bef_del_trg$
DROP TRIGGER IF EXISTS docs_aft_del_trg$
CREATE TRIGGER docs_aft_del_trg AFTER DELETE ON docs FOR EACH ROW
trg_proc:BEGIN
IF @disable_docs_trg = 1 OR @disable_all_trg = 1 THEN
LEAVE trg_proc;
END IF;
prot_proc: BEGIN
DECLARE prot_id INT;
IF @disable_docs_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
END IF;
INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent)
SELECT IFNULL(u.email, USER()) username, "D", "docs", OLD.id, au.ip, au.user_agent
FROM (SELECT NULL FROM dual) d
LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFT JOIN users u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();
INSERT INTO protocol_pos (prot_id, column_name, old_val)
SELECT prot_id, "id", OLD.id
UNION ALL SELECT prot_id, "num", OLD.num
UNION ALL SELECT prot_id, "date", OLD.date
UNION ALL SELECT prot_id, "warehouse", OLD.warehouse
UNION ALL SELECT prot_id, "partner", OLD.partner;
END;
END$
DROP TRIGGER IF EXISTS users_bef_ins_trg$
DROP TRIGGER IF EXISTS users_aft_ins_trg$
CREATE TRIGGER users_aft_ins_trg AFTER INSERT ON users FOR EACH ROW
trg_proc:BEGIN
IF @disable_users_trg = 1 OR @disable_all_trg = 1 THEN
LEAVE trg_proc;
END IF;
prot_proc: BEGIN
DECLARE prot_id INT;
IF @disable_users_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
END IF;
INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent)
SELECT IFNULL(u.email, USER()) username, "I", "users", NEW.id, au.ip, au.user_agent
FROM (SELECT NULL FROM dual) d
LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFT JOIN users u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();
INSERT INTO protocol_pos (prot_id, column_name, new_val)
SELECT prot_id, "id", NEW.id
UNION ALL SELECT prot_id, "email", NEW.email
UNION ALL SELECT prot_id, "pass", NEW.pass;
END;
END$

DROP TRIGGER IF EXISTS users_bef_upd_trg$
DROP TRIGGER IF EXISTS users_aft_upd_trg$
CREATE TRIGGER users_aft_upd_trg AFTER UPDATE ON users FOR EACH ROW
trg_proc:BEGIN
IF @disable_users_trg = 1 OR @disable_all_trg = 1 THEN
LEAVE trg_proc;
END IF;
prot_proc: BEGIN
DECLARE prot_id INT;
IF @disable_users_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
END IF;
IF IFNULL(NEW.id, "-ЪъЪ") = IFNULL(OLD.id, "-ЪъЪ") AND IFNULL(NEW.email, "-ЪъЪ") = IFNULL(OLD.email, "-ЪъЪ") AND IFNULL(NEW.pass, "-ЪъЪ") = IFNULL(OLD.pass, "-ЪъЪ") THEN
LEAVE prot_proc;
END IF;
INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent)
SELECT IFNULL(u.email, USER()) username, "U", "users", NEW.id, au.ip, au.user_agent
FROM (SELECT NULL FROM dual) d
LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFT JOIN users u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();
INSERT INTO protocol_pos (prot_id, column_name, old_val, new_val)
SELECT prot_id, "id", OLD.id, NEW.id FROM dual WHERE IFNULL(NEW.id, "-ЪъЪ") != IFNULL(OLD.id, "-ЪъЪ")
UNION ALL SELECT prot_id, "email", OLD.email, NEW.email FROM dual WHERE IFNULL(NEW.email, "-ЪъЪ") != IFNULL(OLD.email, "-ЪъЪ")
UNION ALL SELECT prot_id, "pass", OLD.pass, NEW.pass FROM dual WHERE IFNULL(NEW.pass, "-ЪъЪ") != IFNULL(OLD.pass, "-ЪъЪ");
END;
END$

DROP TRIGGER IF EXISTS users_bef_del_trg$
DROP TRIGGER IF EXISTS users_aft_del_trg$
CREATE TRIGGER users_aft_del_trg AFTER DELETE ON users FOR EACH ROW
trg_proc:BEGIN
IF @disable_users_trg = 1 OR @disable_all_trg = 1 THEN
LEAVE trg_proc;
END IF;
prot_proc: BEGIN
DECLARE prot_id INT;
IF @disable_users_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
END IF;
INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent)
SELECT IFNULL(u.email, USER()) username, "D", "users", OLD.id, au.ip, au.user_agent
FROM (SELECT NULL FROM dual) d
LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFT JOIN users u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();
INSERT INTO protocol_pos (prot_id, column_name, old_val)
SELECT prot_id, "id", OLD.id
UNION ALL SELECT prot_id, "email", OLD.email
UNION ALL SELECT prot_id, "pass", OLD.pass;
END;
END$

DROP TRIGGER IF EXISTS doc_pos_bef_ins_trg$
DROP TRIGGER IF EXISTS doc_pos_aft_ins_trg$
CREATE TRIGGER doc_pos_aft_ins_trg AFTER INSERT ON doc_pos FOR EACH ROW
trg_proc:BEGIN
IF @disable_doc_pos_trg = 1 OR @disable_all_trg = 1 THEN
LEAVE trg_proc;
END IF;
prot_proc: BEGIN
DECLARE prot_id INT;
IF @disable_doc_pos_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
END IF;
INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent)
SELECT IFNULL(u.email, USER()) username, "I", "doc_pos", NEW.id, au.ip, au.user_agent
FROM (SELECT NULL FROM dual) d
LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFT JOIN users u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();
INSERT INTO protocol_pos (prot_id, column_name, new_val)
SELECT prot_id, "id", NEW.id
UNION ALL SELECT prot_id, "doc_id", NEW.doc_id
UNION ALL SELECT prot_id, "material", NEW.material
UNION ALL SELECT prot_id, "amount", NEW.amount
UNION ALL SELECT prot_id, "price", NEW.price;
END;
END$

DROP TRIGGER IF EXISTS doc_pos_bef_upd_trg$
DROP TRIGGER IF EXISTS doc_pos_aft_upd_trg$
CREATE TRIGGER doc_pos_aft_upd_trg AFTER UPDATE ON doc_pos FOR EACH ROW
trg_proc:BEGIN
IF @disable_doc_pos_trg = 1 OR @disable_all_trg = 1 THEN
LEAVE trg_proc;
END IF;
prot_proc: BEGIN
DECLARE prot_id INT;
IF @disable_doc_pos_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
END IF;
IF IFNULL(NEW.id, "-ЪъЪ") = IFNULL(OLD.id, "-ЪъЪ") AND IFNULL(NEW.doc_id, "-ЪъЪ") = IFNULL(OLD.doc_id, "-ЪъЪ") AND IFNULL(NEW.material, "-ЪъЪ") = IFNULL(OLD.material, "-ЪъЪ") AND IFNULL(NEW.amount, "-ЪъЪ") = IFNULL(OLD.amount, "-ЪъЪ") AND IFNULL(NEW.price, "-ЪъЪ") = IFNULL(OLD.price, "-ЪъЪ") THEN
LEAVE prot_proc;
END IF;
INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent)
SELECT IFNULL(u.email, USER()) username, "U", "doc_pos", NEW.id, au.ip, au.user_agent
FROM (SELECT NULL FROM dual) d
LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFT JOIN users u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();
INSERT INTO protocol_pos (prot_id, column_name, old_val, new_val)
SELECT prot_id, "id", OLD.id, NEW.id FROM dual WHERE IFNULL(NEW.id, "-ЪъЪ") != IFNULL(OLD.id, "-ЪъЪ")
UNION ALL SELECT prot_id, "doc_id", OLD.doc_id, NEW.doc_id FROM dual WHERE IFNULL(NEW.doc_id, "-ЪъЪ") != IFNULL(OLD.doc_id, "-ЪъЪ")
UNION ALL SELECT prot_id, "material", OLD.material, NEW.material FROM dual WHERE IFNULL(NEW.material, "-ЪъЪ") != IFNULL(OLD.material, "-ЪъЪ")
UNION ALL SELECT prot_id, "amount", OLD.amount, NEW.amount FROM dual WHERE IFNULL(NEW.amount, "-ЪъЪ") != IFNULL(OLD.amount, "-ЪъЪ")
UNION ALL SELECT prot_id, "price", OLD.price, NEW.price FROM dual WHERE IFNULL(NEW.price, "-ЪъЪ") != IFNULL(OLD.price, "-ЪъЪ");
END;
END$

DROP TRIGGER IF EXISTS doc_pos_bef_del_trg$
DROP TRIGGER IF EXISTS doc_pos_aft_del_trg$
CREATE TRIGGER doc_pos_aft_del_trg AFTER DELETE ON doc_pos FOR EACH ROW
trg_proc:BEGIN
IF @disable_doc_pos_trg = 1 OR @disable_all_trg = 1 THEN
LEAVE trg_proc;
END IF;
prot_proc: BEGIN
DECLARE prot_id INT;
IF @disable_doc_pos_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
END IF;
INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent)
SELECT IFNULL(u.email, USER()) username, "D", "doc_pos", OLD.id, au.ip, au.user_agent
FROM (SELECT NULL FROM dual) d
LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFT JOIN users u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();
INSERT INTO protocol_pos (prot_id, column_name, old_val)
SELECT prot_id, "id", OLD.id
UNION ALL SELECT prot_id, "doc_id", OLD.doc_id
UNION ALL SELECT prot_id, "material", OLD.material
UNION ALL SELECT prot_id, "amount", OLD.amount
UNION ALL SELECT prot_id, "price", OLD.price;
END;
END$
DROP TRIGGER IF EXISTS protocol_config_bef_ins_trg$
DROP TRIGGER IF EXISTS protocol_config_aft_ins_trg$
CREATE TRIGGER protocol_config_aft_ins_trg AFTER INSERT ON protocol_config FOR EACH ROW
trg_proc:BEGIN
IF @disable_protocol_config_trg = 1 OR @disable_all_trg = 1 THEN
LEAVE trg_proc;
END IF;
prot_proc: BEGIN
DECLARE prot_id INT;
IF @disable_protocol_config_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
END IF;
INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent)
SELECT IFNULL(u.email, USER()) username, "I", "protocol_config", NEW.id, au.ip, au.user_agent
FROM (SELECT NULL FROM dual) d
LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFT JOIN users u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();
INSERT INTO protocol_pos (prot_id, column_name, new_val)
SELECT prot_id, "id", NEW.id
UNION ALL SELECT prot_id, "command", NEW.command
UNION ALL SELECT prot_id, "table_name", NEW.table_name
UNION ALL SELECT prot_id, "column_name", NEW.column_name
UNION ALL SELECT prot_id, "denormalize_column", NEW.denormalize_column;
END;
END$

DROP TRIGGER IF EXISTS protocol_config_bef_upd_trg$
DROP TRIGGER IF EXISTS protocol_config_aft_upd_trg$
CREATE TRIGGER protocol_config_aft_upd_trg AFTER UPDATE ON protocol_config FOR EACH ROW
trg_proc:BEGIN
IF @disable_protocol_config_trg = 1 OR @disable_all_trg = 1 THEN
LEAVE trg_proc;
END IF;
prot_proc: BEGIN
DECLARE prot_id INT;
IF @disable_protocol_config_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
END IF;
IF IFNULL(NEW.id, "-ЪъЪ") = IFNULL(OLD.id, "-ЪъЪ") AND IFNULL(NEW.command, "-ЪъЪ") = IFNULL(OLD.command, "-ЪъЪ") AND IFNULL(NEW.table_name, "-ЪъЪ") = IFNULL(OLD.table_name, "-ЪъЪ") AND IFNULL(NEW.column_name, "-ЪъЪ") = IFNULL(OLD.column_name, "-ЪъЪ") AND IFNULL(NEW.denormalize_column, "-ЪъЪ") = IFNULL(OLD.denormalize_column, "-ЪъЪ") THEN
LEAVE prot_proc;
END IF;
INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent)
SELECT IFNULL(u.email, USER()) username, "U", "protocol_config", NEW.id, au.ip, au.user_agent
FROM (SELECT NULL FROM dual) d
LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFT JOIN users u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();
INSERT INTO protocol_pos (prot_id, column_name, old_val, new_val)
SELECT prot_id, "id", OLD.id, NEW.id FROM dual WHERE IFNULL(NEW.id, "-ЪъЪ") != IFNULL(OLD.id, "-ЪъЪ")
UNION ALL SELECT prot_id, "command", OLD.command, NEW.command FROM dual WHERE IFNULL(NEW.command, "-ЪъЪ") != IFNULL(OLD.command, "-ЪъЪ")
UNION ALL SELECT prot_id, "table_name", OLD.table_name, NEW.table_name FROM dual WHERE IFNULL(NEW.table_name, "-ЪъЪ") != IFNULL(OLD.table_name, "-ЪъЪ")
UNION ALL SELECT prot_id, "column_name", OLD.column_name, NEW.column_name FROM dual WHERE IFNULL(NEW.column_name, "-ЪъЪ") != IFNULL(OLD.column_name, "-ЪъЪ")
UNION ALL SELECT prot_id, "denormalize_column", OLD.denormalize_column, NEW.denormalize_column FROM dual WHERE IFNULL(NEW.denormalize_column, "-ЪъЪ") != IFNULL(OLD.denormalize_column, "-ЪъЪ");
END;
END$

DROP TRIGGER IF EXISTS protocol_config_bef_del_trg$
DROP TRIGGER IF EXISTS protocol_config_aft_del_trg$
CREATE TRIGGER protocol_config_aft_del_trg AFTER DELETE ON protocol_config FOR EACH ROW
trg_proc:BEGIN
IF @disable_protocol_config_trg = 1 OR @disable_all_trg = 1 THEN
LEAVE trg_proc;
END IF;
prot_proc: BEGIN
DECLARE prot_id INT;
IF @disable_protocol_config_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
END IF;
INSERT INTO protocol (username, oper, table_name, table_id, ip, user_agent)
SELECT IFNULL(u.email, USER()) username, "D", "protocol_config", OLD.id, au.ip, au.user_agent
FROM (SELECT NULL FROM dual) d
LEFT JOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFT JOIN users u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();
INSERT INTO protocol_pos (prot_id, column_name, old_val)
SELECT prot_id, "id", OLD.id
UNION ALL SELECT prot_id, "command", OLD.command
UNION ALL SELECT prot_id, "table_name", OLD.table_name
UNION ALL SELECT prot_id, "column_name", OLD.column_name
UNION ALL SELECT prot_id, "denormalize_column", OLD.denormalize_column;
END;
END$

У нас получился текст триггеров выполним его (с DELIMITER $)

Теперь наши таблицы протоколируются и все изменения данных записываются в протокол.
Чекнем настройки протоколирования первым запросом:

Запрос для контроля корректности триггеров в БД

DELIMITER ;
SELECT table_name, comment, rows_cn, data_len_mb
, MAX(need_bl_trg) need_bl_trg
, MAX(exclude_prot) exclude_prot
, MAX(CASE
    WHEN action_statement != gen_trg THEN CONCAT('SELECT generate_triggers("', table_name, '")')
    WHEN gen_trg IS NULL AND (exclude_prot IS NULL OR need_bl_trg = 1) THEN CONCAT('SELECT generate_triggers("', table_name, '")')
  END
  ) create_trg
FROM (
SELECT table_name, comment, rows_cn, data_len_mb, need_bl_trg, exclude_prot, action_statement, gen_trg
FROM (
SELECT table_name, comment, rows_cn, data_len_mb, need_bl_trg, exclude_prot
, CONCAT(SUBSTRING_INDEX(gen_trg, '\n', 2), '\n', action_statement, '$', '\n') action_statement
, gen_trg
FROM (
SELECT t.TABLE_NAME table_name
, t.TABLE_COMMENT comment
, t.TABLE_ROWS rows_cn
, ROUND(t.DATA_LENGTH / 1024 / 1024, 2) data_len_mb
, CASE WHEN r.ROUTINE_NAME IS NOT NULL THEN 1 END need_bl_trg
, CASE WHEN pd.id IS NOT NULL THEN 'Протокол выключен'
   WHEN pc.id IS NOT NULL THEN 'Исключено настройкой'
   WHEN t.TABLE_NAME IN ('protocol', 'protocol_pos') THEN 'Не протоколируется'
   WHEN C.COLUMN_NAME IS NULL THEN 'Нет поля id'
  END exclude_prot
, tr.ACTION_STATEMENT action_statement
, generate_trigger(tr.EVENT_OBJECT_TABLE, tr.ACTION_TIMING, tr.EVENT_MANIPULATION) gen_trg
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.ROUTINES r ON r.ROUTINE_NAME = CONCAT(t.TABLE_NAME, '_trg_proc')
 AND r.ROUTINE_TYPE = 'PROCEDURE' AND r.ROUTINE_SCHEMA = t.TABLE_SCHEMA
LEFT JOIN protocol_config pd ON pd.command = 'disable_protocol'
LEFT JOIN protocol_config pc ON pc.command = 'exclude_table' AND pc.table_name = t.TABLE_NAME
LEFT JOIN INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_SCHEMA = t.TABLE_SCHEMA
 AND C.TABLE_NAME = t.TABLE_NAME AND C.COLUMN_NAME = 'id'
LEFT JOIN INFORMATION_SCHEMA.TRIGGERS tr ON tr.TRIGGER_SCHEMA = t.TABLE_SCHEMA
 AND tr.EVENT_OBJECT_TABLE = t.TABLE_NAME
WHERE t.TABLE_SCHEMA = DATABASE()
 AND t.TABLE_TYPE = 'BASE TABLE'
 AND t.ENGINE = 'InnoDB'
) d) d) d
GROUP BY table_name, comment, rows_cn, data_len_mb
ORDER BY table_name
;

+---------------+--------------------------+-------+-----------+-----------+------------------+----------+
|table_name     |comment                   |rows_cn|data_len_mb|need_bl_trg|exclude_prot      |create_trg|
+---------------+--------------------------+-------+-----------+-----------+------------------+----------+
|docs           |Документы                 |      0|       0.02|       NULL|NULL              |NULL      |
|doc_pos        |Позиции документов        |      0|       0.02|       NULL|NULL              |NULL      |
|protocol       |Протокол изменений        |      0|       0.02|       NULL|Не протоколируется|NULL      |
|protocol_config|Настройка протоколирования|      0|       0.02|       NULL|NULL              |NULL      |
|protocol_pos   |Поля протокола изменений  |      0|       0.02|       NULL|Не протоколируется|NULL      |
|users          |Пользователи системы      |      0|       0.02|       NULL|NULL              |NULL      |
+---------------+--------------------------+-------+-----------+-----------+------------------+----------+
6 rows in set, 0 warnings (5.33 sec)

Добавим пользователя и авторизуемся:

/*
DELETE FROM doc_pos;
DELETE FROM docs;
DELETE FROM auth_users;
DELETE FROM users;
DELETE FROM protocol_pos;
DELETE FROM protocol;
*/
INSERT INTO users (email, pass) VALUES ('test@test.ru', '12345');
Query OK, 1 row affected (0.01 sec)

INSERT INTO auth_users (conn_id, user_id)
SELECT CONNECTION_ID() conn_id
, (SELECT u.id FROM users u WHERE u.email = 'test@test.ru') user_id
;
Query OK, 1 row affected (0.00 sec)

Вставим тестовый документ:

BEGIN;
INSERT INTO docs (num, date, warehouse, partner)
VALUES ('1', '2018-07-17', 'Склад Москва', 'Горизонт, ООО');
SET @doc_id := LAST_INSERT_ID();
INSERT INTO doc_pos (doc_id, material, amount, price)
VALUES (@doc_id, 'Краски акварельные', 10, 52)
, (@doc_id, 'Краски акриловые', 20, 165)
, (@doc_id, 'Ручка шариковая', 7, 30);
COMMIT;

Посмотрим что у нас получилось в протоколе:

SELECT id, date, oper, table_name, table_id
, (SELECT GROUP_CONCAT(pp.column_name, ': ('
     , IFNULL(pp.old_val, 'NULL')
     , ', '
     , IFNULL(pp.new_val, 'NULL')
     , ')'
     SEPARATOR ', '
     )
   FROM protocol_pos pp
   WHERE pp.prot_id = p.id
   ) vals
, p.username
FROM protocol p;

Запрос для html

SELECT id, date, oper, table_name, table_id
, (SELECT CONCAT('<table class="table table-bordered"  style="width: 100%; margin: -9px;">'
   , GROUP_CONCAT('<tr><td style="font-weight: bold; width: 20%;">', pp.column_name, '</td>'
     , '<td style="width: 40%;">', IFNULL(pp.old_val, "<span style='color: #FF0000; font-style: italic;'>NULL</span>"), '</td>'
     , '<td style="width: 40%;">', IFNULL(pp.new_val, "<span style='color: #FF0000; font-style: italic;'>NULL</span>"), '</td></tr>'
     SEPARATOR ''
     )
   , '</table>'
   )
   FROM protocol_pos pp
   WHERE pp.prot_id = p.id
   ) vals
, p.username
FROM protocol p;

+----+---------------------+------+------------+----------+-------------------------------------------------------------------------------------------------------------------------+-------------------+
| id | date                | oper | table_name | table_id | vals                                                                                                                    | username          |
+----+---------------------+------+------------+----------+-------------------------------------------------------------------------------------------------------------------------+-------------------+
|  1 | 2018-10-09 17:21:27 | I    | users      |        1 | email: (NULL, test@test.ru), id: (NULL, 1), pass: (NULL, 12345)                                                         | admin@myhosting.ru|
|  2 | 2018-10-09 17:21:51 | I    | docs       |        1 | date: (NULL, 2018-07-17), id: (NULL, 1), num: (NULL, 1), partner: (NULL, Горизонт, ООО), warehouse: (NULL, Склад Москва)| test@test.ru      |
|  3 | 2018-10-09 17:21:51 | I    | doc_pos    |        1 | amount: (NULL, 10), doc_id: (NULL, 1), id: (NULL, 1), material: (NULL, Краски акварельные), price: (NULL, 52)           | test@test.ru      |
|  4 | 2018-10-09 17:21:51 | I    | doc_pos    |        2 | amount: (NULL, 20), doc_id: (NULL, 1), id: (NULL, 2), material: (NULL, Краски акриловые), price: (NULL, 165)            | test@test.ru      |
|  5 | 2018-10-09 17:21:51 | I    | doc_pos    |        3 | amount: (NULL, 7), doc_id: (NULL, 1), id: (NULL, 3), material: (NULL, Ручка шариковая), price: (NULL, 30)               | test@test.ru      |
+----+---------------------+------+------------+----------+-------------------------------------------------------------------------------------------------------------------------+-------------------+

Как видим все изменения БД протоколируются, начиная от вставки пользователя.

Получим отчёт по продажам:

SELECT NOW() report_time, d.date, SUM(p.amount * p.price) sum
FROM docs d
INNER JOIN doc_pos p ON d.id = p.doc_id
GROUP BY d.date;

+---------------------+------------+------+
| report_time         | date       | sum  |
+---------------------+------------+------+
| 2018-10-09 17:23:47 | 2018-07-17 | 4030 |
+---------------------+------------+------+

Теперь изменим имеющийся документ и добавим ещё один:

BEGIN;
SET @doc_id := (SELECT id FROM docs WHERE num = '1');
UPDATE docs SET date = '2018-07-16', warehouse = warehouse WHERE id = @doc_id;
DELETE FROM doc_pos WHERE doc_id = @doc_id AND material = 'Ручка шариковая';
UPDATE doc_pos p SET p.price = 105, p.material = 'Краски масляные' WHERE p.doc_id = @doc_id AND p.material = 'Краски акриловые';
INSERT INTO docs (num, date, warehouse, partner)
VALUES ('2', '2018-07-18', 'Склад Новосибирск', 'Радуга, ЗАО');
SET @doc_id := LAST_INSERT_ID();
INSERT INTO doc_pos (doc_id, material, amount, price)
VALUES (@doc_id, 'Рамка 10*15', 5, 102)
, (@doc_id, 'Бумага А4', 2, 165);
COMMIT;

Так будет выглядеть новый протокол

SELECT id, date, oper, table_name, table_id
, (SELECT GROUP_CONCAT(pp.column_name, ': ('
     , IFNULL(pp.old_val, 'NULL')
     , ', '
     , IFNULL(pp.new_val, 'NULL')
     , ')'
     SEPARATOR ', '
     )
   FROM protocol_pos pp
   WHERE pp.prot_id = p.id
   ) vals
, p.username
FROM protocol p;

+----+---------------------+------+------------+----------+----------------------------------------------------------------------------------------------------------------------------+-------------------+
| id | date                | oper | table_name | table_id | vals                                                                                                                       | username          |
+----+---------------------+------+------------+----------+----------------------------------------------------------------------------------------------------------------------------+-------------------+
|  1 | 2018-10-09 17:21:27 | I    | users      |        1 | email: (NULL, test@test.ru), id: (NULL, 1), pass: (NULL, 12345)                                                            | admin@myhosting.ru|
|  2 | 2018-10-09 17:21:51 | I    | docs       |        1 | date: (NULL, 2018-07-17), id: (NULL, 1), num: (NULL, 1), partner: (NULL, Горизонт, ООО), warehouse: (NULL, Склад Москва)   | test@test.ru      |
|  3 | 2018-10-09 17:21:51 | I    | doc_pos    |        1 | amount: (NULL, 10), doc_id: (NULL, 1), id: (NULL, 1), material: (NULL, Краски акварельные), price: (NULL, 52)              | test@test.ru      |
|  4 | 2018-10-09 17:21:51 | I    | doc_pos    |        2 | amount: (NULL, 20), doc_id: (NULL, 1), id: (NULL, 2), material: (NULL, Краски акриловые), price: (NULL, 165)               | test@test.ru      |
|  5 | 2018-10-09 17:21:51 | I    | doc_pos    |        3 | amount: (NULL, 7), doc_id: (NULL, 1), id: (NULL, 3), material: (NULL, Ручка шариковая), price: (NULL, 30)                  | test@test.ru      |
|  6 | 2018-10-09 17:24:27 | U    | docs       |        1 | date: (2018-07-17, 2018-07-16)                                                                                             | test@test.ru      |
|  7 | 2018-10-09 17:24:27 | D    | doc_pos    |        3 | amount: (7, NULL), doc_id: (1, NULL), id: (3, NULL), material: (Ручка шариковая, NULL), price: (30, NULL)                  | test@test.ru      |
|  8 | 2018-10-09 17:24:27 | U    | doc_pos    |        2 | material: (Краски акриловые, Краски масляные), price: (165, 105)                                                           | test@test.ru      |
|  9 | 2018-10-09 17:24:27 | I    | docs       |        2 | date: (NULL, 2018-07-18), id: (NULL, 2), num: (NULL, 2), partner: (NULL, Радуга, ЗАО), warehouse: (NULL, Склад Новосибирск)| test@test.ru      |
| 10 | 2018-10-09 17:24:27 | I    | doc_pos    |        4 | amount: (NULL, 5), doc_id: (NULL, 2), id: (NULL, 4), material: (NULL, Рамка 10*15), price: (NULL, 102)                     | test@test.ru      |
| 11 | 2018-10-09 17:24:27 | I    | doc_pos    |        5 | amount: (NULL, 2), doc_id: (NULL, 2), id: (NULL, 5), material: (NULL, Бумага А4), price: (NULL, 165)                       | test@test.ru      |
+----+---------------------+------+------------+----------+----------------------------------------------------------------------------------------------------------------------------+-------------------+

Получим новый отчёт:

SELECT NOW() report_time, d.date, SUM(p.amount * p.price) sum
FROM docs d
INNER JOIN doc_pos p ON d.id = p.doc_id
GROUP BY d.date;

+---------------------+------------+------+
| report_time         | date       | sum  |
+---------------------+------------+------+
| 2018-10-09 17:26:18 | 2018-07-16 | 2620 |
| 2018-10-09 17:26:18 | 2018-07-18 |  840 |
+---------------------+------------+------+

Смотрим на отчёт и не можем найти данные за 2018-07-17 число, хотя точно помним, что они были, у нас даже распечатанный отчёт есть в 2018-10-09 17:23:47

Научим MySQL заглядывать в прошлое! Для этого напишем процедуры которые по протоколу смогут откатывать изменения.

Процедура exec_protocol выполняет изменения по строчке протокола (p_prot_id)

exec_protocol

DELIMITER $
DROP PROCEDURE IF EXISTS exec_protocol$
CREATE PROCEDURE exec_protocol(p_prot_id BIGINT, direction INT) BEGIN
    DECLARE p_sql_text TEXT;
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR
    SELECT CONCAT(
      CASE WHEN p.oper = 'I' AND direction = 1 OR p.oper = 'D' AND direction = -1 THEN 'INSERT INTO'
          WHEN p.oper = 'U' THEN 'UPDATE'
          WHEN p.oper = 'D' AND direction = 1 OR p.oper = 'I' AND direction = -1 THEN 'DELETE FROM'
        END
      , ' ', p.table_name, ' '
      , CASE WHEN p.oper = 'I' AND direction = 1 OR p.oper = 'D' AND direction = -1
          THEN CONCAT('(', GROUP_CONCAT(pos.column_name ORDER BY pos.column_name SEPARATOR ', '), ')'
          , ' VALUES (', GROUP_CONCAT(QUOTE(CASE direction WHEN 1 THEN pos.new_val WHEN -1 THEN pos.old_val END)
            ORDER BY pos.column_name
            SEPARATOR ', '
            )
          , ')'
          )
          WHEN p.oper = 'U'
          THEN CONCAT('SET ', GROUP_CONCAT(pos.column_name
            , ' = ', QUOTE(CASE direction WHEN 1 THEN pos.new_val WHEN -1 THEN pos.old_val END)
            ORDER BY pos.column_name
            SEPARATOR ', '
            )
          , ' WHERE id = ', p.table_id
          )
          WHEN p.oper = 'D' AND direction = 1 OR p.oper = 'I' AND direction = -1
          THEN CONCAT('WHERE id = ', p.table_id)
        END
      ) sql_text
    FROM protocol p
    INNER JOIN protocol_pos pos ON p.id = pos.prot_id
    WHERE p.id = p_prot_id
    ;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO p_sql_text;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @exec_protocol_sql_text := p_sql_text;
        SET @disable_all_prot_trg = 1;
        -- SELECT @exec_protocol_sql_text;
        PREPARE c_sql FROM @exec_protocol_sql_text;
        EXECUTE c_sql;
        DEALLOCATE PREPARE c_sql;
        SET @disable_all_prot_trg = NULL;
    END LOOP;
    CLOSE cur;
END$

Процедура set_prot_snapshot_id откатывает/накатывает изменения протокола по диапазону id

set_prot_snapshot_id

DELIMITER $
DROP PROCEDURE IF EXISTS set_prot_snapshot_id$
CREATE PROCEDURE set_prot_snapshot_id(p_beg_prot_id BIGINT, p_end_prot_id BIGINT, direction INT) BEGIN
    DECLARE p_prot_id BIGINT;
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR
      SELECT p.id
      FROM protocol p
      WHERE p.id >= p_beg_prot_id
       AND (p.id <= p_end_prot_id OR p_end_prot_id IS NULL)
      ORDER BY p.id * direction
      ;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO p_prot_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        CALL exec_protocol(p_prot_id, SIGN(direction));
        -- Если direction = -2, то откатываем базу с удалением протокола
        IF direction = -2 THEN
            DELETE FROM protocol WHERE id = p_prot_id;
        END IF;
    END LOOP;
    CLOSE cur;
END$

Процедура set_prot_snapshot_date откатывает/накатывает изменения протокола за период

set_prot_snapshot_date

DELIMITER $
DROP PROCEDURE IF EXISTS set_prot_snapshot_date$
CREATE PROCEDURE set_prot_snapshot_date(p_beg_date TIMESTAMP, p_end_date TIMESTAMP, direction INT) BEGIN
    DECLARE beg_prot_id BIGINT;
    DECLARE end_prot_id BIGINT;
    SET beg_prot_id := (SELECT id FROM protocol WHERE date >= p_beg_date ORDER BY id LIMIT 1);
    SET end_prot_id := (SELECT id FROM protocol WHERE date <= p_end_date ORDER BY id DESC LIMIT 1);
    CALL set_prot_snapshot_id(beg_prot_id, end_prot_id, direction);
END$

Теперь мы можем легко получить отчёт по продажам на прошлую дату:

DELIMITER ;
BEGIN;
CALL set_prot_snapshot_date('2018-10-09 17:23:47', NULL, -1);
SELECT NOW() report_time, d.date, SUM(p.amount * p.price) sum
FROM docs d
INNER JOIN doc_pos p ON d.id = p.doc_id
GROUP BY d.date;
ROLLBACK;

+---------------------+------------+------+
| report_time         | date       | sum  |
+---------------------+------------+------+
| 2018-10-09 17:28:30 | 2018-07-17 | 4030 |
+---------------------+------------+------+

Как видим, отчёт получился именно таким как он был в прошлом.
И т.к. мы сделали ROLLBACK, то сейчас так же легко получим свежий отчёт:

SELECT NOW() report_time, d.date, SUM(p.amount * p.price) sum
FROM docs d
INNER JOIN doc_pos p ON d.id = p.doc_id
GROUP BY d.date;

+---------------------+------------+------+
| report_time         | date       | sum  |
+---------------------+------------+------+
| 2018-10-09 17:29:18 | 2018-07-16 | 2620 |
| 2018-10-09 17:29:18 | 2018-07-18 |  840 |
+---------------------+------------+------+

Какие функциональные возможности может дать протоколирование:

Возможность получать отчёты на любую дату в прошлом, именно такими какими они были в тот момент времени.
Поиск пользователя, который «испортил» данные в БД.
Аналитика истории изменения данных. Например, скорость прохождения документов в системе, изменение статусов.
Отмена изменений. Например, при удалении документа вместо дополнительных вопросов: «Вы действительно хотите удалить?», можно реализовать возможность отмены изменения.
Расширение п.4, корзина, отмена изменений, откат по истории изменений документа.

UPD1: тест производительности

Вставка 10000 строк, 4 поля на строку
1. с триггерами и протоколированием — 6.89c
2. с триггерами, протоколирование отключено программно — 2.17с
3. без триггеров — 1.37c

SQL запросы

DELIMITER $
DROP PROCEDURE IF EXISTS speed_test$
CREATE PROCEDURE speed_test(n INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
        INSERT INTO docs (num, date, warehouse, partner)
        VALUES (CONCAT('тест', i), DATE(NOW()), 'Склад Новосибирск', 'Радуга, ЗАО');
        SET i := i + 1;
    END WHILE;
END$

-- 1. с триггерами и протоколированием
DELIMITER ;
BEGIN;
CALL speed_test(10000);
ROLLBACK;
MariaDB [test-habr]> DELIMITER ;
MariaDB [test-habr]> BEGIN;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test-habr]> CALL speed_test(10000);
Query OK, 1 row affected (6.89 sec)
MariaDB [test-habr]> ROLLBACK;
Query OK, 0 rows affected (0.88 sec)

-- 2. с триггерами, протоколирование отключено программно
DELIMITER ;
BEGIN;
SET @disable_all_prot_trg = 1;
CALL speed_test(10000);
SET @disable_all_prot_trg = NULL;
ROLLBACK;
MariaDB [test-habr]> DELIMITER ;
MariaDB [test-habr]> BEGIN;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test-habr]> SET @disable_all_prot_trg = 1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test-habr]> CALL speed_test(10000);
Query OK, 1 row affected (2.17 sec)
MariaDB [test-habr]> SET @disable_all_prot_trg = NULL;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test-habr]> ROLLBACK;
Query OK, 0 rows affected (0.12 sec)

-- 3. без триггеров
DELIMITER ;
DROP TRIGGER IF EXISTS docs_aft_ins_trg;
BEGIN;
CALL speed_test(10000);
ROLLBACK;
MariaDB [test-habr]> DELIMITER ;
MariaDB [test-habr]> DROP TRIGGER IF EXISTS docs_aft_ins_trg;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test-habr]> BEGIN;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test-habr]> CALL speed_test(10000);
Query OK, 1 row affected (1.37 sec)
MariaDB [test-habr]> ROLLBACK;
Query OK, 0 rows affected (0.12 sec)

UPD2: альтернативные системы

1. System-Versioned Tables появилось в MariaDB 10.3.4
из плюсов: решение нативное, описанное в стандарте SQL:2011, наверняка работает производительнее решения на триггерах
существенный минус — нельзя узнать кто внёс изменения в БД
2. php-audit, решение аналогичное моему на триггерах, триггеры генерятся php
из плюсов: решение красиво оформлено на github
из минусов: наличие отдельной протокольной таблицы для каждой целевой не позволяет, во-первых, легко отследить все действия пользователя за период, во-вторых, не позволяет легко написать скрипты для отката БД в определённое состояние

Комментарии (17)

ianzag
09.10.2018 16:10
#19210409
+2
Есть же System-Versioned Tables
1. asmm Автор
  09.10.2018 16:20
  #19210453
  Любопытно, вроде то что надо, но у меня версия MariaDB 10.0.32
  Притом MariaDB 10.3.4, в которой появился требуемый функционал, вышла относительно недавно, 18.01.2018
  1. Akuma
    09.10.2018 16:39
    #19210529
    Сейчас вообще-то октябрь. Я бы сказал, что она уже старая :)
    
    asmm Автор
    09.10.2018 20:44
    #19211495
    Решение на mysql разработано мной довольно давно, всё никак руки не доходили оформить в статью. А столкнулся с подобным решение на Oracle, 14 лет назад. На этом же протоколе была реализована репликация БД. Реплицировали удалённые точки через дискеты.
    Внимательно посмотрел System-Versioned Tables. В нём можно посмотреть изменения БД, но нельзя узнать кто внёс эти изменения, это существенный минус.

firk
09.10.2018 16:40
#19210533
Почему на триггерах? Потому что нет доступа к бинарному логу.
Ну как всегда, приобретут какой-то шаред пхп хостинг без всего и костылями делают эмуляцию системных возможностей с огромным оверхедом. А потом такие решения кто-то (кто сам вообще ничего не умеет) заимствует себе в продакшн на хорошем хостинге.
1. asmm Автор
  09.10.2018 20:45
  #19211499
  Каких системных возможностей? Где огромный оверхед? Где-то есть готовое решение с бинлогом?

catanfa
09.10.2018 17:34
#19210785
есть более зрелое решение, тоже на триггерах php-audit
1. ianzag
  09.10.2018 18:05
  #19210901
  Выглядит забавно. Впрочем, ограничения конечно присутствуют:
  
  0. Не отслеживается по понятным причинам DDL.
  
  ========
  PhpAudit has the following limitations:
  
  * A TRUNCATE TABLE will remove all rows from a table and does not execute triggers. Hence, the removing of those rows will not be logged in the audit table.
  * A delete or update of a child row caused by a cascaded foreign key action of a parent row will not activate triggers on the child table. Hence, the update or deletion of those rows will not be logged in the audit table.
  ========
  
  С транкейтом понятно. Неприятно, но жить можно. А вот несрабатывание триггеров на каскаде — это уже может быть непреодолимой проблемой. Впрочем — под какие задачи.
  1. asmm Автор
    09.10.2018 21:03
    #19211579
    +1
    TRUNCATE это фактически DDL команда, как если бы вы сделали DROP TABLE, а потом CREATE, отсюда и не срабатывание триггеров.
    Не срабатывание триггеров на удаление FOREIGN KEY это недоработка MySQL'я
    dev.mysql.com/doc/refman/8.0/en/innodb-restrictions.html
    
    Cascaded foreign key actions do not activate triggers.
    
    Решается добавлением в BEFORE DELETE TRIGGER соответствующего
    
    DELETE FROM <child_table> WHERE parent_id = :OLD.id;
    
    ianzag
    09.10.2018 22:23
    #19211931
    +1
    > Решается добавлением в BEFORE DELETE TRIGGER соответствующего
    
    Главное не забывать что нужно не забыть это сделать при добавлении/изменении очередной таблицы :)
    
    asmm Автор
    09.10.2018 22:57
    #19212057
    Ну тут вроде алгоритм ясен:
    1. отказываемся от фраз ON DELETE CASCADE. Фразу FOREIGN KEY ес-но оставляем, целостность БД превыше всего
    2. При прогоне юнит-тестов или получения REFERENCE ошибки от пользователя при удалении записи вспоминаем что нужно вспоминать о триггере на удаление дочерних не значимых записей :)
1. asmm Автор
  09.10.2018 21:08
  #19211613
  +1
  Более зрелое по наличию на github, однако по функционалу у меня плюсом есть функция для работы с откатом протокола ;)

kolu4iy
09.10.2018 22:10
#19211869
Интересно, как оно себя поведет хотя-бы на нормальном потоке операций вставки/изменения данных… Binary log в таком случае делает вставки в один поток и упирается в iops (ну если не предпринять нужных мер). Может проведете тесты? Вставка потоков в 50, операций по 1000 по одной строке? С включенными триггерами и без них...
1. asmm Автор
  09.10.2018 22:51
  #19212027
  В статье Реализация бизнес-логики в MySQL проводил тесты на свои «специальные» триггеры. Со слов «Хочу пояснить несколько моментов по реализации:
  — такой подход вместо нативных триггеров, как в первом варианте даёт некоторый оверхед.»
  Также ведение протокола может быть требованием бизнес-логики. Например, история изменения статуса документа. И тут уже реализация INSERT'а в триггере будет практически идентична реализации INSERT'а вне триггера.
  Ещё нужно понимать, что ведение протокола не требуется для таблиц которые сами по себе являются логом-протоколом и работают только на вставку с большой интенсивностью. Например, данные с различных датчиков, gps, биометрия.
  Протоколирование особенно полезно для Справочников и различных таблиц состояний, типа документов.
  1. kolu4iy
    10.10.2018 09:21
    #19213149
    Я и хотел увидеть «некоторость» оверхеда. Впрочем ладно, сам попробую.
    
    asmm Автор
    10.10.2018 09:53
    #19213255
    Вставка 10000 строк, 4 поля на строку
    1. с триггерами и протоколированием — 6.89c
    2. с триггерами, протоколирование отключено программно — 2.17с
    3. без триггеров — 1.37c
    
    SQL запросы
    DELIMITER $ DROP PROCEDURE IF EXISTS speed_test$ CREATE PROCEDURE speed_test(n INT) BEGIN DECLARE i INT DEFAULT 0; WHILE i < n DO INSERT INTO docs (num, date, warehouse, partner) VALUES (CONCAT('тест', i), DATE(NOW()), 'Склад Новосибирск', 'Радуга, ЗАО'); SET i := i + 1; END WHILE; END$ -- 1. с триггерами и протоколированием DELIMITER ; BEGIN; CALL speed_test(10000); ROLLBACK; MariaDB [test-habr]> DELIMITER ; MariaDB [test-habr]> BEGIN; Query OK, 0 rows affected (0.00 sec) MariaDB [test-habr]> CALL speed_test(10000); Query OK, 1 row affected (6.89 sec) MariaDB [test-habr]> ROLLBACK; Query OK, 0 rows affected (0.88 sec) -- 2. с триггерами, протоколирование отключено программно DELIMITER ; BEGIN; SET @disable_all_prot_trg = 1; CALL speed_test(10000); SET @disable_all_prot_trg = NULL; ROLLBACK; MariaDB [test-habr]> DELIMITER ; MariaDB [test-habr]> BEGIN; Query OK, 0 rows affected (0.00 sec) MariaDB [test-habr]> SET @disable_all_prot_trg = 1; Query OK, 0 rows affected (0.00 sec) MariaDB [test-habr]> CALL speed_test(10000); Query OK, 1 row affected (2.17 sec) MariaDB [test-habr]> SET @disable_all_prot_trg = NULL; Query OK, 0 rows affected (0.00 sec) MariaDB [test-habr]> ROLLBACK; Query OK, 0 rows affected (0.12 sec) -- 3. без триггеров DELIMITER ; DROP TRIGGER IF EXISTS docs_aft_ins_trg; BEGIN; CALL speed_test(10000); ROLLBACK; MariaDB [test-habr]> DELIMITER ; MariaDB [test-habr]> DROP TRIGGER IF EXISTS docs_aft_ins_trg; Query OK, 0 rows affected (0.00 sec) MariaDB [test-habr]> BEGIN; Query OK, 0 rows affected (0.00 sec) MariaDB [test-habr]> CALL speed_test(10000); Query OK, 1 row affected (1.37 sec) MariaDB [test-habr]> ROLLBACK; Query OK, 0 rows affected (0.12 sec)
    
    kolu4iy
    10.10.2018 11:02
    #19213565
    +1
    Вот спасибо, сэкономили всем время :)
    Понятно, что механизм применим не ко всем таблицам, а только к тем, где реально требуется протоколирование. В случае нашей ERP надёжнее оказалось вынести такое протоколирование на уровень приложения, где оно работает вместе с бизнес-логикой. Но на небольшом проекте с большим количеством ручного ввода данных и большой же ответственностью подобная штука работала и у меня. Спасибо.

Как научить MySQL заглядывать в прошлое +11

UPD1: тест производительности

UPD2: альтернативные системы

Комментарии (17)

asmm Автор

asmm Автор

asmm Автор

asmm Автор

asmm Автор

asmm Автор

asmm Автор

asmm Автор