0

I am trying to load a single 28GB csv file (257 million rows) into MySQL database for almost a week. However I got Error 1114 while loading after 4730 seconds (or more/less, different in every execution). I have tried to find the solution here and already adjusted the my.ini as following:

max_allowed_packet = 1024M
max_heap_table_size = 1024M
tmp_table_size = 1024M
innodb_buffer_pool_size = 2048M
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_log_buffer_size = 1024M
innodb_log_file_size = 1024M
innodb_file_per_table = 1

But the error still occurred. The server logs just showed The table xxx is full. The second problem is that I cannot drop this corrupted, full table. The server connection broken everytime when I want to drop the table (Lost connection). I am wondering how can I modify my my.ini better to deal with this big csv file and drop the broken table?

My RAM is 4G and the harddrive still has enough space (162G, so I don't think this is problem). My session settings are: keep-alive interval / read time out / time out are all 99999 seconds.

Thank you very much!

Jimmy

migrated from stackoverflow.com Nov 15 '15 at 15:25

This question came from our site for professional and enthusiast programmers.

0

wouldn't that whole file needed to be loaded into RAM first? I would recommend splitting it up into chunks not bigger that 3GB each before submitting them.

Also, this post suggests that "setting buffer pool a bit larger than your database size" was a good practice.

It looks to me that your whole set up is not quite up to the task.

  • thank you so much! I am now splitting my data, and also change innodb_buffer_pool_size = 2048M to 32768M. Hoping that it works well later! – ckbjimmy Nov 14 '15 at 23:53
  • Sounds like you have too many indexes on that huge table. – Rick James Dec 3 '15 at 5:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy