Error on indexer

[ddavulcang29b8]

Currently using Magento Open Source ver 2.4.6-p2 with php 8.1, running on a shared hosting

I had uploaded a list of products (around 250 SKU) on the site and set up the index management "Update by schedule" at a 10-min interval at the moment ("Update on save" also having the same issue before)

Everyday the system will at certain time point (previously during midnight, but now at around 11am) it will only able to load 8 products in the frontend only (at the backend all items remain). All of the filter option also disappeared except Price. This will be able to fix by running the command line "bin/magento indexer:reindex", all the product will resume listing on the frontend.

Tried "bin/magento indexer:reset" but the problem persist.

Current cron job command line on the shared hosting is

/opt/alt/php81/usr/bin/php /home/xxx/public_html/bin/magento cron:run 2>&1 | grep -v "Ran jobs by schedule" >> /home/xxx/public_html/var/log/magento.cron.log

In the var/log/system.log, i've found one error:

main.WARNING: Deadlock detected in cron: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was: UPDATE mgif_cron_schedule SET status = ? WHERE (job_code = 'catalog_product_frontend_actions_flush') AND (status = 'running') [] []

In the var/log/debug.log & cron.log, I some lines are showing "main.WARNING: Could not acquire lock for cron job: ..." but those lines shouldn't be affecting the product list

So, the error found in the system.log seems the log for the error, does anyone understand about this error and how to fix the issue? I'm new on setting up magento, thank you

[toniagency82ed]

Do you have some sort of cron scheduler extension installed?

[ddavulcang29b8]

no, I believe I had no extension or anything did related to the cron job

[toniagency82ed]

Are you using elasticsearch? Next time products disappear, try reindexing just the catalog search indexer, if that fixes the problem, you know the issue probably has something to do with elastic search. Check elastic search's log.

[ddavulcang29b8]

I am using OpenSearch.
But I will give a try ElasticSearch as well reindexing only the catalog search indexer see if can solve the issue.

Thanks for the suggestion

[ddavulcang29b8]

If I only reindex the catalog search indexer, it do solve the problem at that moment. And then the problem appears again next day.

Tried Elastic Search and Open Search, problem also persist.

[ddavulcang29b8]

Hi, can anyone help on this?

[ninoooo1953a26]

try this code in your mysql 

UPDATE mgif_cron_schedule SET status = 'pending' WHERE job_code = 'catalog_product_frontend_actions_flush' AND status = 'running';

Number 2 Go to Cpanel and create Increased database timeouts Why Deadlock: A state in which two or more processes are waiting for each other, waiting for resources owned by the other. This prevents any progress.

1. Log in to your cPanel account.
2. Click on the Databases icon.
3. Click on the MySQL Databases tab.
4. Select the database that you want to increase the timeout for.
5. Click on the Edit button.
6. In the Timeouts section, increase the Long query timeout value.
7. Click on the Save button.

The final solution that will work for you is to go to the database and delete the table

mgif_cron_schedule

and make command php /bin/upgrade again