Magento2: SQL error with cron job messagequeue_clean_outdated_locks

[nickbartlett]

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

1. Magento version 2.1.5
2. PHP 5.6.30
3. MySQL: Server version: 5.7.17-11 Percona Server

Steps to reproduce

1. Unsure on steps. Just open the cron_schedule table and it's there.
2. 
   
   
   3.
   

Expected result

1. The cron job should complete successfully.

Actual result

1. 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.
   

[southerncomputer]

it fails because the select statement returns null, which is not valid for the delete statement!

A better sql statement might be:

DELETE FROM queue_lock where id in (select id from (SELECT id FROM queue_lock WHERE (created_at <= '2017-06-03 09:04:48') ) x) ;

which doesn't throw an error!

[KevinMace]

I can confirm i'm also seeing the exact same issue.

[maky13]

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.

[syno-dlr]

Same error here.

[mamsincl]

2.1.7 EE - issue exists

[waynetheisinger]

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?

[southerncomputer]

what about a count of records conditional?

public function releaseOutdatedLocks()
    {
        $date = (new \DateTime())->setTimestamp($this->dateTime->gmtTimestamp());
        $date->add(new \DateInterval('PT' . $this->interval . 'S'));
        $selectObject = $this->getConnection()->select();
        $selectObject
            ->from(['queue_lock' => $this->getTable(self::QUEUE_LOCK_TABLE)])
            ->where(
                'created_at <= ?',
                $date
            );
        $result = $this->getConnection()->fetchAll($selectObject);
        if (count($result)>0)
        $this->getConnection()->deleteFromSelect($selectObject, $this->getTable(self::QUEUE_LOCK_TABLE));
    }
}


                    

[thenguyenit]

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)
    );
}

[larsroettig]

Hi @magento-engcom-team,
I can also reproduce Issue in 2.1.8 Commerce Edition(EE) is a fix already available?

Best regards

Lars

[vkublytskyi]

@larsroettig this issue is still not fixed. Any help is welcome

[opobla]

I have applied @southerncomputer fix and it works for me in M2 2.1.7.

[magento-engcom-team]

@nickbartlett , thank you for your report. Please follow these guidelines for proper tracking of your issue. You can report Commerce-related issues in one of two ways:
You can use the Support portal associated with your account
or
If you are a Partner reporting on behalf of a merchant, use the Partner portal.

GitHub is intended for Magento Open Source users to report on issues related to Open Source only. There are no account management services associated with GitHub.

[udayakumar-ziffity]

This issue occurs in Magento EE 2.1.9 also.

[ccrothers]

This issue occurs in Magento EE 2.1.6 also.

If "GitHub is intended for Magento Open Source users to report on issues related to Open Source only", then please provide the alternative location for reporting issues related to Magento EE.

[aweb3r]

Same in EE 2.3.4