title: Best way of importing a large data file to MysQL
body: <p>I have an application which receives a datafile of size <strong>16GB</strong> (with around <strong>90,00,000 lines</strong>) which is to be imported into a MySQL database. Everytime I ran the php script, I would get the error <strong>'Lost connection to MySQL server during query</strong> even though the database is on the same server where the script is running. </p>
  
  <p>After searching on the internet I found out that this happens when you exhaust <strong>one resource for the db session,</strong> such as memory, and mysql closes the connection.The idea is that the transaction you're running shouldn't try to insert a large amount of data. And that you can work around this by setting a higher value to 'max_allowed_packet'. I did this in my script itself: </p>
  
  <p><code>$db-&gt;query( 'SET @@global.max_allowed_packet = ' . 1 * 1024 * 1024 * 1024);</code></p>
  
  <p>But this doesn't work either. Except that the error occurs after inserting a significantly higher number of rows.'</p>
  
  <p>Now I am splitting the file into smaller files with 10,00,000 lines each and then processing each of them. But even the splitting takes a considerable amount of time. </p>
  
  <p>I am not a PHP programmer, nor have I worked with MySQL before. This is someone else's code that I have to refactor. So my knowledge here is almost zero.</p>
  
  <p>I am using the <code>LOAD DATA LOCAL INFILE</code> statement to dump the data. Is there any way of making the transactions shorter by committing after every few lines?</p>