#mysql #foreign-keys #innodb
#mysql #внешние ключи #innodb
Вопрос:
Нам предстоит выполнить масштабную миграцию схемы, и мы не хотим, чтобы сайт зависал целую вечность, поэтому мы пытаемся сделать это на лету и без блокировки чтения задействованных таблиц. Соответствующие таблицы записываются только в определенное время суток, поэтому у нас есть окно, в котором мы можем позволить себе блокировки записи; но хотя во многих документах mysql утверждается, что ALTER TABLE
следует записывать только таблицы блокировок, по нашему опыту, что-либо нетривиальное, такое как добавление индексного чтения, блокирует его.
Итак, нам пришлось выполнять миграции вручную. Мы добились некоторого успеха со следующей процедурой:
CREATE TABLE new_mytable LIKE mytable;
- примените миграцию к
new_mytable
mysqldump -t mydb mytable > mytable.sql
- используйте sed для изменения
mytable.sql
, чтобы вставить в правильные столбцыnew_mytable
mysql mydb < mytable.sql
- поместите внешние ключи в
mytable
— они не копируютсяCREATE TABLE ... LIKE ...
- добавьте внешние ключи в
new_mytable
RENAME TABLE mytable TO old_mytable, new_mytable TO mytable
- выполните несколько проверок работоспособности
DROP TABLE old_mytable
Как я уже сказал, соответствующие таблицы записываются только в определенное время суток, поэтому мы уверены, что данные остаются согласованными — иначе мы бы и не мечтали попробовать что-то подобное. Несмотря на это, мы столкнулись с проблемой. До сих пор мы пробовали это только на таблицах, где FKS указывают наружу. Сегодня я попробовал это на таблице, на которую ссылались несколько других таблиц. Когда я удалил старую таблицу, мне сказали, что не удалось выполнить ограничение внешнего ключа, и я был в ужасе, увидев, что ссылающиеся таблицы теперь ссылаются на старую таблицу.
Теперь мы могли бы удалить FKS и повторно добавить их в новую, но процесс и так достаточно хрупкий; существует множество таблиц, указывающих на эту; и повторное добавление ключей может привести к блокировкам чтения. Мы уверены — уверены, — что новая таблица содержит все те же данные, что и первая, поэтому было бы неплохо изменить ссылку на эти FKS, не удаляя и не добавляя их. Есть ли способ манипулировать базой данных таким образом, чтобы существующие FKS были изменены для ссылки на новую таблицу?
Ответ №1:
При условии, что вы знаете, как изменять сами таблицы.
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
...Alter the tables and change the keys
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;