尝试将新列添加到 MYSQL 表时失去连接 [英] Lost Connection when trying to add new column to MYSQL table

问题描述

我正在尝试向具有超过 2500 万行的 mysql 表添加一列.我正在运行 sql 命令

I'm trying to add a column to a mysql table that has over 25 million rows. I am running the sql command

ALTER TABLE `table_name` ADD COLUMN `column_name` varchar(128) NULL DEFAULT NULL;

这是使用 mysql 命令行应用程序运行的.

This is being run using the mysql command line application.

每次我尝试运行它都需要几个小时然后我得到错误

Every time i try to run this it takes hours and then i get the error

ERROR 2013 (HY000): 在查询过程中失去与 MySQL 服务器的连接

ERROR 2013 (HY000): Lost connection to MySQL server during query

数据库在 AWS 上的 RDS 实例中运行，并检查监控统计信息，无论是内存还是磁盘空间都没有耗尽.

The database is running in a RDS instance on AWS and checking the monitoring statistics neither the memory or disk space is being exhausted.

还有什么我可以尝试将此列添加到表格中的吗?

Is there anything else i can try to add this column to the table?

推荐答案

检查您的内存使用情况，或者更有可能是您的磁盘使用情况(在此过程中是否有足够的可用空间?).更改表可能需要大量内存或表的磁盘副本.从INPLACE更改alter算法/code> 到 COPY 在您的特定情况下可以更快.

Check your memory usage or, more probably, your disk usage (is there enough free space during the process?). Altering a table may require either a large amount of memory or a copy on disk of your table. Changing the alter algorithm from INPLACE to COPY can be even faster in your particular case.

您也可能达到了 innodb_online_alter_log_max_size 限制，但在这种情况下，只有查询会失败，而不是整个服务器.崩溃可能是由于 ROLLBACK 而发生的，而不是操作本身.

You may also be hitting the innodb_online_alter_log_max_size limit, although in that case, only the query should fail, not the entire server. It is possible that the crash may be happening due to the ROLLBACK, and not the operation itself, though.

最后，某些应用程序配置或托管服务器取消了耗时过长的查询/http 请求，我建议您在命令行客户端上执行相同的查询以进行测试.

Finally, some application configurations or hosting servers cancels a query/http request that is taking too long, I recommend you to execute the same query on the command line client for testing purposes.

这篇关于尝试将新列添加到 MYSQL 表时失去连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！