Database 'gone away' issue after upgrade

[cablesimple]

Hi, I just recently upgraded from 1.3.7 to 1.3.8a. I have one super category that has 170,000+ products under it in various subcategories. When I browsed to that category I noticed after the listed subcategories was this error message:

2006 MySQL server has gone away
in:
[select distinct p.products_id, p.products_image, pd.products_name, p.master_categories_id from (products p left join specials s on p.products_id = s.products_id left join products_description pd on p.products_id = pd.products_id ) where p.products_id = s.products_id and p.products_id = pd.products_id and p.products_status = '1' and s.status = '1' and pd.language_id = '1' and p.products_id in (1, 6, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 26, 31, 33, 35, 36, 37, 39, 40, 42, 43, 44, 45, 48, 50, 52, 65, 68, 69, 70, 71, 73, 76, 78, 79, 81, 82, 87, 91, 92, 93, 383, 387, 394, 992, 1410, 1411, 1584, 1644, 1664, 1770, 1772, 1785, 1832, 1833, 2124, 2169, 2171, 2172, 2173, 2174, 2176, 2177, 2182, 2184, 2185, 2200, 2204, 2208, 2209, 2210, ....

about 150 more pages of products_ids and then finishes up with:

134267, 134268, 134269, 134270, 134271, 134272, 134273, 134274, 134275, 134276, 149240)]
2006 MySQL server has gone away
in:
[select count(*) as total from sessions where sesskey = 'vh1niadfcneud7ssipb58c88l7']

Everything still works fine, except of course the actual feature that was intended to be displayed where the error message is.

So I was poking around and determined it was this that was causing it: "Show Special Products on Main Page - Category with SubCategories" in Configuration->Index List. I turn that off and the problem is gone.

I don't personally need it at the moment, just thought I'd mention that this happened to me for *very* large super categories.

[Website Rob]

By definition, any Store with 170,000+ products should not be on a Shared Server as it is too large.

The error msg. "
2006 MySQL server has gone away" means the Server resources were overloaded from the mySQL database connections -- in essence a busy signal -- and had to wait until there was enough resources to continue.

Presuming you have a Dedicated Server you should have someone optimize mySQL for you.

[cablesimple]

Thanks Rob, it seems I should have provided a bit more background. This isn't a dedicated database server, it does run IIS as well. I've been continually adjusting the mySQL settings throughout the few months I've been developing the site. I have gotten it to run quite quickly actually (with the exception of the admin section, have to do some work on that).

I spent a bit more time looking into this issue while responding to your post. I saved the query to a file and found it to be 1.25mb. With the help of http://dev.mysql.com/doc/refman/5.0/en/gone-away.html I determined that the mysql setting max_allowed_packet could be set to 2m and would fix this problem.

[Website Rob]

Good info. Thanks for the update and sharing the link.