7

After doing a diff on the tables from a production server to a local server I have noticed that new tables have been created all ending with _cl. These are:

#        TABLE: catalog_category_product_cl
#        TABLE: cataloginventory_stock_cl
#        TABLE: catalog_product_price_cl
#        TABLE: catalog_product_category_cl
#        TABLE: customer_dummy_cl
#        TABLE: catalogrule_product_cl
#        TABLE: catalog_product_attribute_cl
#        TABLE: catalogsearch_fulltext_cl
#        TABLE: design_config_dummy_cl
#        TABLE: catalogrule_rule_cl

Seeing as these are all related to indexes within Magento, I assume that they are "change Logs" or suchlike (if this could be confirmed I would appreciated it!).

I have a two part question around this, how are these tables populated, and how often / how are these tables cleaned?

Any help on this would be greatly appreciated.

20

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';
  • We are working on Magento 1.14 EE and have same type of tables, those span in 9GB, Can we delete it. I just read this: maxchadwick.xyz/blog/magento-enterprise-index-lesson-329 I'm not sure what to do about these table as we need to backup and restore db on localhost. – Chetan Sharma May 26 '17 at 20:24
  • IN Magento 2 you can truncate *_cl tables and also reset version_id in mview_state table. It should be the same in Magento 1 EE. I don't know where Magento 1 stores the current or last version id. I'll update my answer. – obscure May 26 '17 at 23:10
  • Btw. two tables are missing from your list: catalog_category_flat_cl & catalog_product_flat_cl. Also I was not able to find the following tables in our Magento 2.3.2 installation: salesrule_rule_cl, targetrule_product_rule_cl & targetrule_rule_product_cl. – fritzmg Aug 24 at 9:17

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.