MysqlError：对于键'PRIMARY'重复条目'1-5'插入不确定如何 [英] MysqlError: Duplicate entry '1-5' for key 'PRIMARY' on insert unsure of how

问题描述

我得到错误 MysqlError：重复条目'1-5'的密钥'PRIMARY'如下面的代码所示。它只发生一次（我可以检测，但它是随机的），我找不到一个原因（New Relic报告），但我不能再现，我没有更多的信息，除了行号和错误给出。

I am getting the error MysqlError: Duplicate entry '1-5' for key 'PRIMARY' as shown below in the code. It only happened once (that I could detect, but it was random) and I couldn't find a cause (New Relic reported), but I cannot reproduce and I don't have much more information except the line number and the error given. The schema and code is below.

num_rows（）不知何故返回一个不为1的值，不应该。

num_rows() is somehow returning a value that is not 1 even though it shouldn't. If someone can give some insight on how to debug or fix that would be helpful.

这里是我的location_items架构：

Here is my schema for location_items:

CREATE TABLE `phppos_location_items` (
  `location_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `location` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `cost_price` decimal(23,10) DEFAULT NULL,
  `unit_price` decimal(23,10) DEFAULT NULL,
  `promo_price` decimal(23,10) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `quantity` decimal(23,10) DEFAULT '0.0000000000',
  `reorder_level` decimal(23,10) DEFAULT NULL,
  `override_default_tax` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`location_id`,`item_id`),
  KEY `phppos_location_items_ibfk_2` (`item_id`),
  CONSTRAINT `phppos_location_items_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `phppos_locations` (`location_id`),
  CONSTRAINT `phppos_location_items_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `phppos_items` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

代码如下：

//Lock tables involved in sale transaction so we do not have deadlock
$this->db->query('LOCK TABLES '.$this->db->dbprefix('customers').' WRITE, '.$this->db->dbprefix('receivings').' WRITE, 
'.$this->db->dbprefix('store_accounts').' WRITE, '.$this->db->dbprefix('receivings_items').' WRITE, 
'.$this->db->dbprefix('giftcards').' WRITE, '.$this->db->dbprefix('location_items').' WRITE, 
'.$this->db->dbprefix('inventory').' WRITE, 
'.$this->db->dbprefix('people').' READ,'.$this->db->dbprefix('items').' WRITE
,'.$this->db->dbprefix('employees_locations').' READ,'.$this->db->dbprefix('locations').' READ, '.$this->db->dbprefix('items_tier_prices').' READ
, '.$this->db->dbprefix('location_items_tier_prices').' READ, '.$this->db->dbprefix('items_taxes').' READ, '.$this->db->dbprefix('item_kits').' READ
, '.$this->db->dbprefix('location_item_kits').' READ, '.$this->db->dbprefix('item_kit_items').' READ, '.$this->db->dbprefix('employees').' READ , '.$this->db->dbprefix('item_kits_tier_prices').' READ
, '.$this->db->dbprefix('location_item_kits_tier_prices').' READ, '.$this->db->dbprefix('suppliers').' READ, '.$this->db->dbprefix('location_items_taxes').' READ
, '.$this->db->dbprefix('location_item_kits_taxes'). ' READ, '.$this->db->dbprefix('item_kits_taxes'). ' READ');


    // other code for inserting data into other tables that are not relevant.

    foreach($items as $line=>$item)
    {
        $cur_item_location_info->quantity = $cur_item_location_info->quantity !== NULL ? $cur_item_location_info->quantity : 0;
        $quantity_data=array(
            'quantity'=>$cur_item_location_info->quantity + $item['quantity'],
            'location_id'=>$this->Employee->get_logged_in_employee_current_location_id(),
            'item_id'=>$item['item_id']

        );
            $this->Item_location->save($quantity_data,$item['item_id']);
    }
    // other code for inserting data into other tables that are not relevant.

    $this->db->query('UNLOCK TABLES');


class Item_location extends CI_Model
{
    function exists($item_id,$location=false)
    {
        if(!$location)
        {
            $location= $this->Employee->get_logged_in_employee_current_location_id();
        }
        $this->db->from('location_items');
        $this->db->where('item_id',$item_id);
        $this->db->where('location_id',$location);
        $query = $this->db->get();

        return ($query->num_rows()==1);
    }


    function save($item_location_data,$item_id=-1,$location_id=false)
    {
        if(!$location_id)
        {
            $location_id= $this->Employee->get_logged_in_employee_current_location_id();
        }

        if (!$this->exists($item_id,$location_id))
        {
            $item_location_data['item_id'] = $item_id;
            $item_location_data['location_id'] = $location_id;

            //MysqlError: Duplicate entry '1-5' for key 'PRIMARY'
            return $this->db->insert('location_items',$item_location_data);
        }

        $this->db->where('item_id',$item_id);
        $this->db->where('location_id',$location_id);
        return $this->db->update('location_items',$item_location_data);

    }
}

function get_logged_in_employee_current_location_id()
    {
        if($this->is_logged_in())
        {
            //If we have a location in the session
            if ($this->session->userdata('employee_current_location_id')!==FALSE)
            {
                return $this->session->userdata('employee_current_location_id');
            }

            //Return the first location user is authenticated for
            return current($this->get_authenticated_location_ids($this->session->userdata('person_id')));
        }

        return FALSE;
    }

推荐答案

检查是否存在先于在事务之外插入数据，因为这留下了数据在平均时间改变的可能性。事实上，你已经看到这个错误一次，但它不容易重复，让我想知道是否可能发生了。

It's not a good idea to check for existence prior to inserting data outside a transaction as this leaves open the possibility of data changing in the mean time. The fact that you've seen this error once but it isn't easily repeatable makes me wonder whether this might have happened.

建议更改第一个如果在 save 函数中阻塞生成以下SQL的代码：

INSERT INTO location_items（item_id，location_id）

VALUES（ $ item_id ， $ location_id ）

启用重复键更新

Would suggest changing the code beneath the first if block in the save function to something that generates the following SQL instead: INSERT INTO location_items (item_id, location_id) VALUES ($item_id,$location_id) ON DUPLICATE KEY UPDATE

这覆盖了单一原子语句中的存在检查和插入或更新。 （要进一步说明如何实际实现它，我需要访问 db 代码。）

This covers the existence check and insert or update in a single atomic statement. (To take this any further and say how to actually implement it I'd need access to the db code.)

EDIT：很抱歉，只需注意到 db 代码是CodeIgniter。这是新的框架，但上述方法看起来完全可能从这里 a>。类似这样：

Sorry, only just noticed the db code is CodeIgniter. Am new to this framework but the above method looks perfectly possible from a brief look here. Something like this:

$sql = "INSERT INTO location_items (item_id, location_id)"
    . " VALUES (?, ?)"
    . " ON DUPLICATE KEY UPDATE"; 
$this->db->query($sql, array($item_id, $location_id));

（如果由于某种原因你不想这样做，会将事务中的语句换成（<$ c $在存在检查和 $ this-> db-> trans_complete（）; 之前，c> $ this-> db-> trans_start（）; $ c>插入/更新后，但IMO引入了不必要的复杂性 - 个人更喜欢第一种方法。）

(If for some reason you prefer not to do this, another way to keep it atomic would be to wrap the statements within a transaction instead ($this->db->trans_start(); before the existence check and $this->db->trans_complete(); after the insert/update. But IMO this introduces unnecessary complexity - personally much prefer the first method.)

这篇关于MysqlError：对于键'PRIMARY'重复条目'1-5'插入不确定如何的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！