Deleting values from `_cl` tables
The risk is minimal. You can restore all the data /consistency by the run of full indexation
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 themview_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!