ERROR: We couldn't change indexer(s)' mode because of an error

Question

I'm facing this error when trying to change indexer mode from 'UPDATE ON SAVE' to 'SCHEDULED'

Edit: We have already checked the logs, and seems to be nothing there.

Answer 1

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 to your Problem:

1. Grant SUPER privileges 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 for SUPER privileges to change triggers (if I understood correctly)