MysqlError: Duplicate entry '1-5' for key 'PRIMARY' on insert unsure of how

Question

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() 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.

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 |

And the code:

//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;
    }

Answer 1

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.

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

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: 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));

(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.)

Answer 2

Looks like a race condition. What likely happened is to roughly simultaneous calls to:

save($data,5);

both get to the exists check at the same time and see that there is no existing entry. Both then try to insert and the fastest gun wins.

Answer 3

You are not going to get a solution so long as the following conditions exist:

1. You cannot reproduce this problem yourself.
2. You do not share your source code and database for someone else to attempt to replicate.

I am not asking you to share your full source code. Rather, I am saying this to temper your expectations.

That being said, duplicates can exist for numerous reasons. It would help your question if you provided your version, but I did find one reason that could be a cause: Memory too low - could be reproducible if you lower your memory or put a high strain on your system. If you've had a hard time reproducing it, memory could well be why as you may not be trying to simulate that.

Other things to consider:

• You may be wasting your time trying to duplicate something that just will not be duplicated.
• If you are concerned you will experience this issue again, you should really consider logging. That can help you to track down the query which caused the issue. I would advise that you not have logging in a production environment and only in development, because it will probably lead to performance penalties that could well be significant. If this is a one-off issue you may never see it again, but it doesn't hurt to be prepared and armed with more information if the issue appears again.

Ultimately, debugging requires the ability to reproduce the error. A bug is part of a computer program, which means there are certain situations and environments in which this will occur, which can be reproduced. When you have no idea how or why a bug was caused there is nowhere to work back from. It is helpful to look to auxiliary concerns as the potential source of your issue by exploring bug reports, etc. If that fails, implement tools like logging that give you more information. This is the only way you will be able to find the root cause of this issue, or get any more specific insight from the SO community on how to do so.

Answer 4

I suspect that the problem could be related with the cache of where statements. This suspect comes from this stackoverflow question.

Basically I think it could happen that:

- in one cycle this code is executed at the end of save method:
     $this->db->where('item_id',$item_id);
     $this->db->where('location_id',$location_id);
     return $this->db->update('location_items',$item_location_data);

  - in the subsequent cycle this code is executed in the exists method:
     $this->db->where('item_id',$item_id);
     $this->db->where('location_id',$location_id);
     return $this->db->update('location_items',$item_location_data);

When executing the "exists" code the cache may still contain the where clauses of the previous statement and the new one (different) will be added. This way the result will be empty and it seems that the row it is not in the table.

Try to use $this->db->flush_cache(); after the update in the save method.

Also try to use echo $this->db->last_query(); to see what is trying to do in the exists query.

Answer 5

may be ' phppos_location_items ' table is exist in past and a delete statement is executed over this table Delete from phppos_location_items; in this case primary key column not accept previous values if you truncate the table then all previous record will be removed

Answer 6

Sorry its slightly long for a comment ..

What submits the form ? I assume its a button somewhere on a Page, When I have had a similar error its been due to a user double clicking on a button and 2 requests being sent, in a very close time to each other. This caused a check similar to yours to have this situatuion

Request 1     Check      Insert
Request 2           Check      Insert 

As Request 2 was the last request (because the second click took priority) the error was shown though the first request completed all of the work.

i use the code

$("form").submit(function() {
    $(this).submit(function() {
        return false;
    });
    return true;
});

from this question

How to prevent form from submitting multiple times from client side?

Answer 7

Try to use

$insert =  $this->db->insert('location_items',$item_location_data);
if($insert)
{
$this->db->reset();
//OR
try $this->db->_reset_write(); to flush all traces of the query
return $insert;
}

Answer 8

Solution 1:

Duplicate entry states that you have one more row which has a primary key same as that of some other previous row. You can ignore this by statement

INSERT IGNORE INTO ..(rest is same, just add ignore)..

Solution 2:

If you wanna overwrite previous row with new one then you need to follow this query:

INSERT INTO TABLE (f1,f2) VALUES ('f1','f2') ON DUPLICATE KEY UPDATE f1='f1',f2='f2'

Solution: 3

Change your primary key by following these queries:

Create new field for primary key:

ALTER TABLE tablename ADD new_primary_key BIGINT NOT NULL FIRST;

Drop existing primary key:

ALTER TABLE tablename DROP PRIMARY KEY

Now make the new field created earlier as primary key with auto increment

ALTER TABLE tablename MODIFY new_primary_key BIGINT AUTO_INCREMENT PRIMARY KEY

(this will not affect other queries in the code, you can keep them as it is and just add LIMIT 1 in select statements)