Magento Forum

   
Page 2 of 3
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘3-2’ for key 1
 
lurebeauty
Sr. Member
 
Avatar
Total Posts:  229
Joined:  2009-07-06
 

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?

 
Magento Community Magento Community
Magento Community
Magento Community
 
chiefair
Mentor
 
Avatar
Total Posts:  1839
Joined:  2009-06-04
 

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.

 
Magento Community Magento Community
Magento Community
Magento Community
 
andysaksena
Member
 
Total Posts:  44
Joined:  2010-05-16
 
elfling - 30 September 2010 05: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_quoteAUTO_INCREMENT=1
TRUNCATE `sales_flat_quote_address`; 
ALTER TABLE `sales_flat_quote_addressAUTO_INCREMENT=1
TRUNCATE `sales_flat_quote_address_item`; 
ALTER TABLE `sales_flat_quote_address_itemAUTO_INCREMENT=1
TRUNCATE `sales_flat_quote_item`; 
ALTER TABLE `sales_flat_quote_itemAUTO_INCREMENT=1
TRUNCATE `sales_flat_quote_item_option`; 
ALTER TABLE `sales_flat_quote_item_optionAUTO_INCREMENT=1;
TRUNCATE `sales_flat_quote_payment`; 
ALTER TABLE `sales_flat_quote_shipping_rateAUTO_INCREMENT=1;

TRUNCATE `log_customer`;
ALTER TABLE `log_customerAUTO_INCREMENT=1;
TRUNCATE `log_quote`;
ALTER TABLE `log_quoteAUTO_INCREMENT=1;
TRUNCATE `log_summary`;
ALTER TABLE `log_summaryAUTO_INCREMENT=1;
TRUNCATE `log_summary_type`;
ALTER TABLE `log_summary_typeAUTO_INCREMENT=1;
TRUNCATE `log_url`;
ALTER TABLE `log_urlAUTO_INCREMENT=1;
TRUNCATE `log_url_info`;
ALTER TABLE `log_url_infoAUTO_INCREMENT=1;
TRUNCATE `log_visitor`;
ALTER TABLE `log_visitorAUTO_INCREMENT=1;
TRUNCATE `log_visitor_info`;
ALTER TABLE `log_visitor_infoAUTO_INCREMENT=1;
TRUNCATE `report_event`;
ALTER TABLE `report_eventAUTO_INCREMENT=1;

Thanks this worked for me..

 
Magento Community Magento Community
Magento Community
Magento Community
 
ggchamp
Sr. Member
 
Total Posts:  153
Joined:  2009-04-30
 
elfling - 30 September 2010 05: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_quoteAUTO_INCREMENT=1
TRUNCATE `sales_flat_quote_address`; 
ALTER TABLE `sales_flat_quote_addressAUTO_INCREMENT=1
TRUNCATE `sales_flat_quote_address_item`; 
ALTER TABLE `sales_flat_quote_address_itemAUTO_INCREMENT=1
TRUNCATE `sales_flat_quote_item`; 
ALTER TABLE `sales_flat_quote_itemAUTO_INCREMENT=1
TRUNCATE `sales_flat_quote_item_option`; 
ALTER TABLE `sales_flat_quote_item_optionAUTO_INCREMENT=1;
TRUNCATE `sales_flat_quote_payment`; 
ALTER TABLE `sales_flat_quote_shipping_rateAUTO_INCREMENT=1;

TRUNCATE `log_customer`;
ALTER TABLE `log_customerAUTO_INCREMENT=1;
TRUNCATE `log_quote`;
ALTER TABLE `log_quoteAUTO_INCREMENT=1;
TRUNCATE `log_summary`;
ALTER TABLE `log_summaryAUTO_INCREMENT=1;
TRUNCATE `log_summary_type`;
ALTER TABLE `log_summary_typeAUTO_INCREMENT=1;
TRUNCATE `log_url`;
ALTER TABLE `log_urlAUTO_INCREMENT=1;
TRUNCATE `log_url_info`;
ALTER TABLE `log_url_infoAUTO_INCREMENT=1;
TRUNCATE `log_visitor`;
ALTER TABLE `log_visitorAUTO_INCREMENT=1;
TRUNCATE `log_visitor_info`;
ALTER TABLE `log_visitor_infoAUTO_INCREMENT=1;
TRUNCATE `report_event`;
ALTER TABLE `report_eventAUTO_INCREMENT=1;

Thank you sir smile

 
Magento Community Magento Community
Magento Community
Magento Community
 
elfling
Enthusiast
 
Avatar
Total Posts:  899
Joined:  2008-10-21
 

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 smile

 
Magento Community Magento Community
Magento Community
Magento Community
 
Surf Web Design
Member
 
Avatar
Total Posts:  31
Joined:  2011-07-28
Calgary, Alberta, Canada
 

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

Image Attachments
database-query.jpg
 
Magento Community Magento Community
Magento Community
Magento Community
 
MageClub
Guru
 
Avatar
Total Posts:  382
Joined:  2009-07-25
India
 

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 05: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_quoteAUTO_INCREMENT=1
TRUNCATE `sales_flat_quote_address`; 
ALTER TABLE `sales_flat_quote_addressAUTO_INCREMENT=1
TRUNCATE `sales_flat_quote_address_item`; 
ALTER TABLE `sales_flat_quote_address_itemAUTO_INCREMENT=1
TRUNCATE `sales_flat_quote_item`; 
ALTER TABLE `sales_flat_quote_itemAUTO_INCREMENT=1
TRUNCATE `sales_flat_quote_item_option`; 
ALTER TABLE `sales_flat_quote_item_optionAUTO_INCREMENT=1;
TRUNCATE `sales_flat_quote_payment`; 
ALTER TABLE `sales_flat_quote_shipping_rateAUTO_INCREMENT=1;

TRUNCATE `log_customer`;
ALTER TABLE `log_customerAUTO_INCREMENT=1;
TRUNCATE `log_quote`;
ALTER TABLE `log_quoteAUTO_INCREMENT=1;
TRUNCATE `log_summary`;
ALTER TABLE `log_summaryAUTO_INCREMENT=1;
TRUNCATE `log_summary_type`;
ALTER TABLE `log_summary_typeAUTO_INCREMENT=1;
TRUNCATE `log_url`;
ALTER TABLE `log_urlAUTO_INCREMENT=1;
TRUNCATE `log_url_info`;
ALTER TABLE `log_url_infoAUTO_INCREMENT=1;
TRUNCATE `log_visitor`;
ALTER TABLE `log_visitorAUTO_INCREMENT=1;
TRUNCATE `log_visitor_info`;
ALTER TABLE `log_visitor_infoAUTO_INCREMENT=1;
TRUNCATE `report_event`;
ALTER TABLE `report_eventAUTO_INCREMENT=1;

 
Magento Community Magento Community
Magento Community
Magento Community
 
elfling
Enthusiast
 
Avatar
Total Posts:  899
Joined:  2008-10-21
 

Try adding this to the top of truncate

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKSUNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKSFOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODESQL_MODE='NO_AUTO_VALUE_ON_ZERO';
SET @OLD_SQL_NOTES=@@SQL_NOTESSQL_NOTES=0;

and then change the order of what gets truncated

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

 
Magento Community Magento Community
Magento Community
Magento Community
 
Hadifarnoud
Sr. Member
 
Total Posts:  210
Joined:  2007-10-07
Guildford, Surrey, UK
 

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

 
Magento Community Magento Community
Magento Community
Magento Community
 
d8bhatta
Member
 
Total Posts:  44
Joined:  2009-06-11
 

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.

 
Magento Community Magento Community
Magento Community
Magento Community
 
crane_cio
Jr. Member
 
Avatar
Total Posts:  3
Joined:  2012-02-20
Elkhart, Indiana
 

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.

 
Magento Community Magento Community
Magento Community
Magento Community
 
henriquebisneto
Jr. Member
 
Total Posts:  1
Joined:  2011-02-08
 

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!

 
Magento Community Magento Community
Magento Community
Magento Community
 
klockornu
Jr. Member
 
Total Posts:  10
Joined:  2012-07-11
Sweden
 

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 05: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_quoteAUTO_INCREMENT=1
TRUNCATE `sales_flat_quote_address`; 
ALTER TABLE `sales_flat_quote_addressAUTO_INCREMENT=1
TRUNCATE `sales_flat_quote_address_item`; 
ALTER TABLE `sales_flat_quote_address_itemAUTO_INCREMENT=1
TRUNCATE `sales_flat_quote_item`; 
ALTER TABLE `sales_flat_quote_itemAUTO_INCREMENT=1
TRUNCATE `sales_flat_quote_item_option`; 
ALTER TABLE `sales_flat_quote_item_optionAUTO_INCREMENT=1;
TRUNCATE `sales_flat_quote_payment`; 
ALTER TABLE `sales_flat_quote_shipping_rateAUTO_INCREMENT=1;

TRUNCATE `log_customer`;
ALTER TABLE `log_customerAUTO_INCREMENT=1;
TRUNCATE `log_quote`;
ALTER TABLE `log_quoteAUTO_INCREMENT=1;
TRUNCATE `log_summary`;
ALTER TABLE `log_summaryAUTO_INCREMENT=1;
TRUNCATE `log_summary_type`;
ALTER TABLE `log_summary_typeAUTO_INCREMENT=1;
TRUNCATE `log_url`;
ALTER TABLE `log_urlAUTO_INCREMENT=1;
TRUNCATE `log_url_info`;
ALTER TABLE `log_url_infoAUTO_INCREMENT=1;
TRUNCATE `log_visitor`;
ALTER TABLE `log_visitorAUTO_INCREMENT=1;
TRUNCATE `log_visitor_info`;
ALTER TABLE `log_visitor_infoAUTO_INCREMENT=1;
TRUNCATE `report_event`;
ALTER TABLE `report_eventAUTO_INCREMENT=1;

 
Magento Community Magento Community
Magento Community
Magento Community
 
Brazil_Dist
Jr. Member
 
Avatar
Total Posts:  10
Joined:  2009-06-11
Roche, Switzerland
 

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

 
Magento Community Magento Community
Magento Community
Magento Community
 
JAI S
Jr. Member
 
Total Posts:  13
Joined:  2008-12-15
 

@ andysaksena

Thanks and it worked for me though it give result as:

0 row(s) affected

Execution Time : 0.014 sec
Transfer Time : 0 sec
Total Time : 0.014 sec

It works like charm smile

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
    Back to top
Page 2 of 3