Threaded Mode | Linear Mode

sonu · 08-12-2008, 10:26 PM

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

daniel · (This post was last modified: 08-12-2008 10:30 PM by daniel.)

How are you running the query? (PHP, mysql command line, phpMyAdmin, etc..)

sonu · 08-12-2008, 10:30 PM

I had written a PHP code to transfer the data.

PHP Code:

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

daniel · 08-12-2008, 10:31 PM

Your script is probably timing out. Do you have access to run phpmyadmin?

sonu · 08-12-2008, 10:31 PM

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?

daniel · 08-12-2008, 10:35 PM

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.