CODEWITHSUNDEEP
phpmysqlinsertfopenmysql-connect
OctaneFX
OctaneFX

Reputation: 106

Browser crashes when about around 4 million records entered in MYSQL

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);
?>

Upvotes: 1

Views: 2300

Answers (7)

ariefbayu
ariefbayu

Reputation: 21979

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!

Upvotes: 0

Elzo Valugi
Elzo Valugi

Reputation: 27876

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.

Upvotes: 2

pavium
pavium

Reputation: 15128

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.

Upvotes: 1

Sergey
Sergey

Reputation:

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.

Upvotes: 0

Havenard
Havenard

Reputation: 27914

  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.

Upvotes: 2

duffymo
duffymo

Reputation: 308988

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?

Upvotes: 2

Charles
Charles

Reputation: 2661

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.

Upvotes: 0

Related Questions