PHP/MySQL data migration

Question

I'm trying to fetch 5100 rows from a MySQL database to another. However, it seems that the script only fetches around 1300 and ends.

The script has two connections, one for each db. The code looks like:

$result = mysqli_query($con_remote,"SELECT * FROM product_images");

while($row = $result->fetch_assoc())
{
    //Insert
    mysqli_query($con_local,"INSERT INTO product_images (id, filename, 
product_id) VALUES('$row[product_image_id]', '$row[product_image_filename]', 
'$row[fk_product_id]')");
}

I have also added ini_set('max_execution_time', 800); at the start of the script in order to avoid execution timeout. What could I improve/change to get all 5100 rows?

Edit: I ended up with exporting the remote database and import it to my local machine and then change the column names.

@WKoppel, I'm not great at using the command line. That's why I made the php script to execute on my local apache. — Frederik, Dec 04 '18 at 12:06
Read about `mysqldump` https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html — Jens, Dec 04 '18 at 12:06
@Frederik well if you want to be a developer I suggest you lose that mindset that you don't wanna do something because you don't feel comfortable doing it. Learn it, doing simpler things like dumping an table from database and importing to another it isn't that hard. With CLI you can do it under a minute. — WKoppel, Dec 04 '18 at 12:10
You need to insert row using batch insert. So use `TRANSACTION` for it. Check this link : https://stackoverflow.com/questions/15149157/best-way-to-insert-many-values-in-mysqli — Hardik Solanki, Dec 04 '18 at 12:12
"I have also added ini_set('max_execution_time', 800);" Your PHP is most likely out of memory.. Make it a habit out of it to **always** program code with `error_reporting(E_ALL); ini_set('display_errors', true)` then you would 95-97% of the PHP errors/notices/warnings.. Some PHP functions like `mysqli_query` does not trigger a error you need to use `mysqli_error()` to see it.. — Raymond Nijland, Dec 04 '18 at 12:13
@Frederik When I started with PHP few years ago I always didn't want to do things that seemed hard, but after completing my task I always felt that it was me that's trying to make look things hard or impossible. Good luck! — WKoppel, Dec 04 '18 at 12:14
His best and most easy option by the way @hardiksolanki would be to use `INSERT INTO table () SELECT FROM table...` — Raymond Nijland, Dec 04 '18 at 12:17
if `multi_query` is the answer you are asking the wrong question @hardiksolanki let me explain that `multi_query` is "dangerours" because you can't use prepared statements to prevent SQL injections and we al know `mysqli_real_escape_string()` is not that great to prevent SQL injections.. And because `multi_query` support queries separated with semicon `;` where `mysqli_query` or `mysqli_prepare` does not it's also possible to inject `1; DROP TABLE table` for example — Raymond Nijland, Dec 04 '18 at 12:25
No problem.. Besides `INSERT INTO table () SELECT FROM table...` is one query which can be executed just fine with `mysqli_query` @hardiksolanki — Raymond Nijland, Dec 04 '18 at 12:27
@RaymondNijland INSERT/SELECT won't work because he has not only two different databases but two different hosts. — Daniel W., Dec 04 '18 at 12:58
Your are right @DanFromGermany just noticed he is using two seperated connections i geuss i need some more coffee.. Want if he going to use a extra [FEDERATED](https://dev.mysql.com/doc/refman/8.0/en/federated-storage-engine.html) table to get the data from local to the remote server then he could use the INSERT/SELECT query like i said.. Iám pretty sure `FEDERATED` table engine is enabled by default on a windows MySQL version but i assume here the topicstarter is using windows on his local machine.. — Raymond Nijland, Dec 04 '18 at 13:18
@RaymondNijland, I use Windows 10 in my local machine, yes :). — Frederik, Dec 04 '18 at 13:25
Ok check `SHOW ENGINES` to see if `FEDERATED` is here then you can use the documentation to get it started.. — Raymond Nijland, Dec 04 '18 at 13:28

score 1 · Answer 1 · answered Dec 04 '18 at 12:55

Please try below this should work. Refer attached code. This is simple no need to retrieve while or for loop

ini_set('max_execution_time', 1200);
// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
mysqli_select_db($conn, 'db1');

// Create connection for second db
$conn2 = new mysqli($servername, $username, $password);

// Check connection
if ($conn2->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully for Second";
mysqli_select_db($conn2, 'db2');
echo '<br>';

$sql = 'INSERT INTO db1.table1 (ID, NAME) 
SELECT ID, NAME FROM db2.table1';

if (mysqli_query($conn2, $sql)) {
      echo "New record created successfully";
} else {
      echo "Error: " . $sql . "<br>" . mysqli_error($conn2);
}
mysqli_close($conn2);
mysqli_close($conn);

გენო მუმლაძე · Answer 2 · 2018-12-04T12:38:03.427

0

insert this php function in above your code:

ini_set('max_execution_time', 1200);
set_time_limit(int $seconds);

edited Dec 04 '18 at 12:38

answered Dec 04 '18 at 12:05

გენო მუმლაძე

102
2
18

score 0 · Answer 3 · answered Dec 04 '18 at 12:39

0

You can set script execution time to zero, means the script can run forever. Add the following at the start of your script:

ini_set('max_execution_time', 0);

answered Dec 04 '18 at 12:39

Manoj Singh

253
1
7

PHP/MySQL data migration

3 Answers3