20. Change events: LISTEN, NOTIFY, WAIT FOR CHANGES

Enterprise, Cluster and Developer editions. In the Express edition, these statements return error 9001.

An application that wants to know what has changed (a stock screen, a dashboard, a cache) does not need to poll the database in a loop: it subscribes to the tables or channels it cares about, then waits on a dedicated connection. Miraj hands it the changes as soon as they are committed:

-- Dedicated application connection
LISTEN TABLE articles COLUMNS (qte, prix);
WAIT FOR CHANGES TIMEOUT 30;

Principles:

• No trigger to write: Miraj itself records the primary key and the modified columns of every row written to a listened table.
• Publication on commit: an event is returned only once the transaction is committed, never after a ROLLBACK. The writes of a single transaction are merged: a row inserted then modified produces a single INSERT, and a row inserted then deleted produces nothing.
• Resuming after a disconnection: each event carries an increasing number. A client that reconnects picks up where it left off.
• A writer never waits for a subscriber: a slow or absent subscriber does not slow down writes. If it falls too far behind, it receives RESYNC and must read everything again.
• No special driver: WAIT FOR CHANGES is an ordinary statement that returns a result set. Any SQL client can use it (Delphi, PHP, graphical tools, miraj-cli). An application that embeds the engine through miraj.dll also has miraj_session_wait_changes (WaitChanges / wait_changes / waitChanges in the wrappers, see 14.9): it waits for at most a given delay and returns at most a given number of rows.

20.1 Subscribing#

LISTEN TABLE [database.]table [COLUMNS (c1, …)] [WHERE condition] [WITH ROW] [FROM n]
LISTEN [CHANNEL] channel [FROM n]
UNLISTEN TABLE [database.]table
UNLISTEN [CHANNEL] channel
UNLISTEN *

• LISTEN TABLE follows the writes to a table: insertions, modifications, deletions, truncations and structure changes. It requires the SELECT privilege on the table, checked at the time of the LISTEN.
  • COLUMNS (…): only the UPDATEs that change at least one of these columns are returned. Insertions and deletions are always returned.
  • WHERE condition: only the rows that satisfy the condition are returned. The condition applies to the columns of the table (the after image, or the before image for a deletion). It cannot contain a subquery, an aggregate or a variable, otherwise error 1235 is returned.
  • WITH ROW: the payload column contains the whole row, as a JSON object.
• LISTEN channel listens to a named channel, fed by NOTIFY (§20.4). It requires no privilege.
• LISTEN returns one row with a single seq column: the session's position in the sequence of events (§20.3).
• A session can have up to 64 subscriptions (change_events_max_listeners); beyond that, error 9008 is returned. A new LISTEN on the same target replaces the previous one.
• UNLISTEN * removes all of the session's subscriptions. Disconnecting and KILL also remove them.
• A table without a primary key can be listened to, with warning 9009: its writes produce only BULK events, without a key.
• Temporary tables are never listened to.

LISTEN, UNLISTEN and WAIT FOR CHANGES are refused in a procedure, a function, a trigger or a scheduled event (error 1314).

20.2 Waiting for changes#

WAIT FOR CHANGES [TIMEOUT seconds] [LIMIT n]

• Returns, in order, the events published since the session's position that match its subscriptions, at most LIMIT of them (1,000 by default), then advances the position: an event that has been returned is not returned again.
• If there is nothing to return, the statement waits for the first event, for at most TIMEOUT seconds (by default @@change_events_wait_timeout, 30 s). Once the delay has elapsed, it returns an empty result.
• TIMEOUT 0 does not block: it collects whatever has arrived and returns immediately.
• Waiting holds no lock: it delays neither writes nor ALTER TABLE.
• KILL QUERY interrupts it (error 1317), as does max_statement_time (error 1969). SHOW PROCESSLIST shows the session in the Waiting for changes state.
• WAIT FOR CHANGES is refused inside an open transaction (error 9006) and without a subscription (error 9007). With autocommit = 0, an implicit transaction that has only read is closed automatically.

Returned columns:

Events:

20.3 Resuming after a disconnection#

Each session has a position: the number of the last event it received. The first LISTEN places it on the last published event, and each WAIT FOR CHANGES advances it.

To resume after a disconnection (application restart, network outage), the client keeps the last seq it processed and resubscribes with FROM:

LISTEN TABLE articles FROM 1790294400000042;
WAIT FOR CHANGES;   -- returns the events that follow 1790294400000042

Events are kept in memory in a queue of bounded size (100,000 events and 64 MiB by default, change_events_buffer_events and change_events_buffer_size). A table remains listened to for change_events_retention (10 minutes by default) after its last subscriber disconnects, so that its changes are found again on reconnection. An explicit UNLISTEN, however, stops tracking immediately.

The session receives a single RESYNC row when resuming is impossible:

• the expected events have left the queue (subscriber too far behind, or absent for longer than the retention);
• the server has restarted since: events are not kept on disk;
• the number comes from another server, for example from another node of the cluster (§20.7).

After RESYNC, reread the data. For a reload without gaps, first note the position:

SELECT MIRAJ_CHANGE_SEQ();          -- for example 1790294400000108
SELECT * FROM articles;             -- full reload
LISTEN TABLE articles FROM 1790294400000108;

Numbers are specific to each server startup: they start from the startup time, in microseconds. A number from a previous startup therefore cannot be mistaken for a number from the current startup.

20.4 Application notifications: NOTIFY#

NOTIFY channel [, 'payload']
SELECT MIRAJ_NOTIFY('channel', 'payload');   -- 1

• NOTIFY sends a notification to the sessions that listen to the channel. The payload is free text of at most 8,000 bytes (change_events_max_payload); beyond that, error 9010 is returned.
• In a transaction, the notification is published only at COMMIT, together with the table changes and under the same commit_seq. A ROLLBACK cancels it. Two identical notifications (same channel, same payload) in a transaction count as one.
• NOTIFY and MIRAJ_NOTIFY() are allowed in procedures, functions and triggers. In a trigger, you write for example SET @n = MIRAJ_NOTIFY('low_stock', NEW.id);. A statement replayed after a write conflict publishes its notification only once.

20.5 Monitoring#

SHOW LISTENERS;
SHOW STATUS LIKE 'Miraj_change_events_%';
SELECT MIRAJ_CHANGE_SEQ();

• SHOW LISTENERS lists the subscriptions: Id, User, Kind, Target, Columns, Filter, With_row, Cursor, Lag (published events the session has not yet received), Waiting and Time. With the PROCESS privilege, it shows all sessions, otherwise those of the user.
• SHOW STATUS: Miraj_change_events_published, _buffered, _buffer_bytes, _evicted, _resyncs, _bulk, _listeners, _waits, _last_seq.

20.6 Settings#

The options set the value at startup; they can also be written, under the same name with underscores, in miraj_config.xml (chapter 11, §11.2). SET GLOBAL then changes them until the next restart. An invalid value prevents the server from starting. In the Express edition, these options are accepted and have no effect.

20.7 On a cluster (Cluster edition)#

A subscriber can connect to the primary or to a secondary: each node publishes the changes of its own tables.

• On the primary, events are published on commit, as on a standalone server.
• On a secondary, they are published when the secondary applies the log received from the primary. It returns the same events as the primary, in the same order, with a commit_seq shared by each transaction; they arrive only with the replication lag (LAG_SECONDS, §12.5.2). Structure changes (SCHEMA) are returned there as well.
• On a secondary, session_id is NULL: the session that wrote is on the primary.
• Filters (COLUMNS, WHERE, WITH ROW) apply in the same way on all nodes.
• Partitioned tables are listened to under their name, on all nodes, whichever partition is written. A row that changes partition changes key: it produces DELETE then INSERT. The change_events_bulk_rows threshold applies per partition. A TRUNCATE of the table, or ALTER TABLE … TRUNCATE PARTITION, produces one BULK (reason TRUNCATE PARTITION) per emptied partition rather than a TRUNCATE.
• NOTIFY stays local to the node that executes it: it does not go through replication. Since writes, and therefore the NOTIFYs of triggers, run on the primary, an application that listens to a channel must subscribe on the primary.
• Each node has its own numbering and its own change_events_… settings. A number obtained on one node is meaningless on another.
• Failover: after a promotion, a client that reconnects to another node with LISTEN … FROM n receives RESYNC and rereads its data. A client already subscribed to the promoted secondary is not affected: the node keeps its numbering and continues to publish.

Recommended strategy: subscribe screens and read caches on a secondary, to offload the primary, and NOTIFY channels on the primary. On RESYNC, reread the data then resubscribe (§20.3).

Differences between nodes, rare in practice:

• With the pessimistic concurrency model (--concurrency pessimistic), a write outside a transaction may be described in finer detail by a secondary than by the primary. The secondary returns a primary-key UPDATE as DELETE + INSERT, cites only the columns actually changed, and details row by row some DELETEs that the primary summarizes as BULK. The multiversion model, the default, produces the same events on all nodes.
• Two nodes configured with different change_events_bulk_rows values do not summarize the same transactions as BULK.
• The rows of a CREATE TABLE … SELECT are returned by a secondary if a subscription already targets that table name (for example left over after a DROP TABLE), but not by the primary.

20.8 Limits#

• Events are kept in memory only: a restart loses them, and the client receives RESYNC.
• A session has a single position for all its subscriptions: if events leave the queue before it reads them, it receives RESYNC, even if those events concerned other tables.
• After RENAME TABLE, the subscription stays on the old name (the reason of the SCHEMA event gives the new one): resubscribe under the new name.
• Pessimistic model, write outside a transaction: a primary-key UPDATE is returned under the new key only; a deletion that has already been compacted produces BULK.
• If the server clock has gone backwards between two startups, an old number may, very rarely, fall within the range of the current startup.