Getting mad on PHP Error

Hi

I have a migration script which migrates data from one database to another. This takes normally a few minutes to run. It should not take too much memory also.
There are about 1700 entries to be migrated, but after 218 I get an Server Error 500

Internal Server Error

The server encountered an internal error or misconfiguration and was unable to complete your request.

Please contact the server administrator at you@example.com to inform them of the time this error occurred, and the actions you performed just before this error.

More information about this error may be available in the server error log.

<?php

ini_set('max_execution_time', 0);
ini_set('memory_limit', '4G');

ini_set('display_errors', true);
error_reporting(E_ALL | E_STRICT);

const DB_URL = "127.0.0.1";
const DB_PORT = "3306";

const DB_LOGIN = "xxx";
const DB_PASSWORD = "xxx";

function dbConnect($database) : ?PDO
{
    $con = new PDO("mysql:host=" . DB_URL . ";port=" . DB_PORT . ";dbname=" . $database, DB_LOGIN, DB_PASSWORD,
        array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES UTF8"));
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    return $con;
}

try
{
    $srcCon = dbConnect("uk_jobview");
    $destCon = dbConnect("uk_eplanning");

    $sql = "TRUNCATE TABLE ep_pmletters";
    $srcstmt = $destCon->prepare($sql);
    $srcstmt->execute();

    $sql = "SELECT letter_id, letter_groupid, letter_systemid, letter_filename, letter_senddate, 
                    letter_type, letter_data, letter_year, letter_isemail, letter_answerid 
            FROM jv_pmletters";
    $srcstmt = $srcCon->prepare($sql);
    $srcstmt->execute();

    $migrated = 0;
    $error = 0;
    $warning = 0;
    while(($letter = $srcstmt->fetch(PDO::FETCH_OBJ)))
    {
        $letterData = json_decode($letter->letter_data);
        if(!$letterData)
        {
            echo "Corrupt letter data for letter id ".$letter->letter_id.". Letter skipped\n";
            $error++;
            continue;
        }

        $sql = "SELECT system_id, system_systemid, system_servicearea, system_servicezone, system_siteid
                FROM ep_systems
                WHERE system_systemid = :id";
        $stmt = $destCon->prepare($sql);
        $stmt->bindValue("id", substr($letter->letter_systemid, 3));
        $stmt->execute();
        $system = $stmt->fetch(PDO::FETCH_OBJ);
        if(!$system)
        {
            echo "Unable to locate system id ".$letter->letter_systemid." in SMAX data. letter skipped\n";
            $error++;
            continue;
        }

        $letterData->site->siteid = $system->system_siteid;
        $letterData->site->serviceArea = $system->system_servicearea;
        $letterData->site->serviceZone = $system->system_servicezone;

        // $destCon->beginTransaction();

        $continue = false;
        $warned = false;
        foreach ($letterData->site->systemList as $sys)
        {
            $sys->number = $sys->systemid = substr($sys->id, 3);
            $sys->id = $system->system_id;
            $sys->systemDescription = $sys->systemname;
            $sys->systemname = null;

            foreach ($sys->jobList as $job)
            {
                $job->systemid = $system->system_id;
                $job->systemNumber = $system->system_systemid;

                $sql = "SELECT case_id, case_number, 
                                workorder_id, workorder_number, workorder_levelofservice, case_type,
                                event_id,
                    CASE WHEN LENGTH(case_description) > 55 THEN SUBSTR(case_description, 47, 8) ELSE '' END AS jobNumber
                FROM ep_jobcases
                INNER JOIN ep_jobworkorders ON (workorder_caseid = case_id)
                LEFT JOIN ep_jobevents ON (event_workorderid = event_id)                                            
                WHERE CASE WHEN LENGTH(case_description) > 55 THEN SUBSTR(case_description, 47, 8) ELSE '' END = :number";
                $stmt = $destCon->prepare($sql);
                $stmt->bindValue("number", $job->number);
                $stmt->execute();
                $case = $stmt->fetch(PDO::FETCH_OBJ);
                if(!$case)
                {
                    $job->eventId = null;
                    $job->workorderId  = null;

                    echo "MUST job ".$job->number." not found in SMAX data. Must number used.\n";
                    $warned = true;
                }
                else
                {
                    $job->number = $case->workorder_number;
                    $job->eventId = $case->event_id;
                    $job->workorderId  = $case->workorder_id;
                    $job->activitytype = $case->case_type;
                    $job->scheduletype = $case->workorder_levelofservice;

                    if($case->event_id)
                    {
                        $sql = "INSERT INTO ep_pmletterxjob (jobletter_eventid, jobletter_letterid) 
                                                VALUES (:ev, :job)";
                        $stmt = $destCon->prepare($sql);
                        $stmt->bindValue("ev", $case->event_id);
                        $stmt->bindValue("job", $letter->letter_id, PDO::PARAM_INT);
                        $stmt->execute();
                    }

                }
            }
            if($continue)
                continue;
        }
        if($continue)
        {
            // $destCon->rollBack();
            continue;
        }

        $letter->letter_systemid = $system->system_id;
        $letter->letter_data = json_encode($letterData);

        $sql = "INSERT INTO ep_pmletters (letter_id, letter_groupid, letter_systemid, letter_filename, letter_senddate, letter_type, 
                                            letter_data, letter_year, letter_isemail, letter_answerid, letter_chatter_message) 
                                VALUES (:id, :group, :system, :filename, :senddate, :type, :data, :year, 1, :answer, NOW())";
        $stmt = $destCon->prepare($sql);
        $stmt->bindValue("id", $letter->letter_id, PDO::PARAM_INT);
        $stmt->bindValue("group", $letter->letter_groupid);
        $stmt->bindValue("system", $letter->letter_systemid);
        $stmt->bindValue("filename", $letter->letter_filename);
        $stmt->bindValue("senddate", $letter->letter_senddate);
        $stmt->bindValue("type", $letter->letter_type);
        $stmt->bindValue("data", $letter->letter_data);
        $stmt->bindValue("year", $letter->letter_year);
        $stmt->bindValue("answer", $letter->letter_answerid, PDO::PARAM_INT);
        $stmt->execute();

        if(!$warned)
            $migrated++;
        else
            $warning++;

        //if($migrated >= 10)
        //    break;

        // $destCon->commit();

    }

    echo $migrated." pm letters migrated\n";
    echo $warning. " pm letters migrated with warnings\n";
    echo $error. " pm letters could not be migrated\n";
    die("Done...");
}
catch (Exception $e)
{
    die("ERROR: ". $e->getMessage());
}

Anyone any ideas?

What’s the server error log tell you went wrong?

Sorry I forgot. There are no entries in apache or PHP error log

Check your PHP.ini config for where the error log is. It… shouldnt be able to dump a 500 without putting an error log entry SOMEWHERE, it just might not be where you think it is putting it.

As far as error generating itself… if it gets through a couple hundred records fine… the only possibilities that strike to mind are that you’re somehow hitting the 4G limit, or that the 219th record has some fundamentally missing data thats causing an error and aborting the script (though that should exit cleanly through the try/catch…)

Ok,

for all who have the same problem:

It is a special setting in the Apache http.conf from MAMP PRO which is called

MAMP_FastCgiServer_MAMP

You need to change it to

MAMP_FastCgiServer_MAMP -idle-timeout 3600

No idea what a FASTCgiServer is good for and why it must timeout in 30s per default. but that is really a mess.

1 Like

Configuration on top of configuration. That always ends well.

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.