503 error on store front catalog pages with "Integrity constraint violation" in logs

This article provides a patch for the known Magento Commerce Cloud 2.2.0 issue related to store front catalog pages being inaccessible, with the error message similar to the following in log: "Integrity constraint violation: 1062 Duplicate entry '%entry%' for key 'PRIMARY', query was: INSERT INTO `search_tmp_%number%".

Issue

Store front catalog pages become inaccessible unexpectedly. The error log has an error description similar to the following: "Integrity constraint violation: 1062 Duplicate entry '%entry%' for key 'PRIMARY', query was: INSERT INTO `search_tmp_%number%".

The issue is related to searching and caused by the existence of the outdated index along with the new one after reindex.

Solution

To fix the problem, you need to remove outdated indexes from elasticsearch and apply the patch to prevent them appearing.

To remove the outdated indexes, you need to find the them in the database and then use the following command:

curl -X DELETE %elasticsearch_domain%:%elasticsearch_port%/%product_id%_v%outdated_version%

Example:

curl -X DELETE 127.0.0.1:9200/magento2_product_8_v332

Patch

The patches are attached to this article. To download a patch, scroll down to the end of the article and click the required file name, or click one the following links:

Download MDVA-9590_EE_2.2.0_COMPOSER_v2.patch

Download MDVA-13203_EE_2.2.4_V1_COMPOSER.patch

Compatible Magento versions

The patches were created for the following editions and versions:

• Magento Commerce Cloud 2.2.0 (MDVA-9590_EE_2.2.0_COMPOSER_v2.patch)
• Magento Commerce Cloud 2.2.4 (MDVA-13203_EE_2.2.4_V1_COMPOSER.patch)

The MDVA-9590_EE_2.2.0_COMPOSER_v2 patch is also compatible (but might not solve the issue) with the following Magento versions and editions:

• Magento Commerce Cloud, Magento Commerce
• 2.2.1-2.2.3

The MDVA-13203_EE_2.2.4_V1_COMPOSER patch is also compatible (but might not solve the issue) with the following Magento versions and editions:

• Magento Commerce Cloud, Magento Commerce
• 2.0.X, 2.1.X

How to apply the patch

We strongly recommend applying and testing the patch on the Integration/Staging environment, before applying it Production. We also recommend to have a recent backup before any manipulations.

How to apply this patch for Magento Commerce Cloud

1. If you do not have a directory named m2-hotfixes in the project root, please create one.
2. Copy the %patch_name%.patch file(s) to the m2-hotfixes directory.

3. Add, commit, and push your code changes:

   git add -A && git commit -m "Apply the $patch_name$ patch" && git push origin

How to apply this patch for Magento Commerce

1. Upload the patch to your Magento root directory.
2. Run the following SSH command:

   patch -p1 < %patch_name%.patch

   (If the above command does not work, try using -p2 instead of -p1)
3. For the changes to be reflected, refresh the cache in the Admin under System > Cache Management.

Useful links

• Apply custom patches to Magento Commerce (Cloud)
• Log files location for Magento Commerce Cloud Starter plan
• Log files location for Magento Commerce Cloud Pro plan
• Log files location for Magento Commerce

Attached Files