The replication coordinator cannot queue a relay-log event for a worker thread because the event size exceeds the slave_pending_jobs_size_max limit.
MySQL error 1864 ER_MTS_EVENT_BIGGER_PENDING_JOBS_SIZE_MAX means a relay-log event is larger than the slave_pending_jobs_size_max setting, so the coordinator thread refuses to schedule it. Lower the event size or raise slave_pending_jobs_size_max on the replica to resolve the error.
ER_MTS_EVENT_BIGGER_PENDING_JOBS_SIZE_MAX
MySQL throws error 1864 when multithreaded replication (MTS) attempts to dispatch a relay-log event to a worker thread but the event size exceeds the slave_pending_jobs_size_max limit. The coordinator thread blocks the event, halting replication until the problem is resolved.
The error appears only on replicas using parallel workers (slave_parallel_workers > 0) and was introduced in MySQL 5.7.2. Ignoring it leaves the replica stuck with a growing relay log, so immediate action is required.
The root cause is a single transaction or binary-log event that is larger than the replica’s permitted pending job queue. On busy systems, large bulk inserts, ALTER TABLE operations, or huge UPDATE statements often generate events big enough to break the limit.
The default slave_pending_jobs_size_max is 16MB. Large DDL, heavy LOAD DATA INFILE, or huge row-based replication events can easily surpass it, especially after row compression is disabled.
Raise slave_pending_jobs_size_max so the event can be queued, or replay the transaction in smaller chunks. Restarting the SQL thread alone does not help; the setting must be changed and the replica restarted or SET persistified.
If increasing the variable is impossible, skip or split the offending transaction on the primary, then rebuild or resync the replica to regain consistency.
During nightly ETL, a 500MB LOAD DATA INFILE runs on the primary, producing a giant replication event. Increasing slave_pending_jobs_size_max to 1G on the replica lets the worker accept the event and continue.
An ALTER TABLE ... ALGORITHM=COPY generates a massive temporary table write in row-based format. Converting to INPLACE or using pt-online-schema-change reduces event size below the default limit.
Keep slave_pending_jobs_size_max slightly larger than the biggest expected binary-log event. Review max_allowed_packet and bulk-load patterns to estimate peak size, then set the replica variable accordingly.
Favor online DDL methods, chunked inserts, and batched updates to control event size. Monitor replica error logs and performance_schema.replication_applier_status_by_worker for early warnings.
Error 1756 (ER_SLAVE_FATAL_ERROR) can follow if the SQL thread stops due to repeated 1864 errors. Once 1864 is cleared, restart the SQL thread to avoid 1756.
Error 1594 (ER_SLAVE_RELAY_LOG_READ_FAILURE) appears when the relay log grows without being applied. Clearing 1864 prevents this secondary failure.
LOAD DATA INFILE or large INSERT ... SELECT statements create single events larger than 16MB.
ALTER TABLE with ALGORITHM=COPY copies the table row by row, producing a huge row-based event.
Row-based logging records every changed row, quickly exceeding the default limit.
Replicas often retain the default 16MB limit even when workloads grow far beyond it.
Triggered when the SQL thread halts after repeated 1864 errors. Clearing 1864 and restarting the SQL thread resolves it.
Occurs if the relay log cannot be read because it has grown too large while the SQL thread is blocked by 1864. Fixing 1864 prevents this failure.
Indicates an event cannot run in parallel mode. Reducing event size or disabling parallel replication can help.
Yes. Use SET PERSIST or SET GLOBAL to adjust the value, then stop and start only the SQL thread, not the entire server.
Higher limits use more memory per worker, but modern servers can handle hundreds of megabytes easily. Monitor memory usage after changes.
The check is enforced only when slave_parallel_workers > 0, so the error surfaces once multithreaded replication is enabled.
Galaxy highlights replication variables and surfaces alerting queries so engineers can spot oversized events and adjust slave_pending_jobs_size_max proactively.
.avif)
.avif){kind=logo}
