LOAD DATA LOCAL INFILE 失去连接，如何修复超时？

发布于 2024-09-28 20:29:48 字数 921 浏览 0 评论 0原文

我正在通过 LOAD DATA 将一个大文本文件加载到我的数据库中。该文件长约 122,000 行。更不用说，数据库被设计为在该表上插入后触发。确切的情况是，查询开始执行，继续执行大约 15-20 分钟，然后返回，

Error Code: 2013
Lost connection to MySQL server during query

然后

Error Code: 2006
MySQL server has gone away

出现错误后，我重新执行查询，它立即完成；

0 row(s) affected
Records: 122574  Deleted: 0  Skipped: 122574  Warnings: 0

并且该表确实是导入的；

select * from quote
122574 row(s) returned

我在 Mac OS X 10.6.4 上运行 mysqld 5.1.51，我的查询是

    LOAD data local INFILE 
        '/Users/ash/quotes/data.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 = 'sp';

数据库连接似乎超时，我该如何解决这个问题？

原文

I am loading a large text file into my db through LOAD DATA. The file is about 122,000 lines long. Let alone, The db is designed to be triggered on AFTER INSERT's on that table. What happens exactly is that the query begin executing, keeps executing for like 15-20 mins and then it returns

Error Code: 2013
Lost connection to MySQL server during query

then

Error Code: 2006
MySQL server has gone away

After the errors appear , i re-execute the query and it finishes immediately ;

0 row(s) affected
Records: 122574  Deleted: 0  Skipped: 122574  Warnings: 0

And the table is indeed imported ;

select * from quote
122574 row(s) returned

I am running mysqld 5.1.51 on Mac OS X 10.6.4 and my query is

    LOAD data local INFILE 
        '/Users/ash/quotes/data.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 = 'sp';

It seems the db-connection is timing out, how do I fix this?

分享到QQ

分享到微博