如何终止MySQL未显示在processlist中的添加列操作

Hey there, let's work through your problem step by step. Adding 70 columns to a 1.5M-row table is a heavy operation, so it's totally reasonable to want to stop it if it's dragging on. Here's what might be going on and what you can do next:

Why You Might Not See the Target Process in SHOW PROCESSLIST #

There are a few common explanations for this:

• The ALTER operation already finished (successfully or failed)：DDL operations like adding columns don't linger in the process list once they're done. If it succeeded, your table will already have the new columns; if it failed, the change was rolled back (InnoDB is transaction-safe here, so no data loss).
• Insufficient permissions：By default, SHOW PROCESSLIST only displays processes owned by your user account. If you ran the ALTER from a different session or user, you won't see it unless you have the PROCESS privilege (usually granted to admins/DBAs).
• Checking from the same session as the ALTER：If you executed the ALTER command in your current terminal, that session is blocked waiting for the operation to finish—you can't run SHOW PROCESSLIST in the same session until the ALTER completes. You need to open a separate connection to check.

How to Verify if the ALTER Is Still Running #

Try these steps to confirm the operation's status:

1. Query the information schema for ALTER processes：

   SELECT id, user, db, command, time, state 
   FROM information_schema.PROCESSLIST 
   WHERE command LIKE 'ALTER%';
   

   This is more precise than SHOW PROCESSLIST and might catch the operation if it's active.
2. Check InnoDB transactions：

   SELECT * FROM information_schema.INNODB_TRX;
   

   Look for long-running transactions—if your ALTER uses InnoDB (the default engine), it might show up here if it's still in progress.
3. Monitor server resources：Check your server's CPU, disk I/O, and memory usage. A running ALTER (especially on a large table) will usually cause high disk I/O as it copies and modifies table data.

What to Do If You Need to Terminate the Operation #

• If you find the process ID：Use the KILL command to stop it:

  KILL [process_id];
  

  For InnoDB, this triggers a rollback of the ALTER operation. It might take some time to complete the rollback, but your data will remain intact.
• If you can't find the process：First verify the table structure with DESCRIBE your_table_name; or SHOW CREATE TABLE your_table_name; to see if the columns were added. If they weren't, the operation likely failed silently (check MySQL's error log for details). If they were added, the operation finished while you were checking.

Pro Tip for Future Large DDL Operations #

Next time you need to make schema changes to a large table, consider:

• Using Online DDL (available in MySQL 5.6+ for InnoDB) to avoid locking the table during the operation. Most ALTER operations can be done online with the ALGORITHM=INPLACE and LOCK=NONE clauses.
• Breaking the change into smaller batches (e.g., adding 10 columns at a time instead of 70) to reduce overall runtime and server impact.
• Using tools like pt-online-schema-change (Percona Toolkit) to perform schema changes without locking the table and allowing easier pause/cancel controls.

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