Ubuntu 16.04.3下MySQL 5.7执行Alter Table时数据库冻结求助

Alright, let's tackle this weird freeze issue you're having—since your table is tiny (only 2.8MiB with 6k rows), the problem isn't about raw data volume. Let's walk through the most likely culprits and how to debug them step by step:

1. Lock Contention (Most Probable) #

Even small tables can grind to a halt if there's an uncommitted transaction holding a lock on the records table. Your ALTER command will hang indefinitely waiting for that lock to release, and GUI tools crash because they hit timeouts waiting for a response.

• How to check:
  • Open a fresh MySQL session and run SHOW PROCESSLIST; before attempting the ALTER. Look for rows where the db matches your database, Command is Sleep or Query, and Info references the records table—these are stuck transactions holding locks.
  • Use these InnoDB-specific queries to dig deeper into locks:

    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
    

    These will show exactly which locks are being held and which operations are waiting on them.
• Fix: Kill the stuck transaction with KILL [process_id]; (replace [process_id] with the ID from the processlist output), then retry your ALTER.

2. Online DDL Quirks in MySQL 5.7.22 #

MySQL 5.7 supports online DDL for most varchar length increases, but some table features or configs can force a full table rebuild (or even stall the operation):

• What to look for:
  • Does your records table have fulltext indexes, spatial indexes, or generated columns? These can block online DDL. Check with SHOW CREATE TABLE records;.
  • The innodb_online_alter_log_max_size variable might be too small, causing the online DDL to stall when trying to log concurrent changes.
• Troubleshooting steps:
  • If you have fulltext/spatial indexes, drop them temporarily, run the ALTER, then re-add them.
  • Check the log size setting: SHOW VARIABLES LIKE 'innodb_online_alter_log_max_size'; If it's under 8MB, try increasing it temporarily with SET GLOBAL innodb_online_alter_log_max_size = 16777216; (16MB) and retry.
  • Force a table rebuild (bypassing online DDL) with:

    ALTER TABLE records MODIFY COLUMN name VARCHAR(150) ALGORITHM=COPY;
    

    This will lock the table during the rebuild, but it might bypass the bug/quirk causing the freeze.

3. System Resource Bottlenecks #

Even small operations can get stuck if your server is starved for resources:

• How to check:
  • Run htop or top while attempting the ALTER. Look for high %wa (wait time for disk I/O) or memory usage that's pushing into swap.
  • Use iostat -x 1 to monitor disk activity—if %util hits 100% on your data disk, that's a bottleneck.
• Fix: Stop any non-essential processes on the server to free up CPU, memory, or disk I/O, then retry the ALTER.

4. Known Bugs in MySQL 5.7.22 #

MySQL 5.7.22 has a few documented bugs related to DDL operations. For example, some collation combinations or edge cases with table metadata could cause hangs or connection crashes.

• Troubleshooting:
  • Enable debug logging for DDL to get more details: Run SET DEBUG='d,ddl_verbose'; before the ALTER, then check /var/log/mysql/error.log again—this will log step-by-step details of the DDL process.
  • If possible, upgrade to the latest 5.7.x patch release (like 5.7.44) to fix any known DDL bugs. Many issues were resolved in subsequent patches after 5.7.22.

5. Table or Disk Corruption (Rare but Possible) #

Corrupted table files or disk issues can cause silent hangs during DDL operations:

• How to check:
  • Run CHECK TABLE records; to scan for table corruption. If errors are found, rebuild the table with ALTER TABLE records ENGINE=InnoDB; (this works for InnoDB tables).
  • Check system logs (/var/log/syslog on Ubuntu) for disk-related errors like "IO error" or "bad sector" messages—these point to underlying hardware issues.

内容的提问来源于stack exchange，提问作者TheOrdinaryGeek