Alter ignore table не работает

Пара слов про Alter Table, или как делать не надо

Это скорее не статья, а небольшая заметка о некоторых особенностях работы с большими таблицами в MySQL.

Причиной написания стало вроде бы будничное добавление новой колонки в таблицу. Но все оказалось не так просто, как предполагалось.

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

Чтобы было понятнее, характеристики таблицы и базы:

• размер таблицы 110Gb
• число строк: 7.5 млн
• storage engine: InnoDB
• есть два sql-сервера, соединенных по схеме master-slave, при этом master — на SSD, а slave — на HDD

Вроде бы очевидное решение для добавления колонки — Alter Table.

Им мы и воспользовались (да, мы понимали, что это плохо, но в данном конкретном случае риски были минимальны).

Результаты оказались довольно неприятными:

• на мастере процесс добавления колонки шел около часа (!)
• на слейве он начался после окончания процесса на мастере и продолжался около 8 часов (!!)
• во время выполнения alter table на слейве полностью остановилась репликация данных (. )

Но нет худа без добра: небольшой бонус оказался в том, что после добавления колонки размер таблицы уменьшился на 10%.

На графиках ниже это наглядно видно.

График загрузки CPU на мастере.

График загрузки CPU на слейве.

Отставание репликации.

Какие неприятности ждут тех, кто делает это на боевых таблицах?

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

Соответственно, если таблица большая, то время недоступности будет значительным (как у нас, при использовании SSD это заняло час, а на обычном диске — 8 часов), что вряд ли ожидают Ваши заказчики.

Во-вторых, как в нашем случае, на время выполнения Alter Table на слейве полностью остановилась синхронизация всех таблиц, а не только той, которую мы изменяли. Поэтому в случае, если у Вас данные на втором сервере критичны и должны быть свежими — Вы рискуете остаться без обновлений со всеми вытекающими последствиями.

Еще один неочевидный момент, с которым мы столкнулись во время добавления колонки (но это было в другой раз) — на диске нужно дополнительное место.

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

Кроме того, во время выполнения всяких Alter Table все изменения записываются в лог-файл, чтобы после изменений накатить данные за то время, в течение которого проводилась операция. И тут тоже может ждать неприятный сюрприз: если таблица изменяется долго, а объем операций большой, то может закончится не только место на диске, но и превыситься лимит на размер файла, указанный в настройках SQL. В любом случае Вас ожидает «the online DDL operation fails, and uncommitted concurrent DML operations are rolled back».

Мы столкнулись с тем, что каталог для временных файлов был маловат, в результате пришлось переопределить innodb_tmpdir.

Посмотреть, куда указывает переменная в данный момент, можно так:

Имейте ввиду, что размер временного каталога также может быть нужен размером с таблицу + индексы. В общем, запасайтесь местом.

А как же делать надо? На самом деле нет единого рецепта на все случаи жизни.

Один из возможных вариантов, как делаем мы для таблиц, которые не критичны на обновление:

• Создаем новую таблицу с нужной структурой
• Заполняем поля из старой таблицы
• Удаляем или переименовываем старую таблицу
• Переименовываем новую

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

UPD. Пользователь syavadee посоветовал использовать percona online schema change. По сути она реализует описанный выше алгоритм с дополнительными плюшками.

UPD. Пользователь arheops рекомендует включить parallel replication/gtid для решения проблем с репликацией.

Ну и попутно, иногда, чтобы понять, насколько большая таблица и сколько в ней строк, нужно, как учат, сделать

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

Поэтому для примерной оценки объема можно воспользоваться следующим способом:

К сожалению, на движке InnoDB полученный размер может отличаться процентов на 50 (в нашем случае с таблицей выше реальное число записей порядка 7.5 млн, а указанный способ показал только 5 млн), но для ориентировочной оценки это вполне подходит.

На этом все, надеюсь, заметка кому-то поможет избежать больших неприятностей с якобы безобидными командами SQL.

Источник

MySQL: таблица Alter IGNORE дает » нарушение ограничения целостности»

Я пытаюсь удалить дубликаты из таблицы MySQL, используя ALTER IGNORE TABLE + уникальный ключ. Документация MySQL говорит:

IGNORE-это расширение MySQL для стандартного SQL. Он управляет работой ALTER TABLE, если в новой таблице имеются дубликаты уникальных ключей или предупреждения при включении строгого режима. Если параметр IGNORE не указан, копия прерывается и откатывается при возникновении ошибок с повторяющимся ключом. Если указан параметр IGNORE, используется только первая строка строки с дубликатами на уникальном ключе. Другие конфликтующие строки удаляются. Неправильные значения усекаются до ближайшего приемлемого значения соответствия.

когда я запускаю запрос .

. Я все еще получаю ошибку #1062-дубликат записи «blabla» для ключа «dupidx».

3 ответов

на IGNORE расширение ключевого слова для MySQL, похоже, имеет ошибка в версии InnoDB на какой-то версии MySQL.

вы всегда можете конвертировать в MyISAM, игнорировать-добавить индекс, а затем конвертировать обратно в InnoDB

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

или попробуйте установить сеанс old_alter_table=1 (Не забудьте установить его обратно!)

проблема в том, что у вас есть дубликаты данных в поле, которое вы пытаетесь индексировать. Перед добавлением уникального индекса необходимо удалить дубликаты-нарушители.

один из способов сделать следующее:

это позволяет вставлять в таблицу только уникальные данные

Источник

Удаление из таблицы строк с повторящюимися данными

Пытаюсь удалить из таблицы все строки с повторяющимися значениями в столбце id . Нашел в документации, что сделать это можно с помощью запроса вида:

Несмотря на присутствие в запросе ключевого слова IGNORE , phpmyadmin все равно выдает ошибку duplicate entry :

Нет ли у вас идей, почему не работает?

3 ответа 3

добавьте временный первично-ключевой столбец с авто-инкрементом (он сразу и наполнится уникальными значениями), а затем, как в этом примере, удалите дублирующиеся (по стобцу id ) строки.

потом можно удалить уже ненужный временный столбец и назначить стобец id первичным ключом.

результат — в первом запросе, описание таблицы — во втором:

MySQL 5.6 Schema Setup:

Query 1:

Query 2:

Оказывается, я смотрел документацию не на ту версию. Я привел ссылку на доки версии 5.1, а у меня сервер версии 5.5.

Таким образом, эта фича работает не во всем версиях и мне следует подумать либо о применении SET SESSION old_alter_table=1 либо о других сособах удаления лишних строк.

Вам надо удалять лишние строки самому. А так как там наверное не только id, но и какие-то осмысленные данные, то это может быть нетривиальной задачей.

В таблице есть что-то, что было уникально до сих пор? Вы можете положиться на это поле/комбинацию полей чтобы сделать так:

Благодаря группировке, каждый id будет упоминаться только один раз. Лишнее удалится. После можно делать ALTER TABLE ADD PRIMARY KEY.

Всё ещё ищете ответ? Посмотрите другие вопросы с метками mysql или задайте свой вопрос.

Похожие

Подписаться на ленту

Для подписки на ленту скопируйте и вставьте эту ссылку в вашу программу для чтения RSS.

дизайн сайта / логотип © 2021 Stack Exchange Inc; материалы пользователей предоставляются на условиях лицензии cc by-sa. rev 2021.10.19.40494

Нажимая «Принять все файлы cookie» вы соглашаетесь, что Stack Exchange может хранить файлы cookie на вашем устройстве и раскрывать информацию в соответствии с нашей Политикой в отношении файлов cookie.

Источник

Ignore table не работает при бэкапе таблиц

Добрый день. Пишу такой запрос, который должен сделать бекап всех таблиц с префиксом ipb_ и при этом добавил туда —ignore-table что не брать какую-либо таблицу. Но ignore table не срабатывает. Почему так, как сделать так, чтобы он срабатывал, в чём ошибка?

Помощь в написании контрольных, курсовых и дипломных работ здесь.

Оператор SET при создании таблиц CREATE TABLE
Встретил такую конструкцию CREATE TABLE cl_db.Train ( trainno INT PRIMARY KEY.

Узнать сколько записей было IGNORE в запросе UPDATE IGNORE
Добрый день уважаемые форумчане, будьте так любезны, подскажите пожалуйста. Имеется запрос.

Объединение таблиц без #Table и @Table
Возможно ли это для следующих запросов: SELECT COUNT(ZP.Id) AS AO, Uch.Uch, Uch.Id FROM Hous Right.

Ошибка при бэкапе базы
Внезапно перестал делаться бэкап. Выдается следующая ошибка: Error 3202: Write on.

Ошибка при бэкапе базы данных
Необходимо сделать backup базы и восстановить ее на другом сервере. Появляется ошибка (на скрине).

SQL Maximum error count при бэкапе
У меня при бэкапе вылетает ошибка с кодом 0x80019002. Написано что для решения проблемы нужно.

Ошибки при бэкапе Symantec Backup Exec 2010 R3
Здравствуйте. Делаю бэкап одного из серверов, всё ок, бэкап готов. Но в «Журнале заданий».

Не работает event->ignore()
хочу, чтобы по нажатию кнопки «свернуть» окно не сворачивалось, для этого переопределяю функцию.

Источник

Проверьте, существует ли столбец перед ALTER TABLE — mysql

Есть ли способ проверить, существует ли столбец в базе данных mySQL до (или как) выполнения оператора ALTER TABLE ADD coumn_name ? Тип IF column DOES NOT EXIST ALTER TABLE .

Я пробовал ALTER IGNORE TABLE my_table ADD my_column , но это все еще вызывает ошибку, если уже добавленный столбец уже существует.

РЕДАКТИРОВАТЬ: использовать случай, чтобы обновить таблицу в уже установленном веб-приложении. Поэтому, чтобы все было просто, я хочу убедиться, что столбцы, которые мне нужны, и если они этого не делают, добавьте их, используя ALTER TABLE

ОТВЕТЫ

Ответ 1

Как вы думаете, вы можете попробовать это?:

Это не один лайнер, но можете ли вы хотя бы посмотреть, будет ли это работать для вас? По крайней мере, ожидая лучшего решения.

Ответ 2

Так как операторы управления mysql (например, «IF» ) работают только в хранимых процедурах, временный может быть создан и выполнен:

Ответ 3

Функции и процедуры утилиты

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

Удаление ограничений и столбцов с помощью утилит выше

С их помощью довольно легко использовать их для проверки столбцов и ограничений для существования:

Ответ 4

Сделайте предложение подсчета с приведенным ниже примером Джоном Уотсоном.

Сохраните этот результат в целое число, а затем сделайте его условием применения предложения ADD COLUMN .

Ответ 5

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

Я использовал код ниже:

Ответ 6

Вы можете создать процедуру с обработчиком CONTINUE в случае существования столбца (обратите внимание, что этот код не работает в PHPMyAdmin):

Этот код не должен вызывать ошибки, если столбец уже существует. Он просто ничего не сделает и продолжит выполнение остальной части SQL.

Ответ 7

Ответ 8

Вы можете проверить, существует ли столбец:

Просто введите имя столбца, имя таблицы и имя базы данных.

Ответ 9

Как сообществу MYSQL:

IGNORE — это расширение MySQL для стандартного SQL. Он управляет тем, как работает ALTER TABLE, если в новой таблице есть дубликаты уникальных клавиш или если предупреждения включены, когда включен строгий режим. Если IGNORE не указан, копия прерывается и откатывается, если возникают ошибки с повторяющимися ключами. Если указано IGNORE, для строк с дубликатами на уникальном ключе используется только одна строка. Остальные конфликтующие строки удаляются. Неверные значения усекаются до ближайшего подходящего значения.

Итак, рабочий код: ALTER IGNORE TABLE CLIENTS ADD CLIENT_NOTES TEXT DEFAULT NULL;

Источник