magento2: Cron task catalog_index_refresh_price fails because of date formatting on 2.4.4

Dear @Quazz @Mavlyan,

We have noticed that this issue has not been updated for a period of 14 Days. Hence we assume that this issue is fixed now, so we are closing it. Please raise a fresh ticket or reopen this ticket if you need more assistance on this.

Regards

It looks like this was fixed internally by ticket “ACP2E-1243: the catalog_index_refresh_price cron fails with an error that is only reported when run between 0000 and 0059 UTC”, commits: e0625468fcbf282b90cfbdd85e139f91e1275cc2 & 0e261c8a202be04a39a4c5ff4563165f0116e90f & d9557e6fd6f6335d7cc10e8a06445979cb113369 Unfortunately there is no clean merge commit, but here is a patch I created out of those 3 commits that can be applied to the magento/module-catalog module: ACP2E-1243-Catalog.txt

Applying this patch on Magento 2.4.5-p1 works and the error no longer occurs for me.

@engcom-Hotel: this ticket can probably be closed since the fix was merged in 2.4-develop about 2 weeks ago.

Note, PHP version must be 8.0.1 or higher 🔔 It seems because previously PDOStatement::execute() did not throw an exception. PdoStatement->execute() was fixed in 8.0.1 (accordingly to https://www.php.net/ChangeLog-8.php )

See description of the fix here: https://bugs.php.net/bug.php?id=72368

The issue is not reproduced on PHP 7.4 but 8.x only.

To reproduce just run this simple Console Command. I’ve copied query from catalog_index_refresh_price cron.

<?php

namespace Mygento\Reproducer\Console\Command;

use Magento\Framework\App\ResourceConnection;
use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;

class ReproduceCommand extends Command
{
    private const NAME = 'reproduce:pdo:bug';

    private \Magento\Framework\DB\Adapter\AdapterInterface $connection;

    public function __construct(
        ResourceConnection $resourceConnection
    ) {
        $this->connection = $resourceConnection->getConnection();

        parent::__construct(self::NAME);
    }

    protected function execute(InputInterface $input, OutputInterface $output)
    {
        $identifierField = 'entity_id';
        $linkField = 'row_id';


        //Copy of Query
        $select = $this->connection
            ->select()
            ->from(
                ['attr' => $this->connection->getTableName('catalog_product_entity_datetime')],
                [
                    $identifierField => 'cat.' . $identifierField,
                ]
            )->joinLeft(
                ['cat' => $this->connection->getTableName('catalog_product_entity')],
                'cat.' . $linkField . '= attr.' . $linkField,
                ''
            )->where(
                'attr.attribute_id = ?',
                79
            )->where(
                'attr.store_id = ?',
                0
            )->where(
                "attr.value = DATE_FORMAT('2022-06-15', '%Y-%m-%d %H:%i:%s')"
            );


        //2nd param is redundant. To fix the issue - just remove it!
        $selectData = $this->connection->fetchCol($select, $identifierField);
    }
}

I got exactly the same error, the cron task failed at midnight but went smoothly at 10 am.

@engcom-November Please test again, I reproduced it on 2.4.4 and the cron MUST be launched at midnight ( default cron schedule) with some products with special prices.

Hello @Quazz,

We have further investigated the issue and this time the issue is reproducible in Magento 2.4-develop. In order to reproduce the issue, we have made some changes in the codebase like:

In the below file below line number 104:

https://github.com/magento/magento2/blob/26057964cdb9057610de42284f605c93a6882407/app/code/Magento/Catalog/Cron/RefreshSpecialPrices.php#L104

We have reinitialized the $timestamp variable as follows:

$timestamp = "1662595200";

And then run the below command to debug the issue:

XDEBUG_CONFIG=idekey=phpstorm n98-magerun2.phar sys:cron:run catalog_index_refresh_price

Now the issue is reproducible for us. Please refer the below screenshot:

Hence confirming the issue.

Thanks

If you want to download the patch, without using any extra tools, you can do so here: https://github.com/magento/quality-patches/blob/master/patches/os/ACSD-47332_2.4.4-p1.patch

I just finished tracing through this myself and came up with the exact same patch as @kassner and @Mavlyan (we DO use special prices). Wish I’d found this thread earlier and saved myself several hours of digging, but the title was definitely misleading as to the actual issue. This has nothing to do with the date formatting, but with the core code incorrectly calling a framework function.

This is still an issue on 2.4.5-p1.

https://github.com/magento/magento2/blob/2.4.4-p1/app/code/Magento/Catalog/Cron/RefreshSpecialPrices.php#L167

The second parameter there, $identifierField, resolves to (string) "entity_id". However, $connection is an object of Magento\Framework\DB\Adapter\Pdo\Mysql, and fetchCol is defined as:

https://github.com/magento/zf1/blob/master/library/Zend/Db/Adapter/Abstract.php#L785-L797

It’s expecting parameters to bound to the preparedStatement (i.e.: WHERE values, etc), but it’s assumed to be the column to be returned. So it’s being used in the wrong way.

I wrote a small patch that makes the error go away, however I can’t really say it is working because I don’t use special prices at all, so couldn’t test it.

ACSD-47332 works for me on 2.4.5p1

UPDATE: Patch is broken, do not use.

==============

There was a hotfix released for this issue (ACSD-47332):

https://experienceleague.adobe.com/tools/commerce-quality-patches/index.html?lang=en

@pmonosolo It looks like that patch doesn’t include some underlying changes to the AbstractFactory that were probably done separately from those commits. Kassner’s diff will address the cron error in isolation without the other changes.

It looks like this was fixed internally by ticket “ACP2E-1243: the catalog_index_refresh_price cron fails with an error that is only reported when run between 0000 and 0059 UTC”, commits: e062546 & 0e261c8 & d9557e6 Unfortunately there is no clean merge commit, but here is a patch I created out of those 3 commits that can be applied to the magento/module-catalog module: ACP2E-1243-Catalog.txt

Applying this patch on Magento 2.4.5-p1 works and the error no longer occurs for me.

@engcom-Hotel: this ticket can probably be closed since the fix was merged in 2.4-develop about 2 weeks ago.

New error shows up:

Type Error occurred when creating object: Magento\Catalog\Cron\RefreshSpecialPrices, Too few arguments to function Magento\Catalog\Cron\RefreshSpecialPrices::__construct(), 6 passed in /home/user/public_html/vendor/magento/framework/ObjectManager/Factory/AbstractFactory.php on line 121 and exactly 7 expected

😦

UPDATE: Patch is broken, do not use.

==============

There was a hotfix released for this issue (ACSD-47332):

https://experienceleague.adobe.com/tools/commerce-quality-patches/index.html?lang=en