# MySQL Semi-Synchronous Replication Error: Timeout and Acknowledgment Failures

Your semi-synchronous replication shows errors:

bash
[ERROR] Timeout waiting for reply from semi-sync slave
[ERROR] Semi-sync replication failed for transaction X
[Warning] Semi-sync replication switched to ASYNC mode
[ERROR] Semi-sync slave acknowledgement timeout exceeded

Semi-synchronous replication provides stronger consistency than async, but introduces timeout and acknowledgment complexities.

Understanding Semi-Synchronous Replication

Two modes: - AFTER_SYNC (default in 5.7+): Master waits for slave ACK after syncing to disk, before committing to client - AFTER_COMMIT: Master commits to client, then waits for slave ACK

  1. 1.Flow:
  2. 2.Master writes transaction to binary log
  3. 3.Master syncs binary log to disk
  4. 4.Master waits for at least one slave ACK (timeout applies)
  5. 5.Master commits transaction to storage engine
  6. 6.Master returns success to client

Step 1: Check Semi-Sync Status

```sql -- Check if plugin is installed SHOW PLUGINS;

-- Check semi-sync variables SHOW VARIABLES LIKE 'rpl_semi_sync%';

-- On master SHOW VARIABLES LIKE 'rpl_semi_sync_master%'; SHOW STATUS LIKE 'rpl_semi_sync_master%';

-- On slave SHOW VARIABLES LIKE 'rpl_semi_sync_slave%'; SHOW STATUS LIKE 'rpl_semi_sync_slave%'; ```

Key variables:

bash
rpl_semi_sync_master_enabled = ON/OFF
rpl_semi_sync_master_timeout = 10000 (milliseconds)
rpl_semi_sync_master_wait_for_slave_count = 1
rpl_semi_sync_master_wait_slave_count = 1

Key status:

bash
rpl_semi_sync_master_status = ON/OFF
rpl_semi_sync_master_yes_tx = (successful transactions)
rpl_semi_sync_master_no_tx = (failed/timed out transactions)
rpl_semi_sync_master_clients = (number of semi-sync slaves)

Step 2: Install Semi-Sync Plugin

If plugin not installed:

```sql -- On master INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

-- On slave INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

-- Enable SET GLOBAL rpl_semi_sync_master_enabled = 1; SET GLOBAL rpl_semi_sync_slave_enabled = 1; ```

Or in configuration:

```ini [mysqld] # On master plugin-load = rpl_semi_sync_master=semisync_master.so rpl_semi_sync_master_enabled = 1 rpl_semi_sync_master_timeout = 10000

# On slave plugin-load = rpl_semi_sync_slave=semisync_slave.so rpl_semi_sync_slave_enabled = 1 ```

Step 3: Fix Timeout Errors

Error: Timeout waiting for reply from semi-sync slave

```sql -- Check current timeout SHOW VARIABLES LIKE 'rpl_semi_sync_master_timeout'; -- Default: 10000ms (10 seconds)

-- Increase timeout if slaves are slow SET GLOBAL rpl_semi_sync_master_timeout = 30000; -- 30 seconds

-- Or permanently ```

ini
[mysqld]
rpl_semi_sync_master_timeout = 30000

Check why slaves are slow:

```sql -- On slave, check replication lag SHOW SLAVE STATUS\G Seconds_Behind_Master

-- Check slave performance SHOW PROCESSLIST; SHOW ENGINE INNODB STATUS\G ```

Common causes of slow acknowledgment: - Network latency - Slave disk I/O bottleneck - Slave processing backlog - Large transactions

Step 4: Fix Fallback to Async

When timeout exceeds, semi-sync falls back to async:

```sql -- Check if currently async SHOW STATUS LIKE 'rpl_semi_sync_master_status'; -- OFF means async mode

-- Check how many transactions failed SHOW STATUS LIKE 'rpl_semi_sync_master_no_tx'; ```

To re-enable semi-sync:

```sql -- Master automatically re-enables when slave ACK arrives -- Check if slave is acknowledging

-- On slave, verify semi-sync is enabled SHOW VARIABLES LIKE 'rpl_semi_sync_slave_enabled';

-- Slave must ACK for master to return to semi-sync -- Verify slave is running and connected SHOW SLAVE STATUS\G ```

Force re-enable:

```sql -- On master SET GLOBAL rpl_semi_sync_master_enabled = 1;

-- Force immediate switch SET GLOBAL rpl_semi_sync_master_trace_level = 1; ```

Step 5: Fix Slave Not Acknowledging

Slave doesn't send ACK:

```sql -- On slave, check semi-sync status SHOW STATUS LIKE 'rpl_semi_sync_slave_status'; -- Should be ON

-- Enable if OFF SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- Restart slave to register with master STOP SLAVE IO_THREAD; START SLAVE IO_THREAD; ```

Check master sees the slave:

```sql -- On master SHOW STATUS LIKE 'rpl_semi_sync_master_clients'; -- Should show at least 1

-- If 0, slave not registered -- Check slave connection SHOW SLAVE HOSTS; ```

Step 6: Configure Multiple Slaves for ACK

Require ACK from multiple slaves:

sql
-- Set number of slaves to wait for
SET GLOBAL rpl_semi_sync_master_wait_for_slave_count = 2;
SET GLOBAL rpl_semi_sync_master_wait_slave_count = 2;

This provides stronger consistency but increases latency.

If not enough slaves available:

```sql -- Error: Not enough semi-sync slaves connected

-- Either: -- 1. Reduce required count SET GLOBAL rpl_semi_sync_master_wait_for_slave_count = 1;

-- 2. Ensure slaves are semi-sync enabled -- On each slave: SET GLOBAL rpl_semi_sync_slave_enabled = 1; STOP SLAVE IO_THREAD; START SLAVE IO_THREAD; ```

Step 7: Handle Network Latency

High network latency causes timeout:

```bash # Measure latency ping slave-host -c 10

# Check bandwidth iperf3 -c slave-host

# Check MySQL network settings ```

```sql -- On master, adjust for latency SET GLOBAL rpl_semi_sync_master_timeout = 50000; -- 50 seconds

-- Use TCP tuning ```

ini
[mysqld]
# Network timeout settings
slave_net_timeout = 60
net_read_timeout = 60
net_write_timeout = 60

Step 8: Handle Slave Disk Bottleneck

Slave disk slow to sync relay log:

```sql -- On slave, check I/O SHOW ENGINE INNODB STATUS\G -- Look for I/O section

-- Enable relay log sync SET GLOBAL sync_relay_log = 1; SET GLOBAL sync_relay_log_info = 1; ```

ini
[mysqld]
# On slave
sync_relay_log = 1
sync_relay_log_info = 1
relay_log_recovery = ON

If sync causes too much slowdown, use TABLE repository:

ini
[mysqld]
relay_log_info_repository = TABLE

Step 9: Fix After-Commit vs After-Sync Issues

Wrong replication mode:

```sql -- Check current mode SHOW VARIABLES LIKE 'rpl_semi_sync_master_wait_mode'; -- AFTER_SYNC (recommended) or AFTER_COMMIT

-- AFTER_SYNC is safer (no client sees unreplicated data) SET GLOBAL rpl_semi_sync_master_wait_mode = AFTER_SYNC; ```

ini
[mysqld]
rpl_semi_sync_master_wait_mode = AFTER_SYNC

Step 10: Monitor Semi-Sync Performance

```sql -- Transaction success rate SELECT VARIABLE_VALUE as semi_sync_yes, (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'rpl_semi_sync_master_no_tx') as semi_sync_no, ROUND(VARIABLE_VALUE / (VARIABLE_VALUE + (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'rpl_semi_sync_master_no_tx')) * 100, 2) as success_pct FROM performance_schema.global_status WHERE VARIABLE_NAME = 'rpl_semi_sync_master_yes_tx';

-- Average time waiting for ACK SHOW STATUS LIKE 'rpl_semi_sync_master_avg_trx_wait_time'; SHOW STATUS LIKE 'rpl_semi_sync_master_avg_net_wait_time'; ```

```bash #!/bin/bash # check_semisync.sh

YES_TX=$(mysql -N -e "SHOW STATUS LIKE 'rpl_semi_sync_master_yes_tx'" | awk '{print $2}') NO_TX=$(mysql -N -e "SHOW STATUS LIKE 'rpl_semi_sync_master_no_tx'" | awk '{print $2}') STATUS=$(mysql -N -e "SHOW STATUS LIKE 'rpl_semi_sync_master_status'" | awk '{print $2}')

if [ "$STATUS" != "ON" ]; then echo "Semi-sync is OFF (async mode)" | mail -s "Semi-Sync Alert" admin@example.com fi

if [ "$NO_TX" -gt 0 ]; then echo "$NO_TX transactions fell back to async" | mail -s "Semi-Sync Alert" admin@example.com fi ```

Step 11: Handle Master Failover with Semi-Sync

When master fails, promote a semi-sync slave:

```sql -- On new master (former slave) STOP SLAVE; RESET SLAVE ALL;

-- Enable semi-sync master SET GLOBAL rpl_semi_sync_master_enabled = 1;

-- Ensure binary logging SHOW VARIABLES LIKE 'log_bin';

-- Other slaves connect to new master CHANGE MASTER TO MASTER_HOST = 'new_master', MASTER_AUTO_POSITION = 1; START SLAVE; ```

Step 12: Debug Semi-Sync Issues

Enable tracing:

```sql -- Set trace level SET GLOBAL rpl_semi_sync_master_trace_level = 32;

-- Levels: -- 1 = general (basic info) -- 2 = detail (more info) -- 4 = net wait (network wait details) -- 8 = function (function calls) -- 16 = advanced (detailed flow) -- 32 = trace (very detailed) ```

Check error log for trace messages:

bash
tail -f /var/log/mysql/error.log | grep semi-sync

Step 13: Performance Tuning

Balance consistency vs performance:

```ini [mysqld] # Reasonable timeout (not too short, not too long) rpl_semi_sync_master_timeout = 5000

# Single slave ACK (balance speed and safety) rpl_semi_sync_master_wait_for_slave_count = 1

# After-sync mode (safest) rpl_semi_sync_master_wait_mode = AFTER_SYNC

# On slave, optimize I/O relay_log_info_repository = TABLE sync_relay_log = 0 -- Faster, slightly less safe

# Parallel replication to speed slave processing slave_parallel_workers = 4 slave_parallel_type = LOGICAL_CLOCK ```

Quick Reference Commands

```sql -- Enable/disable semi-sync SET GLOBAL rpl_semi_sync_master_enabled = 1; SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- Set timeout SET GLOBAL rpl_semi_sync_master_timeout = 10000;

-- Check status SHOW STATUS LIKE 'rpl_semi_sync%'; SHOW VARIABLES LIKE 'rpl_semi_sync%';

-- Set required ACK count SET GLOBAL rpl_semi_sync_master_wait_for_slave_count = 2;

-- Enable trace SET GLOBAL rpl_semi_sync_master_trace_level = 16;

-- Install plugin INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; ```

Troubleshooting Checklist

  • [ ] Verify plugin installed on master and slave
  • [ ] Check semi-sync enabled on both servers
  • [ ] Verify timeout is appropriate for network latency
  • [ ] Check slave is registered with master
  • [ ] Monitor fallback to async transactions
  • [ ] Ensure required ACK count matches available slaves
  • [ ] Use AFTER_SYNC mode for safety
  • [ ] Monitor average wait times
  • [ ] Optimize slave I/O performance
  • [ ] Consider parallel replication for faster slave processing
  • [ ] Set up monitoring for semi-sync status