LOAD DATA weird behavior

Question

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.

Jim Garrison · Accepted Answer · 2010-11-18 00:57:07Z

1

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

answered Nov 18, 2010 at 0:57

Jim Garrison

87k20 gold badges162 silver badges197 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

3ashmawy Over a year ago

thanx Jim for your feedback , i have suspected that it might be a TCP/IP timeout after skimming through your link. So i enabled skip-networking even though the link also stated that enabling the skip-networking option might result in "MySQL server has gone away error". But i gave it a shot anyway and it worked. But the question is why? and what if i later had to use the server remotely ?

Collectives™ on Stack Overflow

LOAD DATA weird behavior

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related