Ошибка mysql 1062 'повторяющаяся запись' при восстановлении резервной копии

Такие «дубликаты» могут возникать по разным причинам:

• ваши данные - тост.
• приложение, ВСТАВЛЕННОЕ с использованием автоинкремента, но позже запустило ОБНОВЛЕНИЕ, чтобы вручную изменить идентификатор на другое, возможно, существующее значение. Иногда это происходит с разработчиками приложений, которые сортируют «по идентификатору» и хотят «исправить сортировку» (фактически нарушив согласованность).
• вариант второй проблемы возникает у людей, которые позже добавляют ограничение «уникальность» после того, как были вставлены неуникальные записи.

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

INSERT INTO ... values (0,...

Просканируйте номера строк дампа mysql, указанные в сообщениях об ошибках.

Пример того, какой mysqldump я ожидаю:

INSERT INTO foo (id,bar,baz) values (1,2,3);
INSERT INTO foo (id,bar,baz) values (0,4,5);
INSERT INTO foo (id,bar,baz) values (2,6,7);

В «обычном» операторе INSERT для поля с автоматическим приращением значение «0» указывает на автоматическое увеличение поля и поэтому не должно отображаться в дампе SQL базы данных, где используется поле с автоматическим приращением. При повторной загрузке базы данных с помощью дампа SQL ваш дамп просит SQL-сервер увеличить текущее значение поля на единицу и вставить этот идентификатор. Если кто-то вручную ОБНОВИЛ идентификатор до нуля после ВСТАВКИ записи, ваш MySQL Dump также будет включать этот странный идентификатор.

Если вы воспроизводите этот дамп в пустой таблице, это попытается создать следующие записи:

1,2,3
2,4,5
2,6,7

Поскольку для поля «id» задано уникальное автоинкремент, второй INSERT создаст «неправильную» запись (ожидаемая: 0,4,5; фактическая: 2,4,5), которая конфликтует со следующей записью (id = 2 ) и в результате выдает сообщение об ошибке.

В одном из вариантов, кто-то «вручную» обновил идентификатор до уже существующего значения, а затем изменил запись на «уникальную». Изменение типа записи на уникальный не заставляет MySQL повторно проверять, соответствуют ли текущие данные требованиям, отсюда возникает отложенная ошибка. Этот вариант может создать такой дамп:

INSERT INTO foo (id,bar,baz) values (1,2,3);
INSERT INTO foo (id,bar,baz) values (1,4,5);
INSERT INTO foo (id,bar,baz) values (2,6,7);

Попытка вставить вторую строку не удастся из-за уникального ограничения.

В обоих случаях использование «--force» игнорирует только «конфликтующую» строку и продолжает импорт. «Конфликтующие» строки будут отсутствовать, но, вероятно, строки, ведущие к этому конфликту, будут там (но с неправильной записью идентификатора).

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

• импортировать данные в два этапа: сначала только схему, а затем данные. Удалите уникальные ограничения из вашей схемы перед импортом данных, позже снова добавьте уникальные ограничения («ALTER TABLE ... add unique ...»).

• схема принудительного импорта и все данные, в результате чего возникают «разные» проблемы с ограничениями. Вручную проверьте, какие записи верны, и повторно назначьте неправильным их исходное значение.

Пример для более позднего выпуска:

mysql -uuser -ppass --execute "SET UNIQUE_CHECKS=0; source db3.sql" db4

Это заставляет импортировать все конфликтующие записи, даже нарушая любые реальные уникальные ограничения. После импорта у вас будет несколько записей для этих трех записей (600806, 187694 и 1567400), и вам придется вручную отсортировать, какие из них являются правильными, проверив свой дамп. Какие из этих «дубликатов» привели к конфликт и вручную обновить неправильные записи «обратно» до нуля (или как там сказано в конфликтующей строке в дампе).

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

Предисловие

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

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

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

Что мы сделали

Мы удалили все наши базы данных на экземпляре с помощью MySQL Workbench, я перезапустил экземпляр и дважды проверил, что там, где не осталось баз данных на экземпляре. На главном сервере мы уже начали создавать новую резервную копию со скриптом, который мы всегда используем.

Затем, когда резервная копия была на отказавшем сервере, мы начали импорт, и через пару часов он импортировал большую часть данных и завершился с ошибками следующего типа:

ERROR 1062 (23000) at line XXX: Duplicate entry 'dbName-tblName' for key 'PRIMARY'
Operation failed with exitcode 1

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

Актуальная проблема

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

Исправление

Мы удалили файл ibdata1 и все файлы innodb_index_stats и innodb_table_stats в папке базы данных mysql, а затем запустили экземпляр. MySQL сообщит вам, что некоторые системные таблицы отсутствуют. вы можете найти больше информации здесь

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

Надеюсь, это сэкономит несколько часов кому-нибудь где-нибудь :)

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

Скорее всего, произошло одно из следующих событий:

• Рассматриваемый столбец / поле не было уникальным в какой-то момент, но позже было изменено.
• Это подчиненная база данных, которую вы сбрасываете, и в какой-то момент было несколько повторов бинлога, и были вставлены потенциальные дублирующиеся ключи
• Неправильный оператор SQL или импорт команд привели к дублированию записей.

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