Magento

This did not work for me, im still get these errors in the admin side when saving a product.  this just started happening now.

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘7831-0-1-187’ for key ‘PRIMARY’

can someone help?

For fixes to work, they actually have to pertain to the operation you are working with. The original poster was dealing with a CSV import issue which can be fixed with the suggested operations in this thread.

Your Magento issue has to do with two different products attempting to share the same key which is discussed in this thread.

elfling - 30 September 2010 06:34 AM

If you get stuck with a problem like this in the future. Open up phpmyadmin and run this query. It will clean out and fix everything.

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;

Thanks this worked for me..

elfling - 30 September 2010 06:34 AM

If you get stuck with a problem like this in the future. Open up phpmyadmin and run this query. It will clean out and fix everything.

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;

Thank you sir

Just make sure when executing that query, that in newer versions of Magento, you clear out the new sales quote tables. Some may get missed and then you will have a new SQL problem

elfling - 03 February 2012 08:43 PM

Just make sure when executing that query, that in newer versions of Magento, you clear out the new sales quote tables. Some may get missed and then you will have a new SQL problem

Would you mind pointing out which tables you’re referring to?  I’m using 1.6.2.0.  There are so many possible mysteries with magento error messages that I don’t want to do the wrong thing by blindly copying and pasting this query while omitting something else, or by trying to cover these others sales tables but not getting the right ones (you weren’t specific enough in your tip, unfortunately).

I’m not sure why any of these Sales related tables would apply to an import problem, but mine started when I tried to import additional images to some new products that I had freshly imported successfully just previously.  The first file I imported had no image-related columns, on purpose, because I wanted to try to organize them into csv files separately (Magento is a PITA in this regard, as you only have one “image” column and thus have to upload multiple csv’s if products contain more than one). 

For this image-only import, every time I tried using the import profile, it would just hang at 0/52 products (the import popup window doesn’t complete, although the first two products would indeed get the new image—I had the number of products for importing at once set to 3, so maybe there is something there.. but would be nice if someone could confirm), so I tried the other import function (not the default profiles), and now I get:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘1-1335398400-0-1-0-299-0’ for key ‘4EF1A09B7972968AC381B86C6B948A5E’

If I create a simple csv with just those first two products with a sku column and an image column, I get that error, but if I use a different product sku and different image, there is no problem.  Seems like whatever caused the import profile to crap out the first time caused those two products to be afflicted with some kind of disease.  I would like to know why it happened in the first place and how to fix it so that I can easily add images without having to go through the back end.  I just don’t get it, to my knowledge I haven’t done anything wrong, but Magento keeps throwing me curve balls.  Shouldn’t the software be smarter than this?

Tell me this, would using the import profile with a csv that has an error in formatting (due to me forgetting to tell Calc to convert all cells to Text before saving) cause this problem?  What in plain english is the problem anyway?  Thanks....

When I run that query it says:
You have to choose at least one column to display

Attached is a screen shot

Using Magento 1.7

Click thumbnail to see full-size image

Ran this and got this,

SQL query:

TRUNCATE `mage_sales_flat_quote` ;

MySQL said:

#1701 - Cannot truncate a table referenced in a foreign key constraint (`urbanbod_newsite`.`mage_sales_flat_quote_address`, CONSTRAINT `FK_46703692EAEE21FC1FA3ACE31F8FF776` FOREIGN KEY (`quote_id`) REFERENCES `urbanbod_newsite`.`mage_sales_flat_quote` (`entity_id`)

elfling - 30 September 2010 06:34 AM

If you get stuck with a problem like this in the future. Open up phpmyadmin and run this query. It will clean out and fix everything.

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;

Try adding this to the top of truncate

and then change the order of what gets truncated

So do
address items
address
payments
shipping
item
item options
and then quote

can anyone explain to me why Magento database design is so bad? is there anything we can do? like adding index

Actually I would like to explain the problem:

Order Increment ID of sales_flat_order table is not same with the increment_last_id
of eav_entity_store table which leads an error saying duplicate increment id. eav_entity_store table is holding increment_id less than increment_id of sales_flat_order table. so new id it tries to generate is already used.

Solution:

copy Mage_Eav_Model_Resource_Entity_Store file into local Mage

change function like :

public function loadByEntityStore(Mage_Core_Model_Abstract $object, $entityTypeId, $storeId)
{
$adapter = $this->_getWriteAdapter();
$bind = array(
‘:entity_type_id’ => $entityTypeId,
‘:store_id’ => $storeId
);
$select = $adapter->select()
->from($this->getMainTable())
->forUpdate(true)
->where(’entity_type_id = :entity_type_id’)
->where(’store_id = :store_id’);

$data = $adapter->fetchRow($select, $bind);

if (!$data) {
return false;
}

/**
* CUSTOM CHANGE STARTS
* $entityTypeId = 5 reprensent sales order entity, check in your DB which ID Is hold by sales order model/entity and replace that in following.
*/
if($entityTypeId == 5) {
$orderSelect = $adapter->select()
->from(’sales_flat_order’)
->forUpdate(false)
->order(’entity_id DESC’)
->limit(’1’);
$orderData = $adapter->fetchRow($orderSelect);

if(isset($orderData[’increment_id’]) && $data[’increment_last_id’] != $orderData[’increment_id’]) {
$data[’increment_last_id’] = $orderData[’increment_id’] ;
}
}
/** CUSTOM CHANGE ENDS **/

$object->setData($data);

$this->_afterLoad($object);

return true;
}

I hope this helps.

We are getting this same problem trying to update pricing on some products from the CSV, prices seem to update but we still get this error.  I am forwarding this thread to our developer to see if this helps.

Consegui limpando as tabelas de Log_ direto do banco de dados

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 ;

Thanks!

Thank you! After a few hours surfing spirit around and look for problems and re-installations without success. But your suggestion went as directly as the last resort. Otherwise, only to switch back to another system. Thank you again. This work for me to.

elfling - 30 September 2010 06:34 AM

If you get stuck with a problem like this in the future. Open up phpmyadmin and run this query. It will clean out and fix everything.

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;

thank’a a LOT!!!!!! you make my DAY!