Second application got bricked after 1.7.4 > 1.8.0 migration at mysqldump backup

A few days ago I created this post https://discord.com/channels/564160730845151244/1072905050399191082/threads/1435303434495594698 where after the migration to 1.8.0 the database got deadlocked during the periodic backup.

I thought it was due to a very large audit table that wasn't cleaned up properly (the worker for that had errors at each attempt), and was 2.23 GB in size. During the Appwrite migration, a process crashed due to OOM.

So I supposed that was the problem and now also migrated the second application that I manage to 1.8.0 (which has much smaller db size). Suddenly user support requests started coming in that nothing was working anymore. Also here, the database got deadlocked with the same issue as in my first post.

I also remembered where i got the backup commands from which i'm using, it was this post here: https://appwrite.io/blog/post/how-to-back-up-your-appwrite-data, but something doesn't seem to be working like that anymore with 1.8.0.

Not sure what changed with this update, but all those months up to now, the backup script i was using was working fine. I would suggest to update that blog post, so people don't use the wrong commands if they implement a backup strategy (which everyone should of course).

See the logs of the current issue with this second application in the thread below... Attached the "old" (problematic) and fixed backup.sh scripts, which are run through a cronjob.

Some AI based investigation:

Technical Report: Production Outage Caused by mysqldump Database Deadlock in Appwrite 1.8.0

Environment:

• Appwrite Version: 1.8.0
• MariaDB Version: 10.11.11 (InnoDB)
• Total Database Size: 440.27 MB
• InnoDB Buffer Pool Size: 128 MB (undersized: 3.4x smaller than database)
• Audit Tables: _2_audit (31.67 MB), _1_audit (10.66 MB), _console_audit (1.70 MB)
• Backup Method: Hourly cron job using mysqldump --all-databases

MariaDB Configuration (Relevant Timeouts):

Timeline of Events:

Nov 6-9: Normal Operation

Backups completing successfully every 4 hours:

Normal completion time: 12-15 seconds

Nov 9, 12:00 - Critical Failure Event:

Critical Observations:

• Duration: 1 hour 55 minutes (normal: 12-15 seconds)
• File size: 20 bytes (normal: 95-107 MB)
• File is corrupt/incomplete

Backup File Evidence:

Root Cause Analysis:

1. Problematic Backup Command:

The backup script was using this command:

docker compose exec -T mariadb sh -c 'exec mysqldump --all-databases --add-drop-database -u"$MYSQL_USER" -p"$MYSQL_PASSWORD"'

Critical Missing Flags:

• ❌ No --single-transaction → Forces table locks instead of consistent MVCC snapshot
• ❌ No --skip-lock-tables → Explicitly requests LOCK TABLES on all tables
• ❌ No timeout → Can hang indefinitely (up to 24 hours due to lock_wait_timeout)
• ❌ Includes audit tables → Adds 43+ MB of non-critical data

2. Lock Acquisition Behavior:

When mysqldump runs without --single-transaction, it issues:

LOCK TABLES `_1__metadata` READ /*!32311 LOCAL */,
            `_1__metadata_perms` READ /*!32311 LOCAL */,
            `_1_audit` READ /*!32311 LOCAL */,
            `_1_database_1_collection_154` READ /*!32311 LOCAL */,
            `_1_database_1_collection_155` READ /*!32311 LOCAL */,
            ... [repeating for ~400+ tables]

This command attempts to acquire metadata locks on ALL tables simultaneously.

3. Database State During Deadlock:

When we investigated, SHOW PROCESSLIST revealed:

Additional Context:

• 6 concurrent mysqldump processes were running (duplicate cron jobs)
• All legitimate application queries were blocked behind the LOCK TABLES requests

4. Appwrite Application Layer Impact:

Starting at 12:01 (1 minute after backup started), Appwrite began logging database timeouts:

5. Swoole Worker Pool Exhaustion:

By 13:50 (near the end of the hang), all Swoole workers were exhausted:

All Swoole workers were blocked waiting for database queries that were stuck behind metadata locks → Complete API unresponsiveness.

Why This Deadlock Occurs:

InnoDB Metadata Lock Contention Spiral:

1. mysqldump without --single-transaction requests metadata locks on ~400 tables
2. Active transactions/queries hold implicit metadata locks on tables they're accessing
3. With undersized buffer pool (128MB vs 440MB database), queries experience disk I/O waits
4. Disk-bound queries hold metadata locks longer than memory-bound queries would
5. mysqldump cannot acquire all 400 locks simultaneously → enters wait state (up to 24 hours!)
6. All new queries queue behind the blocked LOCK TABLES request (metadata locks are FIFO)
7. Swoole workers exhaust as they wait for their queries
8. No new requests can be processed → complete deadlock

Why Buffer Pool Size Matters:

• 128 MB buffer can cache ~6,000-8,000 InnoDB pages (16KB each)
• 440 MB database requires ~28,000 pages
• Cache hit rate: ~28% when cold
• Every cache miss requires disk I/O (adding 5-15ms per page)
• Long-running queries → long-held metadata locks → deadlock vulnerability

The Fix:

Updated backup command:

timeout 1800 docker compose exec -T mariadb sh -c 'exec mysqldump \
  --single-transaction \
  --skip-lock-tables \
  --ignore-table=appwrite._1_audit \
  --ignore-table=appwrite._2_audit \
  --ignore-table=appwrite._console_audit \
  --all-databases \
  --add-drop-database \
  -u"$MYSQL_USER" -p"$MYSQL_PASSWORD"'

How Each Flag Prevents the Deadlock:

1. --single-transaction:

   • Uses START TRANSACTION WITH CONSISTENT SNAPSHOT
   • Leverages InnoDB's MVCC (Multi-Version Concurrency Control)
   • No table locks required - reads from consistent point-in-time snapshot
   • Zero impact on concurrent writes/reads

2. --skip-lock-tables:

   • Explicitly prevents LOCK TABLES commands
   • Safety net in case --single-transaction isn't sufficient for some tables

3. --ignore-table for audit logs:

   • Excludes 43+ MB of non-critical audit data
   • Audit logs can be truncated/cleaned without backup
   • Reduces backup time by ~30-40%

4. timeout 1800 (30 minutes):

   • Kills backup if it hangs
   • Prevents indefinite metadata lock holds
   • Maximum disruption window: 30 minutes instead of 24 hours

Is that solution totally fixing issue?

Did you try restore 1.7.4 backup than upgrade to 1.8.0 and than doing backup again to try fixed method?

I think it is, because the improved mysqldump command doesn't grab continuous locks on the tables.

But i did not restore any 1.7.4 backup, it was always just creating the backup, before the migration to 1.8.0 and after.