5

This is a 2 part question.
Main question:

How risky is to delete the records from the *_cl tables (cataloginventory_stock_cl, catalog_product_attribute_cl, ...) where the version_id field is lower that the version of index associated to the _cl table in the mview_state table?
Example.
The mview_state version for cataloginventory_stock is 5000.
In the cataloginventory_stock_cl table I have 10k records with version_id below 5000.
Is it OK to delete those?
Note: I've read the explanations about the mview provided in here: what is mview in magento2? and I concluded that the cron with code indexer_reindex_all_invalid does exactly what I'm trying to do (I may be wrong here).
The cron does not run anymore because the stock index is set on 'Update on save'.

Bonus question:
How risky it is to completely clean all the _cl tables if I'm sure that all the indexes have been rebuilt and are up to date?

6

The risk is minimal. You can restore all the data /consistency by the run of full indexation

  • Assuming I have all indexes on "Update on save" and they are all rebuilt, does the "minimal" risk become 0 risk? – Marius Feb 26 at 17:44
  • If you have an index set to be updated on save, your can truncate cl tables and issue a full reindex with zero risk. – Alessandro Ronchi Feb 26 at 18:57
  • 1
    Note: I wouldn't manually reindex an "update on schedule" index, switch it to "update on save" and after then perform reindex. – Alessandro Ronchi Feb 26 at 18:59
  • 3
    Thanks Alessandro. Ill post back tomorrow either with valid results or a copy of my resignation – Marius Feb 26 at 19:04
  • 1
    @AlessandroRonchi. Posting back. I cleaned up the _cl tables and nothing bad happened. Nothing good either, but I'm ok with it – Marius Feb 27 at 9:36
3

Main question:

It's ok to delete these so long as you have other means to update the products not yet processed i.e. assuming you're going to manually ('update on save') or programmatically update these.

If you are deleting ID's lower than the version in the view_state table then you're basically removing the entries from being processed next time the cron runs (and specifically when the job indexer_update_all_views runs, which is every minute), this does not affect anything in regards 'update on save' indexes as this data is no longer used.

When you switch an indexer to 'update on save' the MySQL triggers are actually removed for that indexer/view so you should see that in your case, stock updates are no longer triggering inserts of data into the cataloginventory_stock_cl table and the mode for cataloginventory_stock in mview_state should be disabled.

Bonus question

As Alessandro said in the comment of the other answer, if you use 'update on save' there is zero risk. If you plan on continuing to use 'update on schedule' there is also zero risk so long as you DO NOT change the auto increment ID of the *_cl table(s)*.

*If you did you'd also need to reset the version_id in the mview_state table

The vital part of the *_cl tables is the auto increment, once a row is processed the actual column data is fairly irrelevant to be honest, before that the entity_id is obviously key in order to update the relevant entities index.

Magento take the same view (plus to keep table size small/manageable) and the reason I say there is zero risk is because at midnight every day a cron job indexer_clean_all_changelogs runs and actually clears all the processed changelog rows from *_cl tables - this eventually calls Magento\Framework\Mview\View\Changelog::clear() where the last version_id is passed and all rows equal or lower to the ID in *_cl are deleted.

Therefore if you were to clear these out programatically I'd recommend hooking into this method (via it's interface).

Summary

At the end of the day, even if you do manage to mess something up (as long as you don't damage the table structure), you can just set all indexes to run on save, reset them all, refresh them all and then turn them back to schedule to ensure all data is fully up to date - this is a handy chain of commands to help automate this:

bin/magento indexer:set-mode realtime && bin/magento indexer:reset && bin/magento indexer:reindex && bin/magento indexer:set-mode schedule

Or if there are issues with the version_id not being in sync between mview_state and *_cl then you many need to manually re-align them (i.e. make sure version_id in view_state is level/below the next auto increment ID in the *_cl table - obviously this is far from ideal and not recommended, this is purely at a disaster recovery stage!

  • Thanks for the very detailed answers. In the meantime I actually deleted all the records from the _cl tables (indexes are set on update on save) and the universe didn't implode. – Marius Feb 27 at 9:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.