Magento2: Could not change indexers mode

[gplanchat]

Steps to reproduce

1. Install Magento 2 EE on Amazon EC2, with MariaDB on Amazon RDS
2. Go to index management
3. Select all indexers

4. Execute action "Update by schedule"

   Expected result

5. Mode changes for all 11 indexers

   Actual result

6. Error message appears: "We couldn't change indexer(s)' mode because of an error.".

[2016-04-11 13:33:24] main.CRITICAL: Exception message: Table catalog_category_product_cl already exist
Trace: #0 /var/www/.../vendor/magento/framework/Mview/View.php(175): Magento\Framework\Mview\View\Changelog->create()
#1 /var/www/.../vendor/magento/module-indexer/Model/Indexer.php(288): Magento\Framework\Mview\View->subscribe()
#2 /var/www/.../vendor/magento/module-indexer/Controller/Adminhtml/Indexer/MassChangelog.php(26): Magento\Indexer\Model\Indexer->setScheduled(true)
#3 /var/www/.../var/generation/Magento/Indexer/Controller/Adminhtml/Indexer/MassChangelog/Interceptor.php(25): Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog->execute()
#4 /var/www/.../vendor/magento/framework/App/Action/Action.php(102): Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor->execute()
#5 /var/www/.../vendor/magento/module-backend/App/AbstractAction.php(226): Magento\Framework\App\Action\Action->dispatch(Object(Magento\Framework\App\Request\Http))
#6 [internal function]: Magento\Backend\App\AbstractAction->dispatch(Object(Magento\Framework\App\Request\Http))
#7 /var/www/.../vendor/magento/framework/Interception/Interceptor.php(74): call_user_func_array(Array, Array)
#8 /var/www/.../vendor/magento/framework/Interception/Chain/Chain.php(70): Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor->___callParent('dispatch', Array)
#9 /var/www/.../vendor/magento/framework/Interception/Chain/Chain.php(63): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Indexer...', 'dispatch', Object(Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor), Array, 'adminAuthentica...')
#10 /var/www/.../vendor/magento/module-backend/App/Action/Plugin/Authentication.php(143): Magento\Framework\Interception\Chain\Chain->Magento\Framework\Interception\Chain\{closure}(Object(Magento\Framework\App\Request\Http))
#11 [internal function]: Magento\Backend\App\Action\Plugin\Authentication->aroundDispatch(Object(Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#12 /var/www/.../vendor/magento/framework/Interception/Chain/Chain.php(67): call_user_func_array(Array, Array)
#13 /var/www/.../vendor/magento/framework/Interception/Chain/Chain.php(63): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Indexer...', 'dispatch', Object(Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor), Array, 'log')
#14 /var/www/.../vendor/magento/module-logging/App/Action/Plugin/Log.php(69): Magento\Framework\Interception\Chain\Chain->Magento\Framework\Interception\Chain\{closure}(Object(Magento\Framework\App\Request\Http))
#15 [internal function]: Magento\Logging\App\Action\Plugin\Log->aroundDispatch(Object(Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#16 /var/www/.../vendor/magento/framework/Interception/Chain/Chain.php(67): call_user_func_array(Array, Array)
#17 /var/www/.../vendor/magento/framework/Interception/Chain/Chain.php(63): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Indexer...', 'dispatch', Object(Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor), Array, 'designLoader')
#18 /var/www/.../vendor/magento/framework/App/Action/Plugin/Design.php(39): Magento\Framework\Interception\Chain\Chain->Magento\Framework\Interception\Chain\{closure}(Object(Magento\Framework\App\Request\Http))
#19 [internal function]: Magento\Framework\App\Action\Plugin\Design->aroundDispatch(Object(Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#20 /var/www/.../vendor/magento/framework/Interception/Chain/Chain.php(67): call_user_func_array(Array, Array)
#21 /var/www/.../vendor/magento/framework/Interception/Interceptor.php(136): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Indexer...', 'dispatch', Object(Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor), Array, 'adminMassaction...')
#22 /var/www/.../vendor/magento/module-backend/App/Action/Plugin/MassactionKey.php(33): Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Request\Http))
#23 [internal function]: Magento\Backend\App\Action\Plugin\MassactionKey->aroundDispatch(Object(Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#24 /var/www/.../vendor/magento/framework/Interception/Interceptor.php(140): call_user_func_array(Array, Array)
#25 /var/www/.../var/generation/Magento/Indexer/Controller/Adminhtml/Indexer/MassChangelog/Interceptor.php(40): Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor->___callPlugins('dispatch', Array, Array)
#26 /var/www/.../vendor/magento/framework/App/FrontController.php(55): Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor->dispatch(Object(Magento\Framework\App\Request\Http))
#27 [internal function]: Magento\Framework\App\FrontController->dispatch(Object(Magento\Framework\App\Request\Http))
#28 /var/www/.../vendor/magento/framework/Interception/Interceptor.php(74): call_user_func_array(Array, Array)
#29 /var/www/.../vendor/magento/framework/Interception/Chain/Chain.php(70): Magento\Framework\App\FrontController\Interceptor->___callParent('dispatch', Array)
#30 /var/www/.../vendor/magento/framework/Interception/Chain/Chain.php(63): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Framewo...', 'dispatch', Object(Magento\Framework\App\FrontController\Interceptor), Array, 'install')
#31 /var/www/.../vendor/magento/framework/Module/Plugin/DbStatusValidator.php(69): Magento\Framework\Interception\Chain\Chain->Magento\Framework\Interception\Chain\{closure}(Object(Magento\Framework\App\Request\Http))
#32 [internal function]: Magento\Framework\Module\Plugin\DbStatusValidator->aroundDispatch(Object(Magento\Framework\App\FrontController\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#33 /var/www/.../vendor/magento/framework/Interception/Chain/Chain.php(67): call_user_func_array(Array, Array)
#34 /var/www/.../vendor/magento/framework/Interception/Interceptor.php(136): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Framewo...', 'dispatch', Object(Magento\Framework\App\FrontController\Interceptor), Array, 'storeCookieVali...')
#35 /var/www/.../vendor/magento/module-store/Model/Plugin/StoreCookie.php(78): Magento\Framework\App\FrontController\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Request\Http))
#36 [internal function]: Magento\Store\Model\Plugin\StoreCookie->aroundDispatch(Object(Magento\Framework\App\FrontController\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#37 /var/www/.../vendor/magento/framework/Interception/Interceptor.php(140): call_user_func_array(Array, Array)
#38 /var/www/.../var/generation/Magento/Framework/App/FrontController/Interceptor.php(26): Magento\Framework\App\FrontController\Interceptor->___callPlugins('dispatch', Array, Array)
#39 /var/www/.../vendor/magento/framework/App/Http.php(115): Magento\Framework\App\FrontController\Interceptor->dispatch(Object(Magento\Framework\App\Request\Http))
#40 /var/www/.../vendor/magento/framework/App/Bootstrap.php(258): Magento\Framework\App\Http->launch()
#41 /var/www/.../pub/index.php(37): Magento\Framework\App\Bootstrap->run(Object(Magento\Framework\App\Http))
#42 {main} [] []

And after manually deleting table catalog_category_product_cl :

[2016-04-11 13:34:33] main.CRITICAL: Exception message: 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`
Trace: #0 /var/www/.../vendor/magento/framework/DB/Statement/Pdo/Mysql.php(95): Zend_Db_Statement_Pdo->_execute(Array)
#1 /var/www/.../vendor/magento/zendframework1/library/Zend/Db/Statement.php(303): Magento\Framework\DB\Statement\Pdo\Mysql->_execute(Array)
#2 /var/www/.../vendor/magento/zendframework1/library/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /var/www/.../vendor/magento/zendframework1/library/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('DROP TRIGGER IF...', Array)
#4 /var/www/.../vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(444): Zend_Db_Adapter_Pdo_Abstract->query('DROP TRIGGER IF...', Array)
#5 /var/www/.../vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(499): Magento\Framework\DB\Adapter\Pdo\Mysql->_query('DROP TRIGGER IF...', Array)
#6 /var/www/.../vendor/magento/framework/DB/Adapter/Pdo/Mysql.php(3749): Magento\Framework\DB\Adapter\Pdo\Mysql->query('DROP TRIGGER IF...')
#7 /var/www/.../vendor/magento/framework/Mview/View/Subscription.php(109): Magento\Framework\DB\Adapter\Pdo\Mysql->dropTrigger('trg_catalog_cat...')
#8 /var/www/.../vendor/magento/framework/Mview/View.php(187): Magento\Framework\Mview\View\Subscription->create()
#9 /var/www/.../vendor/magento/module-indexer/Model/Indexer.php(288): Magento\Framework\Mview\View->subscribe()
#10 /var/www/.../vendor/magento/module-indexer/Controller/Adminhtml/Indexer/MassChangelog.php(26): Magento\Indexer\Model\Indexer->setScheduled(true)
#11 /var/www/.../var/generation/Magento/Indexer/Controller/Adminhtml/Indexer/MassChangelog/Interceptor.php(25): Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog->execute()
#12 /var/www/.../vendor/magento/framework/App/Action/Action.php(102): Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor->execute()
#13 /var/www/.../vendor/magento/module-backend/App/AbstractAction.php(226): Magento\Framework\App\Action\Action->dispatch(Object(Magento\Framework\App\Request\Http))
#14 [internal function]: Magento\Backend\App\AbstractAction->dispatch(Object(Magento\Framework\App\Request\Http))
#15 /var/www/.../vendor/magento/framework/Interception/Interceptor.php(74): call_user_func_array(Array, Array)
#16 /var/www/.../vendor/magento/framework/Interception/Chain/Chain.php(70): Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor->___callParent('dispatch', Array)
#17 /var/www/.../vendor/magento/framework/Interception/Chain/Chain.php(63): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Indexer...', 'dispatch', Object(Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor), Array, 'adminAuthentica...')
#18 /var/www/.../vendor/magento/module-backend/App/Action/Plugin/Authentication.php(143): Magento\Framework\Interception\Chain\Chain->Magento\Framework\Interception\Chain\{closure}(Object(Magento\Framework\App\Request\Http))
#19 [internal function]: Magento\Backend\App\Action\Plugin\Authentication->aroundDispatch(Object(Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#20 /var/www/.../vendor/magento/framework/Interception/Chain/Chain.php(67): call_user_func_array(Array, Array)
#21 /var/www/.../vendor/magento/framework/Interception/Chain/Chain.php(63): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Indexer...', 'dispatch', Object(Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor), Array, 'log')
#22 /var/www/.../vendor/magento/module-logging/App/Action/Plugin/Log.php(69): Magento\Framework\Interception\Chain\Chain->Magento\Framework\Interception\Chain\{closure}(Object(Magento\Framework\App\Request\Http))
#23 [internal function]: Magento\Logging\App\Action\Plugin\Log->aroundDispatch(Object(Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#24 /var/www/.../vendor/magento/framework/Interception/Chain/Chain.php(67): call_user_func_array(Array, Array)
#25 /var/www/.../vendor/magento/framework/Interception/Chain/Chain.php(63): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Indexer...', 'dispatch', Object(Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor), Array, 'designLoader')
#26 /var/www/.../vendor/magento/framework/App/Action/Plugin/Design.php(39): Magento\Framework\Interception\Chain\Chain->Magento\Framework\Interception\Chain\{closure}(Object(Magento\Framework\App\Request\Http))
#27 [internal function]: Magento\Framework\App\Action\Plugin\Design->aroundDispatch(Object(Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#28 /var/www/.../vendor/magento/framework/Interception/Chain/Chain.php(67): call_user_func_array(Array, Array)
#29 /var/www/.../vendor/magento/framework/Interception/Interceptor.php(136): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Indexer...', 'dispatch', Object(Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor), Array, 'adminMassaction...')
#30 /var/www/.../vendor/magento/module-backend/App/Action/Plugin/MassactionKey.php(33): Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Request\Http))
#31 [internal function]: Magento\Backend\App\Action\Plugin\MassactionKey->aroundDispatch(Object(Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#32 /var/www/.../vendor/magento/framework/Interception/Interceptor.php(140): call_user_func_array(Array, Array)
#33 /var/www/.../var/generation/Magento/Indexer/Controller/Adminhtml/Indexer/MassChangelog/Interceptor.php(40): Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor->___callPlugins('dispatch', Array, Array)
#34 /var/www/.../vendor/magento/framework/App/FrontController.php(55): Magento\Indexer\Controller\Adminhtml\Indexer\MassChangelog\Interceptor->dispatch(Object(Magento\Framework\App\Request\Http))
#35 [internal function]: Magento\Framework\App\FrontController->dispatch(Object(Magento\Framework\App\Request\Http))
#36 /var/www/.../vendor/magento/framework/Interception/Interceptor.php(74): call_user_func_array(Array, Array)
#37 /var/www/.../vendor/magento/framework/Interception/Chain/Chain.php(70): Magento\Framework\App\FrontController\Interceptor->___callParent('dispatch', Array)
#38 /var/www/.../vendor/magento/framework/Interception/Chain/Chain.php(63): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Framewo...', 'dispatch', Object(Magento\Framework\App\FrontController\Interceptor), Array, 'install')
#39 /var/www/.../vendor/magento/framework/Module/Plugin/DbStatusValidator.php(69): Magento\Framework\Interception\Chain\Chain->Magento\Framework\Interception\Chain\{closure}(Object(Magento\Framework\App\Request\Http))
#40 [internal function]: Magento\Framework\Module\Plugin\DbStatusValidator->aroundDispatch(Object(Magento\Framework\App\FrontController\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#41 /var/www/.../vendor/magento/framework/Interception/Chain/Chain.php(67): call_user_func_array(Array, Array)
#42 /var/www/.../vendor/magento/framework/Interception/Interceptor.php(136): Magento\Framework\Interception\Chain\Chain->invokeNext('Magento\\Framewo...', 'dispatch', Object(Magento\Framework\App\FrontController\Interceptor), Array, 'storeCookieVali...')
#43 /var/www/.../vendor/magento/module-store/Model/Plugin/StoreCookie.php(78): Magento\Framework\App\FrontController\Interceptor->Magento\Framework\Interception\{closure}(Object(Magento\Framework\App\Request\Http))
#44 [internal function]: Magento\Store\Model\Plugin\StoreCookie->aroundDispatch(Object(Magento\Framework\App\FrontController\Interceptor), Object(Closure), Object(Magento\Framework\App\Request\Http))
#45 /var/www/.../vendor/magento/framework/Interception/Interceptor.php(140): call_user_func_array(Array, Array)
#46 /var/www/.../var/generation/Magento/Framework/App/FrontController/Interceptor.php(26): Magento\Framework\App\FrontController\Interceptor->___callPlugins('dispatch', Array, Array)
#47 /var/www/.../vendor/magento/framework/App/Http.php(115): Magento\Framework\App\FrontController\Interceptor->dispatch(Object(Magento\Framework\App\Request\Http))
#48 /var/www/.../vendor/magento/framework/App/Bootstrap.php(258): Magento\Framework\App\Http->launch()
#49 /var/www/.../pub/index.php(37): Magento\Framework\App\Bootstrap->run(Object(Magento\Framework\App\Http))
#50 {main} [] []

[macknik]

Also occurring on CE with MySQL db on Amazon RDS

[vzabaznov]

Hi @gplanchat, please check this two articles

• http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html
• https://forums.aws.amazon.com/message.jspa?messageID=183618

Write if doesn't help

[piotrekkaminski]

@gplanchat do you still have this error? Have you tried applying the solutions from article above? if you still have this issue. please open a new ticket and reference this one.

[gplanchat]

Hello @piotrekkaminski

We are currently investigating, I'll give you feedback when we will have finished.

Thank you

[iandd0824]

Came across the same issue today and find the solution at the following link.

https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-functions/

After I applied the custom parameter group with log_bin_trust_function_creators = 1, no more error to change the index mode.

[Shlyocuk]

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