Mysql is not null: MySQL NOT NULL Constraint

В этом посте я поделюсь тем, что мы узнали, и нашими рекомендациями для ваших собственных таблиц базы данных.

Во-первых, важно понять, как определяется безопасность изменений схемы.

Для этой цели мы должны знать о процедуре, которую LHM использует для выполнения миграции, поскольку она использует механизм

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

Таким образом, изменения схемы идентифицируются как безопасные , если:

• После начала миграции: ВСТАВИТЬ , ОБНОВЛЕНИЕ и DELETE операций, нацеленных на исходную таблицу , могут заполнять данными теневую таблицу (с помощью триггеров MySQL) без сбоев. Это известно как обратная совместимость.
• После завершения миграции: количество записей в теневой таблице должно быть равно количеству записей в исходной таблице (через триггеры MySQL и пакетные вставки LHM).

Как указано в заголовке, это исследование сосредоточено на одном конкретном наборе изменений схемы, добавлении NOT NULL столбцы ограничены таблицей. Принимая во внимание внутреннюю работу LHM, можно только догадываться, какие значения будут помещены во вновь добавленный столбец для всех 90 119 исходных 90 120 записей таблицы, в которых изначально отсутствует значение для этого столбца.

Таким образом, при исследовании безопасности этих изменений схемы будут учитываться следующие факторы:

• Включение значения ПО УМОЛЧАНИЮ для определения нового столбца в той же миграции.
• Включение UNIQUE INDEX для нового столбца в той же миграции.
• Режим, на который настроен экземпляр MySQL ( строгий   или нестрогий ).

Для проведения исследования определены несколько шагов для моделирования того, как LHM будет выполнять миграцию при повторении рассматриваемых факторов.

1. Инициализация: установлен режим MySQL и создана исходная таблица.

2. Создание таблицы: Смоделируйте, как LHM применит миграцию.

3. Определение триггеров: смоделируйте, как LHM настроит триггеры MySQL.

После того, как настройка эксперимента определена, мы можем выполнить операции SQL, связанные с данными, чтобы проверить, как триггеры MySQL будут заполнять их из

1. Заполните исходные данные: Создайте исходные записи для выполнения запросов исследования.

2. Активируйте триггеры SQL: выполните определенные запросы, чтобы активировать смоделированные триггеры LHM.

3. Сравните результаты: выполните запрос SELECT как в исходная и теневая таблица, чтобы сравнить их записи (как по длине, так и по содержанию).

Объединив все шаги раздела Experiment Setup вместе с этими шагами, мы можем определить безопасность добавления NOT NULL изменений схемы столбцов, в зависимости от факторов, указанных в разделе 2 ( DEFAULT пункт, UNIQUE INDEX существование и режим MySQL). Полный последовательный эксперимент для INSERT операций, будет выглядеть так:

Поскольку количество записей между исходной и теневой таблицами различно, мы заключаем, что выполнение операций INSERT, когда есть NOT NULL

Подобно тому, как мы построили экспериментальный случай в предыдущем разделе, мы можем повторять факторы эксперимента (тип операции, включение значения DEFAULT , наличие UNIQUE INDEX и режим MySQL), чтобы построить матрицу безопасности изменения схемы. для всех полученных комбинаций.

Напоминаем, что безопасность изменения схемы определяется ответами на два вопроса:

• Будет ли миграция обратно совместима с ? Другими словами, могут ли операции INSERT , UPDATE и DELETE , нацеленные на исходную таблицу , заполнять данные в теневую таблицу (через триггеры MySQL) без сбоя.
• Приведет ли миграция к потере данных ? Другими словами, равно ли количество записей в теневой таблице количеству записей в исходной таблице после завершения миграции.

* Количество записей в теневой таблице совпадает с количеством записей в исходной таблице , но для нового столбца выбрано неявное значение DEFAULT . В этом эксперименте значением была пустая строка («»), но оно будет варьироваться в зависимости от типа данных (проверьте неявные значения по умолчанию MySQL).

Учитывая матрицу случаев из предыдущего раздела:

1. Избегайте добавления столбца NOT NULL без значения по умолчанию. Изменения схемы, вводящие столбцы NOT NULL , должны определить значение DEFAULT , чтобы избежать непредвиденных результатов при миграции.

   В худшем случае (когда экземпляр MySQL настроен на строгий режим) затронутая миграцией таблица нарушит совместимость с существующими приложениями, так как ранее использовались операции, связанные с данными ( ВСТАВИТЬ / ОБНОВЛЕНИЕ / УДАЛИТЬ ) не удалось заполнить таблицу shadow после начала миграции.

   В лучшем случае (когда экземпляр MySQL настроен на нестрогий режим) записи, заполняемые из исходной таблицы в таблицу теневой таблицы либо триггерами MySQL, либо пакетными вставками LHM, получат неявный Значение по умолчанию для нового столбца, что, вероятно, нежелательно.

2.  Будьте предельно осторожны при добавлении UNIQUE INDEX. Введение индексов UNIQUE в механизмы изменения схемы, которые используют механизм теневой таблицы для выполнения своих миграций, оказалось опасным, поскольку это может привести к потере данных при наличии повторяющихся значений для столбцов, покрываемых индексом, до к миграциям.

   Рекомендуется, чтобы разработчики проверяли наличие дубликатов, прежде чем добавлять индекс UNIQUE для набора столбцов.

Это расследование было завершено благодаря отзывам, предоставленным всеми членами команды DB Migrations: Бастианом Бартманном, Сергеем Федоровым, Аней Зенкиной, Сяоли Лян; и преданное руководство Шухао Ву.

Синклер Перес — инженер-технолог в Shopify. Вы можете связаться с ним в Twitter и Github.

Если вас интересует создание систем с нуля для решения реальных проблем, в нашем инженерном блоге есть истории о других проблемах, с которыми мы столкнулись.