Skip to content

MySQL Triggers in Stacksync

This document explains how MySQL triggers are created and named by the connectors service.

Where it happens

  • Code reference:
  • connectors/apps/mysql/switch_app.py
  • connectors/apps/mysql/utils/utils.py

When a base is switched to ON (CDC via triggers), Stacksync: 1. Creates (if needed) a logging database/schema used to store change history rows. 2. Creates a change-history table per synced table. 3. Drops any existing triggers for that table. 4. Creates three triggers (INSERT, UPDATE, DELETE) on the source table that write JSON snapshots into the change-history table.

Change history table name

  • Built by get_mysql_change_history_table_name(app_id, internal_table_id):
  • change_history_{appIdNoDashes}_{internal_table_id}

Trigger names

  • Prefix from get_mysql_trigger_name_prefix(app_id, internal_table_id):
  • stacksync_internal_trigger_{appIdNoDashes}_{internal_table_id}
  • Suffixes per action:
  • Insert: {prefix}_I
  • Update: {prefix}_U
  • Delete: {prefix}_D
  • MySQL trigger name limit is 64 chars; the code ensures the prefix is compact enough.

Object name quoting

  • Identifiers are wrapped with backticks using format_mysql_object_name(name) to avoid reserved-word issues.

JSON payload written by triggers

  • Each trigger inserts or upserts a row into the change-history table with columns:
  • primary_key (uses table primary key write/read id)
  • timestamp (defaults to CURRENT_TIMESTAMP)
  • operation (INSERT/UPDATE/DELETE)
  • new_val (JSON_OBJECT of all tracked columns)
  • is_processed (boolean flag used by downstream readers)

Maintenance

  • On OFF mode, the service drops the three triggers and the corresponding change-history table; if the logging schema becomes empty, it is dropped.
  • Postgres triggers CDC: connectors/apps/postgres/triggers_cdc/utils/triggers_cdc_setup.py
  • BigQuery events handling: connectors/apps/bigquery/switch_app.py