MySQL ALTER TABLE挂起 [英] MySQL ALTER TABLE hangs

问题描述

我知道这个问题已经被问过几次了，但是我刚创建的表正在发生我的问题.它只有10列，只有1行.

I know this question was asked several times already but my problem is happening with a table I've just created. It has only 10 columns and only 1 row in it.

因此，与通常的悬挂问题不同，这不是包含大量数据的大表的情况.但是它挂了.

So unlike the usual hanging problem this is not the case of a large table with a lot of data. And yet it hangs.

这是我正在运行的SQL:

This is the SQL I'm running:

ALTER TABLE `db`.`Search` 
ADD COLUMN `useremail` VARCHAR(256) NOT NULL COMMENT '' AFTER `id`;

足够简单，但挂起(50分钟并计数).我尝试退出Workbench和Eclipse并删除所有java进程和一个mysqld进程.但是没有运气.

Simple enough and yet it hangs (50 minutes and counting.) I tried quitting Workbench and Eclipse and deleting all java processes and the one mysqld process. But no luck.

我正在Mac OSX El Capitain上运行.如何将此列添加到表中?

I'm running on a Mac OSX El Capitain. How do I add this column to my table?

推荐答案

我通过搜索"mysql alter tablehanging"来解决这个问题，并认为答案(在注释中)应予以记录.

I got to this question by searching "mysql alter table hanging" and think the answer (in the comments) should be documented.

如果您正在运行MySQL命令(在本例中为添加列)，并且该命令意外挂起，例如不是由于表中数据的大小，而是检查针对数据库运行的其他进程.我让查询保持运行状态，并通过另一个客户端连接到服务器，然后键入

If you're running a MySQL command (in this case to add a column) and it is unexpectedly hanging, e.g. not due to the size of the data in the table, then check the other processes running against the database. I left the query running, connected to the server with another client, and typed

SHOW PROCESSLIST;

我的查询已挂起，并且此消息在state列中:

My query was hanging, and this message was in the state column:

Waiting for table metadata lock

我注意到我的机器上有几个僵尸连接，将它们杀死(KILL xxx，其中xxx是Id列中的数字)，然后该过程立即完成.

I noticed I had several zombie connections from my machine, killed them (KILL xxx where xxx is the number in the Id column), and then the process finished immediately.

这篇关于MySQL ALTER TABLE挂起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！