Provide information about how to (re-)create mysql triggers

[hostep]

New topic request

Description

This comes from a request over here: magento/magento2#23023

It's not documented yet how mysql triggers are created in the database. It would be beneficial that this could be found easily so people who are missing triggers in their database know how to (re-)create them.

Content checklist

Not sure where this could go, I think either in the mysql section, or in the indexing section?

So to create triggers in the database, you have to make sure the indexers are set to "Update by Schedule". If they are already set to this mode and you are still missing triggers, you can first set them to "Update on Save" and then change them back to "Update by Schedule".

This change of indexer modes can be done in the backend using System => Index Management.
Or via the command line: bin/magento indexer:set-mode <mode> [<indexer1> <indexer2> ...] where mode is either realtime or schedule.

[jeff-matthews]

Thanks @hostep. What do you think about enhancing the descriptions for both modes in the following topic: https://devdocs.magento.com/guides/v2.3/config-guide/cli/config-cli-subcommands-index.html#config-cli-subcommands-index-reindex?

By adding references to "triggers" and/or "missing triggers" specifically, people looking for this info should be able to find it easily using our site search.

[hostep]

Hi @jeff-matthews

Ah yes, that might be a good idea, but I would put it in the Configure indexers section of that page, since that's where the indexer:set-mode command is being explained.

And then maybe also add a link to that section from Mysql page? In this bullet point for example:

Magento uses MySQL database triggers to improve database access during reindexing. Magento does not support any custom triggers in the Magento database because custom triggers can introduce incompatibilities with future Magento versions.

Where we can mention that triggers are only created when the indexers mode are set to Update by schedule with a link to how to do that.

[jeff-matthews]

Good idea! I think that's a solid plan. I'll move this to the Ready for Development column in the Pull Request Progress project. If someone doesn't pick it up in the next few days, I'll work on it. Thanks!