7.x-1.x-dev (2011-Dec-12)

I get the follow error message when adding to the cart a product that contains a product option. It would appear that when I'm logged in the item is added to the cart without the product option, but If I'm not logged in the product is not added to the cart at all.

Products that have no product options are added to the cart without error.

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'commerce_option-1-0-0-und' for key 'PRIMARY': INSERT INTO {field_data_field_size} (entity_type, entity_id, revision_id, bundle, delta, language, field_size_value) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6); Array ( [:db_insert_placeholder_0] => commerce_option [:db_insert_placeholder_1] => 1 [:db_insert_placeholder_2] => 1 [:db_insert_placeholder_3] => bangle_size [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => und [:db_insert_placeholder_6] => Medium ) in field_sql_storage_field_storage_write() (line 448 of F:\xampp\htdocs\cabbagewhite.co.uk\modules\field\modules\field_sql_storage\field_sql_storage.module).

I've had this working but when I restored the site from a 'working' backup the error did occur as I added more items to the cart.

Any help would be greatly appreciated.

T

CommentFileSizeAuthor
#12 commerce_option.module-fix_PDOException_duplicate_entry-1475592.patch763 bytesk-l

Comments

Tezza’s picture

Tezza commented

I deleted the two option sets I had in place and created two more and have not had any further issues thus far.

Initially I had a 'size' field (select box) in one set and the same 'size' field and a 'personalisation' field in the other set (both select boxes).

I'm hoping that was the issue.

I have to say though, configuring the Product Option module has been just about the most challenging thing I've ever tackled in Drupal. One user says he spent two hours trying to figure it out... i say 'Is that all?' :)

If I thought I'd got it bang on this time I would happily contribute to the documentation - no offence to the developer, I do like this module and I'm very grateful for it. However, even at this stage I'm not so sure I did it right. Time will tell, and a good stress test in the near future.

Oh happy days :)

olafkarsten’s picture

olafkarsten commented

no offence to the developer

- Of course not, everyone knows that the documentation is rather poor in all the product enhancement modules. But it doesn't help just to say this countless times, it simply needs someone who makes it better.

SO, it's fine, if you are the "Somebody" :D.

Tezza’s picture

Tezza commented

Fair comment.

I did start to write a walkthrough, expecting it to help me understand the module a little better. For my own part I've never made an issue of documentation before, but on this occasion I really did struggle.

When my current project reaches completion I'm going to take some time to finish what i started. I'll likely never contribute code so I'll attempt to give something back on the doc front.

kevinsiji’s picture

kevinsiji commented

The issue is something related to cache. Just clearing Drupal cache fixed this for me.

tajindersingh’s picture

tajindersingh commented

It seems, this is the issue:
https://drupal.org/node/1886140

tajindersingh’s picture

tajindersingh commented
Component: Miscellaneous » Code
Category: support » bug

Updating priority as it makes impossible to let customer buy products in case it is the reason of products not getting added to cart as it results in PDOException:
https://drupal.org/node/1475592

It's ok till the site is in development, but on production missing orders due to this makes the module not usable.

Trying to find some solution to this.

jawi’s picture

jawi commented

Same issue here. After updating the module no orders can be perchased!

The issue seems to solve after manualy clearing the cache tables in the database - with in particular cache_field.

Any way to fix this perminent?

junetellain626’s picture

junetellain626 commented

Also encountered this error.
Products with options results to a PDO exception error when adding to cart, while products which don't have options can be added just fine.
Unfortunately, clearing the cache_field table did not solve it for me.
Any other tables that needs to be truncated?

FFFFFFFab’s picture

FFFFFFFab commented

Hi, I had the same issue. It seems that truncate the table of the field of my options set resolves the problem... It seems that when I deleted all the orders and carts of my development instance, the option values added and referenced by the options referenced by the line items of the orders during their creation, are not deleted. Then the "option_id" field violates constraint integrity when I created new cart. But I'm not sure of it, I was not be able to reproduce the issue after that...

anybody’s picture

anybody
Primary language German
Location Porta Westfalica
commented

I can confirm the issue. We've just had the same with an anonymous order. This is truely critical.

marcus178’s picture

marcus178 commented

Has there been any progress on this, or is there likely to be any?

k-l’s picture

k-l commented
StatusFileSize
new commerce_option.module-fix_PDOException_duplicate_entry-1475592.patch763 bytes

Seems that using entity_delete() to remove commerce_option instead of just deleting commerce_option db record fixes this error.

tajindersingh’s picture

tajindersingh commented

@KL
Thanks it seems worked.
Will update if continued to work.

Thanks again...

blackcoat’s picture

blackcoat commented

I Tried using patch from KL, and it seems it works...but it doesn't. I'm still getting error:

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'commerce_option-10-0-0-und' for key 'PRIMARY': INSERT INTO....

I'm using contrib. module commerce pricing attributes. I'm getting this error all the time if I delete product through commerce display manager UI, and if I add to cart product with price options. If I add to cart the product without price options, it works well..How bizarre, how bizarre..

anybody’s picture

anybody
Primary language German
Location Porta Westfalica
commented

I just had the same problem again, is it possible to reproduce this to find the true reason? I could not yet find out how to reproduce. This is still critical :(

blackcoat’s picture

blackcoat commented

Are you using commerce Kickstart or commerce installation? Does it happen on both installations? It's a real pain in the neck!

anybody’s picture

anybody
Primary language German
Location Porta Westfalica
commented

Normal Commerce Installation.
In our case it seems as if the field values of the option fields are created before the base table value (option) is created or the base table value is rolled back / deleted but the associated field values are not. Does that help?

blackcoat’s picture

blackcoat commented

I think you pointed out in the right direction. I've asked for help on irc channels, but for time being there is no answer. I don't know how to solve this big issue. I stopped one project because of this issue.

tajindersingh’s picture

tajindersingh commented

#12 working fine for me till now.

To reproduce, simply place some test orders then delete them. The options selected for products while placing order still stay in tables. When we try to place new orders after deleting old orders, primary keys for options conflict for new orders as same exists already.

Solution is to manually delete the values on options tables in case you are not using patch #12 before deleting orders.

khumbu’s picture

khumbu commented

Had the same issue, applied patch #12 did not work at first, manually cleaned out the options tables, still the problem persisted.
Solved it by also cleaning out the revisions options tables.

Everything seems ok now.

Greets,

M.

-Mania-’s picture

-Mania- commented

Could someone please specify which tables are you truncating to make it work manually?

Marvin Daugherty’s picture

Marvin Daugherty commented

I would also deeply appreciate an explanation of how to clean out the tables and precisely which tables need cleaning.

lanceh1412’s picture

lanceh1412 commented

In the error message you will see the name of the table e.g INSERT INTO {field_data_field_size} . It is the option set reference field that stores the actual choices made.

The way this module manages the options is as follows:
When a product is added to the cart a row is written to the commerce_option table and given a unique key (option_id). This row links the option to the line item id and product id. The actual choices are stored in the option set fields (in this example field_data_field_size) and linked by entity id to the commerce_option row (option_id). When a line item was deleted the row in commerce_option was being deleted but the corresponding rows in the choices tables were not being removed. If you look in the option set fields you will see one or more orphans i.e rows with an entity id that doesn't exist in the commerce_option table. These are what need to be removed.

HTH.

  • 14ec628 committed on 7.x-1.x 
    Issue #1475592 by KL: Use Entity API for deleting referenced data...
Anonymous’s picture

Anonymous (not verified) commented
Status: Active » Closed (fixed)

I've committed this patch, although it does nothing to delete the data in the field and revision tables it is always better to use the intended function for deleting of entities rather than a plain old db_delete().

I will work on deleting the field data next, see this issue: https://www.drupal.org/node/1886140

Marvin Daugherty’s picture

Marvin Daugherty commented

I appreciate the time you took to write the tutorial in #23. It really helped.

leducdubleuet’s picture

leducdubleuet commented
Status: Closed (fixed) » Patch (to be ported)

I can confirm that the patch seems to works ok but it did not get into the current 7.x-1.x-dev so I am re-opening this issue.

Thank you all for your time.

mehrdad201’s picture

mehrdad201 commented

Thanks a lot
Patch #12 worked. but don't forget before applying patch, clear tables "field_data_your_option_field" and clear "field_revision_your_option_filed" of deleted line items .

tarazingo’s picture

tarazingo commented

Thank you very much!
Patch #12 worked.
Read carefully #23 before apply patch!