An error occurred while creating object. attribute (Duplicate entry '7-1' for key 'PRIMARY')

[spring00]

Hello. I am using prestashop 1.5.3.1 and I want to add values to the attributes I have set up. For example it allows me to set up 'Colours' but when I try to add the colours e.g. red I get this error message

 

An error occurred while creating object. attribute (Duplicate entry '7-1' for key 'PRIMARY')

 

Please can you help?

[spring00]

Just a quick update. The mystery has deepened as I've just set up another prestashop 1.5.3.1 for another client and this one works perfectly. Same version, same host, same everything. Has anyone else had this problem as I'm at a loss?

[NemoPS]

For some reason the attributes (or any of the related) table doesn't have auto_increment set in the database. Likely ps_attribute_value. Go to phpmyadmin, check that table and set auto_increment for id_attribute_value

[spring00]

Just to give some feedback, I checked the ps_attribute tables for the auto_increment setting for the faulty prestashop installation and the perfect installation and they were exactly the same for all the related tables. I've given up now and deleted the faulty installation and reinstalled. I've had to redo my work but its quicker than trying to figure out this problem. Thanks for your help.

[reptilesun]

I'm having the same problem but already invested over 120 man hours. Anyone figured out the cause?

[spring00]

I am getting really fed up of all the bugs in prestashop 1.5.3. In case this helps anyone else in the end I had to uninstall prestashop (I downloaded a copy of my customised theme first) and I installed another copy. Same version, same softalicious installation and yet this installation is working fine in terms of being able to add attributes and values. I wasted a LOT of time trying to figure this out so I hope this tip helps someone else. The other tip is to uninstall and reinstall any modules which are causing you glitches. What a nightmare. I used to love prestashop but not any more.

[Rogier75]

Same problem here while trying to add values to an attribute;

 

"An error occurred while creating object. attribute (Duplicate entry '4-1' for key 'PRIMARY')"

 

Hope there is another solution apart from a complete reinstall ?

[bigbadtrev]

Same problem, surely this has got to be a simple fix trying to study the database but nothing is jumping out at me. Has anyone found a fix

[bigbadtrev]

Doesnt matter, problem solved, told you it was easy

[Rogier75]

Care to enlighten us on what this "simple fix" entails precisely ?

[bigbadtrev]

within ps_attribute table untick the auto_increment selction and save. go back into the table and select the auto increment selection again and save. go through each table which is associated to the ps_attribute and remove all values within tables. Problem solved

[bigbadtrev]

By the way, is anyone having problems viewing the forum and sometime information pages of prestashop.com within IE, using firefox at the minute but there seems to be a problem using IE8 with prestashop forum etc.

[EuroCasetas]

Hello,

I've solved.

 

You need to join to your BD, to SQL command line and put next text:

 

DROP TABLE IF EXISTS `ps_attribute`;

CREATE TABLE IF NOT EXISTS `ps_attribute` (

`id_attribute` int(10) unsigned NOT NULL auto_increment,

`id_attribute_group` int(10) unsigned NOT NULL,

`color` varchar(32) default NULL,

`position` int(10) unsigned NOT NULL default '0',

PRIMARY KEY (`id_attribute`),

KEY `attribute_group` (`id_attribute_group`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=141 ;

 

 

BR,

EuroCasetas

[Poljotuhren.de]

Same problem. Can someone shed some more light on this? I tried bigbadtrevs way but I do not know how to find all tables and all values, I am at the beginning so no problem to delete all values. What EuroCasetas is suggesting I have no Idea... join to your BD? SQL command line where? Sorry I do not get both ways to the end... Can someone help? Thanks

[sscardefield]

Same problem. Nobody has a straight forward fix? Or can anybody explain EuroCasetas resolution in a little more detail?

[jsylvana]

I just finished troubleshooting a site for this exact issue.

 

The technical problem appears to be a mismatch between the ps_attribute table and the ps_attribute_shop table. Phantom records existed in ps_attribute_shop that appeared to be the result of a failed cascade delete. When records are removed from ps_attribute their corresponding record IS NOT removed from ps_attribute_shop. When new attributes are created this created a referential integrity problem. ps_attribute_shop says there's a ps_attribute that already exists with the newly created ID number (primary key).

 

Properly, if a ps_attribute record is deleted all references to it should cascade. For my brother's site I'm considering imposing a foreign key constraint on ps_attribute_shop that will guarantee this cascade.

 

I can't explain why MySql(innodb) would try to try to reuse keys in the table, perhaps some internal magic to PrestaShop? ps_attribute is set to autoincrement which means the job of assigning ID numbers is done by the database, not prestashop.

 

Anyhow the solution was to remove the records in ps_attribute_shop. Since the site I was working on had only 2 attributes and no values (but somehow 20 attribute_shop records) I was able to simply remove all the attributes and then remove all of the entries in ps_attribute_shop. I don't think it's necessary to wipe those tables though. I think you just want to make sure that all of the values exist in both tables.

 

You could use this statement to inspect the match between them:

 

select * from ps_attribute_shop left join ps_attribute on ps_attribute.id_attribute = ps_attribute_shop.id_attribute

 

This will output all of the attribute_shop entries and then it's corresponding attribute records (if any) or blanks if no attributes exist (bad).

 

To do this I needed the MySql user and password. This installation had been done by a Softaculous installer and those values were auto-magically created. To find them on your installation use FTP to navigate the file system of the shop and they're stored in the file ./config/settings.inc.php

 

Good luck.

Edited May 12, 2013 by jsylvana (see edit history)

[jsylvana]

If you're feeling experimental you can try this to prevent the problem from occuring/recurring.

 

 

ALTER TABLE ps_attribute_shop add CONSTRAINT FOREIGN KEY (`id_attribute`) REFERENCES ps_attribute (`id_attribute`) ON DELETE CASCADE;

 

The way the prestashop is set up out of the box the ps_attribute_shop table looks like this:

 

 

CREATE TABLE `ps_attribute_shop` (

`id_attribute` int(11) unsigned NOT NULL,

`id_shop` int(11) unsigned NOT NULL,

PRIMARY KEY (`id_attribute`,`id_shop`),

KEY `id_shop` (`id_shop`),

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

 

The important thing to note here is that there's no reference between ps_attribute and id_attribute. That's a problem because id_attribute is AT LEAST a foreign key and if it doesn't exist then the record in this table that has an id_attribute can't be meaningful. But it's possible putting this new constraint in place can break something else that relies on PrestaShop to manage the ID values itself.

Edited May 12, 2013 by jsylvana (see edit history)

[jsylvana]

This SQL statement would correct the problem we were having.

 

IF you issue this statement, the records deleted cannot be recovered.

 

But, it should only delete records from ps_attribute_shop that don't exist anyway.

 

 

DELETE FROM ps_attribute_shop WHERE ps_attribute_shop.id_attribute NOT IN (SELECT ps_attribute.id_attribute FROM ps_attribute);

[sscardefield]

Thanks for all that info jsylvana. I am by no means a DB admin, so I just want to verify this. Does this look correct?

 

 

If so, I get a "SELECT doesn't exist" error when I click save.

[jsylvana]

Yes, that command will remove records in ps_attribute_shop with id's that are not in ps_attribute.

 

That's an interesting way of executing it. I would not have thought to do that. I seem to recall the advanced SQL stuff in PrestaShop was a way of bolting in custom report queries which are all read-only select statements.

 

So it's possible that won't work because the delete statement is not a read-only query.

 

I would suggest running the query that examines the contents of the tables first:

 

SELECT * from ps_attribute_shop LEFT JOIN ps_attribute ON ps_attribute.id_attribute = ps_attribute_shop.id_attribute

 

That should clearly show you which records are the problem first AND verify you have the problem I was describing.

[sscardefield]

I switched over to phpmyadmin to run the query. Here is the output:

 

 

I have no idea what that means.

[jsylvana]

Yup. That's the same problem.

 

All those NULLs are Attribute records that exist ONLY in ps_attribute_shop and that's what's causing your problem.

 

The delete statement should take care of that for you.

[sscardefield]

All clean now.

 

 

And I can add attribute values now! Thanks man, really appreciate the help.

[jsylvana]

Glad to help. It looks like a lot of people have been looking for this for a while. Hopefully it'll save some folks some aggravation.

[J.Sahu]

It's very simple to solve if you haven't added many attributes.

First deselect the auto increment of "id_attribute" field in the "ps_attribute" table then you can delete all values of below tables and again select the auto increment of "id_attribute" table.

• ps_attribute
  
• ps_attribute_group
  
• ps_attribute_group_lang
  
• ps_attribute_group_shop
  
• ps_attribute_impact
  
• ps_attribute_lang
  
• ps_attribute_shop
  

Hope this will work for you.

Edited August 21, 2013 by Jnanaranjan Sahu (see edit history)

[Palido]

for me no solution does work - I tried J.Sahus solution and jsylvana's solution still getting this error: Duplicate entry '1-1' for key 'PRIMARY'

INSERT INTO `ps_product_attribute_shop` (`id_product_attribute`, `wholesale_price`, `price`, `ecotax`, `weight`, `unit_price_impact`, `minimal_quantity`, `default_on`, `available_date`, `id_shop`) VALUES ('1', '0', '0', '0', '0', '0', '1', '1', '0000-00-00', '1')

[Ray UK]

Hi sql [spam-filter]'s.

 

I am having a similar problem with my "Loyatly rewards" module ( the native ps one )

 

When a customer tries to convert points into a voucher, this is what happens.

• The vouchers is created
• White Screen

I am thinking the sql fault is causing the module to crash

 

Now when it reaches the white screen and crashes, it does not delete the customers points ( which have just been converted into a voucher ), therefore the customer can create another voucher... and so on.

 

If I manually create a "Cart Rule" I get this error:

 

An error occurred while creating an object. cart_rule (Duplicate entry '743-1' for key 'PRIMARY')

 

So im thinking its a similar problem to the attribute one.

Would you guys know the sql command to "SELECT" them first, and another sql command to "DELETE" the problem tables/fields

 

Cheers

[jsylvana]

I have not looked at the code for this application in a while.  

However, run this query and then post the output and I'll try to help you.  

SELECT * from ps_cart_rule_shop LEFT JOIN ps_cart_rule ON ps_cart_rule.id_cart_rule = ps_cart_rule_shop.id_cart_rule

If you look up thread I explain how to do this there.  Also, since that's a read-only connection we cannot delete errant records this way and this query is a do-no-harm command to run.  

 

[Ray UK]

Thanks JSYLVANA

 

I did read up above and guessed the code was what you have put...

I ran that code but got this:

 

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0008 sec)

 

hmmm

 

There are quite a few cart rules in our prestashop so not sure why its saying empty

[jsylvana]

Try just this then:

SELECT * from ps_cart_rule_shop

[Ray UK]

That is also empty.

 

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0004 sec)

SELECT *
FROM ps_cart_rule_shop
LIMIT 0 , 30

 

I have just browsed that and there is nothing in there at all :/

[Ray UK]

And the other table has

 

Showing rows 0 - 29 (35 total, Query took 0.0010 sec)SELECT *
FROM `ps_cart_rule`
LIMIT 0 , 30

[Ray UK]

I was using the "Customer follow up" module, which used to create lots of codes and delete them when they expire.

I think this may have become a problem when I removed that module... it was around that time anyhow

[jsylvana]

I'm a little surprised that the cart_rule s are not tied to a shop.  That seems to be the design pattern and allows multiple shops to co-exist in a single PrestaShop installation.  

Still, copy out the results from the query into the next message.  We need to look at the data and find out which relationship is out of whack in order to determine how to correct the data.  

SELECT * from ps_cart_rule 

Also, we need to see all 35 records.  

Edited January 6, 2015 by jsylvana (see edit history)

[Ray UK]

Thanks for this by the way... My customers havent been able to use their points for over 1.5 months now due to this

 

I have attached image of sql data because I cant format it correctly for text here

[jsylvana]

select the export button at the bottom of the screen and paste the export file.  A screen shot is not showing me the entire structure.

by way of explanation, the error on primary key is actually on a relationship between two things.  743 is one thing and 1 is the other.  

That 1 is our problem and finding what it's actually referring to is our goal.  

743-1

 

[jsylvana]

I have to get going for the day, kiddo is done with school.  

I'm going out on a limb with a guess at the problem.  After examining the database structure and noting you are a UK operation I'm going to suggest that you also dump out and post the contents of the table

ps_cart_rule_lang

 

It might be quite large in which case the selection

SELECT * FROM ps_cart_rule_lang WHERE id_cart_rule = 743

may tell us what we need to know.  Do you feel lucky?  

[Ray UK]

No worries, and thanks for the help.

Yes im uk so its late here now.

 

Ive dumped the contents of that latest sql query, but its not good news I dont think.

 

|------
|id_cart_rule|id_lang|name
|------
|743|1|£29.99 Credit
 

 

That is the latest Cart Rule that I created today, so I just tried created another few and the code just increases..

So its now

 

An error occurred while creating an object. cart_rule (Duplicate entry '745-1' for key 'PRIMARY')

 

 

Cheers and hoping you can help more tomorrow

Edited January 6, 2015 by MerseyRay (see edit history)

[Ray UK]

Damn, thought I had cracked it then

 

I went into ps_cart_rule_lang and there were over 1000 lines all named "We Miss You" ( well past the 745 that the error shows )

So I deleted every line after 745 and manually create a rule in the back office. It worked 1st time with no error.

 

So I tried enabling the "Loyalty and Rewards" module again, clicked "Convert".

Same thing. It shows a "Page not found", but when I look in the customer vouchers, they have been created and the points are still there to use again

 

Another thing I noticed when the module was working

The vouchers created should have been called "Loyalty Reward" as I specified in the B.O

Instead, they were being called "We Miss You", which is what the "Customer Follow Up" used to create ?

[Ray UK]

Ok, so ive copied the complete site and database to debug it

 

Ive turned debug mode on and get the following error when clicking the "Convert Button"

 

 

So I went back into the modules B.O and changed the name slightly and clicked save. It shows this error

 

"You must choose at least one category for voucher's action"

 

My categories are already chosen though, and if I reload the module B.O after this error, the categories are still selected.

[Ray UK]

Ok, so I have deleted the module v 1.10 via FTP and replaced it with v1.8, and it seems to be working ?

[jsylvana]

Oh good.  That's progress.  
 

I freely admit I'm a software engineer, not a Presta Shop expert or consultant.  

What I see going on is orphaned language records.  There are no database constraints in place that would prevent this so when records are deleted in an entity table they should also remove the join records in these dependency tables.  But they don't.  

One way around this is to put into place constraints that cause these records to delete themselves in a cascade.  It's pretty simple to do but I'm reluctant to recommend anything that works around the code because I can't actually support it in any real sense.  

It sounds like this module was buggy and some of that has been addressed.  

Good luck.   

I make no specific representations about this but were it my shop I would try this: 

ALTER TABLE ps_cart_rule_lang add CONSTRAINT FOREIGN KEY (`id_cart_rule`) REFERENCES ps_cart_rule (`id_cart_rule`) ON DELETE CASCADE;

 

What this does is set an explicit relationship between cart_rule and cart_rule_lang along with a rule that says if a cart_rule is deleted then delete it's records in cart_rule_lang.   In theory you would want to go the other way too, if a language is deleted then delete all of the cart_rule_lang entries that match.  But the problem is coming from adding and deleting cart_rule entries.  

This should be safe to do because IF a cart_rule record is deleted then the meet-up between cart_rule and lang(uage) no longer makes any sense and can be removed since there's no cart_rule to refer to any longer.  

 

[MaXi32]

The problem is because you install prestashop without example data. Prestashop will automatically delete (in a dirty way) all the content of the products, default couriers and many more from database during the installation and left the auto increment number messy. That's why you have the problem. If you install prestashop with example data, there will be no problem. This problem normally occurs when you install prestashop through 1 single click from your hosting provider.

Edited May 27, 2015 by arafatx (see edit history)