Как исправить запрос MySql?

Question

[Александр]

Здравствуйте, уважаемые форумчане.
Есть у меня php файл, который запускается кроном раз в сутки и в нем выполняется запрос, который копирует комментарии старше двух суток из чата в архив . Структура обеих таблиц полностью идентична. И что странно, иногда мне от крона приходит ошибка на почту

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1078458' for key 'PRIMARY'' in /var/www/user/data/www/site.ru/cron/chat.php:10
Stack trace:
#0 /var/www/user/data/www/site.ru/cron/chat.php(10): PDO->query('INSERT INTO `ur...')
#1 {main}
thrown in /var/www/user/data/www/site.ru/cron/chat.php on line 10

Сам запрос такой:

$sql->query("INSERT INTO `chatArhive` SELECT * FROM `comments` where news_id = '77278' and dateAdd < (NOW() - INTERVAL 2 DAY)");

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

Answer 1

[Rsa97]

и дубликатов по идее быть не может

Почему не может? Вы раз в сутки копируете сообщения за двое суток. Дубли просто обязаны появиться.

Comments

[Александр]

Но он же копирует и идентификаторы тоже, по идее откуда они тогда берутся?

[kafkiansky]

Александр, он копирует одни и те же записи 2 раза, в итоге у вас два одинаковых айдишника.

[Александр]

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

[Rsa97]

Александр, Используйте INSERT IGNORE

[kafkiansky]

Александр, вы уверены, что он удаляет все те, что вы скопировали? Чтобы быть уверенным в этом, вам надо использовать транзакцию: достать копируемые записи в массив, скопировать их, потом пройтись по этому массиву и удалить записи из второй таблицы по ключам.

[Rsa97]

Александр, А как выглядит этот запрос на удаление?

[Александр]

Rsa97, Запрос на удаление разделен на два, чтобы получить счетчики сколько удалено комментариев и сколько ответов

$delCom = (int) $sql->exec("delete from `".MY_PREFIX."_comments` where news_id = '77278' and quote_id = '0' and dateAdd < (NOW() - INTERVAL 2 DAY)");

и

$delQuote = (int) $sql->exec("delete from `".MY_PREFIX."_comments` where news_id = '77278' and quote_id != '0' and  dateAdd < (NOW() - INTERVAL 2 DAY)");

[kafkiansky]

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

[Александр]

kafkiansky, с транзакциями приходилось как-то сталкиваться, но очень давно. Не подскажете на примере, для чего мне они нужны?

[kafkiansky]

Александр,

try {
    $pdo->beginTransaction();
    
    $ids = $pdo->query('SELECT id FROM `comments` where news_id = 77278 and dateAdd < (NOW() - INTERVAL 2 DAY')->fetchAll(\PDO::FETCH_COLUMN);

    $stmtInsert = $pdo->prepare('INSERT INTO table(column1, column2) SELECT column1, column2 FROM table2 WHERE table2.id = :id');

    foreach ($ids as $id) {
        $stmtInsert->execute(['id' => $id]);
    }

    $stmtDelete = $pdo->prepare('DELETE FROM table2 WHERE id = :id');

    foreach ($ids as $id) {
        $stmtDelete->execute(['id' => $id]);
    }

    $pdo->commit();
} catch (PDOException $e) {
    $pdo->rollBack();
    // $logger->error($e->getMessage());
}

[Александр]

kafkiansky, спасибо, буду пробовать так делать. Единственное что мне потребуется из всего этого получить 2 счетчика для определения сколько было обработано комментариев и сколько ответов. Они отличаются полем quote_id для комментариев оно имеет значение 0, а для ответов там ид комментария

[Rsa97]

Александр, Можно транзакцией, как советует kafkiansky, а можно через прямой запрос id:

DELETE
  FROM `comments`
  WHERE `news_id` = '77278'
    AND `quote_id` = '0' 
    AND `id` IN (SELECT `id` FROM `chatArhive`);

[Александр]

Rsa97, спасибо, но мне больше понравился подход kafkiansky

[Rsa97]

Александр, В его подходе большое количество запросов DELETE в цикле, что не есть гуд.

[Александр]

Rsa97, т.е это подкорректированный запрос на удаление... А копирование осуществляется моим же методом?
И в запросе не совсем понятно почему выборка по полям идет в первом запросе, а не во втором? Может я чего-то не знаю? Мне кажется условия надо указывать во вложенном запросе, потому как идентификаторы получаем мы именно в нем, а в первом просто удаляем уже по полученным идентификаторам?

DELETE
  FROM `comments`
    WHERE `news_id` = '77278'
    AND `quote_id` = '0' 
    AND dateAdd < (NOW() - INTERVAL 2 DAY)
    AND `id` IN (SELECT `id` FROM `chatArhive` );

Просто мне кажется что так надо?

DELETE
  FROM `comments`
    WHERE  `id` IN (SELECT `id` FROM `chatArhive` 
    WHERE `news_id` = '77278'
    AND `quote_id` = '0' 
    AND dateAdd < (NOW() - INTERVAL 2 DAY) );

[Rsa97]

Александр, Условия по dateAdd вообще не должно быть, иначе рискуете опять что-то пропустить при удалении.
В остальном оба варианта будут работать одинаково, разве что скорость может отличаться.

[Александр]

Rsa97, Тупанул... В плане лишнего условия, ведь в той таблице куда копировали и не может быть сообщений с датой раньше двух суток )) Т.е в запросе AND dateAdd < (NOW() - INTERVAL 2 DAY) - лишнее

Answer 2

[kafkiansky]

Так не копируйте айдишники, зачем? Айдишники первой таблицы пусть сами генерируются. Вставляйте только нужные поля.

Comments

[Александр]

Тогда как правильно подкорректировать запрос для этого?

[kafkiansky]

Александр,

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

[Александр]

kafkiansky, понял. Спасибо, попробую исправить

[Rsa97]

Тогда появятся дубли записей с разными id.

[kafkiansky]

Rsa97, да, это решение ошибки, но не проблемы. Ему надо решить проблему времени.

Answer 3

[Developer]

Заблуждения программистов о времени
https://habr.com/ru/post/146109/

Answer 4

[A N]

Александр, решается довольно просто:

INSERT INTO table1 (column1, column2, column3, ...)
   SELECT t2.column1,  t2.column2, t2.column3, ...
   FROM table2 t2
   WHERE condition
ON DUPLICATE KEY UPDATE
   column2 = VALUES(column2),
   column3 = VALUES(column3),
   ...
;