Load Data and mysqlimport

[karche168]

I am new to mysql, and currently using 4.016 version. I try to import a 7 GB text file which is about 120 million rows with Load Data or mysqlimport, but both fail at the same number of rows 52 million. I already try to use force and debug file but not help. I also import the same text file to SQL Server and it works, so it is not a data issue.
The mysql is currectly configure to use a Large DB configuration. Is there a limitation in mysql or I need to change some setting in order to make it works. The db.myd is about 4.1 GB.
Please help. Thanks

 

[sleipnir214]

In MySQL versions 3.22 and lower, the limit to the size of a table was 4GB. Since that version, the size of a MySQL table is limited by the limits of the filesystem.

For more information, see

http://www.mysql.com/doc/en/Table_size.html

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[vanekl]

What OS are you using?
What file system are you using?
What table type are you using?
When you do a 'show table status' what value is shown for
'max_data_length'?

If max_data_length says something like 4294967295 you can
reset it to something larger:
ALTER TABLE table_name MAX_ROWS=xxx AVG_ROW_LENGTH=yyy;

xxx should be the maximum number of rows you want to store
in the table, and yyy is the average number of bytes each
row holds. InnoDB tables don't have this funny 4 GB constraint.

If you have any field that holds a value whose size is greater than 1MB then you may have to
reset max_allowed_packet in the my.ini or my.cnf file:

[mysqld]
set-variable = max_allowed_packet=4M

You can check what your variables are set to by doing a
show variables;

 

[karche168]

OS = Win2003
FileSystem = Not sure
Table Type = MYISAM
The max table length is 4294967295
The avg row length is 81 and they are all varchar data type

so here is what I did. Expect 150,000,000 million rows times 81 = 12150000000

I ran the alter table statement and set the numbler I get this error
ERROR 1030: Got error 28 from table handler

Any idea?
Thanks

 

[vanekl]

150 M * 81 =~ 12 GB.

"perror 28" says that you have no space left on device, which
means you may have filled up your file system (if the entire
150 M rows are inserted).
How much disk space do you have available where your data
files are stored?

When you do a 'show variables' and see where your tmpdir
directory is located, how much space do you have available
on tmpdir?

You might try a smaller number at first, just to see if the
ALTER TABLE syntax is correct:

ALTER TABLE table_name MAX_ROWS=1000000 AVG_ROW_LENGTH=81;

 

[karche168]

the mysql\data folder is located in D drive which has about 10 GB free. The tmpdir is the C:\WINDOWS\TEMP\ folder which has about 1.5 GB free. I notice the d:\mysql\data folder has a lot of bin files which are about 1 GB each and I have 10 of them. I believe those are log files.
Can you tell me how to flash or remove all those files in mysql.

Thanks again

 

[vanekl]

Log flushing is not my area of expertise but the way I understand it is you do a:

mysqladmin -u root -p flush-logs

Then you can delete or archive the large files in the data dir with the
latest timestamp that were recently renamed by the flush op.

Good to see you've narrowed the problem down to disk space.