Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a really big csv ~260GB and I want to import it into MySQL. I use the following mysql script in MacOS

DROP TABLE IF EXISTS tmp_catpath_5;

create table tmp_catpath_5( a1 BIGINT(20),a2 BIGINT(20),a3 BIGINT(20),a4 BIGINT(20),a5 BIGINT(20), c1 BIGINT(20),c2 BIGINT(20),c3 BIGINT(20),c4 BIGINT(20),c5 BIGINT(20), INDEX (a5) );

load data local infile '/Volumes/Time Machine Backups 1/tmp_catpath_5.csv' into table tmp_catpath_5 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' ;

It works for smaller files e.g. 150GB, but for this size the database disappears immediately after the load command

ERROR 1049 (42000): Unknown database

Is there a MySQL size limit? Is it something else? I use the MySQL from MAMP application (LAMP for MacOS), could it be different from the official MySQL application?

share|improve this question
2  
A 250Gb CSV? +1 sympathy vote. –  Mark Henderson Dec 13 '10 at 22:58
    
Have you tried to disable/delete the indexes on the tables, and enable/recreate them after import? –  Mircea Vutcovici Dec 13 '10 at 23:55
    
I'll give that a try too... i am getting desperate... :) –  jimkont Dec 14 '10 at 0:00

2 Answers 2

up vote 2 down vote accepted

I don't know if there are limits for particular DB engines but I've met with some Department of Defense admins who manage a MySQL system which imports 10TB every day and they are not experiencing this kind of problem.

Rather than use macros I recommend writing a script to parse the input file and create insert statements from it, which can be fed straight to MySQL. That way you are not dealing with the entire file in one hit, just one line at a time. I like to use Perl for such things.

You may also like to consider using truncate instead of dropping he table and recreating it each time.

share|improve this answer
    
This is a one-time script, every table is created once and deleted after processing, the first drop was a left from when i was testing the script with a small database. i 'll try to use the official mysql app first and then the perl script... Thanks again!!! –  jimkont Dec 13 '10 at 23:53

While this doesn't really answer your question, is the possibility of splitting the file into smaller chunks something you've considered? The unix utility "split" can handle this easily.

When you say "crashes", do you mean the MySQL client or the server? Is any error displayed?

share|improve this answer
    
To be more accurate neither, the error is :ERROR 1049 (42000): Unknown database 'xxx'. the database diasapears immediately after the load command –  jimkont Dec 13 '10 at 23:04
    
I 'll try the spitting option. Thanks! –  jimkont Dec 13 '10 at 23:06
    
Now that i think of it, splitting is not convinient, because i have a series of 30 operations, that each one depends on the previous. now everything is on one script that will run (for a number of days). This way i will have to break every step to split / load –  jimkont Dec 13 '10 at 23:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.