Cron task catalog_index_refresh_price fails because of date formatting on 2.4.4

[Quazz]

Preconditions and environment

• Magento version 2.4.4
• MariaDB 10.4
• Need to have clock reach midnight for store time

Steps to reproduce

Have products with special price and special price date range set

Wait for cron task (it runs once per hour, BUT the cron task itself only reaches the error at midnight)

• See https://github.com/magento/magento2/blob/b092dd6df013e8b1e77e8b5d639148ed76a7172a/app/code/Magento/Catalog/Cron/RefreshSpecialPrices.php#L107

• Cron task fails due to date formatting

Expected result

• Cron task runs at midnight
• It does its task and no error is reported

Actual result

• Cron task runs at midnight
• The job does not complete because of SQL error in date format
• Cron Job catalog_index_refresh_price has an error: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens, query was: SELECT `cat`.`entity_id` FROM `catalog_product_entity_datetime` AS `attr` LEFT JOIN `catalog_product_entity` AS `cat` ON cat.entity_id= attr.entity_id WHERE (attr.attribute_id = '77') AND (attr.store_id = '0') AND (attr.value = DATE_FORMAT('2022-05-23', '%Y-%m-%d %H:%i:%s'))

Additional information

Imo, the issue stems from https://github.com/magento/magento2/blob/b092dd6df013e8b1e77e8b5d639148ed76a7172a/app/code/Magento/Catalog/Cron/RefreshSpecialPrices.php#L103

If the second parameter were to be true, it would include the time and thus have the Mysql compliant datetime stamp.

edit: On closer inspection that doesn't appear to be the case.

Release note

No response

Triage and priority

• [ ] Severity: S0 - Affects critical data or functionality and leaves users without workaround.
• [ ] Severity: S1 - Affects critical data or functionality and forces users to employ a workaround.
• [ ] Severity: S2 - Affects non-critical data or functionality and forces users to employ a workaround.
• [ ] Severity: S3 - Affects non-critical data or functionality and does not force users to employ a workaround.
• [ ] Severity: S4 - Affects aesthetics, professional look and feel, “quality” or “usability”.

[m2-assistant[bot]]

Hi @Quazz. Thank you for your report. To speed up processing of this issue, make sure that you provided the following information:

• Summary of the issue
• Information on your environment
• Steps to reproduce
• Expected and actual results

Make sure that the issue is reproducible on the vanilla Magento instance following Steps to reproduce. To deploy vanilla Magento instance on our environment, Add a comment to the issue:

@magento give me 2.4-develop instance - upcoming 2.4.x release

For more details, review the Magento Contributor Assistant documentation.

Add a comment to assign the issue: @magento I am working on this

To learn more about issue processing workflow, refer to the Code Contributions.

---

• Join Magento Community Engineering Slack and ask your questions in #github channel.

:warning: According to the Magento Contribution requirements, all issues must go through the Community Contributions Triage process. Community Contributions Triage is a public meeting.

:clock10: You can find the schedule on the Magento Community Calendar page.

:telephone_receiver: The triage of issues happens in the queue order. If you want to speed up the delivery of your contribution, join the Community Contributions Triage session to discuss the appropriate ticket.

:pencil2: Feel free to post questions/proposals/feedback related to the Community Contributions Triage process to the corresponding Slack Channel

[m2-assistant[bot]]

Hi @engcom-November. Thank you for working on this issue. In order to make sure that issue has enough information and ready for development, please read and check the following instruction: :point_down:

• [ ] 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).

  Details

  If the issue has a valid description, the label Issue: Format is valid will be added to the issue automatically. Please, edit issue description if needed, until label Issue: Format is valid appears.

• [ ] 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue. If the report is valid, add Issue: Clear Description label to the issue by yourself.

• [ ] 3. Add Component: XXXXX label(s) to the ticket, indicating the components it may be related to.

• [ ] 4. Verify that the issue is reproducible on 2.4-develop branch

  Details

  - Add the comment @magento give me 2.4-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.4-develop branch, please, add the label Reproduced on 2.4.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and stop verification process here!

• [ ] 5. Add label Issue: Confirmed once verification is complete.

• [ ] 6. Make sure that automatic system confirms that report has been added to the backlog.

[engcom-November]

Hi @Quazz , Verified the issue on Magento 2.4-develop branch but could not able to reproduce the issue with provided steps. No error was observed in the cron job at any time. Kindly recheck the issue again on Magento 2.4-develop branch and provide missing steps if any if the issue is still reproducible. Thank you.

[loic-paquin]

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.

[Mavlyan]

Note, PHP version must be 8.0.1 or higher :bell: 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.

[Mavlyan]

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

[Quazz]

@engcom-November As pointed out by @Mavlyan the problem only occurs on PHP >8 and only when the time hits midnight (since it checks for "day" changes)

@Mavlyan also provided a possible solution for this problem which seems to work!

[m2-assistant[bot]]

Hi @engcom-Hotel. Thank you for working on this issue. In order to make sure that issue has enough information and ready for development, please read and check the following instruction: :point_down:

• [ ] 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).

  Details

  If the issue has a valid description, the label Issue: Format is valid will be added to the issue automatically. Please, edit issue description if needed, until label Issue: Format is valid appears.

• [ ] 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue. If the report is valid, add Issue: Clear Description label to the issue by yourself.

• [ ] 3. Add Component: XXXXX label(s) to the ticket, indicating the components it may be related to.

• [ ] 4. Verify that the issue is reproducible on 2.4-develop branch

  Details

  - Add the comment @magento give me 2.4-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.4-develop branch, please, add the label Reproduced on 2.4.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and stop verification process here!

• [ ] 5. Add label Issue: Confirmed once verification is complete.

• [ ] 6. Make sure that automatic system confirms that report has been added to the backlog.

[kassner]

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.

[engcom-Hotel]

Hello @Mavlyan,

Thanks for the code sample here https://github.com/magento/magento2/issues/35517#issuecomment-1156442415.

We are able to reproduce the issue using that codebase sample. Can you please let us know from where you picked (File path) this code sample?

This will help us in moving forward with this ticket.

Thanks

[engcom-Hotel]

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

[engcom-Hotel]

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

[github-jira-sync-bot]

:white_check_mark: Jira issue https://jira.corp.adobe.com/browse/AC-6643 is successfully created for this GitHub issue.

[m2-assistant[bot]]

:white_check_mark: Confirmed by @engcom-Hotel. Thank you for verifying the issue.
Issue Available: @engcom-Hotel, You will be automatically unassigned. Contributors/Maintainers can claim this issue to continue. To reclaim and continue work, reassign the ticket to yourself.

[sprankhub]

@kassner, thanks for the fix. I did not test your diff yet, but in https://github.com/magento/magento2/pull/34344, a similar fix to a similar issue has been merged.

[CHallski]

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.

[hostep]

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.

[pmonosolo]

This issue happens on 2.4.5-p1 also. Going to try the fix.

[pmonosolo]

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

[engcom-Hotel]

Hello,

As mentioned in this https://github.com/magento/magento2/issues/35517#issuecomment-1319757036, the issue has been fixed and merged with the 2.4-develop branch. Hence closing this issue.

Thanks

[pmonosolo]

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

:(

[CHallski]

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

[pmonosolo]

Thanks!

I just can't anymore, I mean seriously........

[MatthijsvanNoort]

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

[jaywilliams]

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

[dankhrapiyush]

ACSD-47332 worked for 2.4.5-p2