Jump to content

How can fix SQL errors?


Recommended Posts

Dear,

 

I am a newbie. I tried to upgrade prestashop 1.6.0.14 to 1.6.1.1 by 1-click upgrade.

These errors appeared. Please tell me how to fix them?

 

I installed autions module which paid from addons.prestashop

[WARNING] SQL 1.6.1.0 1062 in INSERT INTO `ps_hook` (`id_hook`, `name`, `title`, `description`, `position`, `live_edit`) VALUES (NULL, 'actionAdminLoginControllerSetMedia', 'Set media on admin login page header', 'This hook is called after adding media to admin login page header', '1', '0'), (NULL, 'actionOrderEdited', 'Order edited', 'This hook is called when an order is edited.', '1', '0'), (NULL, 'displayAdminNavBarBeforeEnd', 'Admin Nav-bar before end', 'Called before the end of the nav-bar.', '1', '0'), (NULL, 'displayAdminAfterHeader', 'Admin after header', 'Hook called just after the header of the backoffice.', '1', '0'), (NULL, 'displayAdminLogin', 'Admin login', 'Hook called just after login of the backoffice.', '1', '0'): Duplicate entry 'actionOrderEdited' for key 'hook_name'
[WARNING] SQL 1.6.1.0 1062 in ALTER IGNORE TABLE `ps_specific_price` ADD UNIQUE KEY `id_product_2` (`id_cart`, `id_product`,`id_shop`,`id_shop_group`,`id_currency`,`id_country`,`id_group`,`id_customer`,`id_product_attribute`,`from_quantity`,`id_specific_price_rule`,`from`,`to`): Duplicate entry '0-1047-0-0-0-0-0-0-0-1-0-0000-00-00 00:00:00-0000-00-00 00:00:00' for key 'id_product_2'
[WARNING] SQL 1.6.1.1 1060 in ALTER TABLE `ps_order_detail` ADD `original_wholesale_price` DECIMAL( 20, 6 ) NOT NULL DEFAULT '0.000000': Duplicate column name 'original_wholesale_price'
[WARNING] SQL 1.6.1.1 1091 in ALTER TABLE `ps_specific_price` DROP KEY `id_product_2`: Can't DROP 'id_product_2'; check that column/key exists
[WARNING] SQL 1.6.1.1 1062 in ALTER IGNORE TABLE `ps_specific_price` ADD UNIQUE KEY `id_product_2` (`id_cart`, `id_product`,`id_shop`,`id_shop_group`,`id_currency`,`id_country`,`id_group`,`id_customer`,`id_product_attribute`,`from_quantity`,`id_specific_price_rule`,`from`,`to`): Duplicate entry '0-1047-0-0-0-0-0-0-0-1-0-0000-00-00 00:00:00-0000-00-00 00:00:00' for key 'id_product_2'
Link to comment
Share on other sites

My impression is that you tried to upgrade more than once and that that causes part of the errors.

 

The main stumbling block seems to be the ps_specific_price table. It says that you have too much too similar discounts for the product with the product_id 1047. So I suggest that you delete those discounts.

  • Like 1
Link to comment
Share on other sites

My impression is that you tried to upgrade more than once and that that causes part of the errors.

 

The main stumbling block seems to be the ps_specific_price table. It says that you have too much too similar discounts for the product with the product_id 1047. So I suggest that you delete those discounts.

 

Dear musicmaster,

 

Thank you for your responsing.

 

I once upgraded ps 1.6.0.14 to 1.6.1.1 

When I enable PS_MODE_DEV true

and PS_DEBUG_PROFILING true

 

These errors appear, I can not login BO or visit FO

 

https://dl.dropboxusercontent.com/u/79130593/problem/bugs.png

 

I googled this error and applied:

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`);

I could visit web, but everytime I click any products (example: product A) this error appear: "No customer reviews for the moment."

 

And then, I login BO and go to that product A -> SEO -> Generate -> Save.

At FO, I click product A, everything is OK.

 

I don't know Why?

Link to comment
Share on other sites

Prestashop changes the indexes of its tables nearly every version. The clause "IGNORE" in the sql commands that accomplish that are supposed to deal with records that don't fit in newly declared unique keys but somehow that doesn't seem to happen with you. Instead the new index was not made.

 

The error in the picture was about the fragment "use index (id_product_2)". That was just a bit of optimization so you could have deleted it. But your solution is good too.

 

I don't understand what you write about "No customer reviews for the moment." That is just a normal statement of the product comment module. So why is that here an error?

  • Like 1
Link to comment
Share on other sites

Prestashop changes the indexes of its tables nearly every version. The clause "IGNORE" in the sql commands that accomplish that are supposed to deal with records that don't fit in newly declared unique keys but somehow that doesn't seem to happen with you. Instead the new index was not made.

 

The error in the picture was about the fragment "use index (id_product_2)". That was just a bit of optimization so you could have deleted it. But your solution is good too.

 

I don't understand what you write about "No customer reviews for the moment." That is just a normal statement of the product comment module. So why is that here an error?

 

Dear musicmaster,

 

I don't know that error too.

If you want to check it, i will send you information.

 

http://thegioidianhac.com/goods/849--flying-peals-2nd-dvd.html

You will see "No customer reviews for the moment." only.

If you login BO and save that product again, you can visit it at FO.

 

And ps 1.6.1.1 is very slow at BO >.<

Link to comment
Share on other sites

Dear musicmaster,

 

Can you teach me how to fix these errors?

[WARNING] SQL 1.6.1.0 1062 in INSERT INTO `ps_hook` (`id_hook`, `name`, `title`, `description`, `position`, `live_edit`) VALUES (NULL, 'actionAdminLoginControllerSetMedia', 'Set media on admin login page header', 'This hook is called after adding media to admin login page header', '1', '0'), (NULL, 'actionOrderEdited', 'Order edited', 'This hook is called when an order is edited.', '1', '0'), (NULL, 'displayAdminNavBarBeforeEnd', 'Admin Nav-bar before end', 'Called before the end of the nav-bar.', '1', '0'), (NULL, 'displayAdminAfterHeader', 'Admin after header', 'Hook called just after the header of the backoffice.', '1', '0'), (NULL, 'displayAdminLogin', 'Admin login', 'Hook called just after login of the backoffice.', '1', '0'): Duplicate entry 'actionOrderEdited' for key 'hook_name'

And

[WARNING] SQL 1.6.1.1 1060 in ALTER TABLE `ps_order_detail` ADD `original_wholesale_price` DECIMAL( 20, 6 ) NOT NULL DEFAULT '0.000000': Duplicate column name 'original_wholesale_price'
Link to comment
Share on other sites

I am not exactly a MySQL expert but I will give it a try.

 

The first warning says that you already have a hook with the name "actionOrderEdited" in your database. You could delete that hook, but it may be that there is already some software using it. The alternative is to edit the file \install\upgrade\sq\1.6.1.0.sql and change the sql command by adding the term "ignore": So you get "insert ignore into"...

 

The second warning is about an attempt to add a field (=column) to a table. It say that field is already there. You could check whether that field has the right specs ("DECIMAL( 20, 6 ) NOT NULL DEFAULT '0.000000'"). My guess would be that there is no problem. If it somehow bothers you you might consider removing the concerning line from the file \install\upgrade\sq\1.6.1.1.sql

  • Like 1
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...