How to teach MySQL to look into the past

The article focuses on the logging of changes in MySQL . I want to show the implementation of logging on triggers and what amazing things you can do with it.

Why on triggers? Because there is no access to the binary log. An implementation with a binary log is potentially more productive, although more difficult to develop, since Parse log is required.

I just want to warn you that this method will create additional load on the server. And if you have actively changing data, then this solution may not be suitable for you or will require some adjustments and improvements.

In general, the solution is complete and complex. It can be implemented "as is" and perfectly cope with its task.

Everything below is implemented on MariaDB version 10.0.32.
Columns with types are recorded : numbers, lines, dates. The logged table must have a unique NOT NULL numeric id field .

First, create a table with a logging config:

DROPTABLEIFEXISTS protocol_config;
CREATETABLE protocol_config (
idint(11) NOTNULL PRIMARY KEY auto_increment
, command VARCHAR(50) NOTNULL-- Команда
, table_name VARCHAR(50) -- Имя таблицы
, column_name VARCHAR(50) -- Имя колонки
, denormalize_column VARCHAR(50) -- Денормализованная колонка в таблице protocol
, UNIQUE (command, table_name, column_name, denormalize_column)
) DEFAULTCHARSET=utf8 COMMENT='Настройка протоколирования';

All options are applied during the generation of the trigger for logging. Those. when changing settings, it is necessary to regenerate the triggers. Command

field - protocol configuration option:

disable_protocol - turns off logging.
exclude_table - specifies the table to exclude from logging. By default, all BASE TABLE ENGINE = InnoDB are involved in the logging .
For example,
exclude_table protocol
exclude_table protocol_pos
exclude_column - indicates the field to be excluded from logging. For example, a denormalized field supported by triggers.

For example,
exclude_column docs sum
denormalize_column - indicates the column that must be additionally denormalized to the protocol ( protocol table ). By default, all fields are logged in the protocol_pos table .

For example,
denormalize_column docs id doc_id
from the docs table will log the id field into the protocol table in the doc_id column . The doc_id field in the protocol table must be created manually.
denormalize_column doc_pos doc_id doc_id
from the doc_pos table will log the doc_id field to the protocol tablein the doc_id column .

Protocol table:

DROPTABLEIFEXISTS protocol_pos;
DROPTABLEIFEXISTS protocol;
CREATETABLE protocol (
idBIGINTNOTNULL PRIMARY KEY auto_increment
, dateTIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMP-- Дата время операции
, oper VARCHAR(1) NOTNULL-- операция I, U, D
, table_name VARCHAR(50) NOTNULL-- Имя таблица
, table_id BIGINTNOTNULL-- значение поля id в протоколируемой таблице
, username VARCHAR(50) NOTNULL-- имя пользователя приложения или БД
, ip varchar(45) -- IP адрес пользователя
, user_agent varchar(256) -- Браузер
, KEY (table_name, date)
) DEFAULTCHARSET=utf8 COMMENT='Протокол изменений';

Protocol_pos table:

DROPTABLEIFEXISTS protocol_pos;
CREATETABLE protocol_pos (
prot_id BIGINTNOTNULL-- ссылка на protocol.id
, column_name VARCHAR(50) NOTNULL-- имя столбца из протоколируемой таблицы
, old_val VARCHAR(2000) -- старое значение столбца
, new_val VARCHAR(2000) -- новое значение столбца
, PRIMARY KEY (prot_id, column_name)
, FOREIGNKEY (prot_id) REFERENCES protocol(id)
) DEFAULTCHARSET=utf8 COMMENT='Поля протокола изменений';

In the protocol table, we fix the operation, and in the protocol_pos table we enter the changed fields.

Now let's take as a basis the trigger generator from my previous article “Implementing business logic in MySQL” and based on it we will write a generator for logging.

The function gen_bl_trigger of triggering the generation of business logic looks at the presence of the procedure <table_name> _trg_proc

gen_bl_trigger

DELIMITER $
DROPFUNCTIONIFEXISTS gen_bl_trigger$
CREATEFUNCTION gen_bl_trigger(table_name VARCHAR(200), trigger_time VARCHAR(200), trigger_type VARCHAR(200)) RETURNSTEXTBEGINDECLAREtextTEXT;
    DECLARE f_proc INT;
    SET group_concat_max_len = 9000000;
    SET f_proc := (SELECT1FROM 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 '';
    ENDIF;
    SETtext := 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
        -- Создаём временную таблицуSETtext := 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, 'IFNOTEXISTS');
        END IF;
        SET text := CONCAT(text, table_name, '_tmp_trg (');
        SET text := CONCAT(text, '\ntime VARCHAR(1)');
        SET text := CONCAT(text, '\n, typeVARCHAR(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;');
        -- Создаём переменныеSETtext := CONCAT(text, (SELECTGROUP_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 '') textFROM INFORMATION_SCHEMA.COLUMNS C
            WHERE C.TABLE_NAME = table_name
            AND C.TABLE_SCHEMA = DATABASE()
            AND C.COLUMN_TYPE != 'text'
            ));
    ENDIF;
    SETtext := 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, ');');
    SETtext := CONCAT(text, '\nCALL ', table_name, '_trg_proc;');
    IF trigger_time = 'BEFORE' THEN
        SETtext := CONCAT(text
        , IF(trigger_type = 'DELETE'
          , ''
          , (SELECTCONCAT('\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 '')
             ) textFROM INFORMATION_SCHEMA.COLUMNS C
             WHERE C.TABLE_NAME = table_name
              AND C.TABLE_SCHEMA = DATABASE()
              AND C.COLUMN_TYPE != 'text'
            )
          )
        );
        SETtext := CONCAT(text, '\nDELETE FROM ', table_name, '_tmp_trg;');
    ELSE
        SETtext := CONCAT(text, '\nDROP TEMPORARY TABLE ', table_name, '_tmp_trg;');
    ENDIF;
    SETtext := CONCAT(text, '\nEND;');
    RETURN text;
END$

Function gen_prot_trigger generating a logging trigger:

gen_prot_trigger

DELIMITER $
DROPFUNCTIONIFEXISTS gen_prot_trigger$
CREATEFUNCTION gen_prot_trigger(table_name VARCHAR(200), trigger_time VARCHAR(200), trigger_type VARCHAR(200)) RETURNSTEXTBEGINDECLAREtextTEXT;
    DECLARE denormalize_columns TEXT;
    DECLARE denormalize_values TEXT;
    DECLARE f_exclude_table INT;
    SET group_concat_max_len = 9000000;
    -- исключим таблицу если её не надо протоколировать, в ней нет поля id или протоколирование выключеноSET f_exclude_table := (
        SELECTCASEWHEN pd.id ISNOTNULLTHEN1WHEN pc.id ISNOTNULLTHEN1WHEN C.COLUMN_NAME ISNULLTHEN1ENDFROM (SELECTNULLFROM dual) d
        LEFTJOIN protocol_config pd ON pd.command = 'disable_protocol'LEFTJOIN protocol_config pc ON pc.command = 'exclude_table'AND pc.table_name = table_name
        LEFTJOIN 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 '';
    ENDIF;
    SETtext := 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;
ENDIF;'
        );
    ENDIF;
    -- денормализованные поля для таблицы protocolSELECTIFNULL(GROUP_CONCAT(', ', dc.denormalize_column ORDERBY dc.id SEPARATOR ''), '') denormalize_columns
    , IFNULL(GROUP_CONCAT(', '
        , CASE trigger_type WHEN'DELETE'THEN'OLD'ELSE'NEW'END
        , dc.column_name
        ORDERBY dc.id SEPARATOR ', '
      )
      , '') denormalize_values
    INTO denormalize_columns, denormalize_values
    FROM INFORMATION_SCHEMA.COLUMNS C
    INNERJOIN 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()
    ;
    -- Вставляем строчку в протоколSETtext := 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();');
    -- Генерим строчки для вставки полей в позиции протоколаSETtext := 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'
    , (SELECTGROUP_CONCAT('SELECT prot_id, "', C.COLUMN_NAME, '", '
        , CASEWHEN trigger_type = 'UPDATE'THENCONCAT('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'THENCONCAT('NEW.', C.COLUMN_NAME)
           WHEN trigger_type = 'DELETE'THENCONCAT('OLD.', C.COLUMN_NAME)
          END
        SEPARATOR '\nUNION ALL '
        ) textFROM INFORMATION_SCHEMA.COLUMNS C
        LEFTJOIN 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 ISNULL
      )
    , ';\nEND;'
    );
    RETURN text;
END$

The function generate_trigger - business logic + logging:

generate_trigger

DELIMITER $
DROPFUNCTIONIFEXISTS generate_trigger$
CREATEFUNCTION generate_trigger(table_name VARCHAR(200), trigger_time VARCHAR(200), trigger_type VARCHAR(200)) RETURNSTEXTBEGINDECLAREtextTEXT;
    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));
    SETtext := '';
    SETtext := 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;
    ENDIF;
    SETtext := 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$

The generate_triggers function to generate the text of all triggers on the table:

generate_triggers

DELIMITER $
DROPFUNCTIONIFEXISTS generate_triggers$
CREATEFUNCTION generate_triggers(p_table_name VARCHAR(200)) RETURNSTEXTBEGINDECLARE table_name VARCHAR(200);
    DECLAREtextTEXT;
    SET group_concat_max_len = 9000000;
    SET table_name := p_table_name;
    SETtext := '';
    SETtext := (SELECTGROUP_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$

Authorization is described in the article “Implementing Row Level Security on MySQL”

DELIMITER ;
DROPTABLEIFEXISTSusers;
CREATETABLE`users` (
  `id`int(11) NOTNULL AUTO_INCREMENT,
  `email`varchar(100) NOTNULL,
  `pass`varchar(30) NOTNULL,
  PRIMARY KEY (`id`),
  UNIQUEKEY`email` (`email`)
) DEFAULTCHARSET=utf8 COMMENT='Пользователи системы';
DROPTABLEIFEXISTS auth_users;
CREATETABLE`auth_users` (
  `conn_id`bigint(20) NOTNULL,
  `user_id`int(11) NOTNULL,
  `login_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMP,
  `ip`varchar(45) DEFAULTNULL,
  `user_agent`varchar(256) DEFAULTNULL,
  PRIMARY KEY (`conn_id`)
  -- , FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=MEMORYDEFAULTCHARSET=utf8 COMMENT='Авторизованные пользователи';

Now create a couple of test tables:

DROPTABLEIFEXISTS doc_pos;
DROPTABLEIFEXISTS docs;
CREATETABLE`docs` (
  `id`int(11) NOTNULL AUTO_INCREMENT,
  `num`VARCHAR(20) NOTNULL,
  `date`DATENOTNULL,
  `warehouse`VARCHAR(100) NOTNULL,
  `partner`VARCHAR(100) NOTNULL,
  PRIMARY KEY (`id`)
) DEFAULTCHARSET=utf8 COMMENT='Документы';
DROPTABLEIFEXISTS doc_pos;
CREATETABLE`doc_pos` (
  `id`int(11) NOTNULL AUTO_INCREMENT,
  `doc_id`int(11) NOTNULL,
  `material`VARCHAR(100) NOTNULL,
  `amount`int(11) NOTNULL,
  `price`int(11) NOTNULL,
  PRIMARY KEY (`id`)
  , FOREIGNKEY (doc_id) REFERENCES docs(id)
) DEFAULTCHARSET=utf8 COMMENT='Позиции документов';

Perform a query to check the correctness of triggers in the database:

Request to control the correctness of triggers in the database

SELECT table_name, comment, rows_cn, data_len_mb
, MAX(need_bl_trg) need_bl_trg
, MAX(exclude_prot) exclude_prot
, MAX(CASEWHEN action_statement != gen_trg THENCONCAT('SELECT generate_triggers("', table_name, '")')
    WHEN gen_trg ISNULLAND (exclude_prot ISNULLOR need_bl_trg = 1) THENCONCAT('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
, CASEWHEN r.ROUTINE_NAME ISNOTNULLTHEN1END need_bl_trg
, CASEWHEN pd.id ISNOTNULLTHEN'Протокол выключен'WHEN pc.id ISNOTNULLTHEN'Исключено настройкой'WHEN t.TABLE_NAME IN ('protocol', 'protocol_pos') THEN'Не протоколируется'WHEN C.COLUMN_NAME ISNULLTHEN'Нет поля 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
LEFTJOIN 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
LEFTJOIN protocol_config pd ON pd.command = 'disable_protocol'LEFTJOIN protocol_config pc ON pc.command = 'exclude_table'AND pc.table_name = t.TABLE_NAME
LEFTJOIN INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_SCHEMA = t.TABLE_SCHEMA
 AND C.TABLE_NAME = t.TABLE_NAME AND C.COLUMN_NAME = 'id'LEFTJOIN 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
GROUPBY table_name, comment, rows_cn, data_len_mb
ORDERBY table_name
;

+ --------------- + -------------------------- + ------ - + ----------- + ----------- + ------------------ + ----- -------------------------------------- +
| table_name | comment | rows_cn | data_len_mb | need_bl_trg | exclude_prot | create_trg |
+ --------------- + -------------------------- + ------ - + ----------- + ----------- + ------------------ + ----- -------------------------------------- +
| docs | Documents | 0 | 0.02 | NULL | NULL | SELECT generate_triggers ("docs") |
| doc_pos | Positions of documents | 0 | 0.02 | NULL | NULL | SELECT generate_triggers ("doc_pos") |
| protocol | Change Protocol | 0 | 0.02 | NULL | Not logged | NULL |
| protocol_config | Setting up logging | 0 | 0.02 | NULL | NULL | SELECT generate_triggers ("protocol_config") |
| protocol_pos | Change Log Fields | 0 | 0.02 | NULL | Not logged | NULL |
| users | System Users | 0 | 0.02 | NULL | NULL | SELECT generate_triggers ("users") |
+ --------------- + -------------------------- + ------ - + ----------- + ----------- + ------------------ + ----- -------------------------------------- +

The system suggests us to create logging triggers on the docs, doc_pos, protocol_config and users

tables. We will wrap the previous SELECT query and execute it again:

Request to control the correctness of triggers in the database

SELECTGROUP_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(CASEWHEN action_statement != gen_trg THENCONCAT('SELECT generate_triggers("', table_name, '")')
    WHEN gen_trg ISNULLAND (exclude_prot ISNULLOR need_bl_trg = 1) THENCONCAT('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
, CASEWHEN r.ROUTINE_NAME ISNOTNULLTHEN1END need_bl_trg
, CASEWHEN pd.id ISNOTNULLTHEN'Протокол выключен'WHEN pc.id ISNOTNULLTHEN'Исключено настройкой'WHEN t.TABLE_NAME IN ('protocol', 'protocol_pos') THEN'Не протоколируется'WHEN C.COLUMN_NAME ISNULLTHEN'Нет поля 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
LEFTJOIN 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
LEFTJOIN protocol_config pd ON pd.command = 'disable_protocol'LEFTJOIN protocol_config pc ON pc.command = 'exclude_table'AND pc.table_name = t.TABLE_NAME
LEFTJOIN INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_SCHEMA = t.TABLE_SCHEMA
 AND C.TABLE_NAME = t.TABLE_NAME AND C.COLUMN_NAME = 'id'LEFTJOIN 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
GROUPBY table_name, comment, rows_cn, data_len_mb
ORDERBY table_name
) d
;

Result:

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

Now execute this query:

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

DROPTRIGGERIFEXISTS docs_bef_ins_trg$
DROPTRIGGERIFEXISTS docs_aft_ins_trg$
CREATETRIGGER docs_aft_ins_trg AFTERINSERTON docs FOREACHROW
trg_proc:BEGINIF @disable_docs_trg = 1OR @disable_all_trg = 1THEN
LEAVE trg_proc;
ENDIF;
prot_proc: BEGINDECLARE prot_id INT;
IF @disable_docs_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
ENDIF;
INSERTINTO protocol (username, oper, table_name, table_id, ip, user_agent)
SELECTIFNULL(u.email, USER()) username, "I", "docs", NEW.id, au.ip, au.user_agent
FROM (SELECTNULLFROM dual) d
LEFTJOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFTJOINusers u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();
INSERTINTO 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$
DROPTRIGGERIFEXISTS docs_bef_upd_trg$
DROPTRIGGERIFEXISTS docs_aft_upd_trg$
CREATETRIGGER docs_aft_upd_trg AFTERUPDATEON docs FOREACHROW
trg_proc:BEGINIF @disable_docs_trg = 1OR @disable_all_trg = 1THEN
LEAVE trg_proc;
ENDIF;
prot_proc: BEGINDECLARE prot_id INT;
IF @disable_docs_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
ENDIF;
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;
ENDIF;
INSERTINTO protocol (username, oper, table_name, table_id, ip, user_agent)
SELECTIFNULL(u.email, USER()) username, "U", "docs", NEW.id, au.ip, au.user_agent
FROM (SELECTNULLFROM dual) d
LEFTJOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFTJOINusers u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();
INSERTINTO protocol_pos (prot_id, column_name, old_val, new_val)
SELECT prot_id, "id", OLD.id, NEW.id FROM dual WHEREIFNULL(NEW.id, "-ЪъЪ") != IFNULL(OLD.id, "-ЪъЪ")
UNION ALL SELECT prot_id, "num", OLD.num, NEW.num FROM dual WHEREIFNULL(NEW.num, "-ЪъЪ") != IFNULL(OLD.num, "-ЪъЪ")
UNION ALL SELECT prot_id, "date", OLD.date, NEW.date FROM dual WHEREIFNULL(NEW.date, "-ЪъЪ") != IFNULL(OLD.date, "-ЪъЪ")
UNION ALL SELECT prot_id, "warehouse", OLD.warehouse, NEW.warehouse FROM dual WHEREIFNULL(NEW.warehouse, "-ЪъЪ") != IFNULL(OLD.warehouse, "-ЪъЪ")
UNION ALL SELECT prot_id, "partner", OLD.partner, NEW.partner FROM dual WHEREIFNULL(NEW.partner, "-ЪъЪ") != IFNULL(OLD.partner, "-ЪъЪ");
END;
END$
DROPTRIGGERIFEXISTS docs_bef_del_trg$
DROPTRIGGERIFEXISTS docs_aft_del_trg$
CREATETRIGGER docs_aft_del_trg AFTERDELETEON docs FOREACHROW
trg_proc:BEGINIF @disable_docs_trg = 1OR @disable_all_trg = 1THEN
LEAVE trg_proc;
ENDIF;
prot_proc: BEGINDECLARE prot_id INT;
IF @disable_docs_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
ENDIF;
INSERTINTO protocol (username, oper, table_name, table_id, ip, user_agent)
SELECTIFNULL(u.email, USER()) username, "D", "docs", OLD.id, au.ip, au.user_agent
FROM (SELECTNULLFROM dual) d
LEFTJOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFTJOINusers u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();
INSERTINTO 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$
DROPTRIGGERIFEXISTS users_bef_ins_trg$
DROPTRIGGERIFEXISTS users_aft_ins_trg$
CREATETRIGGER users_aft_ins_trg AFTERINSERTONusersFOREACHROW
trg_proc:BEGINIF @disable_users_trg = 1OR @disable_all_trg = 1THEN
LEAVE trg_proc;
ENDIF;
prot_proc: BEGINDECLARE prot_id INT;
IF @disable_users_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
ENDIF;
INSERTINTO protocol (username, oper, table_name, table_id, ip, user_agent)
SELECTIFNULL(u.email, USER()) username, "I", "users", NEW.id, au.ip, au.user_agent
FROM (SELECTNULLFROM dual) d
LEFTJOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFTJOINusers u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();
INSERTINTO 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$
DROPTRIGGERIFEXISTS users_bef_upd_trg$
DROPTRIGGERIFEXISTS users_aft_upd_trg$
CREATETRIGGER users_aft_upd_trg AFTERUPDATEONusersFOREACHROW
trg_proc:BEGINIF @disable_users_trg = 1OR @disable_all_trg = 1THEN
LEAVE trg_proc;
ENDIF;
prot_proc: BEGINDECLARE prot_id INT;
IF @disable_users_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
ENDIF;
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;
ENDIF;
INSERTINTO protocol (username, oper, table_name, table_id, ip, user_agent)
SELECTIFNULL(u.email, USER()) username, "U", "users", NEW.id, au.ip, au.user_agent
FROM (SELECTNULLFROM dual) d
LEFTJOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFTJOINusers u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();
INSERTINTO protocol_pos (prot_id, column_name, old_val, new_val)
SELECT prot_id, "id", OLD.id, NEW.id FROM dual WHEREIFNULL(NEW.id, "-ЪъЪ") != IFNULL(OLD.id, "-ЪъЪ")
UNION ALL SELECT prot_id, "email", OLD.email, NEW.email FROM dual WHEREIFNULL(NEW.email, "-ЪъЪ") != IFNULL(OLD.email, "-ЪъЪ")
UNION ALL SELECT prot_id, "pass", OLD.pass, NEW.pass FROM dual WHEREIFNULL(NEW.pass, "-ЪъЪ") != IFNULL(OLD.pass, "-ЪъЪ");
END;
END$
DROPTRIGGERIFEXISTS users_bef_del_trg$
DROPTRIGGERIFEXISTS users_aft_del_trg$
CREATETRIGGER users_aft_del_trg AFTERDELETEONusersFOREACHROW
trg_proc:BEGINIF @disable_users_trg = 1OR @disable_all_trg = 1THEN
LEAVE trg_proc;
ENDIF;
prot_proc: BEGINDECLARE prot_id INT;
IF @disable_users_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
ENDIF;
INSERTINTO protocol (username, oper, table_name, table_id, ip, user_agent)
SELECTIFNULL(u.email, USER()) username, "D", "users", OLD.id, au.ip, au.user_agent
FROM (SELECTNULLFROM dual) d
LEFTJOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFTJOINusers u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();
INSERTINTO 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$
DROPTRIGGERIFEXISTS doc_pos_bef_ins_trg$
DROPTRIGGERIFEXISTS doc_pos_aft_ins_trg$
CREATETRIGGER doc_pos_aft_ins_trg AFTERINSERTON doc_pos FOREACHROW
trg_proc:BEGINIF @disable_doc_pos_trg = 1OR @disable_all_trg = 1THEN
LEAVE trg_proc;
ENDIF;
prot_proc: BEGINDECLARE prot_id INT;
IF @disable_doc_pos_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
ENDIF;
INSERTINTO protocol (username, oper, table_name, table_id, ip, user_agent)
SELECTIFNULL(u.email, USER()) username, "I", "doc_pos", NEW.id, au.ip, au.user_agent
FROM (SELECTNULLFROM dual) d
LEFTJOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFTJOINusers u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();
INSERTINTO 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$
DROPTRIGGERIFEXISTS doc_pos_bef_upd_trg$
DROPTRIGGERIFEXISTS doc_pos_aft_upd_trg$
CREATETRIGGER doc_pos_aft_upd_trg AFTERUPDATEON doc_pos FOREACHROW
trg_proc:BEGINIF @disable_doc_pos_trg = 1OR @disable_all_trg = 1THEN
LEAVE trg_proc;
ENDIF;
prot_proc: BEGINDECLARE prot_id INT;
IF @disable_doc_pos_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
ENDIF;
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;
ENDIF;
INSERTINTO protocol (username, oper, table_name, table_id, ip, user_agent)
SELECTIFNULL(u.email, USER()) username, "U", "doc_pos", NEW.id, au.ip, au.user_agent
FROM (SELECTNULLFROM dual) d
LEFTJOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFTJOINusers u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();
INSERTINTO protocol_pos (prot_id, column_name, old_val, new_val)
SELECT prot_id, "id", OLD.id, NEW.id FROM dual WHEREIFNULL(NEW.id, "-ЪъЪ") != IFNULL(OLD.id, "-ЪъЪ")
UNION ALL SELECT prot_id, "doc_id", OLD.doc_id, NEW.doc_id FROM dual WHEREIFNULL(NEW.doc_id, "-ЪъЪ") != IFNULL(OLD.doc_id, "-ЪъЪ")
UNION ALL SELECT prot_id, "material", OLD.material, NEW.material FROM dual WHEREIFNULL(NEW.material, "-ЪъЪ") != IFNULL(OLD.material, "-ЪъЪ")
UNION ALL SELECT prot_id, "amount", OLD.amount, NEW.amount FROM dual WHEREIFNULL(NEW.amount, "-ЪъЪ") != IFNULL(OLD.amount, "-ЪъЪ")
UNION ALL SELECT prot_id, "price", OLD.price, NEW.price FROM dual WHEREIFNULL(NEW.price, "-ЪъЪ") != IFNULL(OLD.price, "-ЪъЪ");
END;
END$
DROPTRIGGERIFEXISTS doc_pos_bef_del_trg$
DROPTRIGGERIFEXISTS doc_pos_aft_del_trg$
CREATETRIGGER doc_pos_aft_del_trg AFTERDELETEON doc_pos FOREACHROW
trg_proc:BEGINIF @disable_doc_pos_trg = 1OR @disable_all_trg = 1THEN
LEAVE trg_proc;
ENDIF;
prot_proc: BEGINDECLARE prot_id INT;
IF @disable_doc_pos_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
ENDIF;
INSERTINTO protocol (username, oper, table_name, table_id, ip, user_agent)
SELECTIFNULL(u.email, USER()) username, "D", "doc_pos", OLD.id, au.ip, au.user_agent
FROM (SELECTNULLFROM dual) d
LEFTJOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFTJOINusers u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();
INSERTINTO 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$
DROPTRIGGERIFEXISTS protocol_config_bef_ins_trg$
DROPTRIGGERIFEXISTS protocol_config_aft_ins_trg$
CREATETRIGGER protocol_config_aft_ins_trg AFTERINSERTON protocol_config FOREACHROW
trg_proc:BEGINIF @disable_protocol_config_trg = 1OR @disable_all_trg = 1THEN
LEAVE trg_proc;
ENDIF;
prot_proc: BEGINDECLARE prot_id INT;
IF @disable_protocol_config_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
ENDIF;
INSERTINTO protocol (username, oper, table_name, table_id, ip, user_agent)
SELECTIFNULL(u.email, USER()) username, "I", "protocol_config", NEW.id, au.ip, au.user_agent
FROM (SELECTNULLFROM dual) d
LEFTJOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFTJOINusers u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();
INSERTINTO 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$
DROPTRIGGERIFEXISTS protocol_config_bef_upd_trg$
DROPTRIGGERIFEXISTS protocol_config_aft_upd_trg$
CREATETRIGGER protocol_config_aft_upd_trg AFTERUPDATEON protocol_config FOREACHROW
trg_proc:BEGINIF @disable_protocol_config_trg = 1OR @disable_all_trg = 1THEN
LEAVE trg_proc;
ENDIF;
prot_proc: BEGINDECLARE prot_id INT;
IF @disable_protocol_config_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
ENDIF;
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;
ENDIF;
INSERTINTO protocol (username, oper, table_name, table_id, ip, user_agent)
SELECTIFNULL(u.email, USER()) username, "U", "protocol_config", NEW.id, au.ip, au.user_agent
FROM (SELECTNULLFROM dual) d
LEFTJOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFTJOINusers u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();
INSERTINTO protocol_pos (prot_id, column_name, old_val, new_val)
SELECT prot_id, "id", OLD.id, NEW.id FROM dual WHEREIFNULL(NEW.id, "-ЪъЪ") != IFNULL(OLD.id, "-ЪъЪ")
UNION ALL SELECT prot_id, "command", OLD.command, NEW.command FROM dual WHEREIFNULL(NEW.command, "-ЪъЪ") != IFNULL(OLD.command, "-ЪъЪ")
UNION ALL SELECT prot_id, "table_name", OLD.table_name, NEW.table_name FROM dual WHEREIFNULL(NEW.table_name, "-ЪъЪ") != IFNULL(OLD.table_name, "-ЪъЪ")
UNION ALL SELECT prot_id, "column_name", OLD.column_name, NEW.column_name FROM dual WHEREIFNULL(NEW.column_name, "-ЪъЪ") != IFNULL(OLD.column_name, "-ЪъЪ")
UNION ALL SELECT prot_id, "denormalize_column", OLD.denormalize_column, NEW.denormalize_column FROM dual WHEREIFNULL(NEW.denormalize_column, "-ЪъЪ") != IFNULL(OLD.denormalize_column, "-ЪъЪ");
END;
END$
DROPTRIGGERIFEXISTS protocol_config_bef_del_trg$
DROPTRIGGERIFEXISTS protocol_config_aft_del_trg$
CREATETRIGGER protocol_config_aft_del_trg AFTERDELETEON protocol_config FOREACHROW
trg_proc:BEGINIF @disable_protocol_config_trg = 1OR @disable_all_trg = 1THEN
LEAVE trg_proc;
ENDIF;
prot_proc: BEGINDECLARE prot_id INT;
IF @disable_protocol_config_prot_trg = 1 OR @disable_all_prot_trg = 1 THEN
LEAVE prot_proc;
ENDIF;
INSERTINTO protocol (username, oper, table_name, table_id, ip, user_agent)
SELECTIFNULL(u.email, USER()) username, "D", "protocol_config", OLD.id, au.ip, au.user_agent
FROM (SELECTNULLFROM dual) d
LEFTJOIN auth_users au ON au.conn_id = CONNECTION_ID()
LEFTJOINusers u ON u.id = au.user_id;
SET prot_id := LAST_INSERT_ID();
INSERTINTO 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$

We got the text of the triggers and execute it (with DELIMITER $ )

Now our tables are logged and all changes to the data are recorded in the protocol.
Check the logging settings with the first request:

Request to control the correctness of triggers in the database

DELIMITER ;
SELECT table_name, comment, rows_cn, data_len_mb
, MAX(need_bl_trg) need_bl_trg
, MAX(exclude_prot) exclude_prot
, MAX(CASEWHEN action_statement != gen_trg THENCONCAT('SELECT generate_triggers("', table_name, '")')
    WHEN gen_trg ISNULLAND (exclude_prot ISNULLOR need_bl_trg = 1) THENCONCAT('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
, CASEWHEN r.ROUTINE_NAME ISNOTNULLTHEN1END need_bl_trg
, CASEWHEN pd.id ISNOTNULLTHEN'Протокол выключен'WHEN pc.id ISNOTNULLTHEN'Исключено настройкой'WHEN t.TABLE_NAME IN ('protocol', 'protocol_pos') THEN'Не протоколируется'WHEN C.COLUMN_NAME ISNULLTHEN'Нет поля 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
LEFTJOIN 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
LEFTJOIN protocol_config pd ON pd.command = 'disable_protocol'LEFTJOIN protocol_config pc ON pc.command = 'exclude_table'AND pc.table_name = t.TABLE_NAME
LEFTJOIN INFORMATION_SCHEMA.COLUMNS C ON C.TABLE_SCHEMA = t.TABLE_SCHEMA
 AND C.TABLE_NAME = t.TABLE_NAME AND C.COLUMN_NAME = 'id'LEFTJOIN 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
GROUPBY table_name, comment, rows_cn, data_len_mb
ORDERBY table_name
;

+ --------------- + -------------------------- + ------ - + ----------- + ----------- + ------------------ + ----- ----- +
| table_name | comment | rows_cn | data_len_mb | need_bl_trg | exclude_prot | create_trg |
+ --------------- + -------------------------- + ------ - + ----------- + ----------- + ------------------ + ----- ----- +
| docs | Documents | 0 | 0.02 | NULL | NULL | NULL |
| doc_pos | Positions of documents | 0 | 0.02 | NULL | NULL | NULL |
| protocol | Change Protocol | 0 | 0.02 | NULL | Not logged | NULL |
| protocol_config | Setting up logging | 0 | 0.02 | NULL | NULL | NULL |
| protocol_pos | Change Log Fields | 0 | 0.02 | NULL | Not logged | NULL |
| users | System Users | 0 | 0.02 | NULL | NULL | NULL |
+ --------------- + -------------------------- + ------ - + ----------- + ----------- + ------------------ + ----- ----- +
6 rows in set, 0 warnings (5.33 sec)

Add a user and log in:

/*
DELETE FROM doc_pos;
DELETE FROM docs;
DELETE FROM auth_users;
DELETE FROM users;
DELETE FROM protocol_pos;
DELETE FROM protocol;
*/INSERTINTOusers (email, pass) VALUES ('test@test.ru', '12345');
Query OK, 1 row affected (0.01 sec)
INSERTINTO auth_users (conn_id, user_id)
SELECT CONNECTION_ID() conn_id
, (SELECT u.id FROMusers u WHERE u.email = 'test@test.ru') user_id
;
Query OK, 1 row affected (0.00 sec)

Insert a test document:

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

Let's see what we got in the protocol:

SELECTid, date, oper, table_name, table_id
, (SELECTGROUP_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;

Request for html

SELECTid, date, oper, table_name, table_id
, (SELECTCONCAT('<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, Horizon, LLC), warehouse: (NULL, Warehouse Moscow) | 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, Watercolor paints), 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, Acrylic paints), 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, Ballpoint pen), price: (NULL, 30) | test@test.ru |
+ ---- + --------------------- + ------ + ------------ + - -------- + ----------------------------------------- -------------------------------------------------- ------------------------------ + ------------------- +

As you can see, all database changes are logged, starting from the user's insertion.

Get a sales report:

SELECTNOW() report_time, d.date, SUM(p.amount * p.price) sumFROM docs d
INNERJOIN doc_pos p ON d.id = p.doc_id
GROUPBY d.date;

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

Now we will change the existing document and add another one:

BEGIN;
SET @doc_id := (SELECTidFROM docs WHEREnum = '1');
UPDATE docs SETdate = '2018-07-16', warehouse = warehouse WHEREid = @doc_id;
DELETEFROM 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 = 'Краски акриловые';
INSERTINTO docs (num, date, warehouse, partner)
VALUES ('2', '2018-07-18', 'Склад Новосибирск', 'Радуга, ЗАО');
SET @doc_id := LAST_INSERT_ID();
INSERTINTO doc_pos (doc_id, material, amount, price)
VALUES (@doc_id, 'Рамка 10*15', 5, 102)
, (@doc_id, 'Бумага А4', 2, 165);
COMMIT;

This is what the new protocol will look like.

SELECTid, date, oper, table_name, table_id
, (SELECTGROUP_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      |
+----+---------------------+------+------------+----------+----------------------------------------------------------------------------------------------------------------------------+-------------------+

Get a new report:

SELECTNOW() report_time, d.date, SUM(p.amount * p.price) sumFROM docs d
INNERJOIN doc_pos p ON d.id = p.doc_id
GROUPBY 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 |
+ --------------------- + ------------ + ------ +

We look at the report and cannot find the data for 2018-07-17 number, although we do remember that they were, we even have a printed report in 2018-10-09 17:23:47

Let's teach MySQL to look into the past! To do this, we w

How to teach MySQL to look into the past

Also popular now: