2

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?

  • Try to use ALGORITHM=INSTANT if your table do not matches any limitation of Online DDL Operations. – Akina Oct 18 '18 at 6:36
  • ERROR 2013 (HY000): Lost connection to MySQL server during query isn't usually an indication of a timeout. It's usually an indication of something more serious, like a crash, which forcibly closes client connections without a response. Review the MySQL Server's error log. – Michael - sqlbot Oct 20 '18 at 15:46
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.

1

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.

  • Where might I find this #sql... file that you speak of? – Vincent Jan 23 at 23:34
  • In unix, do find / -name '#sql%' . – Rick James Jan 24 at 3:56
  • I'm using a Windows server. – Vincent Jan 24 at 16:01
  • @Vincent - It will be in the directory where the table is. – Rick James Jan 25 at 6:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.