SQLinfo.ru - Все о MySQL

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

Задавайте вопросы, мы ответим

Вы не зашли.

#1 27.08.2014 10:55:12

animegirl
Активист
Зарегистрирован: 28.07.2011
Сообщений: 288

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

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

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


Скажи миру - НЯ!

Неактивен

 

#2 27.08.2014 15:39:10

animegirl
Активист
Зарегистрирован: 28.07.2011
Сообщений: 288

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

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


Скажи миру - НЯ!

Неактивен

 

#3 28.08.2014 20:29:59

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

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

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

Неактивен

 

#4 29.08.2014 14:31:15

animegirl
Активист
Зарегистрирован: 28.07.2011
Сообщений: 288

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

rgbeast написал:

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

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

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


Скажи миру - НЯ!

Неактивен

 

#5 29.08.2014 17:22:29

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

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

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

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

Неактивен

 

#6 31.08.2014 14:38:34

animegirl
Активист
Зарегистрирован: 28.07.2011
Сообщений: 288

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

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'

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


Скажи миру - НЯ!

Неактивен

 

#7 01.09.2014 03:25:33

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

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

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

Неактивен

 

#8 01.09.2014 11:08:56

animegirl
Активист
Зарегистрирован: 28.07.2011
Сообщений: 288

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

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


Скажи миру - НЯ!

Неактивен

 

#9 01.09.2014 12:48:50

animegirl
Активист
Зарегистрирован: 28.07.2011
Сообщений: 288

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

rgbeast написал:

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

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

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


Скажи миру - НЯ!

Неактивен

 

#10 01.09.2014 13:25:36

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

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

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

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

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

Неактивен

 

#11 01.09.2014 13:36:22

animegirl
Активист
Зарегистрирован: 28.07.2011
Сообщений: 288

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

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, тем самым выйдет двойная запись, из-за двойного срабатывания триггера?


Скажи миру - НЯ!

Неактивен

 

#12 01.09.2014 13:41:52

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

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

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

Неактивен

 

#13 01.09.2014 16:57:11

animegirl
Активист
Зарегистрирован: 28.07.2011
Сообщений: 288

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

rgbeast написал:

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

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


Скажи миру - НЯ!

Неактивен

 

#14 01.09.2014 17:02:37

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

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

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

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson