1

I downloaded a database that was exported to the TXT format and has about 700MB with 7 million records (1 per line). I made a script to import the data to a mysql database, but when about 4 million records inserted into, the browser crashes. I have tested in Firefox and IE. Can someone give me an opinion and some advice about this?

The script is this:

<?php
set_time_limit(0);
ini_set('memory_limit','128M');

$conexao = mysql_connect("localhost","root","") or die (mysql_error());
$base = mysql_select_db("lista102",$conexao) or die (mysql_error());
$ponteiro = fopen("TELEFONES_1.txt","r");
$conta = 0;
function myflush(){ ob_flush(); flush(); }

while(!feof($ponteiro)){
    $conta++;

    $linha = fgets($ponteiro,4096);
    $linha = str_replace("\"", "", $linha);
    $arr = explode(";",$linha);
    $sql = "insert into usuarios (CPF_CNPJ,NOME,LOG,END,NUM,COMPL,BAIR,MUN,CEP,DDD,TELEF) values ('".$arr[0]."','".$arr[1]."','".$arr[2]."','".$arr[3]."','".$arr[4]."','".$arr[5]."','".$arr[6]."','".$arr[7]."','".$arr[8]."','".$arr[9]."','".trim($arr[10])."')";
    $rs = mysql_query($sql);
    if(!$rs){ echo $conta ." error";}

    if(($conta%5000)==4999) { sleep(10); echo "<br>Pause: ".$conta; }
    myflush();
}

echo "<BR>Eof, import complete";
fclose($ponteiro);
mysql_close($conexao);
?>
OctaneFX
  • 106
  • 7

7 Answers7

2

Try splitting the file in 100 MB chunks. This is a quick solving suggestion to get the job done. The browser issue can get complicated to solve. Try also different browsers.

phpMyadmin has options to continue the query if a crash happened. Allows interrupt of import in case script detects it is close to time limit. This might be good way to import large files, however it can break transactions.

Elzo Valugi
  • 27,240
  • 15
  • 95
  • 114
  • I try something similar - so far I've inserted over two million rows into a MySQL db using PHP. There IS a tendency for the browser to be overwhelmed by too much data, and IE is a little more sensitive than FF. – pavium Aug 22 '09 at 22:38
  • Oh, I should add that I've inserted over 2 million records, but NOT all in one insertion. The maximum so far has been about 250k, and until I reduced the amount of screen output, the browser tended to choke. – pavium Aug 23 '09 at 00:25
2

I'm not sure why you need a web browser to insert records into mysql. Why not just use the import facilities of the database itself and leave the web out of it?

If that's not possible, I'd wonder if chunking the inserts into groups of 1000 at a time would help. Rather than committing the entire database as a single transaction, I'd recommend breaking it up.

Are you using InnoDB?

duffymo
  • 305,152
  • 44
  • 369
  • 561
  • 1
    Using a web browser to insert records allows it to be done by a relatively unskilled operator without logging-in to MySQL and using commands. It fits well with the modern 'which button do I click?' culture. – pavium Aug 22 '09 at 22:48
  • 2
    Hi duffymo, I use browser because the end user don´t have skill and access to other ways :D – OctaneFX Aug 22 '09 at 22:59
2

  1. What I've first noticed is that you are using flush() unsafely. Doing flush() when the httpd buffer is full result in an error and your script dies. Give up all this myflush() workaround and use a single ob_implicit_flush() instead.

  2. You don't need to be seeing it with your browser to make it work to the end, you can place a ignore_user_abort() so your code shall complete its job even if your browser dies.

  3. Not sure why your browser is dying. Maybe your script is generating too much content.

Havenard
  • 27,022
  • 5
  • 36
  • 62
1

Try it with no

<br> Pause: nnnn

output to the browser, and see if that helps. It may be simply that the browser is choking on the long web page it's asked to render.

Also, is PHP timing out during the long transfer?

It doesn't help, also, that you have sleep(10) adding to the time it takes.

pavium
  • 14,808
  • 4
  • 33
  • 50
  • No timeout during transfer, when is about 4 milion record, the browser freeze and his process increase memory consumption until crash – OctaneFX Aug 22 '09 at 22:37
0

You can try splitting up the file in different TXT files, and redo the process using the files. I know I at least used that approach once.

Charles
  • 2,615
  • 3
  • 29
  • 35
0

The browser is choking because the request is taking too long to complete. Is there a reason this process should be part of a web page? If you absolutely have to do it this way, consider splitting up your data in manageable chunks.

0

Run your code in command line using PHP-CLI. This way, you will never encounter time-out for long running process. Although, the situation is your browser crash before time-out ^^. If you try to execute in hosting server which you don't have shell access, run the code using crontab. But, you have to make sure that the crontab only run once!

ariefbayu
  • 21,849
  • 12
  • 71
  • 92