title: MysqlError: Duplicate entry '1-5' for key 'PRIMARY' on insert unsure of how
body: <p>I am getting the error <code>MysqlError: Duplicate entry '1-5' for key 'PRIMARY'</code> 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.</p>
  
  <p><code>num_rows()</code> 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.</p>
  
  <p>Here is my schema for location_items:</p>
  
  <pre><code>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 |
  </code></pre>
  
  <p>And the code:</p>
  
  <pre><code>//Lock tables involved in sale transaction so we do not have deadlock
  $this-&gt;db-&gt;query('LOCK TABLES '.$this-&gt;db-&gt;dbprefix('customers').' WRITE, '.$this-&gt;db-&gt;dbprefix('receivings').' WRITE, 
  '.$this-&gt;db-&gt;dbprefix('store_accounts').' WRITE, '.$this-&gt;db-&gt;dbprefix('receivings_items').' WRITE, 
  '.$this-&gt;db-&gt;dbprefix('giftcards').' WRITE, '.$this-&gt;db-&gt;dbprefix('location_items').' WRITE, 
  '.$this-&gt;db-&gt;dbprefix('inventory').' WRITE, 
  '.$this-&gt;db-&gt;dbprefix('people').' READ,'.$this-&gt;db-&gt;dbprefix('items').' WRITE
  ,'.$this-&gt;db-&gt;dbprefix('employees_locations').' READ,'.$this-&gt;db-&gt;dbprefix('locations').' READ, '.$this-&gt;db-&gt;dbprefix('items_tier_prices').' READ
  , '.$this-&gt;db-&gt;dbprefix('location_items_tier_prices').' READ, '.$this-&gt;db-&gt;dbprefix('items_taxes').' READ, '.$this-&gt;db-&gt;dbprefix('item_kits').' READ
  , '.$this-&gt;db-&gt;dbprefix('location_item_kits').' READ, '.$this-&gt;db-&gt;dbprefix('item_kit_items').' READ, '.$this-&gt;db-&gt;dbprefix('employees').' READ , '.$this-&gt;db-&gt;dbprefix('item_kits_tier_prices').' READ
  , '.$this-&gt;db-&gt;dbprefix('location_item_kits_tier_prices').' READ, '.$this-&gt;db-&gt;dbprefix('suppliers').' READ, '.$this-&gt;db-&gt;dbprefix('location_items_taxes').' READ
  , '.$this-&gt;db-&gt;dbprefix('location_item_kits_taxes'). ' READ, '.$this-&gt;db-&gt;dbprefix('item_kits_taxes'). ' READ');
  
  
      // other code for inserting data into other tables that are not relevant.
  
      foreach($items as $line=&gt;$item)
      {
          $cur_item_location_info-&gt;quantity = $cur_item_location_info-&gt;quantity !== NULL ? $cur_item_location_info-&gt;quantity : 0;
          $quantity_data=array(
              'quantity'=&gt;$cur_item_location_info-&gt;quantity + $item['quantity'],
              'location_id'=&gt;$this-&gt;Employee-&gt;get_logged_in_employee_current_location_id(),
              'item_id'=&gt;$item['item_id']
  
          );
              $this-&gt;Item_location-&gt;save($quantity_data,$item['item_id']);
      }
      // other code for inserting data into other tables that are not relevant.
  
      $this-&gt;db-&gt;query('UNLOCK TABLES');
  
  
  class Item_location extends CI_Model
  {
      function exists($item_id,$location=false)
      {
          if(!$location)
          {
              $location= $this-&gt;Employee-&gt;get_logged_in_employee_current_location_id();
          }
          $this-&gt;db-&gt;from('location_items');
          $this-&gt;db-&gt;where('item_id',$item_id);
          $this-&gt;db-&gt;where('location_id',$location);
          $query = $this-&gt;db-&gt;get();
  
          return ($query-&gt;num_rows()==1);
      }
  
  
      function save($item_location_data,$item_id=-1,$location_id=false)
      {
          if(!$location_id)
          {
              $location_id= $this-&gt;Employee-&gt;get_logged_in_employee_current_location_id();
          }
  
          if (!$this-&gt;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-&gt;db-&gt;insert('location_items',$item_location_data);
          }
  
          $this-&gt;db-&gt;where('item_id',$item_id);
          $this-&gt;db-&gt;where('location_id',$location_id);
          return $this-&gt;db-&gt;update('location_items',$item_location_data);
  
      }
  }
  
  function get_logged_in_employee_current_location_id()
      {
          if($this-&gt;is_logged_in())
          {
              //If we have a location in the session
              if ($this-&gt;session-&gt;userdata('employee_current_location_id')!==FALSE)
              {
                  return $this-&gt;session-&gt;userdata('employee_current_location_id');
              }
  
              //Return the first location user is authenticated for
              return current($this-&gt;get_authenticated_location_ids($this-&gt;session-&gt;userdata('person_id')));
          }
  
          return FALSE;
      }
  </code></pre>