Webmin/Virtualmin - Handling with large file - Internal Server Error

Question

I'm using a PHP script to insert the unique value of an array into the database. Here's the script

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

if (isset($_POST['submit'])) {
    $df = $_POST['df'];

    $arr = array();
    $file = fopen('files/'.$df.'.csv', 'r');
    while (($line = fgetcsv($file)) !== false) {
        $arr[] = $line[2];
    }
    $arruniq = array_unique($arr);
    fclose($file);
    $arr_count = count($arr);

    $time = strtotime(date_default_timezone_get());
    $ts = date("Y-m-d H:i:s", $time);

    $mysqli = new mysqli("localhost", "root", "pass", "tab");
    if ($mysqli === false) {
        die("ERROR: DB could not connect. ");
    }

    $sqlins = "insert into tab (created_time, uniq_id) values (?, ?);";
    for ($i = 0; $i < $arr_count; $i++) {
        if ($stmt = $mysqli->prepare($sqlins)) {
            $stmt->bind_param("ss", $ts, $arruniq[$i]);
            $stmt->execute();
        } else {
            echo "ERROR: Could not prepare query";
        }
    }
    $mysqli->close();
}

The script is reading a CSV file, unique the values and insert into the database. Here's the problem, the script works fine if the file size is smaller, but if I use a larger file I'm getting this "500 internal server error".

No error is displaying or logged in the server PHP logs as well as apache logs.

I even increased the execution time of the script and memory limit of the script by using ini_set('memory_limit','2048M'); and max_execution_time headers, which also didn't work.

Did you update `upload_max_filesize` and `post_max_size` also? — BadPiggie, May 23 '20 at 14:34
yes, i set that too `upload_max_filesize 200M` and `post_max_size 200M`, but no changes — Jason Maddox, May 23 '20 at 14:43
We have to make sure the issue is really in csv file or someother causing. Where did you checked the logs? has the access log making entry for your page load? — Vigneshwaran, May 23 '20 at 14:53
this is the error i'm getting from apache2 error logs `[fcgid:warn] [pid 3221] mod_fcgid: process 4423 graceful kill fail, sending SIGKILL` `[mpm_prefork:notice] [pid 3220] AH00169: caught SIGTERM, shutting down` — Jason Maddox, May 23 '20 at 15:15
You need to stop manually checking for errors. Please read: [Should we ever check for mysqli_connect() errors manually?](https://stackoverflow.com/q/58808332/1839439) and [Should I manually check for errors when calling “mysqli_stmt_prepare”?](https://stackoverflow.com/q/62216426/1839439) — Dharman, Jun 12 '20 at 00:01

Jason Maddox · Accepted Answer · 2020-05-24T15:36:35.350

1

This is an issue with execution time of the script. Since i'm using webmin/ virtualmin max_execution_time header didn't work. So I've changed that in the virtualmin server

virtualmin server -> server configuration -> website options -> Maximum PHP script run time

Now the issue is solved, But it takes longer time for executing script.

edited May 24 '20 at 15:36

answered May 23 '20 at 15:57

Jason Maddox

25
6

score 0 · Answer 2 · answered Jun 12 '20 at 00:06

It should be faster if you use prepared statements and transactions.

$mysqli->begin_transaction();
$stmt = $mysqli->prepare("INSERT INTO tab (created_time, uniq_id) values (?, ?);");
foreach($arruniq as $item) {
    $stmt->bind_param("ss", $ts, $item);
    $stmt->execute();
}
$mysqli->commit();

Prepared statements when used correctly give you slight performance improvement, because the server doesn't need to parse the SQL every time and the transactions save you some time because the commit is not called after each row.

Webmin/Virtualmin - Handling with large file - Internal Server Error

2 Answers2