sqlstate "Integrity constraint violation" error when customers place an order

Question

My customers getting the error bellow when they place an order. Orders go through, payments being taken but not inventory. Inventory stays same. Result of this customers order again. But then they are able to order without an error.

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '691-0-1' for key 'PRIMARY' 

I tried to repair tables, clean the cache, full index but this error comes back. This wasn't happening before. I checked catalog_product_entitiy table for entitiy_id 691 it looks normal.

This happens with two different payment method. Paypal and realex. I have checked almost everything.

magento 1.8.1 CE

Answer 1

Please be sure to take the backup of the database before doing this, then run this in phpamyadmin:

 TRUNCATE `sales_flat_quote`; 
ALTER TABLE `sales_flat_quote` AUTO_INCREMENT=1; 
TRUNCATE `sales_flat_quote_address`; 
ALTER TABLE `sales_flat_quote_address` AUTO_INCREMENT=1; 
TRUNCATE `sales_flat_quote_address_item`; 
ALTER TABLE `sales_flat_quote_address_item` AUTO_INCREMENT=1; 
TRUNCATE `sales_flat_quote_item`; 
ALTER TABLE `sales_flat_quote_item` AUTO_INCREMENT=1; 
TRUNCATE `sales_flat_quote_item_option`; 
ALTER TABLE `sales_flat_quote_item_option` AUTO_INCREMENT=1;
TRUNCATE `sales_flat_quote_payment`; 
ALTER TABLE `sales_flat_quote_shipping_rate` AUTO_INCREMENT=1;

TRUNCATE `log_customer`;
ALTER TABLE `log_customer` AUTO_INCREMENT=1;
TRUNCATE `log_quote`;
ALTER TABLE `log_quote` AUTO_INCREMENT=1;
TRUNCATE `log_summary`;
ALTER TABLE `log_summary` AUTO_INCREMENT=1;
TRUNCATE `log_summary_type`;
ALTER TABLE `log_summary_type` AUTO_INCREMENT=1;
TRUNCATE `log_url`;
ALTER TABLE `log_url` AUTO_INCREMENT=1;
TRUNCATE `log_url_info`;
ALTER TABLE `log_url_info` AUTO_INCREMENT=1;
TRUNCATE `log_visitor`;
ALTER TABLE `log_visitor` AUTO_INCREMENT=1;
TRUNCATE `log_visitor_info`;
ALTER TABLE `log_visitor_info` AUTO_INCREMENT=1;
TRUNCATE `report_event`;
ALTER TABLE `report_event` AUTO_INCREMENT=1;