4

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?

3
  • Try to use ALGORITHM=INSTANT if your table do not matches any limitation of Online DDL Operations.
    – Akina
    Oct 18, 2018 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. Oct 20, 2018 at 15:46
  • Note that your query may still be running. Try show full processlist Feb 18, 2021 at 1:16

2 Answers 2

3

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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