Форум пользователей MySQL

animegirl · 27.08.2014 10:55:12

Как радостно начиналось, как печально зашла в тупик (

У меня у каждой таблицы, есть её копия для логирования, если в таблице происходят изменения, то в логирующую для начала перед изменением сносится предыдущий вариант. Всё было прекрасно, пока я не решила перевязать все таблицы внешними ключами по логике приложения. Внесла смысл в хаос, но радость длилась не долго, так как большинство действий по внешним ключам были наследственными. Поняла, что теряется логирование, что раньше было всегда при изменениях прописано в ПХП скриптах. Ну хорошо, задумалась над автоматизацией, изучила феномен триггеров. Обрадовалась. Накидала на таблицы триггеров для логирования во вторую таблице ПЕРЕД вносом изменений. А теперь тестируя наткнулась на печальную реальность. Когда в таблице А удаляется/изменяется строка, к которой привязана строка из таблицы Б, триггер таблицы А делает копию строки в таблицу А_лог, внешний ключ как и прописано наследует изменения и производит их в таблице Б, но триггер таблицы Б игнорирует эти изменения и ничего не оставляет в таблице Б_лог ..... это лечится?

animegirl · 27.08.2014 15:39:10

Перелопатила интернет и выяснила, что это проблема висит в багах разработчиков аж с 2005-го года. Исходя из этого пришла к выводу, что имеет смысл заменить внешние ключи триггерами, которые их имитируют. А вот последние примером найти не смогла . Ведь по сути это схоже с транзакциями, то есть нужно прописывать откаты и всё это в процедурном виде. Не совсем в этом понимаю пока ещё. Если есть уже готовые решения и знаете ссылочку на них, буду рада и благодарна.

rgbeast · 28.08.2014 20:29:59

Триггеры в MySQL не срабатывают при апдейте по внешнему ключу. Но можно повесить триггер на запрос UPDATE, который вызывает такое изменение.

animegirl · 29.08.2014 14:31:15

rgbeast написал:
Триггеры в MySQL не срабатывают при апдейте по внешнему ключу. Но можно повесить триггер на запрос UPDATE, который вызывает такое изменение.

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

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

rgbeast · 29.08.2014 17:22:29

Проблем не будет, если одинаковые триггеры есть и на мастере и на слейве. mixed значит, что для каждого запроса это или row или statement. Если row, то триггеры на слейве не будут срабатывать, на него просто попадут все изменения, уже сделанные триггерами на мастере. Если statement, то триггеры отработают на слейве.

http://dev.mysql.com/doc/refman/5.1/en/ … ggers.html

animegirl · 31.08.2014 14:38:34

rgbeast написал:
Проблем не будет, если одинаковые триггеры есть и на мастере и на слейве. mixed значит, что для каждого запроса это или row или statement. Если row, то триггеры на слейве не будут срабатывать, на него просто попадут все изменения, уже сделанные триггерами на мастере. Если statement, то триггеры отработают на слейве.

http://dev.mysql.com/doc/refman/5.1/en/ … ggers.html

Мда, решила аккуратно протестировать на сервере на тестовых таблицах - обвалила реплику.
Таблицы:

-- --------------------------------------------------------

--

-- Структура таблицы `test_a`

--

CREATE TABLE IF NOT EXISTS `test_a` (

  `uid` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `check` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL,

  PRIMARY KEY (`uid`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=2 ;

--

-- Дамп данных таблицы `test_a`

--

INSERT INTO `test_a` (`uid`, `check`) VALUES

(1, 'c');

--

-- Триггеры `test_a`

--

DROP TRIGGER IF EXISTS `test_a`;

DELIMITER //

CREATE TRIGGER `test_a` BEFORE UPDATE ON `test_a`

 FOR EACH ROW BEGIN

        INSERT INTO `test_b` (`uid`,`check`)

            VALUES (`OLD`.`uid`,`OLD`.`check`);

    END

//

DELIMITER ;

-- --------------------------------------------------------

--

-- Структура таблицы `test_b`

--

CREATE TABLE IF NOT EXISTS `test_b` (

  `uid` int(10) unsigned NOT NULL,

  `check` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL,

  KEY `uid` (`uid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--

-- Дамп данных таблицы `test_b`

--

INSERT INTO `test_b` (`uid`, `check`) VALUES

(1, 'a1'),

(1, 'b');

--

-- Триггеры `test_b`

--

DROP TRIGGER IF EXISTS `test_b`;

DELIMITER //

CREATE TRIGGER `test_b` BEFORE UPDATE ON `test_b`

 FOR EACH ROW BEGIN

        INSERT INTO `test_c` (`uid`,`check`)

            VALUES (`OLD`.`uid`,`OLD`.`check`);

    END

//

DELIMITER ;

-- --------------------------------------------------------

--

-- Структура таблицы `test_c`

--

CREATE TABLE IF NOT EXISTS `test_c` (

  `uid` int(10) unsigned NOT NULL,

  `check` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL,

  KEY `uid` (`uid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--

-- Дамп данных таблицы `test_c`

--

INSERT INTO `test_c` (`uid`, `check`) VALUES

(1, 'a');

А вот какая ошибка весит на реплике сейчас:
Slave_IO_Running Yes
Slave_SQL_Running No
Last_Error Error 'The user specified as a definer ('root'@'%') does not exist' on query. Default database: 'dbase'. Query: 'UPDATE `dbase`.`test_a` SET `check` = 'd' WHERE `test_a`.`uid` = 1'

Как мне теперь этот косяк исправлять?

rgbeast · 01.09.2014 03:25:33

Пользователь, который является создателем триггера root@% не существует. Убедитесь, что пользователи также одинаковые на мастере и слейве.

animegirl · 01.09.2014 11:08:56

Спасибо, не ожидала такого момента. Хммм, но это не есть хорошо. Так как юзера 'root'@'%' я хотела прикрыть, он не вызывает у меня особого доверия.

animegirl · 01.09.2014 12:48:50

rgbeast написал:
Проблем не будет, если одинаковые триггеры есть и на мастере и на слейве. mixed значит, что для каждого запроса это или row или statement. Если row, то триггеры на слейве не будут срабатывать, на него просто попадут все изменения, уже сделанные триггерами на мастере. Если statement, то триггеры отработают на слейве.

http://dev.mysql.com/doc/refman/5.1/en/ … ggers.html

Эксперимент прошёл вроде как удачно. Но вот этот момент меня всё ещё немного тревожит. И так, ещё раз для закрепления:
Если на мастере сработает триггер, то данные в логфайл запишутся как row-based replication и на слэйве триггеры не сработают - верно?
Если на мастере не сработает триггер, то данные в логфайл запишутся как statement-based replication и в случае если триггеры и там и там идентичны, то на слэйве, тоже ничего не сработает - верно?

rgbeast · 01.09.2014 13:25:36

См. SHOW CREATE TRIGGER
http://dev.mysql.com/doc/refman/5.1/en/ … igger.html

У каждого триггера, функции или процедуры есть создатель (definer). Он должен присутствовать на обоих машинах. Это не обязательно root.

Правильнее так. MySQL решит какую репликацию использовать.
1. Если это row, то на слейве триггер не срабатывает, но все изменения передаются на слейв.
2. Если это statement, то реплицируется запросы, на слейве срабатывают триггеры.

animegirl · 01.09.2014 13:36:22

rgbeast написал:
См. SHOW CREATE TRIGGER
http://dev.mysql.com/doc/refman/5.1/en/ … igger.html

У каждого триггера, функции или процедуры есть создатель (definer). Он должен присутствовать на обоих машинах. Это не обязательно root.

Правильнее так. MySQL решит какую репликацию использовать.
1. Если это row, то на слейве триггер не срабатывает, но все изменения передаются на слейв.
2. Если это statement, то реплицируется запросы, на слейве срабатывают триггеры.

2. Так, а существует вероятность того, что MySQL решит передать репликацию, как statement, тем самым выйдет двойная запись, из-за двойного срабатывания триггера?

rgbeast · 01.09.2014 13:41:52

Может передать как statement. Statement выполнится на мастере и слейве. На мастере и слейве независимо сработает триггер, но результат триггера на мастере не будет реплицирован, поэтому двойного срабатывания не будет.

animegirl · 01.09.2014 16:57:11

rgbeast написал:
У каждого триггера, функции или процедуры есть создатель (definer). Он должен присутствовать на обоих машинах. Это не обязательно root.

А пароли тоже должны совпадать или там только имя важно? (Кстати зачем это сделано?)

rgbeast · 01.09.2014 17:02:37

Пароли не обязаны совпадать. Функция по-умолчанию распоряжается базой с правами своего создателя. Это нужно, так как функция может иметь больше привилегий, чем тот, кто ее вызывает.

Форум пользователей MySQL

#1 27.08.2014 10:55:12

Внешний ключ, наследственность, триггер

#2 27.08.2014 15:39:10

Re: Внешний ключ, наследственность, триггер

#3 28.08.2014 20:29:59

Re: Внешний ключ, наследственность, триггер

#4 29.08.2014 14:31:15

Re: Внешний ключ, наследственность, триггер

rgbeast написал:

#5 29.08.2014 17:22:29

Re: Внешний ключ, наследственность, триггер

#6 31.08.2014 14:38:34

Re: Внешний ключ, наследственность, триггер

rgbeast написал:

#7 01.09.2014 03:25:33

Re: Внешний ключ, наследственность, триггер

#8 01.09.2014 11:08:56

Re: Внешний ключ, наследственность, триггер

#9 01.09.2014 12:48:50

Re: Внешний ключ, наследственность, триггер

rgbeast написал:

#10 01.09.2014 13:25:36

Re: Внешний ключ, наследственность, триггер

#11 01.09.2014 13:36:22

Re: Внешний ключ, наследственность, триггер

rgbeast написал:

#12 01.09.2014 13:41:52

Re: Внешний ключ, наследственность, триггер

#13 01.09.2014 16:57:11

Re: Внешний ключ, наследственность, триггер

rgbeast написал:

#14 01.09.2014 17:02:37

Re: Внешний ключ, наследственность, триггер

Board footer