Key 'id_product_2' doesn't exist in table 'ps_specific_price'

[Sterminous]

When i tried to add products i got this error: " An error occurred while creating an object. product ", then i enabled debug mode and when i go to my store it says this error: " Key 'id_product_2' doesn't exist in table 'ps_specific_price' " 

			SELECT *, ( IF (`id_group` = 1, 2, 0) +  IF (`id_country` = 21, 4, 0) +  IF (`id_currency` = 1, 8, 0) +  IF (`id_shop` = 1, 16, 0) +  IF (`id_customer` = 0, 32, 0)) AS `score`
				FROM `ps_specific_price` USE INDEX (id_product_2)
				WHERE `id_product` IN (0, 2)
				AND `id_product_attribute` IN (0, 0)
				AND `id_shop` IN (0, 1)
				AND `id_currency` IN (0, 1)
				AND `id_country` IN (0, 21)
				AND `id_group` IN (0, 1)
				AND `id_customer` IN (0, 0)
				AND
				(
					(`from` = '0000-00-00 00:00:00' OR '2015-07-27 04:30:00' >= `from`)
					AND
					(`to` = '0000-00-00 00:00:00' OR '2015-07-27 04:30:00' <= `to`)
				)
				AND id_cart IN (0, 0)
				AND IF(`from_quantity` > 1, `from_quantity`, 0) <= 1 ORDER BY `id_product_attribute` DESC, `from_quantity` DESC, `id_specific_price_rule` ASC, `score` DESC, `to` DESC, `from` DESC LIMIT 1

How to fix this? I reinstalled it many times, but always the same error.

 

EDIT: I use Prestashop 1.6.1.0

Edited July 27, 2015 by Sterminous (see edit history)

[bellini13]

Your database table 'ps_specific_price' is missing a required index.  You will need to execute the following statement to have it added

ALTER TABLE `ps_specific_price` ADD UNIQUE( `id_specific_price_rule`, `id_product`, `id_shop`, `id_shop_group`, `id_currency`, `id_country`, `id_group`, `id_customer`, `id_product_attribute`, `from_quantity`, `from`, `to`);

[Sterminous]

 

Your database table 'ps_specific_price' is missing a required index.  You will need to execute the following statement to have it added

ALTER TABLE `ps_specific_price` ADD UNIQUE( `id_specific_price_rule`, `id_product`, `id_shop`, `id_shop_group`, `id_currency`, `id_country`, `id_group`, `id_customer`, `id_product_attribute`, `from_quantity`, `from`, `to`);

Did it, but MySQL said error: "MySQL returned an empty result".

[bellini13]

what did you do... exactly.

[Rich Walker]

Hi bellini13

 

I have the same problem. If i run 

 

ALTER TABLE `ps_specific_price` ADD UNIQUE( `id_specific_price_rule`, `id_product`, `id_shop`, `id_shop_group`, `id_currency`, `id_country`, `id_group`, `id_customer`, `id_product_attribute`, `from_quantity`, `from`, `to`);

 

I get 

 

MySQL said: 

#1062 - Duplicate entry '0-492-0-0-0-0-0-6198-298-1-0000-00-00 00:00:00-0000-00-00 00:00:' for key 'id_specific_price_rule_2' 

 

Any help would be appreciated. 

 

Thanks

[Sterminous]

what did you do... exactly.

I went to my phpmyadmin, selected database that i use, pressed the "SQL" tab and entered that command.

[bellini13]

ok, try this instead

 

This will remove the existing index named id_specific_price_rule_2.  I don't know why you have this index, but it should not exist

ALTER TABLE ps_specific_price DROP INDEX id_specific_price_rule_2;

Then execute this statement which will add the index with the correct name id_product_2

ALTER TABLE `ps_specific_price` ADD UNIQUE `id_product_2` ( `id_specific_price_rule`, `id_product`, `id_shop`, `id_shop_group`, `id_currency`, `id_country`, `id_group`, `id_customer`, `id_product_attribute`, `from_quantity`, `from`, `to`);

[Sterminous]

 

ok, try this instead

 

This will remove the existing index named id_specific_price_rule_2.  I don't know why you have this index, but it should not exist

ALTER TABLE ps_specific_price DROP INDEX id_specific_price_rule_2;

Then execute this statement which will add the index with the correct name id_product_2

ALTER TABLE `ps_specific_price` ADD UNIQUE `id_product_2` ( `id_specific_price_rule`, `id_product`, `id_shop`, `id_shop_group`, `id_currency`, `id_country`, `id_group`, `id_customer`, `id_product_attribute`, `from_quantity`, `from`, `to`);

MySQL says this error when i ran the first command: "#1091 - Can't DROP 'id_specific_price_rule_2'; check that column/key exists."

And when i ran second command it says error: "#1061 - Duplicate key name 'id_product_2' ", i checked the table "ps_specific_price" and there is no column called id_product_2 

Still can't add products

[bellini13]

these are not columns, they are indexes.  

[Sterminous]

these are not columns, they are indexes.  

Okey, but still that error :/

[bellini13]

then open up the indexes and delete the indexes that should not exist.  compare it to a clean install of the same Prestashop version

[don@ptg]

Did anyone find a solution to this? I am having exactly the same problem. A little background info. My work has a functioning 1.6.1.0 PrestaShop folder that was installed in a server that needs to be moved to another location.  It was upgraded a month ago and the site functions correctly in its original folder. Once the files and database where transfered to another server per http://doc.prestashop.com/display/PS16/System+Administrator+Guide#SystemAdministratorGuide-MovingPrestaShop I keep getting the Key 'id_product_2' doesn't exist in table 'ps_specific_price' on the new server. The database was imported several times, with the same error each time. I installed at fresh install of the last version of PrestaShop and compared the structure of ps_specific_price to the malfunctioning one and they appear to be the same.

The first attachment is for the site with the error. The second attachment is for the fresh install. The third attachment is the home page. The admin area functions. It has the same error and a white screen though once you try to move to the products page.  I tried the solutions suggested above with exactly the same results as the other members. In my case the problem is still not fixed. The most unusual thing to me is that the database works fine in the old site folder, the problems only show in the new version. The files where moved using ftp. 

 

Thank you for the help!

[bellini13]

it is referring to indexes on this table, try looking under the Structure tab, and there will be an expandable section called Indexes.  View the indexes and confirm that 'id_product_2' is missing.  Then you will need to add it

[ItalianBoutiqueShoes]

Hello, I have the same problem. I did not find INDEXES on my phpmyadmin. Where should I look for it?

[Sterminous]

I got this problem solved, my template had demo store files and those files were messed up. Removed demo store files and did clean prestashop install and everything is working.

[Arun Geek]

I sloved this problem by

 

ALTER TABLE `ps_specific_price` ADD UNIQUE `id_product_2` (`id_specific_price`, `id_specific_price_rule`, `id_product`, `id_shop`, `id_shop_group`, `id_currency`, `id_country`, `id_group`, `id_customer`, `id_product_attribute`, `from_quantity`, `from`, `to`);

 

[ItalianBoutiqueShoes]

Thanks, it fix my error.

[Arun Geek]

you are welcome

[ItalianBoutiqueShoes]

Just one more question: someone know what `id_product_2` is for? Thanks

[don@ptg]

Thanks to all who replied,  I was able to get the database working on the new server without the error using the help from this thread. 

[mdaldoss]

Same for me: Arun Geek geek answer, although I still have to check what exactly did, because I'm on my test server, worked for me.

[leste]

Yes good solution !

problem after update 1.6.1 and also 1.6.1.1

ALTER TABLE `ps_specific_price` ADD UNIQUE `id_product_2` (`id_specific_price`, `id_specific_price_rule`, `id_product`, `id_shop`, `id_shop_group`, `id_currency`, `id_country`, `id_group`, `id_customer`, `id_product_attribute`, `from_quantity`, `from`, `to`);

[duredo]

thanks Arun Geek, my problem solved (y)

[Arun Geek]

I'm glad your problem solved. This was my first answer in Prestashop forum. I'm delighted with response.

[duredo]

but still a mystery for me, where did 'id_product_2' come from after upgrade 1.6.1.0 to 1.6.1.1

[bellini13]

id_product_2 is an index, not a column.  it was added with PS v1.6.1.0