Duplicate entry '1' for key 'PRIMARY'

silence

silence

Well-known member
Alright I've been trying to fix this all night. Every other datawriter I look at is doing this exact thing, and not receiving this error D:
It simply does not compute to me, I am checking if there is existing data, yet it's trying to overwrite a duplicate entry!

Controller:
PHP: 
        if (empty($info['auth_id']))
        {
            $dwAccount = XenForo_DataWriter::create('Teamspeak_DataWriter_Account');
            $teamspeak = $this->getHelper("Teamspeak_Helper_Query");

            $index = array(
                        'user_id' => $user_id,
                        'auth_id' => $auth_id
            );
            if (!empty($info['user_id']))
            {
                $dwAccount->setExistingData($index);
            }
            $dwAccount->bulkSet($index);
            $dwAccount->save();
            if (!empty($info['user_id']))
            {
                $teamspeak->_updateServerGroup($user_id, $auth_id, $info['auth_id']);
            }
            else
            {
                $teamspeak->_updateServerGroup($user_id, $auth_id);
            }
            return $this->responseRedirect(
                XenForo_ControllerResponse_Redirect::SUCCESS,
                XenForo_Link::buildPublicLink('account/teamspeak')
            );
        }
DataWriter:
PHP: 
<?php

class Teamspeak_DataWriter_Account extends XenForo_DataWriter
{
    /**
    * Gets the fields that are defined for the table. See parent for explanation.
    *
    * @return array
    */
    protected function _getFields()
    {
        return array(
            'xf_teamspeak' => array(
                'user_id'    => array(
                    'type' => self::TYPE_UINT
                ),
                'auth_id'    => array(
                    'type' => self::TYPE_STRING, 'required' => true
                ),
            )
        );
    }
    /**
    * 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 (!$id = $this->_getExistingPrimaryKey($data))
        {
            return false;
        }
        return array('xf_teamspeak' => $this->_getAccountModel()->getUserID($id));
    }
    /**
    * 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'));
    }
    /**
    * Get the Teamspeak account model.
    *
    * @return Teamspeak_Model_Account
    */
    protected function _getAccountModel()
    {
        return $this->getModelFromCache('Teamspeak_Model_Account');
    }
}
?>
 
Try this very small adjustment in your _getExistingData function:
PHP: 
if (!$id = $this->_getExistingPrimaryKey($data, 'user_id'))
{
     return false;
}

Where 'user_id' is the primary key of your table -- I presume it is 'user_id'.

The _getExistingPrimaryKey function is seeing that you're passing an array of data to it so it tries to work out itself what the primary key of your table is. One way it can do this is if you have an Auto Incrementing value in your table -- and you don't have one.

So it effectively fails in finding the primary key, therefore it believes you're adding a new record rather than updating.

What my small code change does is it says, "This is the data and 'user_id' is the primary key. Use that." And it does, and the process becomes an update rather than an insert.

An alternative is, when you're setting the $writer->setExistingData you could pass $index['user_id'] as the existing data. Either should do the trick.
 
Chris Deeming said:
Try this very small adjustment in your _getExistingData function:
PHP: 
if (!$id = $this->_getExistingPrimaryKey($data, 'user_id'))
{
     return false;
}

Where 'user_id' is the primary key of your table -- I presume it is 'user_id'.

The _getExistingPrimaryKey function is seeing that you're passing an array of data to it so it tries to work out itself what the primary key of your table is. One way it can do this is if you have an Auto Incrementing value in your table -- and you don't have one.

So it effectively fails in finding the primary key, therefore it believes you're adding a new record rather than updating.

What my small code change does is it says, "This is the data and 'user_id' is the primary key. Use that." And it does, and the process becomes an update rather than an insert.

An alternative is, when you're setting the $writer->setExistingData you could pass $index['user_id'] as the existing data. Either should do the trick.
Click to expand...
Yes I had that before, I removed it since I thought that may have been the issue. However, adding it again and the issue still persists.
I adjusted the controller to your edit but the issue persists :(
 
Liam W said:
What's in your model?
Click to expand...
PHP: 
<?php

class Teamspeak_Model_Account extends XenForo_Model
{
    public function getAuthID($user_id)
    {
        $auth_id = $this->_getDb()->fetchRow('SELECT auth_id FROM xf_teamspeak WHERE user_id = ?', $user_id);
        if (!empty($auth_id))
        {
            $string = reset($auth_id);
            return $string;
        }
        return 'None';
    }
    public function getUserID($user_id)
    {
        return $this->_getDb()->fetchRow('SELECT user_id FROM xf_teamspeak WHERE user_id = ?', $user_id);
    }
    public function checkUser($user_id, $auth_id)
    {
        $query = $this->_getDb()->fetchRow('SELECT user_id, auth_id FROM xf_teamspeak WHERE user_id = "' . $user_id . '" OR auth_id = "' . $auth_id . '"');
        return array(
            'user_id' => (isset($query[0]['user_id']) ? $query[0]['user_id'] : null),
            'auth_id' => (isset($query[0]['auth_id']) ? $query[0]['auth_id'] : null)
        );
    }
}
 
Does it work if you change

PHP: 
 return $this->_getDb()->fetchRow('SELECT user_id FROM xf_teamspeak WHERE user_id = ?', $user_id);

to

PHP: 
 return $this->_getDb()->fetchRow('SELECT * FROM xf_teamspeak WHERE user_id =  '. $user_id);

That is what my datawriters look like (you know what I mean), and it works fine.

However, make sure you're setting existing data in the datawriter when you're using it. Right now it's trying to add a new record with a userid that's already specified.
 
Liam W said:
Does it work if you change

PHP: 
 return $this->_getDb()->fetchRow('SELECT user_id FROM xf_teamspeak WHERE user_id = ?', $user_id);

to

PHP: 
 return $this->_getDb()->fetchRow('SELECT * FROM xf_teamspeak WHERE user_id =  '. $user_id);

That is what my datawriters look like (you know what I mean), and it works fine.
Click to expand...
Unfortunately not :(
I'm using MySqli and MariaDB so it may be some glitch since honestly, I'm dumbfounded.
 
Gah you deleted your post! But here is what you requested anyways ;)
PHP: 
<?php

class Teamspeak_ControllerPublic_Account extends XFCP_Teamspeak_ControllerPublic_Account
{
    public function actionTeamspeak()
    {
        $visitor = XenForo_Visitor::getInstance();
        $user_id = $visitor->get('user_id');
        $auth = $this->_getUserModel()->getUserAuthenticationObjectByUserId($visitor['user_id']);
        if (!$auth)
        {
            return $this->responseNoPermission();
        }
        if (!$visitor->hasPermission('teamspeak', 'teamspeakAccount'))
        {
            return $this->responseNoPermission();
        }
        $auth_id = $this->_getAccountModel()->getAuthID($user_id);
        $viewParams = array(
            'auth_id' => $auth_id
        );
        $helper = new XenForo_ControllerHelper_Account($this);
        return $helper->getWrapper('account', 'teamspeak',
            $this->responseView('XenForo_ViewPublic_Account_Teamspeak', 'account_teamspeak', $viewParams)
        );
    }
    public function actionTeamspeakUpdate()
    {
        $input = $this->getInput();
        $user_id = XenForo_Visitor::getUserId();
        $auth_id = $input->filterSingle('auth_id', XenForo_Input::STRING);
        $info = $this->_getAccountModel()->checkUser($user_id, $auth_id);
        if (empty($info['auth_id']))
        {
            $dwAccount = XenForo_DataWriter::create('Teamspeak_DataWriter_Account');
            $teamspeak = $this->getHelper("Teamspeak_Helper_Query");
            $index = array(
                        'user_id' => $user_id,
                        'auth_id' => $auth_id
            );
            if (!empty($info['user_id']))
            {
                $dwAccount->setExistingData($index['user_id']);
            }
            $dwAccount->bulkSet($index);
            $dwAccount->save();
            if (!empty($info['user_id']))
            {
                $teamspeak->_updateServerGroup($user_id, $auth_id, $info['auth_id']);
            }
            else
            {
                $teamspeak->_updateServerGroup($user_id, $auth_id);
            }
            return $this->responseRedirect(
                XenForo_ControllerResponse_Redirect::SUCCESS,
                XenForo_Link::buildPublicLink('account/teamspeak')
            );
        }
        else
        {
            return $this->responseError("That UserID is currently in use! Please try another!");
        }
    }
    protected function _getAccountModel()
    {
        return $this->getModelFromCache('Teamspeak_Model_Account');
    }
}
the error occurs at ' $dwAccount->save();'
 
One last thing :)

Could you try changing this line in your controller to the one below it?

PHP: 
$dwAccount->setExistingData($index);

PHP: 
$dwAccount->setExistingData($index['user_id']);
 
Ok, you've already done that...

Last thing before I become stumped as well ;)

Change the fetchRow to fetchAssoc...

I'm just thinking of all the error's i've had and what I did to fix them :)
 
Nope that didn't work either :(
Don't worry I'm not hesitant to try anything! If I gotta put on a tribal suit and cover myself with butter, I will!
I'm using XF 1.2b4 but I'm certain I was getting this error on 1.1.4 as well.
 
silence said:
Nope that didn't work either :(
Don't worry I'm not hesitant to try anything! If I gotta put on a tribal suit and cover myself with butter, I will!
I'm using XF 1.2b4 but I'm certain I was getting this error on 1.1.4 as well.
Click to expand...

All my addons are on 1.2 and they use a similar system...

One thing, could you either:

1. Remove user_id from the index array [OR]
2. Don't bulk set the array, but set the other thing instead (auth_id).

$dw->set($index['auth_id']);
 
Liam W said:
All my addons are on 1.2 and they use a similar system...

One thing, could you either:

1. Remove user_id from the index array [OR]
2. Don't bulk set the array, but set the other thing instead (auth_id).

$dw->set($index['auth_id']);
Click to expand...
2nd method: Mysqli statement execute error : Field 'user_id' doesn't have a default value
 
silence said:
If I gotta put on a tribal suit and cover myself with butter, I will!
Click to expand...

That won't do it. You also have to stick a feather in your ear, dance on one leg and yell Wauka Wauka Wuaka. Oh, this must be done during a full moon in the middle of the grocery store.
 
Biker said:
That won't do it. You also have to stick a feather in your ear, dance on one leg and yell Wauka Wauka Wuaka. Oh, this must be done during a full moon in the middle of the grocery store.
Click to expand...
What is this Willy Wonka's Chocolate factory?!
And a grocery store huh? They should have everything I need! Genius!
 
silence said:
2nd method: Mysqli statement execute error : Field 'user_id' doesn't have a default value
Click to expand...

I'm currently walking to Tesco, however this is annoying me now :P

Could you send me the complete package and I'll look at it later?
 
Liam W said:
I'm currently walking to Tesco, however this is annoying me now :p

Could you send me the complete package and I'll look at it later?
Click to expand...
It's posted in the resource section 'Teamspeak Integration' but the updated package I'm working on just has like 3 less queries (#ballin). It should suffer from the same bug though.
 
Liam W said:
Does it work if you change

PHP: 
 return $this->_getDb()->fetchRow('SELECT user_id FROM xf_teamspeak WHERE user_id = ?', $user_id);

to

PHP: 
 return $this->_getDb()->fetchRow('SELECT * FROM xf_teamspeak WHERE user_id =  '. $user_id);

That is what my datawriters look like (you know what I mean), and it works fine.

However, make sure you're setting existing data in the datawriter when you're using it. Right now it's trying to add a new record with a userid that's already specified.
Click to expand...
You realize that is the same exact query, right?
 
You must log in or register to reply here.

Similar threads

btmgreg
  • Question Question
XF 2.2 ACP Error - Job XF:Feeder: MySQL query error [1062]: Duplicate entry '4-1641832730903322626' for key 'PRIMARY'
Replies
5
Views
706
btmgreg
btmgreg
B
MySQL query error [1062]: Duplicate entry '9738' for key 'PRIMARY'
Replies
1
Views
1K
by_carizma
B
M
Fixed XF\Db\DuplicateKeyException: MySQL query error [1062]: Duplicate entry '241-0' for key 'PRIMARY' src/XF/Db/AbstractStatement.php:228
Replies
1
Views
1K
XF Bug Bot
XF Bug Bot
R
Not a bug MySQL query error [1062]: Duplicate entry '1597117-79' for key 'PRIMARY' src/XF/Db/AbstractStatement.php:217
Replies
3
Views
628
Chris D
Chris D
T
Partial fix Duplicate entry '1133-405' for key 'PRIMARY'
Replies
1
Views
814
Mike
Mike
Back
Top Bottom