Блог

Главная - Вопросы по программированию - Могу ли я изменить таблицу, на которую ссылается внешний ключ mysql, ничего не блокируя?

Могу ли я изменить таблицу, на которую ссылается внешний ключ mysql, ничего не блокируя?

#mysql #foreign-keys #innodb

#mysql #внешние ключи #innodb

Вопрос:

Нам предстоит выполнить масштабную миграцию схемы, и мы не хотим, чтобы сайт зависал целую вечность, поэтому мы пытаемся сделать это на лету и без блокировки чтения задействованных таблиц. Соответствующие таблицы записываются только в определенное время суток, поэтому у нас есть окно, в котором мы можем позволить себе блокировки записи; но хотя во многих документах mysql утверждается, что ALTER TABLE следует записывать только таблицы блокировок, по нашему опыту, что-либо нетривиальное, такое как добавление индексного чтения, блокирует его.

Итак, нам пришлось выполнять миграции вручную. Мы добились некоторого успеха со следующей процедурой:

  1. CREATE TABLE new_mytable LIKE mytable;
  2. примените миграцию к new_mytable
  3. mysqldump -t mydb mytable > mytable.sql
  4. используйте sed для изменения mytable.sql , чтобы вставить в правильные столбцы new_mytable
  5. mysql mydb < mytable.sql
  6. поместите внешние ключи в mytable — они не копируются CREATE TABLE ... LIKE ...
  7. добавьте внешние ключи в new_mytable
  8. RENAME TABLE mytable TO old_mytable, new_mytable TO mytable
  9. выполните несколько проверок работоспособности
  10. 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;