Lost connection to server executing ALTER TABLE

Question

I'm trying to execute an ALTER TABLE statement in MySQL 5.6. Every time I run the query, it's creating a file with a name like #sql2-503-918bf.ibd taking full table size. I don't have enough disk space to run the query, because whenever I try to run it, another #sql… file will be created.

Query:

ALTER TABLE kt_ticket_message 
    ADD COLUMN from_email varchar(255) NOT NULL COMMENT 'From email';

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

Table size is 5 GB, database size is 6 GB.

These are parameters in the config file:

wait_timeout=30
interactive_timeout=60
max_allowed_packet=1M

Can I increase the timeout parameters?

Answer 1

Change specific timeouts value to 6000 it's available in all new versions of MySQL Workbench.

Open Edit → Preferences → SQL Editor → DBMS connection read time out (in seconds): 600

Changed the value to 6000.

Also unchecked limit rows as putting a limit in every time I want to search the whole data set gets tiresome.

Answer 2

Some variants of ALTER TABLE need to copy the table over. In doing so, they create a file named #sql..., which will later be renamed to the real table name. The use of ALGORITHM=... is aimed at doing the copy or not. However, if the ALTER cannot work without the copy, it will complain.

ALTER TABLE will continue to fail until you have more space. Or perhaps we can find some other workaround. Please provide SHOW CREATE TABLE and the ALTER.

If you have some #sql files left around after ALTER crashes, you can safely delete them.

As for increasing the timeout, what client are you using? I ask because the mysql commandline tool responds (I think) to interactive_timeout, while some other clients respond to wait_timeout. `SET both of them higher just in case.