Page 1 of 1

Transefering huge data from one table to another

Posted: Fri Aug 08, 2008 12:41 am
by eskio
Hi,
I was transferring data from one table of a database to a table in another database. Both databases are stored in MySQL server. But the query only execute around 2,5000 records and stops without any error.
My first table contains around 64,000 records.
Can any one suggest how I can transfer all the records without any interruption and inform me why the interruption is occurring?

Thanks

Re: Transefering huge data from one table to another

Posted: Fri Aug 08, 2008 7:59 am
by ghurtado
How are you running the query? (PHP, mysql command line, phpMyAdmin, etc..)

Re: Transefering huge data from one table to another

Posted: Sat Aug 09, 2008 1:10 am
by eskio
I had written a PHP code to transfer the data.

Code: Select all

$Link1 = 'Connection to first DB';
$Link2 = 'Connection to 2nd DB';
$query = "SELECT * FROM TableName ";
$result = @mysql_query($query,$Link1) or die("Error: ".mysql_error());
while($rowdata = @mysql_fetch_array($result)){
    $value1=$rowdata['XXX'];
    $value2=$rowdata['XXX'];
    $query_insert = "INSERT INTO TableName2 (id,code,description) VALUES($value1, '$value2' ,'') ";
    @mysql_query($query_insert,$Link2) or die("Error ".mysql_error());  
} //while($rowdata = @mysql_fetch_array($result))           
print "Query executed successfully!!<hr>";

Re: Transefering huge data from one table to another

Posted: Sun Aug 10, 2008 11:24 am
by Bill H
Your script is probably timing out. Do you have access to run phpmyadmin?

Re: Transefering huge data from one table to another

Posted: Mon Aug 11, 2008 5:27 am
by eskio
I have access to run phpmyadmin, but when I transfered data I have to use base64_encode() and md5() functions. That is why I did not use phpmyadmin.

How can I fix the timeout?

Re: Transefering huge data from one table to another

Posted: Mon Aug 11, 2008 5:36 am
by Apollo
Process it in chunks, e.g. let the script process 100 entries at a time, and then call itself (with &start=100) using a meta refresh to do the next 100, etc.

Dirty, but it works, and you get to see how far it is in the process while it's busy.

Re: Transefering huge data from one table to another

Posted: Tue Aug 12, 2008 6:24 am
by eskio
thanks your the idea.

Code: Select all

$LIMIT1=0;
for ($i=0; $i<=65; $i++){
  $LIMIT1 += $i*1000;
  $query = "SELECT * FROM TableName LIMIT $LIMIT1, 1000";
  .............
  .............
} // for
$i<=65 because I have over 64000 records

Re: Transefering huge data from one table to another

Posted: Wed Aug 13, 2008 5:05 am
by devendra-m
How can I fix the timeout?
call set_time_limit(0); at the top of your page.