CODEWITHSUNDEEP
phpmysql
GregM
GregM

Reputation: 3734

After running a bunch of queries the table keeps crashing

Table 'geoPoint' is marked as crashed and should be repaired

I call this function with a file that has 500 lines this results in 500 queries that get called in quick succession...

This may not be the most efficient, but why does it constantly crash the geopoint table??

CSVImport($table, $fields,$_FILES["file"["tmp_name"],$cardID,2);

function CSVImport($table, $fields, $file,$cardID,$intype) {
    if($file == null)
    {
        echo "Error No File";
         die('Cannot open uploaded file.');
    }
    $handle = fopen($file,'r');
    if(!$handle) die('Cannot open uploaded file.');

    $tmpfname = tempnam("/tmp", "FOO");
    copy($file, $tmpfname);
    $row_count = 0;
    $rows = array();

    while (($data = fgetcsv($handle, 0, ",")) !== FALSE) {
        $row_count++;
        $itemcount = 0;

        $NAME = $data[0];
        $LAT = $data[1];
        echo "<td>{$LAT}</td>";
        $LON = $data[2];
        echo "<td>{$LON}</td>";
        $STREETNUM = $data[3];
        echo "<td>{$STREETNUM}</td>";
        $STREETNAME = $data[4];
        echo "<td>{$STREETNAME}</td>";
        $CITY = $data[5];
        echo "<td>{$CITY}</td>";
        $ZIP = $data[6];
        echo "<td>{$ZIP}</td>";
        $COUNTY = $data[7];
        echo "<td>{$COUNTY}</td>";
        $UUID = $data[8];
        echo "<td>{$GEOTYPE}</td>";
        $DATE = $data[9];
        echo "<td>{$DATE}</td>";
        $GARCOMUUID = $data[10];
        echo "<td>{$GARCOMUUID}</td>";
        $CITYUUID = $data[11];
        echo "<td>{$CITYUUID}</td>";
        $CITYSPECUUID = $data[12];
        $latRound = number_format((float)$LAT, 3, '.', '');
        $lonRound = number_format((float)$LON, 3, '.', '');
        number_format((float)$foo, 2, '.', '');

        echo "<td>{$CITYSPECUUID}</td>";

        $custquery="SELECT * FROM `geoPoint` WHERE `lat` LIKE '%{$latRound}%' AND `lon` LIKE '%{$lonRound}%' AND `street_num` = {$STREETNUM}";

        $custre=mysql_query($custquery);
        if (!$custre) {    echo 'Could not run custre query: ' . mysql_error();    exit;    }
        $custarr = mysql_fetch_array($custre, MYSQL_ASSOC);

        if($custarr == null)
            $itemisnew = 0;
        else
        {
            $itemisnew = mysql_num_rows(custarr);
        }
        if($itemisnew == 0)
            echo "<td>NEW</td>";
        else if($itemisnew == 1)
            echo "<td>REPLACE</td>";
        else
            echo "<td>MULTIPLE!</td>";
    }

    fclose($handle);

Upvotes: 0

Views: 107

Answers (1)

Bill Lambert
Bill Lambert

Reputation: 31

A table crash is not something you can cause in PHP. It's the result of either a hardware failure (bad memory or disk corruption), misconfiguration, or a bug in MySQL.

When the table crashes, does anything show up in MySQL's error log ? If MySQL is set to use too much memory, it could be tripping Linux' out-of-memory handler, which would kill the MySQL process, leaving your table in a "dirty" state. You might not notice since many distros' scripts will monitor and restart a crashed daemon automatically. That would be my first guess.

Upvotes: 3

Related Questions