MySQL database triggers not set up

You would suspect database triggers not being set up, if after adding or changing an entity (product, category, target rule, and so on) - no records are added to the corresponding change log table.

Solution

WARNING
We strongly recommend creating a database backup before performing any manipulations, and avoiding them during high site load periods.

Avoid change log tables being oversized

Ensure that the indexer_update_all_views cron job is always successfully completed.

You can use the following SQL query to get all failed instances of the indexer_update_all_views cron job:

select * from cron_schedule where job_code = "indexer_update_all_views" and status
  <> "success" and status <> "pending";

Or you can check its status in the logs by searching for the indexer_update_all_views entries:

  • <install_directory>/var/log/cron.log - for versions 2.3.1+ and 2.2.8+
  • <install_directory>/var/log/system.log - for earlier versions

Re-set MySQL table triggers

To set up the missing MySQL table triggers, you need to re-set the indexer mode:

  1. Switch to ‘On Save’.
  2. Switch back to ‘On Schedule’.

Use the following command to perform this operation.

WARNING
Before switching indexer modes, we recommend putting your website in maintenance mode and disable cron jobs to avoid database locks.
php bin/magento indexer:set-mode {realtime|schedule} [indexerName]
INFO
The indexers-related database triggers are added when the indexer mode is set to schedule and removed when the indexer mode is set to realtime. If the triggers are missing from your database while the indexers are set to schedule, change the indexers to realtime and then change them back to schedule. This resets the triggers.
Previous pageDatabase upload loses connection to MySQL
Next pageDeadlocks in MySQL

Commerce


B2B Reimagined: Transforming Go-to-Market Strategies for Profitable Growth

Online | Strategy Keynote | General Audience

B2B brands are facing a digital revolution. Buyers expect hyper-relevant content and self-service, while internally AI is transforming...

Wed, Mar 19, 1:00 PM PDT (8:00 PM UTC)

Register

Elevate and Empower Teams with Agentic AI for Exceptional Experiences

Online | Strategy Keynote | General Audience

Elevate and empower your CX teams with AI that transforms creativity, personalization, and productivity. Discover how Adobe is...

Tue, Mar 18, 1:00 PM PDT (8:00 PM UTC)

Register

Connect with Experience League at Summit!

Get front-row access to top sessions, hands-on activities, and networking—wherever you are!

Learn more