ERROR 2013 (HY000): Lost connection to MySQL server during query.
Posted by: Sharath Chandra Karnati
Date: October 18, 2018 12:09AM

I tried a query to alter a table which contains 2,00,000 records and has 14 columns and its size is 4.38 GB. The alter query creates a temp table every time it is executed.

The query is

ALTER TABLE table_name ADD COLUMN column_name varchar(255) NOT NULL COMMENT " ";

The available space in the server is 12GB and available RAM is 7GB.

The query always takes more than 15 min and then stops and leaves behind a #sql- file.
even though the table is 4.38GB the temp file it leaves behind is 4.7GB.
The MySQL memory allocation is as follows

key_buffer_size = 1G
connect_timeout = 10
delayed_insert_timeout = 300
innodb_flush_log_at_timeout = 1
innodb_lock_wait_timeout = 50
innodb_rollback_on_timeout = OFF
interactive_timeout = 60
lock_wait_timeout = 31536000
net_read_timeout = 30
net_write_timeout = 60
rpl_stop_slave_timeout = 31536000
slave_net_timeout = 3600
wait_timeout= 30

Of these net_read_timeout and net_write_timeout are not mentioned in my.cnf

This issue has been going on for 3 days and only on this table it is happening.
I have other servers that have larger tables and they do not show any of these problems and their cinfigurations are almost same.

I would be vary happy if anyone would come up with a solution.

Options: ReplyQuote


Subject
Written By
Posted
ERROR 2013 (HY000): Lost connection to MySQL server during query.
October 18, 2018 12:09AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.