Как создать журнал изменений для таблицы?

Мне нужно создать историю изменений строк таблицы при изменении определенного поля. Итак, я хотел создать триггер для обновления таблицы. Когда поле txta изменяется, я хочу, чтобы вся строка была скопирована в debug, который является клонированной версией msser_210 с добавленным столбцом для datetime в конце, без данных. Я хотел бы добавить NOW() при изменении, чтобы у меня была отметка времени. Вот что я до сих пор пытался:

DELIMITER $$
CREATE TRIGGER history_trigger
BEFORE UPDATE ON msser_210
    FOR EACH ROW
        BEGIN
        IF OLD.txta != NEW.txta
        THEN
            INSERT INTO `debug_history` (`idpm`,`posn`,`prnb`,`doid`,`ofcr`,`pidm`,`hitm`,`sitm`,`item`,`dsca`,`igid`,`kitm`,`leng`,`widt`,`hght`,`thik`,`radi`,`quas`,`wght`,`effc`,`colr`,`bdat`,`edat`,`back`,`cuid`,`intb`,`aggr`,`unqu`,`oqua`,`unsq`,`stoc`,`allo`,`hall`,`tqan`,`bqan`,`pkey`,`pric`,`cvqs`,`unsp`,`disc`,`dart`,`ksid`,`anhg`,`txta`,`txti`,`mndn`, `changedate`) VALUES (OLD.idpm,OLD.posn,OLD.prnb,OLD.doid,OLD.ofcr,OLD.pidm,OLD.hitm,OLD.sitm,OLD.item,OLD.dsca,OLD.igid,OLD.kitm,OLD.leng,OLD.widt,OLD.hght,OLD.thik,OLD.radi,OLD.quas,OLD.wght,OLD.effc,OLD.colr,OLD.bdat,OLD.edat,OLD.back,OLD.cuid,OLD.intb,OLD.aggr,OLD.unqu,OLD.oqua,OLD.unsq,OLD.stoc,OLD.allo,OLD.hall,OLD.tqan,OLD.bqan,OLD.pkey,OLD.pric,OLD.cvqs,OLD.unsp,OLD.disc,OLD.dart,OLD.ksid,OLD.anhg,OLD.txta,OLD.txti, OLD.mndn, NOW());
    END IF;
END;
$$

Почему я хочу это сделать, потому что у нас есть (вероятно) PHP-скрипт с ошибкой, который записывает одну и ту же текстовую строку в каждое поле базы данных, но мы не знаем, когда и почему это происходит, ни какой скрипт это делает. Может есть более элегантное решение?

ОБНОВЛЕНИЕ: я нашел параметр «Отслеживать изменения» в phpMyAdmin, но, по-видимому, он не отслеживает UPDATE запросы наших программ, выдаваемые php, хотя операторы DROP и CREATE TABLE из PHP отслеживаются. Если я отправлю UPDATE через phpMyAdmin, он будет отслеживаться. Короче говоря, я вернулся к своему первоначальному плану с помощью спускового крючка.

UPDATE2: сам нашел ответ


mysql debugging changelog
person siryx    schedule 09.08.2017    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Обновление: согласно комментарию OP явно контекст очень специфичен. Команде инфраструктуры, не имеющей доступа (или возможности обратной связи и управления кодом группы разработчиков), необходим механизм, с помощью которого можно регистрировать изменения таблиц в производственной базе данных.

Предупреждения об использовании триггеров:

Отладка триггеров может быть сложной задачей, не в последнюю очередь из-за того, что они прозрачны, и для новичка в вашем коде никогда не бывает очевидно, что триггер выполняет какое-то действие за кулисами. (Я говорю по собственному опыту.) Они также могут вызывать проблемы на реплицированные, с несколькими главными серверами и кластерные установки. (Опять же, я говорю по опыту.) Также, если они терпят неудачу по какой-то несвязанной причине (например, таблица, в которую они записывают, сломана), вся транзакция может / потерпит неудачу (InnoDB) - что может не быть что вы хотите. (Особенно с несущественными функциями «отладки».)

В противном случае триггеры - совершенно верный инструмент. И в вашем конкретном сценарии, вероятно, лучший вариант, доступный вам.

Вам доступны несколько других вариантов, два из которых я бы выделил:

Хранимые процедуры как уровень доступа к данным

Если вы очень ориентированы на данные и у вас уже есть бизнес-логика внутри базы данных (горячо обсуждаемая тема, я здесь не утверждаю, что у вас должна или не должна быть бизнес-логика в базе данных), тогда чтение и запись в базу данных через хранимые процедуры имеют явное преимущество.

В эти хранимые процедуры можно вставить любую транзакционно связанную логику, чтобы вызывающий транзакционно небезопасный (типичный пример PHP) должен был вызвать только 1 запрос (call sp_insert_tablename(123, 'abc')) и транзакционный безопасность может быть обеспечена базой данных.

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

Уровень / библиотека абстракции данных

Аналогичный принцип. Найдите уровень абстракции данных для своего клиента (при условии, что у вас есть доступ для изменения его внутренних компонентов). Для веб-приложения PHP или .NET существует несколько популярных вариантов, каждый из которых позволяет вам переопределять (расширять посредством наследования кода) операции сохранения / удаления для выполнения любых дополнительных действий, которые вы хотите - точно так же, как для хранимых процедур (но с логикой хранятся внутри моделей в клиенте).

Если вам нужен конкретный пример, вам нужно предоставить нам дополнительную информацию о том, какой стек / язык / фреймворк (ы) вы используете.

С обоими вариантами убедитесь, что вы правильно обрабатываете сценарии ошибок.

person wally    schedule 09.08.2017
comment
Tbh программа - безнадежное дело, и я не ее разработчик, я не могу кодировать php. Я являюсь системным администратором серверов, и мне сказали регистрировать все, что написано в данной базе данных, чтобы разработчики могли отлаживать (нет даже модульных тестов и т. Д.), Так что это хорошо, и все, но я ничего не могу с этим поделать - person siryx; 09.08.2017
comment
Совершенно нормально. Тогда я бы предположил, что мой ответ по-прежнему отвечает на ваш вопрос - в противном случае триггеры - вполне допустимый инструмент. Придерживайтесь решения, которое вы придумали, все будет хорошо. Опыт, который у меня был с триггерами, был связан с тем же сценарием, что и вы - парень, который их туда вставил, был вынужден. В рамках более широкой темы, если бы я был на вашем месте, я бы поднял руку (есть ли у вас формальный процесс для инфраструктуры для разработчиков интерфейсов, например DevOps?) И сказал бы, что меня вынуждают сделать это, что является хорошо, но он нас укусит, и я хочу, чтобы это было зафиксировано. - person wally; 09.08.2017
comment
Я пробовал это, но количество столбцов в новых таблицах отличается, потому что я добавил столбец для измененного состояния и хотел его также установить. - person siryx; 09.08.2017
comment
Это обычная проблема при дублировании данных в таблицах. Вы можете ограничить количество копируемых полей, явно указав подмножество полей, делая это автоматически (как в вашем примере), что приведет к поломке как триггера, так и, возможно, всей транзакции при изменении схемы. Подготовленные операторы могут обойти это (построение запроса с помощью кода), но это действительно будет взлом. - person wally; 09.08.2017
comment
Я нашел решение, при котором мне придется вручную вводить каждое имя, но я не хочу вручную копировать все имена столбцов. Должен быть способ быстрее. Я не возражаю против взлома этого, так как я могу просто сбросить триггер после того, как закончу - person siryx; 09.08.2017

arrow_upward
0
arrow_downward

debug_history клонируется через pypMyAdmin из исходной таблицы. К нему вручную добавлен дополнительный столбец с измененными данными.

ALTER TABLE debug_history ADD COLUMN changedate DATETIME DEFAULT NULL;

Я решил, потому что не было другого способа, чтобы мне самому вводить все имена. Поскольку я ленив, я получил недавний дамп SQL, скопировал INSERT INTO-Statement из файла, который используется для восстановления msser_210, и изменил значения.

Я добавил дополнительную строку со строкой автоинкремента, удалил первичный ключ и установил новый первичный ключ для новой строки.

ALTER TABLE debug_history DROP PRIMARY KEY;
ALTER TABLE debug_history ADD COLUMN changenumber INT NOT NULL PRIMARY KEY AUTO_INCREMENT;

Теперь у меня есть рабочий журнал изменений, запускаемый при изменении в поле txta (см. Вопрос для триггера с исходным форматом). Я переименовал столбец txta в debug_history в txta_old и создал новый столбец txta_new.

ALTER TABLE debug_history CHANGE txta txta_old TEXT NOT NULL $$
ALTER TABLE debug_history ADD COLUMN txta_new TEXT NOT NULL AFTER txta_old $$

Впоследствии мне пришлось изменить триггер, потому что мне пришлось вручную скопировать все имена ..

DROP TRIGGER history_trigger
DELIMITER $$
CREATE TRIGGER history_trigger
BEFORE UPDATE ON msser_210
    FOR EACH ROW
        BEGIN
        IF OLD.txta != NEW.txta
        THEN
            INSERT INTO `debug_history` (`idpm`,`posn`,`prnb`,`doid`,`ofcr`,`pidm`,`hitm`,`sitm`,`item`,`dsca`,`igid`,`kitm`,`leng`,`widt`,`hght`,`thik`,`radi`,`quas`,`wght`,`effc`,`colr`,`bdat`,`edat`,`back`,`cuid`,`intb`,`aggr`,`unqu`,`oqua`,`unsq`,`stoc`,`allo`,`hall`,`tqan`,`bqan`,`pkey`,`pric`,`cvqs`,`unsp`,`disc`,`dart`,`ksid`,`anhg`,`txta_old`,`txta_new`,`txti`,`mndn`, `changedate`) VALUES (OLD.idpm,OLD.posn,OLD.prnb,OLD.doid,OLD.ofcr,OLD.pidm,OLD.hitm,OLD.sitm,OLD.item,OLD.dsca,OLD.igid,OLD.kitm,OLD.leng,OLD.widt,OLD.hght,OLD.thik,OLD.radi,OLD.quas,OLD.wght,OLD.effc,OLD.colr,OLD.bdat,OLD.edat,OLD.back,OLD.cuid,OLD.intb,OLD.aggr,OLD.unqu,OLD.oqua,OLD.unsq,OLD.stoc,OLD.allo,OLD.hall,OLD.tqan,OLD.bqan,OLD.pkey,OLD.pric,OLD.cvqs,OLD.unsp,OLD.disc,OLD.dart,OLD.ksid,OLD.anhg,OLD.txta,NEW.txta,OLD.txti, OLD.mndn, NOW());
        END IF;
    END;
$$
person siryx    schedule 10.08.2017