CODEWITHSUNDEEP
phpmysqlmysqlitimeout
danipen
danipen

Reputation: 341

Timeout importing huge txt in mysql

I'm trying to import a huge txt in mysql.
it is my first attempt to use php and mysql so I'm not sure where is the problem.
I have a txt of almost the 4 million row and I need to store it in a db.
I wrote this pice of php to do the job:

$con=mysqli_connect('localhost', 'root', 'root', 'commuting');
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }


$handle = fopen("matrix_pendo2001.txt", "r") or die("Couldn't get handle");
if ($handle) {
    while (!feof($handle)) {

        $line = fgets($handle, 4096);

        $a = substr($line,0,3);
        $b = substr($line,4,3);
        $c = substr($line,8,1);
        $d = substr($line,10,1);
        $e = substr($line,12,1);
        $f = substr($line,14,3);
        $g = substr($line,18,3);
        $h = substr($line,22,3);
        $i = substr($line,26,1);
        $l = substr($line,28,2);
        $m = substr($line,31,1);
        $n = substr($line,33,1);
        $o = substr($line,35,8);

        $sql="INSERT INTO ita (ProvRes, ComRes, Sesso, Motivo, Luogo, ProvLav, ComLav, StatExt, MercPrev, Mezzo, Ora, Tempo, Num)
        VALUES 
        ('$a', '$b', '$c', '$d', '$e', '$f', '$g', '$h', '$i', '$l', '$m', '$n', '$o')";

        if (!mysqli_query($con,$sql))
          {
          die('Error: ' . mysqli_error($con));
          }
        echo "record '<b>" . $line . "'</b> added. <br/>";

    }
    fclose($handle);
}
mysqli_close($con);

The script seems to work well, but I can't get all the row in the db.
after less than 1 million the script ends without apparent error.
I have set the max_execution_time to 0 and increased the memory_limit to 2048MB but nothing change.

Upvotes: 0

Views: 737

Answers (1)

mcuadros
mcuadros

Reputation: 4144

I suggest you use LOAD DATA INFILE, this SQL command can be executed directly in the server and will avoid timeouts and will be the fastest way because run in localhost.

This is a small example:

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
  FIELDS TERMINATED BY ','  LINES STARTING BY 'xxx';

Based on this question: loading-fixed-width-space-delimited-txt-file-into-mysql you can try this possible solution:

LOAD DATA LOCAL INFILE 
'/yourfile.txt' 
INTO TABLE clip
(@row)
SET a = TRIM(SUBSTR(@row,0,3)),
    b = TRIM(SUBSTR(@row,4,3)),
    c = TRIM(SUBSTR(@row,10,1)),
    ......
;

Upvotes: 2

Related Questions