Database performance and Duplicate Entry

[chrisj]

I have a table called psn_user_game_trophy which associates 3 different models and includes a date. So it is a total of 4 int(11)'s. So far the average has been at 400 per user so it can easily pass 1 million with a small forum. What kind of options should I take to ensure this table does not hurt the performance of the site?

Here is an typical query for this table:

return $this->_getDb()->fetchAll($this->limitQueryResults('
    SELECT *
    FROM psn_user_game_trophy
    INNER JOIN psn_game ON psn_user_game_trophy.game_id = psn_game.game_id
    INNER JOIN psn_game_trophy ON psn_user_game_trophy.game_id = psn_game_trophy.game_id AND
    psn_user_game_trophy.trophy_id = psn_game_trophy.trophy_id
    WHERE ' . $whereClause . '
    ORDER BY trophydate desc
', $limitOptions['limit'], $limitOptions['offset']));

Also I noticed I had two duplicate entry errors within two hours for two different tables. Both are updated in my resource intensive cron.

Below are the two sections of the code that the error occurred in. I believe a duplicate entry shouldn't occur unless it is a mysql or xenforo error.

// find if there
$existingUserGameTrophy =
    $psnUserGameTrophyModel->getPsnUserGameTrophyByUserIdGameIdTrophyId($userId, $existingGame['game_id'], $userTrophy['id']);
   
if(!empty($existingUserGameTrophy)){
    // user already had trophy, we should never have to update this
} else {
    $dwPsnUserGameTrophy = XenForo_DataWriter::create('PsnLeaderboards_DataWriter_PsnUserGameTrophy');
    $dwPsnUserGameTrophy->set('user_id', $userId);
    $dwPsnUserGameTrophy->set('game_id', $existingGame['game_id']);
    $dwPsnUserGameTrophy->set('trophy_id', $userTrophy['id']);
    $dwPsnUserGameTrophy->set('trophydate', $userTrophy['trophydate']);
    $dwPsnUserGameTrophy->save();
}

$existingUserGame = $psnUserGameModel->getPsnUserGameByUserIdGameId($userId, $psnGame['game_id']);
 
$dwPsnUserGame = XenForo_DataWriter::create('PsnLeaderboards_DataWriter_PsnUserGame');
 
if (!empty($existingUserGame['user_id']) && !empty($existingUserGame['game_id'])) {
    $dwPsnUserGame->setExistingData(array($existingUserGame['user_id'], $existingUserGame['game_id']));
} else {
    $dwPsnUserGame->set('user_id', $userId);
    $dwPsnUserGame->set('game_id', $psnGame['game_id']);
}
 
$dwPsnUserGame->set('user_total',        $game['total']);
$dwPsnUserGame->set('user_platinum',    $game['platinum']);
$dwPsnUserGame->set('user_gold',        $game['gold']);
$dwPsnUserGame->set('user_silver',        $game['silver']);
$dwPsnUserGame->set('user_bronze',        $game['bronze']);
$dwPsnUserGame->set('lastupdated',        $game['lastupdated']);
 
$dwPsnUserGame->save();

 

[chrisj]

Update:
I ran the cron manually so I can view all the data that I print out and I ran into a duplicate entry but I'm almost certain that the data was not already in the database as my cron goes through all the users and this should be the first time it ever got to user 2452. I looked in the database and found the entry.

Also I saw related entries that my code never should have gotten to had the code crashed at the $dwPsnUserGameTrophy->save();
Am I having a concurrency issue?

 

[Jake Bunce]

A "duplicate entry" error would be from MySQL. That means you are inserting a new record with a key that already exists. Pretty simple.

If you are performing an insertion that might be an update then be sure to check for the existence of the key in the table before saving the datawriter, like in the example code I posted:

http://xenforo.com/community/threads/duplicate-entry-_getupdatecondition-not-working.33571/

 

[chrisj]

A "duplicate entry" error would be from MySQL. That means you are inserting a new record with a key that already exists. Pretty simple.

If you are performing an insertion that might be an update then be sure to check for the existence of the key in the table before saving the datawriter, like in the example code I posted:

http://xenforo.com/community/threads/duplicate-entry-_getupdatecondition-not-working.33571/

That's what I did here:

$dwPsnUserGame->setExistingData(array($existingUserGame['user_id'], $existingUserGame['game_id']));

and for the first time I just check to find an existing and if it is I don't bother trying to update it.

It only failed on a couple out of 2-3 hundred thousand entries. It seems that implies it may just be a mysql error. Like I said in the 2nd post, one of the times it got to a entry that should not have been in the database already and yet it performed a duplicate key entry. Mysql or xenforo may have accidentally tried to insert it twice instead of once. It's just annoying to see the error pop up but I haven't seen it in a while.

 

[Luke F]

What indices are on the table(s)?

 

[chrisj]

What indices are on the table(s)?

I have not created any.

This is my table structure:

CREATE TABLE IF NOT EXISTS `psn_user_game_trophy` (
            `user_id` INT( 11 ) NOT NULL default '0',
            `game_id` INT( 11 ) NOT NULL default '0',
            `trophy_id` INT ( 11 ) NOT NULL default '0',
            `trophydate` INT( 11 ) NOT NULL default '0',
            PRIMARY KEY (`user_id`,`game_id`,`trophy_id`))

 

[Jake Bunce]

Your table definition specifies three columns for the primary key (`user_id`,`game_id`,`trophy_id`), so you need to make sure your inserts don't collide with those three values.

 

[chrisj]

I'm pretty sure all my updating code is correct and I just saw a duplicate entry problem for a very simple psn_user account with only 1 primary key.

This is my code for psn_user_game_trophy.

    /**
    * Gets the actual existing data out of data that was passed in. See parent for explanation.
    *
    * @param mixed
    *
    * @see XenForo_DataWriter::_getExistingData()
    *
    * @return array|false
    */
    protected function _getExistingData($data)
    {
        // if you need help with this look at XenForo_DataWriter_Permission
        if (!is_array($data))
        {
            return false;
        }
        else if (isset($data['user_id'], $data['game_id'], $data['trophy_id']))
        {
            $userId = $data['user_id'];
            $gameId = $data['game_id'];
            $trophyId = $data['trophy_id'];
        }
        else if (isset($data[0], $data[1], $data[2]))
        {
            $userId = $data[1];
            $gameId = $data[1];
            $trophyId = $data[2];
        }
        else
        {
            return false;
        }
 
        return array('psn_game_trophy' =>
            $this->_getPsnUserGameTrophyModel()->getPsnUserGameTrophyByUserIdGameIdTrophyId($userId, $gameId, $trophyId));
    }
 
    /**
    * Gets SQL condition to update the existing record.
    *
    * @see XenForo_DataWriter::_getUpdateCondition()
    *
    * @return string
    */
    protected function _getUpdateCondition($tableName)
    {
        return 'user_id = ' . $this->_db->quote($this->getExisting('user_id')) .
              ' AND game_id = ' . $this->_db->quote($this->getExisting('game_id')) .
              ' AND trophy_id = ' . $this->_db->quote($this->getExisting('trophy_id'));
    }

 

[chrisj]

Update:
Am I having a concurrency issue?

I found out today this was the issue. The error was when the cron would overlap since I have it running every 15 minutes and for the first day, that is possible.

I kept on testing the cron manually and of course the issue never popped up. It's not a big deal anymore know that I know what was causing the error but any idea if there is a simple solution? I thought about a semaphore but then the cron would be locked out if there was ever a bug that caused the original cron to not finish.

Should I throw all datawriter saves into a try?