Изменение таблицы MySQL (Percona 5.7) завершается ошибкой из-за повторяющихся ошибок записи

Мы обновили MySQL (Percona) с 5.6 до 5.7, и нам сказали, что некоторые таблицы необходимо изменить/исправить, потому что поле «дата и время» теперь другое. Изменение таблицы завершается с ошибкой дублирования записи, несмотря на наличие первичного ключа в обоих полях, device_id и ts, и все данные кажутся в порядке (без дубликатов). Насколько я знаю, первичный ключ также должен быть уникальным.

Мы попытались выбрать повторяющиеся записи по условиям «device_id» и «ts» в части WHERE, и он находит только 1 экземпляр записи. Когда мы пытаемся выбрать только по полю ts, он находит 2 экземпляра одной и той же записи. Как это возможно? Что это за дубликаты? Ключи повреждены? Есть ли другой способ, кроме как создать новую таблицу и передать туда все данные с помощью INSERT IGNORE?

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

ERROR 1062 (23000): Duplicate entry '486-2014-10-26 02:39:33' for key 'PRIMARY'

Редактировать 2: Структура таблицы выглядит следующим образом (device_id, ts и 32 датчика):

CREATE TABLE IF NOT EXISTS `sensor_log` (
    `device_id` int(11) NOT NULL,
    `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `D1` smallint(6) DEFAULT NULL,
    `D2` smallint(6) DEFAULT NULL,
    ...
    `D30` smallint(6) DEFAULT NULL,
    `D31` smallint(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(ts))
(PARTITION p_sl_2013 VALUES LESS THAN (1388530800) ENGINE = InnoDB,
    PARTITION p_sl_2014 VALUES LESS THAN (1420066800) ENGINE = InnoDB,
    PARTITION p_sl_2015 VALUES LESS THAN (1451602800) ENGINE = InnoDB,
    PARTITION p_sl_2016 VALUES LESS THAN (1483225200) ENGINE = InnoDB,
    PARTITION p_sl_2017_q1 VALUES LESS THAN (1490997600) ENGINE = InnoDB,
    PARTITION p_sl_2017_q2 VALUES LESS THAN (1498860000) ENGINE = InnoDB,
    PARTITION p_sl_2017_q3 VALUES LESS THAN (1506808800) ENGINE = InnoDB,
    PARTITION p_sl_2017_q4 VALUES LESS THAN (1514761200) ENGINE = InnoDB,
    PARTITION p_sl_2018_q1 VALUES LESS THAN (1522533600) ENGINE = InnoDB,
    PARTITION p_sl_2018_q2 VALUES LESS THAN (1530396000) ENGINE = InnoDB,
    PARTITION p_sl_2018_q3 VALUES LESS THAN (1538344800) ENGINE = InnoDB,
    PARTITION p_sl_2018_q4 VALUES LESS THAN (1546297200) ENGINE = InnoDB,
    PARTITION p_sl_2019_q1 VALUES LESS THAN (1551394800) ENGINE = InnoDB,
    PARTITION p_sl_2019_q2 VALUES LESS THAN (1556661600) ENGINE = InnoDB,
    PARTITION p_sl_2019_q3 VALUES LESS THAN (1561932000) ENGINE = InnoDB,
    PARTITION p_sl_2019_q4 VALUES LESS THAN (1567288800) ENGINE = InnoDB,
    PARTITION p_sl_2019_q5 VALUES LESS THAN (1572562800) ENGINE = InnoDB,
    PARTITION p_sl_2019_q6 VALUES LESS THAN (1577833200) ENGINE = InnoDB,
    PARTITION p_sl_X VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;

--
-- Indexes for table `sensor_log`
--
ALTER TABLE `sensor_log`
ADD PRIMARY KEY (`device_id`,`ts`);

mysql duplicates percona alter-table mysql-5.7
person LostInTheEcho    schedule 21.12.2018    source источник
comment
Пожалуйста, всегда включайте полные сообщения об ошибках, а также другую полезную информацию, такую ​​как определение таблицы. Вы можете избавить себя от большого количества написания, и это позволит избежать путаницы и догадок.   -  person fancyPants    schedule 21.12.2018
comment
Я совершенно забыл, что также может добавить ошибку. Я добавил это сейчас. Также мы пытались ALTER TABLE с FORCE, но появляется та же ошибка.   -  person LostInTheEcho    schedule 21.12.2018

Ответы (1)


arrow_upward
0
arrow_downward

Когда ошибка выглядит примерно так:

ERROR 1022 (23000): Can't write; duplicate key in table '#sql-6b04_a0'

то вы, вероятно, пытаетесь дважды создать внешний ключ с одним и тем же именем. Или что-то вроде того. Проверьте свои внешние ключи.

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

ALTER TABLE foo FORCE;

РЕДАКТИРОВАТЬ:

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

SELECT device_id, ts, COUNT(*)
FROM your_table
GROUP BY device_id, ts
HAVING COUNT(*) > 1

Затем вам нужно будет очистить свою таблицу, убедившись, что присутствует только одна запись для комбинации device_id и ts.

person fancyPants    schedule 21.12.2018
comment
В своем первоначальном сообщении я упомянул, что дубликаты не видны при выборе данных по обоим столбцам в части WHERE. Дубликаты видны только в том случае, если вы выбираете только один столбец, например, ts. Чтобы убедиться, что это так, мы выполнили запрос SELECT, который вы написали выше, и он вернул: Пустой набор (7 часов 53 минуты 38,30 секунды). - person LostInTheEcho; 22.12.2018
comment
Когда ваш запрос выполняется почти 8 часов, вы почти наверняка не выбрали столбцы, составляющие ваш первичный ключ. Или ваша база данных работает на флешке USB 2.0 или что-то в этом роде. И ваше сообщение об ошибке точно указывает, какая это строка. 486-2014-10-26 02:39:33 означает, что `486 — это значение для первой части вашего первичного ключа, остальное — это столбец отметки времени в вашем первичном ключе. Вы выбрали правильные столбцы? Я не могу сказать, потому что вы все еще не делитесь оператором создания таблицы, как я сказал вам в первую очередь. - person fancyPants; 22.12.2018
comment
Предоставляйте всю ценную информацию как она есть, не описывая ее, тогда люди смогут помочь вам намного лучше и избежать путаницы. - person fancyPants; 22.12.2018
comment
Я добавил структуру таблицы, информацию о разделе и первичный ключ в исходный пост. Запрос выполняется 8 часов, потому что таблица имеет размер 270 ГБ и содержит более 2,6 миллиарда записей. Сегодня мы выполнили два запроса: select * from sensor_log where ts = '2014-10-26 02:39:33' вернул 2 записи для device_id 486. select * from sensor_log where ts = '2014-10-26 02:39:33' and device_id = 486 вернул 1 запись. - person LostInTheEcho; 22.12.2018