View Status of EE Mview Changelog Tables

Posted Over 6 years ago. Visible to the public.

This SQL:

select emm.view_name,
    emm.status,
    emm.version_id as metadata_version,
    cl_max_versions.max_version as cl_version,
    (cl_max_versions.max_version - emm.version_id) as behind
from enterprise_mview_metadata emm
left join (
    select 'enterprise_url_rewrite_redirect' as view_name, max(version_id) as max_version from enterprise_url_rewrite_redirect_cl union
    select 'cataloginventory_stock_status_view' as view_name, max(version_id) as max_version from cataloginventory_stock_status_cl union
    select 'enterprise_url_rewrite_category' as view_name, max(version_id) as max_version from enterprise_url_rewrite_category_cl union
    select 'enterprise_url_rewrite_product' as view_name, max(version_id) as max_version from enterprise_url_rewrite_product_cl union
    select 'catalog_category_product_view' as view_name, max(version_id) as max_version from catalog_category_product_index_cl union
    select 'catalog_category_product_cat_view' as view_name, max(version_id) as max_version from catalog_category_product_cat_cl union
    select 'catalog_product_index_price_view' as view_name, max(version_id) as max_version from catalog_product_index_price_cl union
    select 'catalog_category_view' as view_name, max(version_id) as max_version from catalog_category_flat_cl union
    select 'catalog_product_view' as view_name, max(version_id) as max_version from catalog_product_flat_cl union
    select 'catalogsearch_fulltext_cl' as view_name, max(version_id) as max_version from catalogsearch_fulltext_cl
) cl_max_versions on emm.view_name = cl_max_versions.view_name;

Gives this output:

+------------------------------------+--------+------------------+------------+--------+
| view_name                          | status | metadata_version | cl_version | behind |
+------------------------------------+--------+------------------+------------+--------+
| enterprise_url_rewrite_redirect    |      1 |              914 |        914 |      0 |
| cataloginventory_stock_status_view |      1 |               11 |         12 |      1 |
| enterprise_url_rewrite_category    |      1 |             8031 |       8031 |      0 |
| enterprise_url_rewrite_product     |      1 |            15952 |      15952 |      0 |
| catalog_category_product_view      |      1 |         45644239 |   45644239 |      0 |
| catalog_category_product_cat_view  |      1 |            10572 |      10572 |      0 |
| catalog_product_index_price_view   |      1 |          3340897 |    3340898 |      1 |
| catalog_category_view              |      1 |            18158 |      18158 |      0 |
| catalog_product_view               |      1 |          1455636 |    1455636 |      0 |
| catalogsearch_fulltext_cl          |      1 |         47635424 |   47635425 |      1 |
+------------------------------------+--------+------------------+------------+--------+
Mike Whitby
Posted by Mike Whitby to Magento (2017-08-11 15:21)