CODEWITHSUNDEEP
mysql
nomind
nomind

Reputation: 303

Lost connection to mysqld on a single large insert

I am trying to restore data in a table with definition

CREATE TABLE UserDomainSummaries (
summary_id              INT UNSIGNED,
user_id                 BIGINT UNSIGNED,
domain_id               BIGINT UNSIGNED,
city_id                 INT UNSIGNED,
state_id                INT UNSIGNED,
country_id              INT UNSIGNED,
visitCount              INT UNSIGNED NOT NULL,
pageViewCount           INT UNSIGNED NOT NULL,
totalTimeSpentInSecs    INT UNSIGNED NOT NULL,
ffVisitCount            INT UNSIGNED NOT NULL,
ffPageViewCount         INT UNSIGNED NOT NULL,
ffTotalTimeSpentInSecs  INT UNSIGNED NOT NULL,

PRIMARY KEY(summary_id, user_id, domain_id, country_id, state_id, city_id),
FOREIGN KEY(summary_id) REFERENCES Summaries(id) ON DELETE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=utf8;`

from a file with single insert with large number of rows totaling to 200MB. But, in between a error pops up saying

ERROR 2013 (HY000): Lost Connection To MySQL Server During Query

I have tried tweaking with the values of 

innodb_buffer_pool_size
net_read_timeout
bulk_insert_buffer_size
max_allowed_packet

but to no avail.

Why is this happening?

Upvotes: 2

Views: 2047

Answers (2)

ajreal
ajreal

Reputation: 47321

You can check the wait_timeout

The number of seconds the server waits for activity on a noninteractive connection before closing it. This timeout applies only to TCP/IP and Unix socket file connections, not to connections made using named pipes, or shared memory.

Beside using load data, you can break the single query (big, fat) into multiple queries (small, slim).

Upvotes: 0

bensiu
bensiu

Reputation: 25604

Use LOAD DATA istead

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Upvotes: 2

Related Questions