CODEWITHSUNDEEP
mysqlload-data-infile
3ashmawy
3ashmawy

Reputation: 469

LOAD DATA weird behavior

I have the following query that basically loads huge amount of data into my database:

LOAD data local INFILE 
    '/Users/Masters/Model/quotes/sp500hst-1.txt' 
    INTO TABLE quote fields 
    TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' 
    (quote.date,quote.ticker,quote.open,quote.high,quote.low,quote.close,
     quote.volume,@market) 
    SET market = 'sp500';

A snippet from the file sp500hst-1.txt is :

20090821,A,25.6,25.61,25.22,25.55,34758
20090824,A,25.64,25.74,25.33,25.5,22247
20090825,A,25.5,25.7,25.225,25.34,30891
20090826,A,25.32,25.6425,25.145,25.48,33334

The file is about 150,000 lines long and i have an "AFTER INSERT" trigger procedure, 2 indicies other than the primary one for the table quote.

What happens is that the query keeps running for like 15 minutes, returns "Lost connection to server". When i run the query again it INSTANTLY returns success with the number of rows affected.

I can understand why it takes too much time , but what i need to resolve is the "Lost connection to server" glitch.

Upvotes: 0

Views: 219

Answers (1)

Jim Garrison
Jim Garrison

Reputation: 86754

Take a look at this page -- It has a list of suggestions for dealing with this problem.

Upvotes: 1

Related Questions