magento2: SQL error with cron job messagequeue_clean_outdated_locks

The cron job messagequeue_clean_outdated_locks fails and results in an error. This causes all following cron tasks to become “Too late for the schedule” and stops execution of all crons. The “message” from the cron_schedule table says: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT queue_lock.* FROM queue_lock WHERE (created_at <= ‘2017-06-03 09:04:’ at line 1, query was: DELETE FROM (SELECT queue_lock.* FROM queue_lock WHERE (created_at <= ‘2017-06-03 09:04:48’))

I ran this query manually to double check and it indeed fails. Seems like this could/should be written without the sub select.

Preconditions

Magento version 2.1.5
PHP 5.6.30
MySQL: Server version: 5.7.17-11 Percona Server

Steps to reproduce

Unsure on steps. Just open the cron_schedule table and it’s there.

Expected result

The cron job should complete successfully.

Actual result

The cron job messagequeue_clean_outdated_locks fails and results in an error. This causes all following cron tasks to become “Too late for the schedule” and stops execution of all crons.

About this issue

Original URL
State: closed
Created 7 years ago
Reactions: 8
Comments: 15 (7 by maintainers)

Most upvoted comments

Same the error in Magento EE: 2.1.6 and have a little worry as don’t have any action about changing status for this issue: BTW, My code for overriding:

function releaseOutdatedLocks()
{
    $connection = $this->getConnection();

    $date = (new \DateTime())->setTimestamp($this->dateTime->gmtTimestamp());
    $date->add(new \DateInterval('PT' . $this->interval . 'S'));
    $this->getConnection()->delete(
        $this->getTable(self::QUEUE_LOCK_TABLE),
        $connection->quoteInto('created_at <= ?', $date)
    );
}

thenguyenit on Sep 5, 2017

And this error is found by the integration tests

...................S......................................... 2562 / 2583 ( 99%)

Time: 43.04 minutes, Memory: 706.00MB
There was 1 failure:
1) Magento\Cron\Observer\ProcessCronQueueObserverTest::testDispatchNoFailed
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT `queue_lock`.* FROM `trv_queue_lock` AS `queue_lock` WHERE (created_at <' at line 1, query was: DELETE FROM (SELECT `queue_lock`.* FROM `trv_queue_lock` AS `queue_lock` WHERE (created_at <= '2017-08-17 10:00:58'))
/home/travis/build/package/vendor/dev/tests/integration/testsuite/Magento/Cron/Observer/ProcessCronQueueObserverTest.php:48
                                                                         
FAILURES!                                                                
Tests: 2583, Assertions: 11784, Failures: 1, Incomplete: 69, Skipped: 92.
=== Memory Usage System Stats ===
Memory usage (OS):	765.28M (109.33% of 700.00M reported by PHP)
Estimated memory leak:	65.28M (8.53% of used memory)

How did it get into production?

waynetheisinger on Aug 16, 2017

Got the same issue on my side. The cron call the delete as following: $this->getConnection()->delete($selectObject); But it should be $this->getConnection()->deleteFromSelect($selectObject, $this->getTable(self::QUEUE_LOCK_TABLE)); vendor/magento/module-message-queue/Model/ResourceModel/Lock.php

Even if the select return nothing i have the error, so it’s not related to the select. The syntax of the delete is just wrong.

maky13 on Jun 27, 2017