TL;DR Why does my PHP script exit with Warning: Empty row packet body ?
I have a PHP CLI script that migrates product data between 2 MySQL databases, using the mysqli functions. The structure of each database is different, and my script transforms the data to convert from the structure of A to the structure of B.
The basic structure of my code is this:
Loop over A.products
Loop over A.product_variants that match the id from A.products
Insert into B.products
As A.products has over a million rows, to avoid running out of memory I am using MYSQLI_USE_RESULT instead of MYSQLI_STORE_RESULT.
I tried running a query to get A.product_variants while I am looping over the A.products result set, and I got this error: Commands out of sync; you can't run this command now
To solve this, I now fetch A.products using one connection, and A.product_variants using a separate connection. The insert uses a third connection to database B.
This now works successfully until it reaches around row 1030 in A.products. It does not always stop at the same row, but close. Then I get this error and the loop exits:
Warning: Empty row packet body in script.php line 500
Which is the $mysqli->fetch_object() line:
while ($product = $res_products->fetch_object())
What causes this error? How can I fix it?