Those tables are related to MView from Magento 2. It's the indexer that runs in cron every minute by default. MView is used if you set the indexer mode to 'Index by Schedule'.
Magento checks if the _cl
tables exist, if not, are created every time the indexer/MView runs.
The code related to these table creation is in \Magento\Framework\Mview\View::subscribe()
.
The insertions in these tables are done by MySQL triggers.
All triggers in Magento 2 database are used to update _cl
tables: SHOW TRIGGERS
.
If admin changes the price of the product than it will insert rows in some of the tables. When cron runs after 1 minute, it will trigger indexing on those entities for each involved indexer. After each indexer run, if there are entities affected/cache tags (catalog_product_123), it triggers full page cache/Varnish type cleaning.
_cl
tables rows are deleted if are old, like every hour. There is a cron job for this.
// Magento_Indexer/etc/crontab.xml
<job name="indexer_clean_all_changelogs" instance="Magento\Indexer\Cron\ClearChangelog" method="execute">
<schedule>0 * * * *</schedule>
</job>
Check this:
what is mview in magento2?
EDIT
Also reset version_id to 0
in mview_state
table. Otherwise it won't reindex until ids in *_cl
tables reach the version_id
in mview_state
.
There may be more *_cl
tables than this:
TRUNCATE TABLE `cataloginventory_stock_cl`;
TRUNCATE TABLE `catalogrule_product_cl`;
TRUNCATE TABLE `catalogrule_rule_cl`;
TRUNCATE TABLE `catalogsearch_fulltext_cl`;
TRUNCATE TABLE `catalog_category_product_cl`;
TRUNCATE TABLE `catalog_product_attribute_cl`;
TRUNCATE TABLE `catalog_product_category_cl`;
TRUNCATE TABLE `catalog_product_price_cl`;
TRUNCATE TABLE `customer_dummy_cl`;
TRUNCATE TABLE `design_config_dummy_cl`;
TRUNCATE TABLE `salesrule_rule_cl`;
TRUNCATE TABLE `targetrule_product_rule_cl`;
TRUNCATE TABLE `targetrule_rule_product_cl`;
UPDATE `mview_state` SET `version_id` = '0';