PHP script with MySQL query timing out

Question

I am having issues running a PHP script which inserts data to MySQL. The error I get is "504 Gateway Time - out nginx" When the PHP page gets stuck with this timeout 10,102 lines of data have been entered to the database. I'm planning to insert 160,000 lines in one load of the script.

I have made my code more efficient by using a prepared statement for the SQL. The SQL is also set up in this structure:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

I have read SO PHP script times out and How to keep a php script from timing out because of a long mysql query

I have tried adding to the start of my code but doesn't seem to make a difference:

set_time_limit(0);
ignore_user_abort(1);

Can anyone show me data to split dataset into chunnks and for each chunk data is inserted?

I will show the section of code that inserts to MySQL below

 // prepare and bind
$stmt = $link->prepare("INSERT INTO MyGuests (`eventID`,`location`,`date`,`barcode`,`runner`,`time`,`Run Points`,`Volunteer Points`,`Gender`, `Gender pos`) VALUES (?,?,?,?,?,?,?,?,?,?)");
$stmt->bind_param("isssssiisi", $eventID,$location,$date,$barcode,$runner,$time,$runpoints,$volpoints,$gender,$genderpos);

// set parameters and execute
for( $x=0; $x < count($array_runner); $x++ ){
    $eventID=null;
    $barcode=$array_barcode[$x];
    $runner=$array_runner[$x];
    $time=$array_time[$x];
    $runpoints=$array_score[$x];
    $volpoints=' ';
    $gender=$array_gender[$x];
    $genderpos=$array_gender_pos[$x];

    $stmt->execute();

}

$stmt->close();
$link->close();

I am new to working with MySQL and am looking for some guidance with this problem.

Answer 1

set_time_limit(0); resets the count when it is executed. It does not change the max_execution_time in php.ini so to make it have any useful effect you would have to run it in the loop.

 // prepare and bind
$stmt = $link->prepare("INSERT INTO MyGuests (`eventID`,`location`,`date`,`barcode`,`runner`,`time`,`Run Points`,`Volunteer Points`,`Gender`, `Gender pos`) VALUES (?,?,?,?,?,?,?,?,?,?)");
$stmt->bind_param("isssssiisi", $eventID,$location,$date,$barcode,$runner,$time,$runpoints,$volpoints,$gender,$genderpos);

// set parameters and execute
for( $x=0; $x < count($array_runner); $x++ ){
    $eventID=null;
    $barcode=$array_barcode[$x];
    $runner=$array_runner[$x];
    $time=$array_time[$x];
    $runpoints=$array_score[$x];
    $volpoints=' ';
    $gender=$array_gender[$x];
    $genderpos=$array_gender_pos[$x];

    $stmt->execute();

    // every 5000 times through the loop reset the timeout
    if ( $x % 5000 == 0 ) {
        set_time_limit(30);
    }
}

$stmt->close();
$link->close();

Of course you can play with the value 5000 so it does the reset less often.

From the Manual:

When called, set_time_limit() restarts the timeout counter from zero. In other words, if the timeout is the default 30 seconds, and 25 seconds into script execution a call such as set_time_limit(20) is made, the script will run for a total of 45 seconds before timing out.

Answer 2

If you are using query inside a loop with so large number of rows it would definitely stuck.

The best way I can suggest is simply handle all the data to be inserted in a PHP string and then fire a single query to insert data.

Let me elaborate

$data_to_insert = '' // will contain all data to inserted
$count = 1;
$eventID = null; // if it is null for all rows

for( $x=0; $x < count($array_runner); $x++ )
{    
    if($count == 1)  // checking if it is the first value to be inserted
    {
        $data_to_insert = "(";
        $count = 2;
    }
    else // with second value onwards
    {
        $data_to_insert = ",(" ;
    }

    $data_to_insert = $data_to_insert .   $eventID . ",";
    $data_to_insert = $data_to_insert . "'".   $barcode . "'";
    $data_to_insert = $data_to_insert . "'".   $array_runner[$x] . "'";
    $data_to_insert = ")";
}
// so in the last $data_to_insert should look like this
// $data_to_insert =  (eventid1 , 'barcode1', 'runner1'), (eventid2 , 'barcode2', 'runner2') and so on...

Then fire the query

mysqli_query("INSERT INTO MyGuests (`eventID`,`barcode`,`runner`)  values" . $data_to_insert);
// which would look like
// INSERT INTO MyGuests (`eventID`,`barcode`,`runner`)  values (eventid1 , 'barcode1', 'runner1'), (eventid2 , 'barcode2', 'runner2')

Note : There might be some syntax error in my code, but you get the logic here.