OK, I ran a few tests.
First a little more background - the program parses Apache log files and builds a web access database. The part we're talking about here reads each hit record from the log file, decides whether to log it (I throw away pretty much everything except page views) and allocates collections of hits into visits.
When I'm done with each hit object, I invoke a storeHit() method that saves the hit to a database table. Likewise for each visit.
Each hit and visit opens a database connection when it needs to access the db, runs a query, then closes the connection. At any given time, there may be several visits open (new visits can start before old ones finish). There should only be one hit open at a time.
The hit query is simply an INSERT or an UPDATE that stores a single database record of 22 fields.
The visit query is likewise an INSERT or an UPDATE that stores a single record of 18 fields.
Now, about the tests:
- The errors occur at different places in the input data stream. We're talking about anywhere between 10 and 40 errors out of 14,600 records processed.
- There's nothing in the slow query log.
- I'm routing all db connects and close's through a pair of functions (below). I added some code (removed here for clarity) to log opens, closes, and errors. There are never more than a few connections open when the errors occur. All connections are closed (explicitly, using db_close() below) by the time the program finishes.
- the mysqli_connect_errno() function does successfully detect failures to connect. I've been using it to log when errors occur.
- I tried sleep'ing after processing each log file. sleep(20) reduces the errors, sleep(30) eliminates them (repeatably).
- There's an awful lot of code. As a sample, here's the storeHit() method from the hit class. Note that the connection is opened when we start and closed when we finish. I even unset() the mysqli object to try to force it to be released.
All connects and closes are routed throught the two functions below: db_connect() and db_close().
Code: Select all
function db_connect() {
$db = new mysqli($hostname, $username, $password, $database);
return $db;
}
/** db_close - close the connection and return a status.
* If the handle was invalid, db_close returns true.
*
* @param handle db Handle to a mysqli object.
* @return boolean status TRUE if close was successful. False otherwise.
*/
function db_close($db) {
if($db) {
$status = $db->close();
} else {
$status = true;
}
return $status;
}
Below is the storeHit() method from the Hit class. The storeVisit() method for the Visit class is very similar.
Code: Select all
/** storeHit - Save the hit to the database.
* If the hit has already been stored, storeHit updates the record. Otherwise it does an
* INSERT. If the store was successful, storeHit returns the hitID (generated by the database
* upon the first save).
* If the store fails, storeHit creates a Logger object and logs the error.
*
* @param none.
* @return integer hitID If successful.
* @return boolean FALSE If failure.
*
*/
public function storeHit() {
$this->dbHandle = db_connect();
$safeQueryString = addslashes($this->queryString);
$safeReferer = addslashes($this->referer);
// If the hit is already stored, then do an UPDATE. Otherwise, an INSERT.
if($this->hitIsStored) {
$query = "update hit set " .
" hitDate = '{$this->hitDate}'," .
" serverName = '{$this->serverName}'," .
" localIP = '{$this->localIP}'," .
" requestMethod = '{$this->requestMethod}'," .
" userID = '{$this->userID}'," .
" urlPath = '{$this->urlPath}'," .
" queryString = '$safeQueryString'," .
" remoteUser = '{$this->remoteUser}'," .
" remoteIP = '{$this->remoteIP}'," .
" userAgent = '{$this->userAgent}'," .
" cookieString = '{$this->cookieString}'," .
" userCookie = '{$this->userCookie}'," .
" sessionCookie = '{$this->sessionCookie}'," .
" referer = '$safeReferer'," .
" host = '{$this->host}'," .
" status = '{$this->status}'," .
" bytesSent = '{$this->bytesSent}'," .
" timeToServe = '{$this->timeToServe}'," .
" timeStamp = '{$this->timestamp}'," .
" visitID = '{$this->visitID}'," .
" siteID = '{$this->siteID}'";
} else {
$query = "insert into hit (" .
" hitDate, serverName, localIP, requestMethod, userID, urlPath, queryString, " .
" remoteUser, remoteIP, userAgent, cookieString, userCookie, sessionCookie, " .
" referer, host, status, bytesSent, timeToServe, timestamp, visitID, siteID) ";
$query .= "values ( " .
"'{$this->hitDate}', '{$this->serverName}', '{$this->localIP}', '{$this->requestMethod}'," .
"'{$this->userID}', '{$this->urlPath}', '{$safeQueryString}', '{$this->remoteUser}'," .
"'{$this->remoteIP}', '{$this->userAgent}', '{$this->cookieString}','{$this->userCookie}'," .
"'{$this->sessionCookie}', '{$safeReferer}', '{$this->host}', '{$this->status}'," .
"'{$this->bytesSent}', '{$this->timeToServe}', '{$this->timestamp}', '{$this->visitID}'," .
"'{$this->siteID}'" .
")";
}
$result = $this->dbHandle->query($query);
if (!$result) {
include_once('lib/Logger.php');
$logger = new Logger("WebLogDBErrorLog.log");
$err = $this->dbHandle->error;
$this->error = "Hit->store() Database error executing |$query|\n" .
" Error was \"$err\"\n";
$this->error .= "Hit details\n";
$this->error .= $this->toString() . "\n";
$logger->logerror($this->error);
$returnStatus = false;
} else {
$this->hitID = $this->dbHandle->insert_id; // It was a new record. Get the key.
$this->hitIsStored = true;
$returnStatus = $this->hitID;
}
// Close the database connection and release the mysqli object before leaving.
if(db_close($this->dbHandle)) {
unset($this->dbHandle);
}
return $returnStatus;
} // End of storeHit.
I'd appreciate any further suggestions!
Thanks for listening,
smac