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
Transefering huge data from one table to another
Moderator: General Moderators
Re: Transefering huge data from one table to another
How are you running the query? (PHP, mysql command line, phpMyAdmin, etc..)
Re: Transefering huge data from one table to another
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>";- Bill H
- DevNet Resident
- Posts: 1136
- Joined: Sat Jun 01, 2002 10:16 am
- Location: San Diego CA
- Contact:
Re: Transefering huge data from one table to another
Your script is probably timing out. Do you have access to run phpmyadmin?
Re: Transefering huge data from one table to another
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?
How can I fix the timeout?
Re: Transefering huge data from one table to another
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.
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
thanks your the idea.
$i<=65 because I have over 64000 records
Code: Select all
$LIMIT1=0;
for ($i=0; $i<=65; $i++){
$LIMIT1 += $i*1000;
$query = "SELECT * FROM TableName LIMIT $LIMIT1, 1000";
.............
.............
} // for-
devendra-m
- Forum Contributor
- Posts: 111
- Joined: Wed Sep 12, 2007 3:16 am
Re: Transefering huge data from one table to another
call set_time_limit(0); at the top of your page.How can I fix the timeout?