we are experiencing an issue while trying to update our indexer modes.

$ php bin/magento indexer:set-mode schedule catalog_product_attribute Product EAV indexer process unknown error: Table catalog_product_attribute_cl already exist

$ php bin/magento indexer:set-mode schedule catalog_product_flat catalog_category_flat catalog_category_product catalog_product_category
Product Flat Data indexer process unknown error:

Table catalog_product_flat_cl already exist

Category Flat Data indexer process unknown error:

SQLSTATE[HY000]: General error: 1419 You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable), query was: DROP TRIGGER IF EXISTS `trg_catalog_category_entity_after_insert`

Category Products indexer process unknown error:

Table catalog_category_product_cl already exist

Product Categories indexer process unknown error:

Table catalog_product_category_cl already exist

This is our indexes settings:

$ php bin/magento indexer:show-mode
Customer Grid:                                     Update by Schedule
Product Flat Data:                                 Update on Save
Category Flat Data:                                Update on Save
Category Products:                                 Update on Save
Product Categories:                                Update on Save
Product Price:                                     Update on Save
Product EAV:                                       Update on Save
Stock:                                             Update on Save
Catalog Search:                                    Update on Save
Catalog Rule Product:                              Update on Save
Catalog Product Rule:                              Update on Save

Could you help, please?

  • Decision was to change choise of index managment right there in the base in sql table @ mg_mview_state if u use CPANEl and magento – Shlyocuk Jul 5 '17 at 17:55

the problem is, that your mysql user does not have the permission to drop the trigger, which does the index update after the object is updated.

when you change the indexer mode, this trigger is not needed anymore, since the index is updated by schedule and not by the mysql trigger anymore.

Here is an old answer regarding magento1 ee which basically applies here, since the basic indexer business logic from magento1 ee is used for magento2: DB user requires SUPER privilege to create attributes (SQLSTATE[42000])

I think there are 2 possible solutions for your Problem:

  1. Grant SUPER priviledges to the mysql user on that database (I'm not 100% sure what it does include): https://stackoverflow.com/questions/11946012/how-to-add-super-privileges-to-mysql-database

  2. In your mysql configuration, set log_bin_trust_create_function_creators = 1 which should remove the need of SUPER priviledges to change triggers (if I understood correctly)

|improve this answer|||||
  • it will be security issue . its not recommendable any other option , because in live we can not do this – Pradeep Kumar Jan 23 '17 at 13:01

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.