[Indexes] Product 'version_id' lost last 'auro_increment' value after MySQL restart.

[swnsma]

Preconditions

1. MySQL 5.7.21
2. Magento CE/EE (Any version. Checked on 2.1.12 and 2.2.3) with Sample Data.
3. Enabled Flat Tables (BO -> Stores -> Configuration -> Catalog -> Catalog -> Storefront -> Use Flat Catalog Product).
4. Indexer set to 'UPDATE BY SCHEDULE' mode (BO -> System -> Index Management).
5. Initial reindex was done and flats already created.
6. Table catalog_product_attribute_cl is empty.

Steps to reproduce

1. Go to BO.
2. Open product edit form (Catalog -> Products -> Product with id №8).
3. Edit product attribute (e.g. name to 'Test Before Restart MySQL').
4. Press 'Save'.
5. Edit 1-2 more product with changing of any attributes.
6. Check that new records have created in catalog_product_attribute_cl. Remember 'version_id'.
7. Wait (or execute) crons in next order:

• indexer_update_all_views;
• indexer_clean_all_changelogs;

8. Check that catalog_product_attribute_cl empty.
9. Check product on frontend - changes have applied.
10. Restart MySQL.
11. Edit the product. Change name to 'Test After Restart MySQL'
12. Run Cron. E.G. php bin/magento cron:run.
13. Go To Frontend -> Gears -> Bags.
14. Find product with id №8.

Expected result

1. Product has name 'Test After Restart MySQL'.

Actual result

1. Product has name 'Test Before Restart MySQL'.

Additional information

Explanation.
From the MySQL documentation.

InnoDB uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, InnoDB reinitializes the counter for each table for the first INSERT to the table, as described earlier.

As result, in case of MySQL restart while catalog_product_attribute_cl table is empty, auto_increment value will be dropped to the initial value (0), but last value of 'version_id' in mview_state will be the same as before MySQL restart.
In that case any 'changelog' event will not be processed until 'version_id' in catalog_product_attribute_cl will not be greater or equal that version_id in mview_state.

[magento-engcom-team]

Hi @swnsma. Thank you for your report.
The issue has been fixed in #14636 by @ihor-sviziev in 2.3-develop branch
Related commit(s):

• 4d2960874f0ffaed2d1b39c3df63d71c0edf8ab2

The fix will be available with the upcoming 2.3.0 release.

[magento-engcom-team]

Hi @swnsma. Thank you for your report.
The issue has been fixed in #14635 by @ihor-sviziev in 2.2-develop branch
Related commit(s):

• 17b585f3b8b411124d1a8bbbcb9e6d097ca26575

The fix will be available with the upcoming 2.2.5 release.

[magento-engcom-team]

Hi @swnsma. Thank you for your report.
The issue has been fixed in #14471 by @swnsma in 2.1-develop branch
Related commit(s):

• bb91de765d83144b5ff8526bfa446f3534b59c4e

The fix will be available with the upcoming 2.1.15 release.