1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Database performance and Duplicate Entry

Discussion in 'XenForo Development Discussions' started by chrisj, Jul 13, 2012.

  1. chrisj

    chrisj Active Member

    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:
    PHP:
    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.

    PHP:
    // 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();
    }
    PHP:
    $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();
     
  2. chrisj

    chrisj Active Member

    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?
     
  3. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

  4. chrisj

    chrisj Active Member

    That's what I did here:

    PHP:
    $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.
     
  5. Luke F

    Luke F Well-Known Member

    What indices are on the table(s)?
     
  6. chrisj

    chrisj Active Member

    I have not created any.

    This is my table structure:
    Code:
    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`))
     
  7. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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.
     
  8. chrisj

    chrisj Active Member

    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.

    PHP:
        /**
        * 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'));
        }
     
  9. chrisj

    chrisj Active Member

    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?
     

Share This Page