[Solved] [PrestaShopDatabaseException] MySQL server has gone away after moving to VPS

[J.Sahu]

Hello, I've recently migrated a shop from shared server to VPS. Copied all files to the new server and imported the database. Completed the initial set ups. Now the homepage of the site is loading, but all other pages showing database exception. It is showing a long query. Can someone suggest me something. My mysql connect timeout is 60 secs. The query is so long that posting here freezes the browser. I'm posting an exrept of that sql query. Can someone suggest me something over this ?

SELECT product_shop.id_product, MAX(product_attribute_shop.id_product_attribute) id_product_attribute
					FROM `awfps_product` p
					 INNER JOIN awfps_product_shop product_shop
		ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
					LEFT JOIN  `awfps_product_attribute` pa ON (product_shop.id_product = pa.id_product)
					 LEFT JOIN awfps_product_attribute_shop product_attribute_shop
		ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop = 1 AND product_attribute_shop.default_on = 1)
					WHERE product_shop.`active` = 1
						 AND (( product_shop.`id_product` = 38) OR( product_shop.`id_product` = 40) OR( product_shop.`id_product` = 41)................all products
						AND p.`id_product` IN (
							SELECT cp.`id_product`
							FROM `awfps_category_group` cg
							LEFT JOIN `awfps_category_product` cp ON (cp.`id_category` = cg.`id_category`)
							WHERE cg.`id_group` IN (3)
						)
					 AND product_shop.`visibility` IN ("both", "catalog")
					GROUP BY product_shop.id_product
					ORDER BY RAND() LIMIT 1

Edited March 19, 2014 by J.Sahu (see edit history)

[J.Sahu]

I got the solution. I searched for the bug in debugging tool and found some clues. The error was for special blocks module. When you have a lot of products in the special category or in other words if you have applied catalog price discount to a lot of products then the special block module retrieves all products with the same category and chooses a particular to show in the special block. Disabled it and it's working. 

[dalibro]

Had an issue like this with one of my customers.

I've changed max_allowed_packet in my.cnf.

 

Solution: Ask your host to change "max_allowed_packet" to a higher value.

[Edvinas]

On 8/26/2014 at 11:15 PM, dalibro said:

Had an issue like this with one of my customers.

I've changed max_allowed_packet in my.cnf.

 

Solution: Ask your host to change "max_allowed_packet" to a higher value.

What value? 

max_allowed_packet=1 ?

max_allowed_packet=500M ?

max_allowed_packet=16777216 ?

[Nickz]

you don't expect an answer do You?

Posted August 26, 2014

Edited March 25, 2021 by Nickz
? missing (see edit history)

[Edvinas]

of course not 😄  just tiered searching for solution about this

[Nickz]

12 minutes ago, Edvinas said:

of course not 😄  just tiered searching for solution about this

Do host on a VPS?

Which distro are you using?

[Pramila Shivane]

Hi guys, i am developing first time prestashop in my server enviroment and i hit on a problem. how to slove it please help me

[Nickz]

22 hours ago, Pramila Shivane said:

in my server enviroment

Please state specs of the Server.

What were you trying to accomplish?