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

Duplicate entry '1' for key 'PRIMARY'

silence

Well-known member
#1
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');
    }
}
?>
 

Chris D

XenForo developer
Staff member
#2
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.
 

silence

Well-known member
#3
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.
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 :(
 

silence

Well-known member
#5
What's in your model?
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)
        );
    }
}
 

Liam W

Well-known member
#6
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.
 

silence

Well-known member
#7
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.
Unfortunately not :(
I'm using MySqli and MariaDB so it may be some glitch since honestly, I'm dumbfounded.
 

silence

Well-known member
#8
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();'
 

Liam W

Well-known member
#9
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']);
 

Liam W

Well-known member
#11
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 :)
 

silence

Well-known member
#12
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.
 

Liam W

Well-known member
#13
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.
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']);
 

silence

Well-known member
#14
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']);
2nd method: Mysqli statement execute error : Field 'user_id' doesn't have a default value
 

Biker

Well-known member
#15
If I gotta put on a tribal suit and cover myself with butter, I will!
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.
 

silence

Well-known member
#16
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.
What is this Willy Wonka's Chocolate factory?!
And a grocery store huh? They should have everything I need! Genius!
 

silence

Well-known member
#18
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?
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.
 

Jeremy

Well-known member
#19
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.
You realize that is the same exact query, right?