How to reproduce
* create some orders that need confirmation
* delete these orders
* do something that resets mysql serials, maybe restore db from backup

Result:
* On new order pdo exception and wsod like
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '5' for key 1: INSERT INTO {commerce_order_confirm} (order_id, confirmation_code) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1); Array ( [:db_insert_placeholder_0] => 5 [:db_insert_placeholder_1] => AFbui1edQ6CO ) in drupal_write_record() (Zeile 7106 von .../includes/common.inc).

Analysis:
* Commerce order confirm does not delete its entries on commerce_order delete - it should
* Commerce order confirm does not overwrite entries on insert - it should just to be safe

Fix proposal
* implement hook_entity_delete
* use $primary_keys parameter of drupal_write_record

Comments

Jooblay.net’s picture

Jooblay.net commented

@axel.rutz thanks and great work. Yes we are having this on a host of our sites. Same steps seem to throw this error. It seems clearing the cache helps on a temp basis. More testing is needed on this though.

We receive this error when adding any product to your cart.

On further review of this issue clearing cache with drush cache-clear seems to have clean up this for us. It also seems to only happen upon installing the database from dev to live.