editing products causing duplicate key error (mysql)

1. Create a product, enter a price and any other required fields and save it.
2. Edit the product you just created and change the title (or anything not related to the ec_product table)
3. Save it

The following error is displayed

Duplicate entry '399' for key 'vid' query: INSERT INTO ec_product (nid, vid, ptype, sku, price, hide_cart_link) VALUES (399, 399, 'tangible', '', 22.00, 0) in /home/me/drupal-6.14/includes/common.inc on line 3468.

I tracked this down to how ec_product_save works in ec_product.module

Because ec_product_save is called from ec_product_nodeapi for both "insert" AND "update" ops, there is logic in ec_product_save which tries to be smart about when to make an update or when to make an insert. This logic doesn't quite work though.

Basically, it first tries an update and then checks for db_affected_rows from the update statement. This in turn will make a call to mysql_affected_rows() which, because of how mysql works, will return 0 if you dont actually change any records (even though you *did* fire an update statement off). So ec_product then tries an *insert* because update didn't "change" anything.

This may only be an issue if you're using MySQL, i don't know how db_affected_rows works with other databases.

It's this final insert which is causing the warning because of duplicate keys.
You'll find that if you change something related to the table ec_product (SKU, price) that you don't get the error because the affected rows is then 1 and there is no insert fired.